September 21, 2009 at 3:37 pm
I run a large ERP database with 180gig mdf file. SSMS always reports that the file has 20% or so free space but i could never successfully complete a shrink. As an alternative I began looking at SSIS to perform an ETL (transfer sql server objects task) to load a new database with the same tables, indexes, views and security. To my delight after performing the ETL my db was 30gig smaller.
Why is sql2005 unable to shrink an mdf file? Is sql 2008 any better? Are there other suggested methods for shrinking a db?
It should be noted that i used a new SAN partition with aligned partitions at 1024Kb on the drive used to create the new db. Is it possible that a properly aligned partition can have an impact of reducing the size of the db 30gig?
Thanks!
G
September 21, 2009 at 7:50 pm
My first question is why are you wanting to shrink the database?
The second question is can you provide the commands you are using to try and shrink the database?
Fraggle
September 22, 2009 at 7:09 am
Well sql 2005 does not appear to manage space effectively. I can see from many copies of our ERP systems in testing that saving that 30% of disk space which is unused would free up nearly 1 terbyte. Since we pay per gig for our SAN it will save money. So shrinking the database is a cost saving measure for our organization.
Here is the script which i have tried but failed to do a shrink.
USE [NDHT]
GO
DBCC SHRINKFILE (N'NDHT_Data' , 0, TRUNCATEONLY)
GO
September 22, 2009 at 7:21 am
Patrick Groce (9/22/2009)
Well sql 2005 does not appear to manage space effectively. I can see from many copies of our ERP systems in testing that saving that 30% of disk space which is unused would free up nearly 1 terbyte. Since we pay per gig for our SAN it will save money. So shrinking the database is a cost saving measure for our organization.Here is the script which i have tried but failed to do a shrink.
USE [NDHT]
GO
DBCC SHRINKFILE (N'NDHT_Data' , 0, TRUNCATEONLY)
GO
That is only true up to a certain point, there would have been a valid reason why the database grew to that size, shrinking has a negative effect on performance, it can cause fragmentation and you have the situation that the database will grow again to the size that it grew to.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 7:53 am
It sounds like i have done the right thing then by using ETL tools to copy the old db into a new one. And i achieved the goal of saving space plus indexes and views were rebuilt so there are no fragmentation issues.
There reason the db got to this point was our ERP upgrade scripts perform many alters against the db. We have have been through a couple large upgrades to date.
I am pleased with my results. I cannot see any other way of achieving my results unless someone can make futher suggestions.
Thanks for your time.
September 22, 2009 at 8:05 am
ShrinkFile should be able to shrink the file. I've seen issues with log files, but it seems data files usually shrink.
I understand your wanting to save space, but be sure that you have enough for normal operations. Data grows, and there is supposed to be free space in the files to handle growth, as well as things like index rebuilds.
Was the old and new DB structure the same? Just an mdf file? I'm surprised that didn't work. Did you try another target level, aiming to shrink to a reasonable level, leaving some free space for data to grow?
September 22, 2009 at 8:07 am
The TRUNCATEONLY parameter prevented the shrink from retrieving any space. This option means 'just chop any unused space off the end of the file without moving any data about'. If the last extent in the database happened to be in use no space would be recovered.
If you want to shrink run with this option first then if necessary run without the option specifying a size you want to shrink to.
WARNING - this will severely fragment your database so you will need to reindex it immediately afterwards which will cause growth in the database if there was no free space.
Presuming your tables are not all heaps frequent reindexing will help control database growth
Make sure the growth factors on your newly created databases are sensible, not 10% or 1MB, one will cause the database to grow too large if say it was already 50GB big and cause a slow down whilst it grew, the other would cause sever fragmentation at the disk level.
---------------------------------------------------------------------
September 22, 2009 at 8:36 am
Nice catch, George. Didn't notice that. Thanks for the note.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply