INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Log Files running out of disk space

Log Files running out of disk space

(OP)
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...

RE: Log Files running out of disk space

Try this:

CODE --> T-SQL

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

RE: Log Files running out of disk space

(OP)
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?

RE: Log Files running out of disk space

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.

RE: Log Files running out of disk space

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: What should I know before I post?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close