reclaim "unused space" from a table?

We have a table that is showing a large amount of "reserved" space:
Rows: 4095513
Reserved 11255328 KB
Data: 1176296 KB
Index_size: 531240 KB
unused: 9547792 KB
(found wiht sp_spaceused)
We have only simple types in this table... no ntext or blobs or
anything.
Just int, smalldatetime, varchar(50) etc. (only 10 fields)
So our data and indices come to about 1.6 GB, but "unused" space is 9.1
GB.
We are running out of drive space...why would it reserve so much space
for this table?"cmay" <cmay@.walshgroup.com> wrote in message
news:1139343553.343130.288690@.g43g2000cwa.googlegroups.com...
> We have a table that is showing a large amount of "reserved" space:
> Rows: 4095513
> Reserved 11255328 KB
> Data: 1176296 KB
> Index_size: 531240 KB
> unused: 9547792 KB
> (found wiht sp_spaceused)
> We have only simple types in this table... no ntext or blobs or
> anything.
> Just int, smalldatetime, varchar(50) etc. (only 10 fields)
> So our data and indices come to about 1.6 GB, but "unused" space is 9.1
> GB.
> We are running out of drive space...why would it reserve so much space
> for this table?
>
What did you specify when you created the database? Take a look at DBCC
SHRINKFILE.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick,
The DB was created like 10 years ago, so I have no idea what happened
back then.
The DB is set to auto grow by 10%. From what I have read shrinking the
DB won't solve this problem. The DB is 15 GB with just about all of it
being considered "used" (I could get back a couple hundred MB by
shrinking the DB), but the 9GB of space being eaten up by this one
table is what is really painful.
I tried running DBCC CleanTable but that didn't help either.
I'm going to try to insert all the data into a new table and then drop
the old one, but that isn't really ideal.
If there are any other things I can try I would love to give it a go.|||Can you try rebuilding the clustered index? That will automatically move all
the data to new space.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cmay" <cmay@.walshgroup.com> wrote in message
news:1139354564.333651.258690@.g14g2000cwa.googlegroups.com...
> Rick,
> The DB was created like 10 years ago, so I have no idea what happened
> back then.
> The DB is set to auto grow by 10%. From what I have read shrinking the
> DB won't solve this problem. The DB is 15 GB with just about all of it
> being considered "used" (I could get back a couple hundred MB by
> shrinking the DB), but the 9GB of space being eaten up by this one
> table is what is really painful.
> I tried running DBCC CleanTable but that didn't help either.
> I'm going to try to insert all the data into a new table and then drop
> the old one, but that isn't really ideal.
> If there are any other things I can try I would love to give it a go.
>|||We actually didn't have a clustered index on this table.
I ended up running an insert from the offending table to a new table,
then dropped the old one, and the new one looks great.|||In general all tables should have a clustered index. You have no way to
control fragmentation or issues like this without one.
--
Andrew J. Kelly SQL MVP
"cmay" <cmay@.walshgroup.com> wrote in message
news:1140532518.546041.33250@.z14g2000cwz.googlegroups.com...
> We actually didn't have a clustered index on this table.
> I ended up running an insert from the offending table to a new table,
> then dropped the old one, and the new one looks great.
>

REAL REAL Experts needed for pretending to be replication_agent

