May 4, 2004 at 3:00 am
Hi all! I am new to sql server central, I hope I chose the right forum for posting.
My problem is about reducing the space used by the data file of one SQL database. The SQL server is version 7.0 but I noticed very similar behaviour on a 2k installation I use for test purpose.
Well, I found out that the space is being locked mainly by one table. The table has a column of type text. When I Run exec sp_spaceused 'tableName' on the said table I can see that most of the space in this table is defined by SQL as "unused". Here's the result I get:
rows reserved data index_size unused
-----------------------------------------------------------------
64037 3764864 KB 626984 KB 40 KB 3137840 KB
You will notice the proportion among the data size and the unused space, which scores an impressing (for me :p) ratio of 5 to one!
As a scheduled job SHRINKDATABASE runs on regular weekly basis, however I run manually the command SHRINKFILE on the datafile that is bigger that I'd like and here is what I got:
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 1 514856 53704 514736 514736
As effect to this action there is no space release from the unused amount abouve.
However I noticed an interesting fact: running recursive shrink file from the enterprise manager wizard (about 15 times one after the other without users connected) managed to release most of the unused space.
Now, the question is: why the space is not released at first? and besides, why shell I run 15 times the shrink command to achieve a real benefit in space reallocation?
I am really eager to know better how to deal with SQL, since I am fairly naive to the matter! Thank you in advance!
Emmanuele
May 4, 2004 at 4:40 pm
sp_spaceused 'tablename' is known to report incorrect size (almost seems by design). More precise way is to use sp_spaceused 'table', true. This is especialy more important when you had just created some of the indexes, as the space uage reports are not right. BTW, I noticed that your index size is 40K. Either you have no indexes on the table or they are not taken into account (more reason to run with the parameter 'true' ). Now coming to the issue of shrinking the file, I never had this problem. I use both QA and EM for shrinking files. How ever, I have seen tables without a clustered index make file shrinking a bit lengthy.
All this applies to SQL 2K.
May 5, 2004 at 2:09 am
I heard that sp_spaceused may report incorrect size, especially with regards to indexes. Actually I ran DBCC UPDATEUSAGE ('Database', 'Table') to update the sysindex table, and then I got the result above.
I now tried also sp_spaceused with the True parameter, but I had the very same report from sql.
The table has a clustered index, I guess it is small but that should make sense it is built on two very short columns.
I was suspecting the text field to be allocating and not releasing some space, I noticed tables with no text (blob) fields have not so much unused spce (proportionally).
Now since the space used for the data is 1 to 5 with the unused space, I thought that this could not be only a precision problem.
What I noticed running the shrink functino, is that the unused space is not released all at once, but little by little, until, after 15 times, it relelased 90 percent of the unused space (from the monitored table, I am not referring to all of them).
I was asking if there is a way to force SQL release all (or most) of the unused space at once, that would be very helpful.
thanks
May 6, 2004 at 10:14 am
Hi,
If you want to reduce spaceused by your database, make a backup log then a dbcc shrinkfile of your data file. Maybe it can be necessary to made backup log with truncate_only.
May 7, 2004 at 5:15 pm
As Le Rhun said, sometimes just shrinking alone is not good enough. The shrink can not shrink sections of the transaction log that are marked active. For my pesky databases that have logs that are busy, I schedule a job weekly that does a backup of the log with truncate only and then I follow that with a step that does a dbcc shrinkfile on the log.
I typically run this at 11:05 PM which is 5 minutes after the last hourly log back up of the day. The nightly full back up kicks off at midnight. As long as my nightly full runs sucessfully, I am good to go. A concern to this technique is that if your nightly backup fails, and you are relying on the chain of log backups to do a point in time restore sometime before a new full backup is taken, you will be in trouble. Good luck.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply