September 22, 2003 at 1:52 pm
When you use a "Delete" command to delete ALL the records in a table the sp_spaceused still reports that the table is using space. But when you "Truncate a table", sp_spaceused reports that no space is associated with the table. Is there a way to recover the used data and index space after issuing a "Delete from MyTable" command, so that it is associated with UNUSED space instead of USED space. I also don't want to have to drop and recreate the table to release the space. Thought that maybe there might be a DBCC command to do this.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 22, 2003 at 2:28 pm
dbcc DBREINDEX(TableName)
or you could try dbcc INDEXDEFRAG.
--Jonathan
--Jonathan
September 22, 2003 at 2:30 pm
You can try DBCC updateusage, or the switch with
exec sp_spaceused tablename, true
to recheck the allocation automatically without running updateusage.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 22, 2003 at 5:20 pm
Basically I want to release the pages that don't contain any records back to the database. Reason is because at one time these tables had a lot of records, and now none, but the database seems to think these pages are still used. I would like to release them so they are not considered used pages. When they are empty used pages the still are part of the database backup size. If I could release these pages then they would not be backed up, and my database backup would be smaller, as well as my database. Tried these suggestions, but they did not help.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 22, 2003 at 5:24 pm
Problems solved. I ran a maintenance plan. Tomorrow, I'll figure out exactly which DBCC solved my problem, and let everyone know.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 22, 2003 at 5:43 pm
I've had this multiple times and running dbcc updateusage has fixed the reported free space amount, I'm interested to hear what did fix the reported amount if that didn't.
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 23, 2003 at 11:18 am
As promised I said I would give a little recap of regarding resolving my used space verses unused space.
Now first let me say I'd like to thank both Jonathan and Ray Higdon. These guys pointed me in a couple of directions that eventually lead to a solution.
First let me repeat the situation. I was trying to delete all the records from my tables, without modifying the schema, so I did this with a series of “Truncate table” and “Delete from” statements. Had to do the “Delete from” statements because a number of table had foreign key constraints.
The real reason I was doing this was so I could send a database backup to a developer. I couldn’t send a full backup because of the size of the backup, so I thought if I removed all the records, then the size of the backup would be small. Well how wrong I was. Turns out there is more to it releasing that unused table space back to the database.
What I found that after doing this that all the tables where I truncated the tables did in fact release there pages back to the unused portion of the database. But the tables where I deleted all the records where I performed a “Delete from” statement did not. By using the sp_spaceused command I could see that the “Deleted From” tables still had pages allocated to data and indexes.
I tested the solutions that Jonathan and Ray Higdon suggested. I found that the “dbcc dbreindex” and “dbcc INDEXDEFRAG” did release most of the index space back to the unused bucket, but did not release any data space. Also tried “DBCC updateusage”, but this command only updated “sysindexes” table, although this command did fix some incorrect space usage reports generated by my sp_spaceused. After running these commands, my database used space was a little smaller, since some index space was released.
To release the data pages I used the “DBCC shrinkdatabase” with the “TRUNCATEONLY” option. This command released most of the unused data pages back to the operating system. So now I finally had a clean database, although my database backup was still quite large, due to the large transaction log. Which was easily solved by truncating the transaction log.
Hope this helps explain how I recovered my unused space.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2003 at 12:30 pm
Makes sense, sometimes you can get it a little smaller with dbcc shrinkfile.
Thanks for posting update!
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 23, 2003 at 1:25 pm
Greg, Im curious as to whether you had a clustered index on the table?
Andy
September 23, 2003 at 2:33 pm
Funny you should ask. Turns out the biggest table, and the one I was working with did not have a clustered index. From my testing it seems when you have a clustered index the pages are released when you run a "delete from" command.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2003 at 4:10 pm
quote:
Funny you should ask. Turns out the biggest table, and the one I was working with did not have a clustered index. From my testing it seems when you have a clustered index the pages are released when you run a "delete from" command.Gregory Larsen, DBA
Absolutely. I guess I ASSuMEd too much in my reply; although this nastiness of heaps was touched on in another thread yesterday...
--Jonathan
--Jonathan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply