Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Need help quickly: LFD large

Author  Topic 

stevegravley
Starting Member

2 Posts

Posted - 2012-10-01 : 09:58:16
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

15732 Posts

Posted - 2012-10-01 : 10:18:03
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 - 2012-10-01 : 10:42:29
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

15732 Posts

Posted - 2012-10-01 : 11:50:55
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
   

- Advertisement -