Hi All,
We know that, "NOT FOR REPLICATION" option makes it possible for
replication agents ,let's say, to insert rows without being
constrained by IDENTITY restriction or check constraints(And also it
does not change SEED value in the table).
That means, being a replication agent is like having a PASS CARD that
allows them to get rid of constraint checks. (But disabling
constraints is not a solution)
But, during conflict resolution coding, I need to INSERT some data to
some tables (related tables), but I am facing CONSTRAINTS that merge
agent does not face.
Is there any way of pretending to be like merge_agent for constraint
overcoming? THIS IS THE CORE QUESTION.
To be more specific, is there any way of setting sessionproperty for
replication_agent? That is: when you execute the following query in a
normal connection;
select sessionproperty('replication_agent')
you get "0".
but for replication_agent that function returns "1". Microsoft guys
somehow set connection property for agent, and how they do it is
scritly undocumented.
I tried setting context_info in sysprocesses table to "8" but it did
not work. That SP also is not enough by itself
:sp_MSsetcontext_replagent
Please help, if any way of pretending to be merge agent.
PS:I event wrote an application with ReplMerg.exe process name
guessing that SQL may know agent from its process name.
And also please don't suggest disabling constraints because it is not
enough, e.g. replication_agent does not affect SEED value for
inserts that it does..
I hope some REAL expert will hear my SCREAM.
Thanks alot in advance,
Nury SWORD
MCDBA - MCSD
Toronto
I suggest you contact PSS for an answer to your question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Nury SWORD" <nurysword@.hotmail.com> wrote in message
news:16342dc6.0409170707.65ef87e1@.posting.google.c om...
> Hi All,
> We know that, "NOT FOR REPLICATION" option makes it possible for
> replication agents ,let's say, to insert rows without being
> constrained by IDENTITY restriction or check constraints(And also it
> does not change SEED value in the table).
> That means, being a replication agent is like having a PASS CARD that
> allows them to get rid of constraint checks. (But disabling
> constraints is not a solution)
> But, during conflict resolution coding, I need to INSERT some data to
> some tables (related tables), but I am facing CONSTRAINTS that merge
> agent does not face.
> Is there any way of pretending to be like merge_agent for constraint
> overcoming? THIS IS THE CORE QUESTION.
> To be more specific, is there any way of setting sessionproperty for
> replication_agent? That is: when you execute the following query in a
> normal connection;
> select sessionproperty('replication_agent')
> you get "0".
> but for replication_agent that function returns "1". Microsoft guys
> somehow set connection property for agent, and how they do it is
> scritly undocumented.
> I tried setting context_info in sysprocesses table to "8" but it did
> not work. That SP also is not enough by itself
> :sp_MSsetcontext_replagent
> Please help, if any way of pretending to be merge agent.
> PS:I event wrote an application with ReplMerg.exe process name
> guessing that SQL may know agent from its process name.
> And also please don't suggest disabling constraints because it is not
> enough, e.g. replication_agent does not affect SEED value for
> inserts that it does..
> I hope some REAL expert will hear my SCREAM.
> Thanks alot in advance,
> Nury SWORD
> MCDBA - MCSD
> Toronto
|||I finally found a merge replication guru guy. He is actually a
contractor in Toronto and selling a special component which is totally
able to pretend to be merge agent.
It is simple to use but may be a little bit expensive for start-up
companies. Since we desperately needed that feature the company just
paid for it instead of digging for weeks over weeks.
It gets SQL Server credentials as class properties and you call
ExecuteSQL method, it executes it as if it is merge agent.
For example I can execute the following SQL against my DB using that
component:
INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
test')
and it works!!
you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
constraints.
If you need to contact that merge replication consultant just send me
an email.
Nury Sword
NurySword@.hotmail.com
MCSD - MCDBA
Toronto
|||Nury,
as far as I understand, it is not possible to take the context of the merge
agent. Are you sure that this is how the component works?
Do you have to 'tell' the component what table you are working with? If so,
it might just be doing some dynamic SQL:
exec('set identity_insert tcompany on;insert into tcompany(id, companyname)
values(34,''test'');set identity_insert tcompany off')
Can you send me the details of this consultant as I'd like to ask him about
it.
Regards,
Paul Ibison (SQL Server MVP)
"Nury" <nurysword@.hotmail.com> wrote in message
news:1106863095.810873.7130@.z14g2000cwz.googlegrou ps.com...
> I finally found a merge replication guru guy. He is actually a
> contractor in Toronto and selling a special component which is totally
> able to pretend to be merge agent.
> It is simple to use but may be a little bit expensive for start-up
> companies. Since we desperately needed that feature the company just
> paid for it instead of digging for weeks over weeks.
> It gets SQL Server credentials as class properties and you call
> ExecuteSQL method, it executes it as if it is merge agent.
> For example I can execute the following SQL against my DB using that
> component:
> INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
> test')
> and it works!!
> you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
> constraints.
> If you need to contact that merge replication consultant just send me
> an email.
> Nury Sword
> NurySword@.hotmail.com
> MCSD - MCDBA
> Toronto
>