If you have a SQL server with a full hard drive there’s a pretty good chance that the drive was filled with SQL log files (.ldf files). You have to backup the Transaction logs before you can shrink the log file; the problem is that from SQL Management Studio you can typically only backup to local hard drives. If all your local hard drives are full – this is a problem! SQL Management Studio doesn’t backup to mapped drives either.
Note: A database needs to be using the Full recovery model for this article to be relevant.
You could mess around with scripting a backup or changing the account that SQL Agent uses, but the quickest easiest solution is to backup to a UNC path: \\Server\Share
Start the Transaction Log Backup from within SQL Management Studio. Choose to Backup the database, select ‘Transaction Log’ for Backup Type, then Add a Disk to the Destination: Browsing to the network share won’t work, you need to type in the entire path: \\server\share\filename.trn
Be sure and ‘Remove’ the other backup destination.
Click on the Options page, then make sure ‘Truncate the transaction log’ is selected.
Click OK to start the transaction log backup.
This first backup should be about the same size as the ldf file. Now do another Transaction Log Backup of the database – save it to a different .trn file. This file should be considerably smaller than the first backup. At this point you can manually shrink the (.ldf) log file:
Right click on the database and choose Tasks > Shrink > Files
Select Log as the File Type – you may need to choose which File Name if there are more than one.
Make sure ‘Release unused space’ is listed.
Click OK to shrink the file. Look at the ldf file in Windows Explorer to verify that the file actually shrunk.
To prevent the hard drive from filling up again, you have two choices:
- Change the database’s recovery model to Simple. You will no longer be able to restore the database to a specific point in time, but the log files won’t grow out of control as long as regular database backups are performed.
- Schedule Transaction Log backups of this database to occur multiple times each day.
Terrapin Consulting is your best choice for top-notch Dynamics GP Support.
full hard drive | hard drive full | no space | ldf | log file | transaction log | log backup | can’t save | won’t shrink
|Terrapin Consulting is a premier provider of Dynamics GP Support. We can support you from anywhere in the USA & Canada.||At Terrapin Consulting there’ll be a veteran CPA keeping an eye on your Dynamics GP system.|
|www.terrcon.com / 888-368-6670 / firstname.lastname@example.org|