asked
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.
Checkpoints and the Active Portion of the Log:
http://technet.microsoft.com/en-us/library/ms189573(v=sql.105).aspx
Not being at all facetious, but here's the command: CHECKPOINT Just execute that.
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.
I mentioned that and agree, but if they're out of disk space and this is an emergency then this is an option. As always the end user needs to weigh the options and ramifications of their actions. It will depend on what the root cause of the backup failures is.
If they are in full recovery and are already running log backups then they're set for point in time recovery. I'd try to avoid messing that up as much as possible.
This would work, but with the side effects it brings, I would view it as a last resort.
6 People are following this question.
