x

SQL Database Shrink

Hi Friends,

My SQL Server 2008 R2 database size is huge. Is it safe to shrink?

Regards, Melwin Lawrance
more ▼

asked Sep 19 '12 at 11:30 AM in Default

melwin gravatar image

melwin
50 2 2 2

What are the sizes and utilization of your files - mdf/ldf?
Sep 20 '12 at 12:32 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Safe to shrink? Sure. Wise to shrink? Usually not. There are two issues with shrinking databases. First, and probably most important, it leads to serious fragmentation of your data files. Because of this, it's very strongly recommended against shrinking a database. Second, if the database has grown to a certain size due to the data being inserted into it, it probably needs to be that size in order to maintain your systems. Shrinking it is just likely to lead to the database growing again.

Are there circumstances where shrinking might make sense? Sure. Let's say you're in full recovery and for some reason log backups failed and your log file grew outrageously, then a one-time shrink on that file makes sense. Or, a user made a mistake and created a million rows or something on the database that you've subsequently cleaned up, then shrinking might make sense. But unless you're dealing with this type of egregious example, no, I would not recommend shrinking the database.
more ▼

answered Sep 19 '12 at 12:10 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

Thanks Grant for your response. I have a maintenance plan running everyday night at 11 which has the below mentioned tasks are running in this order.
1. Check DB Integrity
2. Back Up Db Full
3. Back up Db transaction log
4. Shrink Db
5. Rebuild index
6. Update statistics
7. Reorganize Index
8. Clean up history
9. maintenace clean up task ( which will dlete the .bak and .trn files which are older than 3 days.

It was running with out any problems for the last 3 months, suddenly the it started failing due to space issues on the drive. if i check the maintenace history it failing in Rebuild task due to less space.

Now my db and log files are in huge size. I have shrinked the log files couple of times for to get some space, but i am worried with the size of the db, because it may get fail tonight due to the big size of the db.

what should i do now? Please advice.

Regards,

Melwin
Sep 19 '12 at 12:22 PM melwin
Just one question - why are you doing those jobs all at once?
Sep 19 '12 at 12:30 PM ThomasRushton ♦

If your database is in FULL recovery, but you're only running log backups once a day, that's your first problem. Log backups are meant to be run frequently to provide a recovery mechanism to a point in time, not simply to act as a flush for committed transactions. I link to a number of articles on this, and other backup topics, in this blog post: http://www.scarydba.com/2012/01/11/database-backups-things-you-need-to-do-now/

Getting your log backups under control will help you deal with log size.

Shrinking your database every single night for months, you have seriously fragmented files. All you're getting done is cleaning out a little free space at the end of the files and then reallocating it again during the day as more data gets added. I'd stop now. Whatever database size you have, that's what you have. If you need to store 500gb of data, you can't store less. If you need 2tb, you can't store less. So, if the database is getting big, you need more drive space or more drives. If the log is getting big, you need to adjust the frequency of the log backups. And stop shrinking your database over & over.

More...
Sep 19 '12 at 12:40 PM Grant Fritchey ♦♦

I can't see what you're running, but it's worth noting. If you're rebuilding all indexes, then the next two steps, update statistics and reorganize indexes are wasted. Updating the stats, unless you tell it otherwise is a sampled operation, but rebuilding the index is a scanned operation, meaning, it's much more accurate. So by updating the stats after rebulding the indexes, you're making the stats worse. Reorganize is just a way to defrag lightly fragmented indexes by logically rearranging them. But if that index has just been rebuilt, there's nothing for this step to do.

There are sophisticated processes that can filter which indexes get rebuilt and which get regorged, but you need to be careful about stats updates around these processes for reasons outlined above.
Sep 19 '12 at 12:42 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Rebuild and Reorg may grow your database files and/or log files (log growth in reorg and datafile growth due to the fill factor leaving some empty space on each page and thus requiring more pages). As others have said already - I'd advice against shrinking the database regularly. But if you need to shrink your files, I'd do that as the very last step of the maintenance. Otherwise you'll shrink your files, followed by a maintenance task which grows the file(s) again.
more ▼

answered Sep 20 '12 at 01:21 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x235
x35

asked: Sep 19 '12 at 11:30 AM

Seen: 917 times

Last Updated: Sep 20 '12 at 01:21 PM