October 13, 2009 at 4:38 pm
Hi
We have a database that is about 46Gb with 1Gb free. When running sp_spaceused I get the following data:
reserved=36220312 KB
data=34770736 KB
index_size=1197256 KB
unused=252320 KB
However after running a DBCC SHRINKFILE on the file (using the gui) with the same size as the file already is, the database is still 46Gb but with 30,5Gb free. sp_spaceused reports the following
reserved=5949816 KB
data=4603456 KB
index_size=1197256 KB
unused=149104 KB
How do I accomplish that kind of "compression" (lack of a better word) without shrinkfile and is there any way to avoid the kind of waste?
October 14, 2009 at 4:32 am
You should shrink the db only if you really, really need it - but not on a regular basis. Please take a look at
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx?pr=blog
Also this forum offers lots of good advice regarding this.
That's not a "waste". The database will grow in time, whether you shrink it or not.
October 14, 2009 at 7:33 am
dmoldovan (10/14/2009)
You should shrink the db only if you really, really need it - but not on a regular basis. Please take a look athttp://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx?pr=blog
Also this forum offers lots of good advice regarding this.
That's not a "waste". The database will grow in time, whether you shrink it or not.
As I wrote, in reality I don't shrink the database to a smaller size, but just forces SQL to reorganize all pages because the database will in most cases grow. But currently dbcc shrinkfile is the only method that I know of that reorganizes all pages and I'm looking for another method of doing that (I've tried different scripts that reorganizes index and tables without luck) and perhaps a way to avoid that internal fragmentation through fillfactor or other methods.
October 14, 2009 at 1:39 pm
anders-906993 (10/14/2009)
As I wrote, in reality I don't shrink the database to a smaller size, but just forces SQL to reorganize all pages because the database will in most cases grow. But currently dbcc shrinkfile is the only method that I know of that reorganizes all pages and I'm looking for another method of doing that (I've tried different scripts that reorganizes index and tables without luck) and perhaps a way to avoid that internal fragmentation through fillfactor or other methods.
Did you recently delete a lot of data from the database? Was the data that was deleted contained in an LOB type column?
I am guessing here, but that is what I think happened. After deleting that data - the tables that contain the LOB data are not compacted until you perform an index rebuild on the clustered index (if you have one) and set LOB_COMPACTION on (default).
If that doesn't fix the problem - then most likely your table does not contain a clustered index. Without a clustered index there is nothing that can be done to free up that space unless you create one.
So, by shrinking the data file - SQL Server now can re-organize the pages and free up the allocation to those pages. It also causes the other tables/indexes to become fragmented and you need to perform a reindex now to defragment them.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 16, 2009 at 2:09 pm
Well, the database is a part of our dw/bi (I'm in operations and dba is just one of my chores), but I'm quite sure that some of the data or most of the data are changed every night. The thing about LOB and clustered index is indeed interesting and I'm going to have a look into that next week.
Did you recently delete a lot of data from the database? Was the data that was deleted contained in an LOB type column?
I am guessing here, but that is what I think happened. After deleting that data - the tables that contain the LOB data are not compacted until you perform an index rebuild on the clustered index (if you have one) and set LOB_COMPACTION on (default).
If that doesn't fix the problem - then most likely your table does not contain a clustered index. Without a clustered index there is nothing that can be done to free up that space unless you create one.
So, by shrinking the data file - SQL Server now can re-organize the pages and free up the allocation to those pages. It also causes the other tables/indexes to become fragmented and you need to perform a reindex now to defragment them.
December 15, 2009 at 7:24 am
We do shrink each file, but first we check if it contains a lot of unallocated space (e.g. > 10%):
.
.
-- Skip small files
IF @totalextents < 2000
BEGIN
PRINT 'Skipping file ID ' + CAST(@filegroupid AS VARCHAR(10)) + ', total extents allocated is < 2000 (' + CAST(@totalextents AS VARCHAR(50)) + ' is allocated)...'
GOTO NEXTFILEGROUP
END
-- If there is not much space to regain by shrinking, skip that file
SELECT @regain = CAST(@usedextents AS DECIMAL(30,2)) / CAST(@totalextents AS DECIMAL(30,0))
IF @regain > 0.90
BEGIN
PRINT 'Skipping file ID ' + CAST(@filegroupid AS VARCHAR(10)) + ', there is less than 10% space to regain (' + CAST(CAST((@regain * 100) AS INT) AS VARCHAR(10)) + '% is used)...'
GOTO NEXTFILEGROUP
END
SET @sqlcommand = N'USE ' + @databasename + N'; DBCC SHRINKFILE (' + CAST(@filegroupid AS VARCHAR(10)) + N',' + CAST(@targetsize AS VARCHAR(10)) + N');'
PRINT 'Shrinking database file with Fileid ' + CAST(@filegroupid AS VARCHAR(10)) + ' in database ' + @databasename + ' with command ' + @sqlcommand + '...'
.
.
After shrinking, it is absolutely nessecary to run ALTER INDEX ... REORGANIZE with a fillfactor of e.g. 95%.
It looks like it is the only way to get rid of all that unallocated space. Now, the database will stay small (unallocated space will stay low), even if you run rebuild/reorgaize regularly (be sure to use the same fillfactor as when you did the REORGANIZE immediately following your SHRINKFILE command).
It works! It runs once a month.
Anyone that disagrees?
Lennart Gerdvall
payex.com
December 15, 2009 at 7:43 am
Data warehouses tend to grow pretty well. Why both to shrink? If you needed 45GB at one point, won't you need it again relatively soon?
If there is a space issue, you could shrink, but you're not getting any benefit by shrinking because you think there is wasted space. Growing a database is relatively expensive, so I'd leave that space in there for now. Index rebuilds, maintenance, adding back data, all will use space.
December 15, 2009 at 8:02 am
Lennart Gerdvall (12/15/2009)
Anyone that disagrees?
Absolutely, 100% disagree. Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ and
http://brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
and all the various posts that those two link to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2009 at 8:17 am
And index reorg might be what you're looking for. It'll shuffle around the pages, condensing them and removing as much fragmentation as it can. Note that a reorg won't use a fillfactor, like an index rebuild would. Also, be careful doing a reorg as it uses the log file heavily.
December 15, 2009 at 8:39 am
Edit: Removed incorrect info.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2009 at 9:04 am
From Books Online: http://msdn.microsoft.com/en-us/library/ms189858.aspx
Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.
December 16, 2009 at 3:03 am
Well, e.g. we have a partitioned database which allocates a new file for each month. We remove unallocated space in all prvious mont if nessecary. The database had grown to 530 GB and now after shrink and reorg it only has 5% unallocated space and uses 300 GB. So we saved 230 GB SAN-disk. And, do you people never delete old data not used. Why pay for unallocated space?
Lennart Gerdvall
payex.com
December 16, 2009 at 3:44 am
Lennart Gerdvall (12/16/2009)
And, do you people never delete old data not used.
Sure, and if that old data that has been removed creates free space that will not be reused within a reasonable amount of time, then I would do a once-off shrink. However that's not the common situation.
The common situation is that old data is removed on a schedule and new data is constantly added. In that case, there's no point in shrinking the file (giving the free space back to the OS) only for the data file to have to grow within a short period of time, reclaiming all that space that was released. If that's happening, there's absolutely no gain in shrinking. You can't say it's saving space because it's space that will will be needed again by the database.
Shrinks are not cheap operations, nether are data file grows. Rather just leave the free space inside the data file so that SQL can reuse it as it needs to do so
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply