• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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.
0
Ed
Asked:
Ed
3 Solutions
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now