Reducing the Data and Log file Size in SQL SERVER 2005

Question

Reducing the Data and Log file Size in SQL SERVER 2005

nikhil.verma

SSCrazy

Points: 2603
More actions
January 11, 2010 at 12:47 pm

#137959

Hello
In my Production Envrionment , for most of the databases ( SQL SERVER 2005) the recovery model is set to SIMPLE. Our Monthly analysis showed that database backup sizes are increasing continously . Can you please suggest the way to shirnk the data and log files in this case before starting the backup. Or can you provide me with some scripts/ Steps which can be helpful in reducing the size.
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

If it's a production environment, I would think that the data is increasing regularly, thus making your backup size increase. Normal & expected.

Shrinking the data file will not help your backup size, but will cause fragmentation problems in your database.

nikhil.verma SSCrazy Points: 2603 More actions · Answer 2

Is it advisable to schedule the DBCC SHRINKFILE before the backup happens. This will help in reducing the log file size and to release some space to OS as Truncating will not release to OS

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Search on Shrink Database and you will get lots of information about why NOT to shrink your database.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

nikhil.verma (1/11/2010)
Is it advisable to schedule the DBCC SHRINKFILE before the backup happens. This will help in reducing the log file size and to release some space to OS as Truncating will not release to OS

NO - you do not want to ever schedule a process to shrink your database files. Shrinking your database files has all kinds of problems associated with it. For example, shrinking your data file is going to cause index fragmentation which will cause your system to perform less efficiently.

Then, of course - the constance shrinking/growing of the file will cause file fragmentation which will cause performance problems also.

Shrinking a log file will just cause you to have a slower system as the log file needs to grow again to handle the largest transactions in your database.

Why do you think you need to release space back to the OS?

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

nikhil.verma (1/11/2010)
Hello
In my Production Envrionment , for most of the databases ( SQL SERVER 2005) the recovery model is set to SIMPLE. Our Monthly analysis showed that database backup sizes are increasing continously . Can you please suggest the way to shirnk the data and log files in this case before starting the backup. Or can you provide me with some scripts/ Steps which can be helpful in reducing the size.
Thanks in advance.

Can you not relate the growth to the amount of data being input to the database?

Shrinking data files leads to fragmentation and poor performance.

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 6

If shrinking datbase/logfile causes index fragmentation and poor performance. Then why that option is there in SQL Sever.

If you don't shrink logfile, lets say logfile grows rapidly and there is no free space on disk, don't you think this effect to database operations? how you resolve it

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

A good place to start would be with the last article I reference in my signature block regarding Managing Transaction Logs.

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

laddu4700 (1/11/2010)
If shrinking datbase/logfile causes index fragmentation and poor performance. Then why that option is there in SQL Sever.
If you don't shrink logfile, lets say logfile grows rapidly and there is no free space on disk, don't you think this effect to database operations? how you resolve it

There are a few situations that may require shrinking, such as a need for free space, so the option is there. But it is not recommended in most cases.

The OP seems concerned about the size of the backup. Backup size is related to the amount of data, so shrinking (removing unused space) will not be of any benefit.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 9

I agree with homebrew01, by shrinking the database log file, you will not reduce the size of your backup. It is normal that a database will grow, so it is normal that the size of you backup grow. I will recommend you a webcast from Kimberly Tripp called "Creating a recoverable database". http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032278586%5B/url%5D. Take the time to check it out it is very interesting.

My blog