Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011

Why SHRINKFILE is a very bad thing, and what to do about it. Expand / Collapse
Author
Message
Posted Saturday, December 17, 2011 6:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 1:19 PM
Points: 31, Visits: 100
You must mean GilaMonster.
Post #1223524
Posted Saturday, December 17, 2011 11:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:40 AM
Points: 1,568, Visits: 666
Ken -
I would say that either method of Shrinking the file (in 1GB increments or all in one go) will have just as negative impact of data fragmentation; as you said your script does cause the Shrink process to yield and allow other processes to run, so it is arguably the lesser of the two evils. It seems like you are in a tough situation with your space, of course I dont know your situation in detail, but a couple of Data Warehouse items spring to mind - (1) can you purge older data? (2) can you use compression to help free space? Compression is much better in 2008 than 2005, but it could still be an option for you, see KT's blog : http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2005-and-2008-Compression.aspx, an older post, but still relevant; also a Bing search on 'data compression sql server 2005' will give you other resources.

Nico,
Yes, the title was intended to be a teaser to read the article, and I'm glad you read it because of the title. But the title is correct, Shrinkfile IS a very bad thing to do to a database, IF you don't understand the consequences and how to mitigate the performance impact. I would be very surprised if you havent incurred performance impacts from Shrinking your database files. Renaming your files and recreating the objects seems like a lot of work to me, but if it works for you, then its a viable solution. The FileGroup option was just that - just an option, if it doesn't fit with your environment for whatever reason, then it's not a workable solution.

Brian,
You do seem to have a reasonable process worked out for your situation. And, you are right, ShrinkFile is a valid tool in the DBA toolbox, if you understand and handle the consequences, just don't abuse it or it will bite you.



Post #1223534
Posted Tuesday, December 20, 2011 9:29 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
Here we go again in 2012, double cooked pork. Paul said it will cause fragmentation a long time ago. I said thou should defrag a long time ago.

The title should be "DBCC Shrinkfile Best Practice".
1. index defrag, file defrag
2. size it correctly (to the best you can)
3. partition and archive
....

DBCC Shinkfile is a double-edged sword. It exists for a reason, just like your congress exists or political parties exist.


Jason
http://dbace.us
Post #1224563
Posted Saturday, March 31, 2012 4:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 3:27 PM
Points: 2, Visits: 37
Hi All,
I've got a question regarding shrink file and index rebuid/reorganize. We have inherited HUGE database with one file (ca. 1TB). Indexes are fragmented massively, and, as you presume, after running rebuild against this db we end up with 1,5 TB monster. Is there any way to reduce size of this database ? I was thinking about new file, then running index rebuild on database that is already well defragmented (1,5TB one). How will new file react; will it grow a lot? Could we shring old file without causing index fragmenation after such action ?

Thanks for all the answers !! :)
Post #1276240
Posted Saturday, March 31, 2012 4:35 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 5,976, Visits: 12,887
piotr, for future reference. you will get a better, more focused responses if you start your own new thread.

A shrink of a data file will always fragment that file, so running one after a index rebuild is self defeating. The fact that the file is now larger after the rebuild has no effect on performance, if you have the space for that file leave it as it is.

Another rebuild won't make the file grow again as it now has the space within the file to accommodate the sorting. this is another reason to leave the file as it is, if you shrink it will just have to grow again when you rebuild.


---------------------------------------------------------------------

Post #1276244
Posted Sunday, April 1, 2012 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 3:27 PM
Points: 2, Visits: 37
Thanks for advice on 'new thread' thingie :)

As for db, we really need to find a way to create some free space on drive. Now we have around 50-70 GBs (1%) to mess around with, on disk containing this datafile. Database is in simple recovery model. In other words, I need to find a way to shrink the 1,5TB file without messing up indexes.
Post #1276301
Posted Sunday, April 1, 2012 4:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 5,976, Visits: 12,887
is there other data on this drive other than the database then?

Is the log file on the same drive and if so what size is it? a shrink of the log is less destructive.

some ideas, but maybe you just need another drive to support and maintain this database. Is it expected to grow in the future?

try a shrinkfile with the truncateonly option, that just chops off the unused end of the file and returns it to the OS without moving data about. However in most cases there is data right at the end of the file and you get nothing back.

Look at the growth factor of the file, if its a percentage the file might actually have grown a lot more than it needed to to accommodate actual space needs. Adjust it and try again.

Dont just rebuild everything in one go, rebuild in chunks. Only rebuild indexes that need rebuilding. There are loads of scripts to achieve that including a good basic one in BOL.

If you shrink a data file the data will be fragmented, there is NO way round that


---------------------------------------------------------------------

Post #1276308
Posted Monday, April 2, 2012 9:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:48 AM
Points: 21, Visits: 63
If you have enough space on another drive create a new database, copy/export the data to it from the existing db and arrange it in a more logical manner. If you don't have enough space you're probably a bit stuck. A general rule of thumb I use is not to let dbs grow more than 1Tb.
Post #1276694
Posted Monday, April 2, 2012 9:23 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 5,976, Visits: 12,887
I'd be looking to add either files or filegroups to the database first and moving data accordingly.

---------------------------------------------------------------------

Post #1276702
Posted Monday, April 2, 2012 9:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:48 AM
Points: 21, Visits: 63
As I said, arrange it in a more logical manner.
Post #1276703
« Prev Topic | Next Topic »

Add to briefcase «««7891011

Permissions Expand / Collapse