Log Files running out of disk space

[GCL2007]

On SQL Server 2008 R2, am running into a problem with Transaction log files getting too large and am running out of space. For example, I have log files of 96000000 KB, and two at 50000000 KB. We tried changing recovery model to simple, but that didn't help us recover space. What are the steps I need to do to free up space.. I think this is a basic problem, but I come from an Oracle background... Thank you! If someone could tell me the steps, I would greatly appreciate it before my disk fills up...

 

[TheBugSlayer]

Try this:

USE [master]
GO
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE WITH NO_WAIT
USE YourDB 
DBCC SHRINKFILE(YourDB_log)
ALTER DATABASE [YourDB] SET RECOVERY FULL WITH NO_WAIT
GO

Then reduce your log minimum size if necessary.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[GCL2007]

Thanks.. Database should have already been changed to Simple recovery in SQL Server Management Studio, so I'm guessing I skip that Alter? and not sure if they will want to set it back to full. I assume I could just skip those two Alter lines?

 

[yelworcm]

you can skip the alter lines, but you will be running in the equivalent of NOARCHIVELOG mode. you will still be able to make hot backups, but you will not be able to back up logs, or recover to a point in time. Also, you should note that the log files will expand to the size of the largest transaction 9or set of concurrent transactions), so have a look at what ETL processes you may have pumping data into these databases, and see if you can get them to commit smaller batches.

 

[SQLBill]

Also, check what size you have set your data and log files to grow by. You may have them growing by huge amounts. So lets say you set the log file at 1000 MB, but the autogrow is set at 20000 MB, that means everytime the log file has to grow it will grow 20 GB even if you don't need that space.

Another thing with log files, is what kind of activity are you having on the database? You could be having a process dump lots of data that takes up space and then gets 'cleaned up', leaving extra space. We have a process that does that on one of our databases, so we set the log file for a size that is big enough for the initial data dump and just leave it there. If space is an issue, you could set up a job to shrink the log file nightly. But with log files it's not really that big of a deal to just leave them at a large size...performance-wise it can be better since the file doesn't have to keep growing as data is being entered.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875