Logfile too big sql 2008r2

Hi

My logfile on my sql reporting server is 32 gig and causing other things to fail due to lack of disk space,...


What's the best way to approach this as it does need to be shrank very soon but I'm worried about having to do this.
EdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Lee SavidgeCommented:
The backup maintenance plan probably doesn't back the log file up. If you use full recovery mode in SQL you MUST back up the log file as well otherwise it will simply fill up your disk. Once backed up you can shrink it but the backup will truncate it anyway.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Lee's right but why would you have a reporting database with full recovery model? Can't you set it to simple recovery model?
0
Steve WalesSenior Database AdministratorCommented:
Have a read of this article which explains the "how" to do it:

http://www.experts-exchange.com/articles/11077/How-to-shrink-a-bloated-log-file.html

Not mentioned in the article (I need to fix that one day):  
- When running the shrink you will need to likely run the shrink command twice to see full gains because of active transactions running the first time.
- If it's purely a reporting database and the ability to perform point in time recovery is not needed, you could just change to simple recovery model and perform the shrink then - but ONLY do this if you don't need the ability to recover to any point in time.

If you're in Full Recovery mode backup of the logfile is required or else the issue will happen again (although Vitor makes a good point).

Lee may have used terminology to confuse...

>>Once backed up you can shrink it but the backup will truncate it anyway.

"Truncating" the log doesn't free disk space, it just marks it as available to be re-used.  A log is truncated after it is backed up, but to reclaim disk space, you actually need to shrink it after you've backed up the log.
0
Scott PletcherSenior DBACommented:
Run these commands from SQL Server.  I've assumed the default names for the SSRS databases; you'll have to change them if your particular SSRS install used a different name for these databases.

You can put the main ReportServer db back to full recovery after shrinking it,  but I wouldn't recommend it.  That db doesn't really need to be in full recovery mode.


ALTER DATABASE ReportServer SET RECOVERY SIMPLE
ALTER DATABASE ReportServerTempDB SET RECOVERY SIMPLE

USE ReportServer
DBCC SHRINKFILE ( 2 )
ALTER DATABASE ReportServer MODIFY FILE ( NAME = ReportServer_Log, SIZE = 2GB )
ALTER DATABASE ReportServer MODIFY FILE ( NAME = ReportServer_Log, SIZE = 4GB )

USE ReportServerTempDB
DBCC SHRINKFILE ( 2 )
ALTER DATABASE ReportServerTempDB MODIFY FILE ( NAME = ReportServerTempDB_Log, SIZE = 2GB )
ALTER DATABASE ReportServerTempDB MODIFY FILE ( NAME = ReportServerTempDB_Log, SIZE = 4GB )

--ALTER DATABASE ReportServer SET RECOVERY FULL --possible but not recommended
--if you do this, be sure immediately afterward to take a full backup of this db.
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
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 2008

From novice to tech pro — start learning today.