best practice for dealing with full transaction log and disk
Hi all what is the best thing to do when you have a backup job that fails to run and you end up with a big tran log and full disk? How do you recover from this situation without increasing disk space? As far as i know SQL needs space to create a backup, how do i force it to release space? is it better to shrink files first, or use shrink database, then backup? Update : I've pasted error below. SQL Backup failed with exit code: 5145 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed. It is a sharepoint database can I just dump the transaction logs for all of them i.e. will sharepoint still function in simple recovery mode? So something like this? USE CHECKPOINT BACKUP LOG WITH TRUNCATE_ONLY DBCC SHRINKFILE GO
Shrinking is no good until you fix the issue with the backups. I suggest running CHECKPOINT manually and then running a log backup. Assuming that works successfully, then you should probably shrink the log file, not the database. If that doesn't work, you can try attaching an additional file to the database for the logs and then run a checkpoint followed by a log backup. If that works, then you can shrink the file. If that doesn't work, then you have to stop the database, detach it, delete the log, then reattach it without the log file. It can be recreated. Shrinking the log in an emergency like this is OK, but shrinking databases or database files is usually considered very problematic. Stick to shrinking the log file.
One alternative is to change the recovery mode of the database to simple. This will truncate the log and then you can shrink it back to its more natural size. Change the recovery mode back to full and take a full backup of the database. This solution breaks any transaction log chains you had and that's the main reason for the immediate backup. Of course if you are using replication or database mirroring this may not be an option. Investigate the reasons for the job failures and maybe set up an alert for future occurrences.