| Author |
Topic |
|
ball
Starting Member
5 Posts |
 Posted - 2003-07-31 : 19:18:50
|
| I have a database of size of 5GB (data + index). I deleted 7 million rows from a table (10 million rows before the delete) yesterday. After the delete, the size of the database file growed to 5.5GB. I use sp_spaceused to check the table and find that about 1GB is reserved for the table. And for the database, 2.5G is reserved. Disk space is a big deal for us since we use the expensive RAID disk.I've tried everywhere for a solution. I don't want to send the data to another database, delete the old one and then rename the new one. So does anybody know how to even a tricky way?Thanks. |
|
|
ball
Starting Member
5 Posts |
Posted - 2003-07-31 : 19:19:49
|
| Sorry, forgot to mention that I am using SQL 2000 on a Win2000 server. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-31 : 19:22:42
|
| Run sp_spaceused @updateusage = TRUE to update the information:From SQL Server Books Online:"When updateusage is specified, Microsoft® SQL Server™ scans the data pages in the database and makes any necessary corrections to the sysindexes table regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the sysindexes information for the table may not be current. This process can take some time to run on large tables or databases. Use it only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately."Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-31 : 19:25:31
|
| If you then want to shrink the database (can only shrink if there is free space in the file which there should be after the delete), run DBCC SHRINKDATABASE and/or DBCC SHRINKFILE.Tara |
|
|
|
ball
Starting Member
5 Posts |
Posted - 2003-07-31 : 19:29:33
|
| Tara,Thank you for the reply. However that doesn't work. I did all those but the reserved space is still the same. It looks like that the SQL server wants to keep those space as reserved. sp_spaceused @updateusage = TRUE only corrects the report of the space used but doesn't release the reserved space.Shrink database can only free space from the end. I believe first, reserved space doesn't count as free space. Secondly they are across the database and not all put at the end of the file. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-31 : 20:21:47
|
| You can't free up any reserved space without deleting some data, changing the fillfactor on the indexes, etc... What was the reserved for your table before you deleted the data? What do you have for the fillfactor? Sending the data to another database, deleting the old one and then renaming the new one will not fix your problem. The reserved space will remain the same as long as you haven't deleted any data, removed some indexes, changed the fillfactor on the indexes, etc...Tara |
|
|
|
ball
Starting Member
5 Posts |
Posted - 2003-07-31 : 23:10:18
|
| OK, then why after the delete of 7 million rows, my database file becomes bigger? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-01 : 07:23:42
|
| The log file(s) will get bigger, but the data file(s) will not. If you backup or truncate the log you'll be able to shrink it more effectively. |
|
|
|
ball
Starting Member
5 Posts |
Posted - 2003-08-01 : 10:10:22
|
| No, I immediately backed up and truncated the log file after the delete. It is the database file that becomes bigger after the delete. The good news is that the database file becomes 100M smaller this morning. Hopefully it will keep shinking by itself. |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2003-08-04 : 18:07:31
|
| you could try to use dbcc dbreindex to performa a reorganization of your index and data pages. After that you should be able to shrink the datafile.Sérgio MonteiroTrust in no Oracle |
|
|
|
gambit
Starting Member
1 Post |
Posted - 2004-03-05 : 17:44:18
|
| Did you ever find a solution? I had a similar, VERY ANNOYING problem. I had a table that is using 1.7GB of space but reserving 14GB. I didnt need the data in this particular table so I truncated it. This seemed to fix the immediate problem, but I would like to know a better solution in case I run into this again with more important data. I dumped all the data into a flat file before I truncated in case I changed my mind. The most annoying part is that the file was only 63MB... Hmmm...- Gambit |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-05 : 23:52:02
|
| It's CRUCIAL to run DBCC UPDATEUSAGE if you get suspicious row counts or space allocation figures from sp_spaceused. Unless you have a read only database there is bound to be some discrepency until you run it.As far as table-> 1.7GB, data file-> 63MB, the difference is a combination of (too many?) indexes, inaccurate row and page counts, and fragmentation. |
|
|
|
gvphubli
Yak Posting Veteran
54 Posts |
Posted - 2004-03-08 : 09:15:24
|
| One more thing, when you create the database for the first time let the file sizes (data and log) to be at the minimum like 1024 KB. Else you can't shrink the files though you delete some data.- = Cracky DBA = -http://www.geocities.com/gvphubli/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-08 : 13:49:32
|
| Actually, you should create your initial database size according to what size you expect the database to be in a certain amount of time, like 6 months. By creating your database at a smaller siz than it should be, you are going to receive a performance hit each time it has to expand the files.Tara |
|
|
|
diamond
Starting Member
2 Posts |
Posted - 2004-03-12 : 15:29:26
|
| I had similar situation when I archived some data and database will reserve space. Shrinkdatabase does not work for this. Only shinkfiles will work. Try this:use [userdatabase]Select * from sysfiles This will give you information on data and log files in your user database.Then runDbcc shrinkfile( file_name, size)Where file name= database filebame which you can also find in task pad and size is what you want as per task pad. This is in mb.Depending on the size of your database, it may take several hours. Preferably run in the evening before leaving and enjoy in the morning.It alway work for me.I brought down size from 50 Gb to 17 Gbs..diamond |
|
|
|
|
How to free reserved space?