May 8, 2003 at 3:39 pm
I am wondering why there is so much unused space in my db. The backup is only 230MB (zipped) but if restored it became almost 6GB. I realized there is a lot of unsed space hanging around.
I checked with sp_spaceused and got following:
database_name database_size unallocated space
----------------------- ------------------ ------------------
madison 6755.13 MB 326.26 MB
reserved data index_size unused
------------------ ---------------------------------- ------------------
6581624 KB 845880 KB 363184 KB 5372560 KB
After executing the following:
EXEC sp_msforeachtable 'DBCC DBREINDEX (''?'')'
DBCC SHRINKDATABASE ('MADISON')
DBCC UPDATEUSAGE ('MADISON')
Only minor size reduction gained:
database_name database_size unallocated space
------------------------------------------ ------------------
madison 5614.69 MB 1100.02 MB
reserved data index_size unused
------------------ ---------------------------------- ------------------
4622000 KB 675928 KB 237656 KB 3708416 KB
So from 5,3 GB to 3,7 Gb it is not a lot freed unused space and the question is why is it not freed or is it just the way db handles space? What is the server hiding? Autoshrink is on in option tab. Anything else that could be done or checked?
Thanks
mipo
May 8, 2003 at 5:46 pm
Try to run this query to see reserved/used per table. Then look for "text" fields (ntext, image).
SELECT id, SUM(reserved) AS Reserved, SUM(used) AS Used, SUM(reserved - used) * 8 AS Unused,
(SELECT name FROM sysobjects WHERE id = si.id) AS TableName
FROM sysindexes si
WHERE (indid IN (0, 1, 255))
GROUP BY id
ORDER BY Unused DESC
May 9, 2003 at 5:25 am
Thanks for the input.
My concern is really the unused space and how it will be released.
We run several times the script:
EXEC sp_msforeachtable 'DBCC DBREINDEX (''?'')'
DBCC SHRINKDATABASE ('MADISON')
DBCC UPDATEUSAGE ('MADISON')
and unused space came down below 1MB. The entire db size came down to 500MB!
I do not understand why we can run several times this script to get a better result?
Why not in one go?
We use Auto-growth by 10% and unrestricted file growth for this db.
I do not have a specified file size and so what would be the target size in this case?
Can I chose a free value in MB maybe not realistic?
It is not quite transparent what SQL server does behind and how he manages his dbs!!.
Thanks
mipo
May 9, 2003 at 7:50 am
I think you are backup up without overwriting old media....
This means your bak file grows until a backup set expires (if the option is on)
May 9, 2003 at 8:21 am
ehh, sorry I didn't read good what you wrote...
Try to take a look in the taskpad or try these two:
sp_helpdb
sp_spaceused
May 9, 2003 at 9:46 am
Check this article from Microsoft:
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432
Also, run DBCC SHOWCONTIG to see what's fragmented in your database.
May 12, 2003 at 6:20 pm
With respect to that MS support note about blob extents being fragmented, would it help to shrink in Ent Manager with the "Move pages to beginning of file" check-box checked to get rid of this fragmentation & have near-full extents?
On a related matter, why is the "Move pages to beginning of file" option not supported in DBCC SHRINKFILE or SHRINKDATABASE commands ?
May 13, 2003 at 11:13 am
From running Profiler you can see that when you check "Move pages" checkbox EM executes:
DBCC shrinkdatabase('yourdb', 10).
When you leave this box unchecked the command executed is:
DBCC shrinkdatabase('yourdb', 10, TRUNCATEONLY )
May 13, 2003 at 8:58 pm
Thank you, that's a good tip to know,using profiler.
My question is, is there a simpler workaround to defrag blob extents that doesn't involve exporting data & dropping tables? Will a backup and restore do the trick?
May 14, 2003 at 9:27 am
No, backup and restore won't help.
If you really don't want to drop and re-create the table you can try to create a clustered index if you don't have one already. If you do have a clustered index try to run dbcc dbreindex on it.
Also, if it's SQL 2000 you can look at 'text in row' option. Read the limitations and side effects to make sure your application can work with that option.
May 15, 2003 at 2:22 am
To add to this... DBCC ShrinkDB does not recover space no longer used by text and image data if they are stored in the same filegroup as the data.
The only way I have found to do this is by creating another filegroup, move the offending table to this new filegroup, shrink the database and then move it back.
May 15, 2003 at 5:43 am
I do have text in one file only (madison_data.mdf) and I run the following
query and unused space came extremely down but file size remains around 4,5GB:
USE MADISON
DBCC SHRINKFILE('MADISON_DATA',EMPTYFILE)
I checked the Microsoft note from bbychkov (see above) and according to this note:
The DBCC SHRINKFILE and DBCC SHRINKDATABASE commands will not move data and reduce the file size if the EstimatedPages value equals the MinimumSize value that is reported when the command completes.
In this artice there's a query you can run to find out if this is the case.
For my db it was the case. I run the query above and unused space came dramatically down but as said above file size is still quite big. I wanted to run another query as described in this Microsoft article:
When the DBCC SHRINKFILE query completes, run an ALTER DATABASE query to remove the old files from the database.
But I can not remove an old file when there's only one file.
I tried the following query:
DBCC SHRINKDATABASE ('MADISON',10)
DBCC SHRINKDATABASE (MADISON',10,TRUNCATEONLY)
which did not succeed.
Another engineer told that shrinking occurs on a deferred basis so it can take a couple of days including a couple of restarts of the SQL server before alle space colleage is freed.
I think SHRINKING a db is a subject which never exhausts and many of us are urged to post there questions as it is never clear!
Thanks
mipo
May 15, 2003 at 6:48 am
You an try these:
use madison
BACKUP LOG madison WITH TRUNCATE_ONLY
go
use madison
go
dbcc shrinkfile (madison_log,400)
go
Take the db backup after these commands.
Thanks. Sanjay
May 16, 2003 at 10:23 am
not sure if this will be the answer to your question but i've just had a similar problem. essentially, i had heavily fragmented secondary non clustered indexes... use dbcc showcontig to check the extent of the fragmentation... i created a clustered index on the table to bring everything back into line...
May 17, 2003 at 6:44 am
Could you give me more details about how handled your problem and how you created the clustered index etc.
Thanks mipo
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply