December 19, 2006 at 5:54 pm
Is it considered good practice to run dbcc shrinkfile or shrinkdatabase on a regular basis? I have a db that is transaction heavy and although the backup log truncates the logical portion of the log file, the physical size of the file will often balloon-up and get out of control. I guess I'm wondering if over use of these 2 'dbcc' functions has detrimental effects on the data or the functionality of the database itself. Are most people adding that to their scheduled maintenance tasks?
December 19, 2006 at 6:33 pm
Right-click your db in EM and choose properties. In the Options tab, Under Settings, CHECK the Auto Srink option.
This will automatically shrink the database leaving it with 25% free space.
If you preriodically (scheduled) back up the log, its physical size ballooning up should be monitored. This is not an appropriate behavior. There must be a procedure/job that's causing this. Look into it.
Backing up the log will not shrink the logfile size but it would truncate the log. Why would you want to shrink it? If you normally backup the log, there should be no reason to shrink the log file.
December 19, 2006 at 7:40 pm
The size of your data and log files should be set so that they would not normally need to grow during prime time. If they need to grow, the transaction causing the growth will stop whilst the file grows. This may impact other transactions as well depending on record locks etc. This will impact performance.
Shrinking the log back to a relatively small size is probably a bad call.
This does not mean the DBCC Shrinkfile/Database should not be used. It just means that you should shrink to a size that is determined by the actual usage of the database.
As for whether these commands have a detrimental affect - not really. The certainly won't cause corruption or loss of data (if that is what you are concerned about)
Cheers
Stephen
December 20, 2006 at 6:17 am
never never never ever ever ever enable autoshrink on a production database and probably never on any database.
You should attempt to balance the sizes of the mdf and ldf files so that they neither grow or shrink. growth and shrinking gives rise to ntfs fragmentation which degrades performance.
( just have a view with defrag manager and see how your files are fragmented if you think shrink has no detrimental effect - assuming you're letting your databases grow and then shrinking them )
Shrinking a mdf will lead to internal fragmentation of the data structures and degrade performance.
I have a server in a managed data centre and they thought a good idea was to have a log shrink after each tran log backup - result 14mb log file in 8 fragments - classic!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 20, 2006 at 6:46 am
What is the best way to control the size of the LDF? Again, we're talking physical size here, not logical. Physical size will fluctuate from 100MB to 5GB, but the logical size will usually remain at 100-200MB on average.
December 20, 2006 at 8:28 am
things like data loads and index rebuilds can bloat a tran log - techniques to avoid this include:-
I've used all these techniques with varying success. Sometimes, say for reindexing, you may need to batch rebuilds. More obvious tricks on data loads are to truncate rather than drop tables or delete data.
The most important thing is that if you tidy up tran log sizes to run a defarg on ntfs afterwards.
To be honest if, say, my nightly jobs require a consistant t log of 3 Gb then so be it, I leave it at 3gb.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 20, 2006 at 2:19 pm
I agree with Colin, do not set autoshrink. When a database is shrinking, you will get an error, log file is full, if someone sends a large transaction at this time. To better control the log file size and obtain the best performance, you can do as Colin described.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply