MS SQL 2008 shrinking database and log file disk space usage

Hey folks,

I am trying to set up a proper regular routine for shrinking database files. The main reason I want the shrinking to be put in place is that my log file usage is ridiculously low (e.g. 0.5%), means the log file uses gigabytes on the disk whereas stores only megabytes of the log data only.

This is my configuration:
SQL Server 2008 64-bit
Database compatibility level 80 (SQL Server 2000)
Recovery mode = Full

To be able to effectively use SHRINKDATABASE or SHRINKFILE I need to switch the database to simple recovery mode. After performing dbcc SHRINKDATABASE('mydbname', TRUNCATEONLY), the log file size decreases from 4GB to couple MB, well so far it works the way I expect it work. But now it starts to get interesting. I switch the db back to full recovery and as soon as a first user logs in and starts creating DML transactions the log file grows back to the size of gigs, i.e. whilst the log file stores a few transaction logs only its size is enormous.

Any ideas of how to achieve an optimal log file disk space usage?

Thanks in advance for sharing your thoughts on this!
garsmiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zaheer IqbalTechnical Assurance & ImplementationCommented:
Hi Garsmi

Any reason why you are using sql server 2000 level 80 ?

What is this DB used for ?
Is it an in house application or off the shelf..?

Are the log files getting truncated after a successful backup ?
0
Zaheer IqbalTechnical Assurance & ImplementationCommented:
0
David ToddSenior DBACommented:
Hi,

When switching back to full recovery model, the very next thing you are supposed to do is take a full database backup.

What is the size of the database? Given the standard answer for most DBA questions is it depends, and the depends for your log file of 4GB is what is the database size?

I suggest that the repeated shrinking and growth is bad for performance - it fragments the physical log files, it creates masses of virtual log files (vlfs), every time the file grows it needs to be zeroed or initialised. (Instant file initialisation only applies to data files, not log files)

If you say that your database size is 100GB then a 4 GB log file is really small. On the other hand, if the data file is 1GB then I agree that 4GB is way too much too big.

If the log file is immediately growing back to 4GB then you should check out what the growth increment is set to. And if 4GB is the sweet spot it sits at then let it be. If as just above 4GB is out of proportion to the database size, then you need to investigate why that is happening - what features are you using that could contribute to that size? Do you have a lot of varchar( max ) or other large objects being updated?
 Are you taking transaction log backups as well? How often? How big are they?

Regards
  David
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

garsmiAuthor Commented:
Thank you for the answers.

I'll try to sum up my comments:
# CL 80 has to be kept, using a higher CL is a "no go", don't think it this much influences the shrinking and / or log file size
# it's a transactional (back-end) database, DML statements being the most frequently occuring types of transactions, avg rate of txs = 10 per minute.
# the total size of the db is 14GB, thus, the 4GB log seems rather big to me
# am aware of the fragmentation when running the shrink too often, I want to run it once a month
# the log file is set to increment by 10%
# no large objects (data types) used
# transaction log backups are done hourly, full db backup daily
# the log files do not ge truncated after the backup is done
# I forgot to mention the monthly shrink being setup the way I described had worked before I migrated the db from Sql Server 2005 to 2008 whilst keeping the CL at 80.

Two questions:
# When do I need to do the post-shrink full db backup? Before or after the db is set back to full recovery mode? Just wondering if this anyhow matters..
# Is there a way to do an effective db shrink w/o putting the db to simple recovery mode beforehand?
0
Anthony PerkinsCommented:
I am trying to set up a proper regular routine for shrinking database files.
Short answer: Don't.

Long answer: Feel free to do it if you have no concern for performance.

Real answer: Change to Simple Recovery Model.  You cannot do point-in-time restores anyhow.  So in your case there is no benefit to using Full-Recovery Model.
0
Anthony PerkinsCommented:
the log files do not ge truncated after the backup is done
Sure they do.  It is just that your definition of "truncated" does not match the definition covered in SQL Server's BOL.

# I forgot to mention the monthly shrink being setup the way I described had worked before I migrated the db from Sql Server 2005 to 2008 whilst keeping the CL at 80.
Correct.  They thankfully got rid of the TRUNCATE_ONLY keyword.  It got too many people into trouble (see Brent's blog on the subject  How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008, R2, 2012)
0
David ToddSenior DBACommented:
Hi,

The real kicker here is that 4GB is not an overly large transaction log for a 14GB database.

I suggest that instead of 10% growth that you set initial size to 1GB with 100MB growth.

If you want to keep the transaction log smaller, then do more frequent transaction log backups.

One way to keep track of log usage is to create a Management Data Warehouse, and turn on the data collection. Its possible that the growth is for some monthly process. If you can identify that process, then either refine the process to use less logging (maybe it will take longer to run) or more frequent log backups at that time.

Note that this logging may take as much as 5-10GB per database per month. From memory, with the default settings, it settled on 15GB for monitoring two instances for me. Your mileage may be very different. Its hard to justify nearly 10GB of monitoring data to find and 'fix' a 4GB log.

All that to say: The large transaction log is a symptom, not the problem in this case. It doesn't seem to be causing any real issues, other than you perceive it to be too large, and I suggest that its not too large.

Regards
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garsmiAuthor Commented:
thanks again to all for your valuable comments.
My main concern here is that when I look at my log file, its size is too big to store so small amount of logs. When I run a script to calculate it's usage, i.e. comparing the physical size of logs to the space the log file occupies on HDD I end up with usage of less than 1%
I don't really care about the way of decreasing the log file size, just don't want to store a shot of vodka in a barrel. Why would I need to consume a disk space for nothing?
0
David ToddSenior DBACommented:
Hi,

Just a thought - if you are having to shrink the transaction log because you are not doing transaction log backups, then switch to simple recovery model.

Its a standard good practice to change model to simple recovery mode, so new databases are in simple recovery.

Regards
  David
0
Anthony PerkinsCommented:
David,

if you are having to shrink the transaction log because you are not doing transaction log backups, then switch to simple recovery model.
I had suggested that unfortunately it appears to have fallen on deaf ears.  I have yet to hear what they plan to get out of Full Recovery Model, if they think for a minute it is point-in-time restore, they are seriously mistaken.  I would challenge them to do a point-in-time restore after truncating the log.

The rule of thumb should be:
If you are unable/unwilling to do frequent transaction log backups to keep the Transaction Log from growing then you should be setting the Recovery Model to Simple and optionally doing Differential Backups.
0
David ToddSenior DBACommented:
Hi AC,

Completely agree ...

Regards
  David
0
garsmiAuthor Commented:
thanks again for all your comments
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.