best practice for dealing with full transaction log and disk

Question

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 <database>
CHECKPOINT
BACKUP LOG <mylogfile> WITH TRUNCATE_ONLY
DBCC SHRINKFILE <mylogfile>
GO

Answer 1

0

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.

answered May 01 '12 at 11:24 AM

Grant Fritchey ♦♦
62.7k ● 12 ● 20 ● 66

sorry i'm not familiar with checkpoint... how do i use this? can you post me a code example?

May 01 '12 at 11:48 AM jhowe

Checkpoints and the Active Portion of the Log: http://technet.microsoft.com/en-us/library/ms189573(v=sql.105).aspx

May 01 '12 at 12:16 PM Shawn_Melton

Not being at all facetious, but here's the command: CHECKPOINT Just execute that.

May 01 '12 at 12:37 PM Grant Fritchey ♦♦

Ok thanks Grant i'll give it a go.

May 01 '12 at 12:39 PM jhowe

Thanks Grant, that seems to be all good now and our automated backup job is still failing i think from a different issue. I will need to investigate that now thanks for your help.

May 01 '12 at 03:06 PM jhowe

(comments are locked)

Answer 2

0

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.

answered May 01 '12 at 12:22 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

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.

May 01 '12 at 12:38 PM Grant Fritchey ♦♦

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.

May 01 '12 at 01:25 PM Blackhawk-17

This would work, but with the side effects it brings, I would view it as a last resort.

May 01 '12 at 05:20 PM TimothyAWiseman

(comments are locked)

best practice for dealing with full transaction log and disk

Follow this question

Related Questions