SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Need help quickly: LFD large
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevegravley
Starting Member

2 Posts

Posted - 10/01/2012 :  09:58:16  Show Profile  Reply with Quote
Ladies and Gents,

I have a clustered sql 2008 R2 (server 2003) server. I came in this morning to a drive that has only 4gb left. My lfd if 106GB and my mdf is 92GB.

My database is in FULL backup mode. I was backing up the transaction logs and it was running great, but I recently setup DPM and figured I was ok to disable my backup jobs on SQL last friday.

I guess I was wrong.

Anyway, I need help getting my ldf file shrunk. I'm not sure how to do this as I don't have enough space to run a backup job.

Please help!

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 10/01/2012 :  10:18:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
Do you have a network drive you can back up your log to? Also, can you use compressed backups? (Available in Standard and Enterprise for 2008 R2)

Failing that, if you absolutely do not need to restore this log, you can back up to DISK='NUL', or change the recovery model to Simple. This will clear the log and allow you to shrink the file. If you change to simple you'll have to change back to Full after shrinking.

Once you get the file size down: 1) set up a regular transaction log backup job and 2) set a maximum file size for the transaction log file. Make it large enough to handle your largest transactions, but leave enough space on the disk. DO NOT allow it to grow to a point where you have to shrink it again, pick a size you can live with and leave it at that size.
Go to Top of Page

stevegravley
Starting Member

2 Posts

Posted - 10/01/2012 :  10:42:29  Show Profile  Reply with Quote
I tried the network drive, but when I tried to backup using the GUI, it only sees Clustered drives. I do have space on the OS disk C: (around 50 GB), but I wasn't able to access it through Management Studio. I also have plenty of space I could map to on the network if there's a way I could backup to that drive.

If these are not solid options, you're saying I could switch the recovery mode to simple, shrink it, then revert?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 10/01/2012 :  11:50:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
Backing up to a mapped network drive won't work, you have to use a UNC path. The SQL Server service account also must have access to the server and path.

Changing the recovery model is indeed an option, but you will break your backup chain (no point-in-time recovery between full backups).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000