6

I have a database which grow to 20 GB, after archiving some data, the real size of tables is just 5GB. Backup script does save a copy of the backup to a different location. Moving 20GB or 5Gb does make a difference, so I would like to reduce the physical size, but everywhere I read to do not shrink if I don't want to affect performance.

But it this case of (periodically/quarterly/annually) archiving, is it still recommended not to shrink?

  • 2
    "Backup script does save a copy of the backup to a different location" backup should be 5GB, not 20GB. So it will make no difference. – Remus Rusanu Feb 9 '15 at 12:11
  • To add to Remus his answer, you should review this link. The gist is that backups only contain actual data, not the unallocated. – Reaces Feb 9 '15 at 12:40
7

What you are seeing on net is mostly a copied advise where people actually want to say that "please don't make shrinking data file or log file a daily routine operation". Had it been so bad Microsoft would have removed it but it is still there and even most experienced DBA's and developer use it but they are aware about the after affects so they know what to do after. Only shrinking can give you back free space

Yes of course you can shrink data and log file because you want to reclaim space and if reclaiming space is very much required. If you see Paul's article he has given a method like

The method I like to recommend is as follows:

  • Create a new filegroup

  • Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time

  • Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

You can use it if it suits you. Since you are aware shrinking is bad activity and would do it once in a year may be twice this is actually not bad. Shrinking do causes logical fragmentation so dont forget to rebuild indexes after shrinking.

Backup script does save a copy of the backup to a different location. Moving 20GB or 5Gb does make a difference

Backup only includes data and few amount of transaction log(if required) to bring back database in consistent state after restore. So again backup would be around 5 G not 20G but yes when you restore it back it would take 20 G space

PS: I am not supporter of shrinking the database or database files but in extreme scenario and knowing the repercussions you can do it.

| improve this answer | |
3

Shrinking database log file is necessary if log has grown out of control whether after a one-time data delete or where recovery model of the database is FULL and no log backups have been carried out.

To reverse the effect of a one-time operation that caused unexpected log file growth are convincing reasons for shrinking a database\ database file.

Shrinking database is not a recommended process and should not be part of any regular maintenance operation but in any case you need to do so, you must REBUILD\ REORGANIZE indexes of database along with statistics update.

| improve this answer | |
  • 1
    Statistics updates are only needed for reorganize - they happen automatically as part of a rebuild, so performing them again is just wasteful (and can lead to worse stats anyway if you don't use full scan). Also note that shrinking a file and then running a rebuild/reorganize on all the indexes will just make the file grow again, since space needs to be made to facilitate that data movement. So in a lot of cases you'll be right back where you started. – Aaron Bertrand Feb 9 '15 at 14:57
1

Yes, it is still recommended not to shrink. If your data file goes up to 20 GB every quarter, then think about this: after you've shrunk it to 5 GB, you've freed up an extra 15 GB on the drive. What are you going to use that space for? Are you going to lease it out to someone temporarily, then kick them off the drive when your database starts to grow again?

Shrinking just to grow again is extremely disruptive. Not only are you potentially fragmenting all of your existing data to try and squeeze it into more space (and the impact can vary depending on exactly how you are shrinking), but every time the file has to grow, all transactional activity on that database must stop and wait for that operation to complete. This is particularly bad with the default settings where files are set to grow by a measly 1MB at a time or - even worse - 10% at a time.

Leave your data file 20 GB. You've archived data and cleared out some space? Great. Now you have plenty of room in your data file to accommodate growth until the next time you archive data. The size of the data file itself has no bearing whatsoever on the size of the backup file, since a backup only backs up used pages, not free space (unless, for some reason, you are "backing up" the MDF file rather than taking actual SQL Server backups).

| improve this answer | |
0

Recommendation would always be not to Shrink:

Because by going that option of shrinking the database there would be a request to SQL server to release the unused space which would cause index fragmentation and ultimately hamper the performance.

If you're Database is a growing database it would still grow and shrinking is not the solution for that. Also look for the transactional log file and its growth.Use below artcile which will help in optimising the log file:

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

first preference would be if the budget fits in go for the space and let DB have the free space it needs rather than shrinking and creating a space on disk.

See below which also clarifies:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.