Empty table still holding Pages

If anyone has seen this before I would appreciate any input.
In a new datamart that was just built there is a worktable for importing new
data. I use DTS to insert the data and then use a delete from statement to
remove the records from the table. Recently the database ran out of space
prematurely and after some investigating I found that with no rows in table
it was still holding space in that datafile. The output from sp_spaceused
worktable is below:
worktable, 0, 571336 KB, 552000 KB, 8 KB, 19328 KB
Also when I run a select * from worktable the query searches the held pages
attempting to return data, even though there are no rows in the table. Has
anyone seen this behavior before? Btw we are using SQL Server 2000 SP3.
--
Jason Strate
j.strate@.comcast.netWhen you fire delete from... statement on the table extent allocation
remains as it is , meaning though the rows gets deleted from the table
allocated extent remains as it is and hence data pages. To remove allocated
extents as well as data you will have run TRUNCATE TABLE command which is
quite faster and does minimally logged operation compare to DELETE FROM
command.
Also to get correct result from sp_spaceused command run DBCC UPDATEUSAGE
... WITH COUNT_ROWS
after any bulk operation like ins/upd/del performed.
To get correct count of rows from the table run query "select count(*) from
table"
--
-Vishal|||Truncate table permissions only default to table owner, members of sysadmin,
db_owner, ddl_admin and is not transferred.
if you are not table owner then you need to be added to ddl_admin/db_owner
database role.
Whereas to run dbcc updateusage you will have to be member of db_owner
databse role
--
-Vishal|||Hello
> In a new datamart that was just built there is a worktable for importing
new
> data. I use DTS to insert the data and then use a delete from statement
to
> remove the records from the table. Recently the database ran out of space
> prematurely and after some investigating I found that with no rows in
table
> it was still holding space in that datafile.
To free space from deleted rows in table you must rebuild or defragment
indexes. To do this you can run:
DBCC INDEXDEFRAG
DBCC REINDEX
DBCC DBREINDEX
CREATE INDEX WITH DROP_EXISTING
You must choose appropriate method according to your conditions.
Serge Shakhov