Video Screencast Help

BE2010R3 - SharePoint SQL Log Backup Not Truncating?

Created: 15 Oct 2012 • Updated: 15 Oct 2012 | 6 comments
This issue has been solved. See solution.

I set up a backup job to backup the SQL Logs for my SharePoint environment through the SharePoint agent.  I set it up to do "Log - Back up and truncate transaction log" and the backup job completed successfully.  However, when I checked my log sizes, they're still ridicuosly huge (I have one that's 256GB!!).  How do I check to find out if it is attempting a truncate and why it's not truncating?

Thanks in advance.

Comments 6 CommentsJump to latest comment

Jaydeep S's picture

Backup Exec SharePoint or SQL Agent will truncate the logs however, will not shrink the log file. You will need to use SQL utilites like DBCC Shrinkfile to get the log file size down. You could run this with a SQL maintainance job to shrink the database following the Backup Exec Log backup (typically last from the week as in just before the Backup Exec Full backup)

ArulPrasad's picture

Running a Truncate will remove the transaction that is added to the database from the Physical file.

Now the Log File Size remains the same with Empty pages. In order to avoid such situation

1. Run a frequent Log Backup so the Pages would be cleared frequently.

i.e if the log file size is 10 GB , your Database transaction for an hour is 9 GB , run a Hourly log backup so the transaction log maintains the size without growing.

for now you can perform the Shrink database to remove the empty pages from the Log file and follow the best practices for SQL database backups.


Nuri Inuki's picture

The shrinks aren't working. SQL complaining about the server using the end of the file, so it can't move it.  Oh yeaaah.  Any ideas?

Jaydeep S's picture

It would help if you could post te entire error message. Here is Microsft technet page about how Shrinking the log file works, might be of some help.

Edit - Also this - 'Factors that can delay log truncation

Nuri Inuki's picture

Full error message is:

Cannot shrink log file 2 (db_log) because the logical log file located at the end of the file is in use.

The log file is 99% free.  Not sure if I have to wait because maybe data was written to it recently or something...?

Jaydeep S's picture

Looks like there is some help on msdn forum about this

I am not sure if this solution will work for SharePoint databases as well. I will post it anyways -