| Author |
Topic |
|
Mort
Starting Member
3 Posts |
 Posted - 2008-06-26 : 09:34:53
|
| I just had an odd problem occur on one of my SQL Server databases. I had a table with a lot of data (5 GB of data), to shrink the database, I deleted the contents of the table and tried to shrink the database. To my surprise, the database file hasn't changed.I went into the database, verified that the table contains 0 rows and found out that even though the table doesn't contain any rows, it's size is still 5GB.I have tried to run a DBCC to check the database, without finding any errors.Anyone have any good ideas about how to free the data of the deleted rows ? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-26 : 09:50:12
|
| How did you delete the contents of table? Truncate or Delete command. you should be able to shrink unused portion of database. |
 |
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-26 : 11:30:33
|
| check updatestats and DBCC UPDATEUSAGEALso DBCC REINDEXthen check your Space again.Maninder |
|
|
|
Mort
Starting Member
3 Posts |
Posted - 2008-06-27 : 02:50:20
|
quote:
How did you delete the contents of table?
DELETE FROM [MyTable]quote:
check updatestats and DBCC UPDATEUSAGEALso DBCC REINDEXthen check your Space again.
Tried DBCC UPDATEUSAGE and DBCC DBREINDEX without any change in used diskspace. |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-27 : 15:22:56
|
| EXEC sp_spaceused 'Tablename'What are the Datatypes in the TABLE.The database Size will not changed or reduce size if you are modifying the table or deleting some rows.try to shrink the database and reindex the database. The size will not decrease, because the space you get is only per block, but doing so, the rows/blocks will not be "merged" to see that space.Well table will not grow for some time now...Or Create a New Table and Copy the contents of the Original into this new Table and then delete Original Table.Maninder |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-27 : 15:28:45
|
| mainder,DBCC UPDATEUSAGE only needs to be run on 2000 (or lower) or when you've upgrade from a lower version to 2005. In the case of an upgrade, you only have to run it once and then never again. See BOL for details. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-27 : 15:54:15
|
| Yes tkizer is correct.This time i asked him to run EXEC sp_spaceused 'Tablename', so this will list all the table info.but again.. I know we are not going to end up anywhere as that will have no affect on the Database size whatsoever. Try these 2 Tricks1. Rebuild the index of the table , or2. Add a row and then delete the same.Maninder |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-27 : 15:58:54
|
| Then -- for SQL Server 2000:DBCC INDEXDEFRAGDBCC DBREINDEX-- for SQL Server 2005:ALTER INDEX .... commands-- particularly the REBUILD / REORGANIZE optionsManinder |
|
|
|
Mort
Starting Member
3 Posts |
Posted - 2008-06-30 : 03:23:15
|
| A colleague of mine decided to drop the table and recreate it again, in order to get rid of the problem. Unfortunately this means that I won't be able to find out what went wrong or how to resolve a similar problem in the future. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-30 : 23:24:32
|
| As said above in case you have similar issue later, check table space with sp_spaceused, rebuild clustered index on it then check space again. |
|
|
|
|
Deleting data, but table size remains