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.
| 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. |
 |
|
|
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? |
|
|
|
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). |
|
|
|
|
|
|
|
|
Need help quickly: LFD large