how can space be released from tables?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Sean T (T_at_discussions.microsoft.com)
Date: 02/22/05 

Date: Mon, 21 Feb 2005 19:21:02 -0800

In our enterprise, data is frequently transfered between databse servers
running SQL Svr 2k, via DTS packages and bulk insert. We transfer large
amounts of data at a time, usually around 100k rows but sometimes in excess
of 500k into a temporary, staging table on the destination server. This data
is then merged into the main tables on the same server and deleted from the
staging table.

As a result of this usage pattern, the number of rows in the staging table
can flutuate wildly, from 0 rows up to over a million.

I am often finding that although the rows in the staging table are processed
and removed, the space allocated to the table stays much higher than one
would expect. I thought that SQL Server would release the space once it was
no longer required. After a day of processing here is a typical output from
sp_spaceused

name rows reserved data
index_size unused
helper_Xfer_CallAttempts 0 2133288 KB 1787600 KB 560 KB
345128 KB

and dbcc checktable

DBCC results for 'helper_Xfer_CallAttempts'.
There are 0 rows in 223450 pages for object 'helper_Xfer_CallAttempts'.

I know that these numbers seem to reflect reailty because even executing a
statement that results in a table scan, such as select * from mytable, takes
much longer than it should for a table with 0 rows!

Does anybody have any experience of this, and have any ideas as to what
causes it/what can be done about it? I don't want to resort to forcing a
TRUNCATE TABLE, which is the only thing I have found that reclaims the space.

Thanks,

Sean.

Relevant Pages

  • Re: Server Replication Software: What are folks using?
    ... We have one staging server and 2 live, ... Most web content is in the DB and migrated in a workflow manner from staging ... Is anyone familiar/using any other server replication software? ... - we can usually find the issue within two email cycles or a quick ...
    (microsoft.public.dotnet.general)
  • Re: Disable "problem with security certificate" in control
    ... on the staging server, then run them on the live server after ... website's security certificate" page before going to any HTTPS pages. ...
    (microsoft.public.inetsdk.programming.webbrowser_ctl)
  • Re: Server Replication Software: What are folks using?
    ... We use a home-grown .net CMS product that we've built in a staging/live ... We have one staging server and 2 live, ... Most web content is in the DB and migrated in a workflow manner from staging ... If you are a supported customer, ...
    (microsoft.public.dotnet.general)
  • Re: Online-Versionskontrolle
    ... Interessant auch für Web Entwicklung mit Staging (auf mehrere Server für ... Für Bugtracking ist fogbugz.com sehr populär, dort gibt es auch zeitlich unbegrenzte kostenlose Accounts. ...
    (microsoft.public.de.german.entwickler.dotnet.csharp)
  • Re: SBS 2008 Folder Redirection
    ... Storing large amounts of data doesn't sound like a reason not to use the server to hold the data. ... Maybe if you started a new thread with a more complete explanation of what the problem is with using document redirection. ...
    (microsoft.public.windows.server.sbs)