x

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

asked May 01, 2012 at 11:20 AM in Default

jhowe gravatar image

jhowe
1.1k 49 57 60

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered May 01, 2012 at 11:24 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

sorry i'm not familiar with checkpoint... how do i use this? can you post me a code example?
May 01, 2012 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, 2012 at 12:16 PM Shawn_Melton
Not being at all facetious, but here's the command: CHECKPOINT Just execute that.
May 01, 2012 at 12:37 PM Grant Fritchey ♦♦
Ok thanks Grant i'll give it a go.
May 01, 2012 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, 2012 at 03:06 PM jhowe
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered May 01, 2012 at 12:22 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

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, 2012 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, 2012 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, 2012 at 05:20 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x580

asked: May 01, 2012 at 11:20 AM

Seen: 2968 times

Last Updated: May 01, 2012 at 05:20 PM