Post reply

Can we initiate a t-log backup of the Database when its t-log is full?

  • September 3, 2009 at 2:00 am

    #385305

    Hi,

    I need some help regarding the T-log backup process. Yesterday, I was talking with one of my colleague at work regarding T-log backup. The situation was something like this:

    There was a SQL Server 2005 + SP3 instance running on a Windows 2003 SP2 box. Most of the DBs are configured with Full recovery model, with a daily full backup along with 4 hours log backup. We found that for one DB (Say A), the t-log backup was failed. I have verified that the disk is having enough disk space and the T-log has 500 MB of free space.

    My question is, if the T-log of database 'A' is full, then can we initiate a t-log backup of the Database?

    I understand that, we can initiate a t-log backup, but need to clarify from other SQL expert in the community. Also, if anyone can guide me towards any documents supporting that, will be of great help.

    Thanks to everyone.:-)

    Thanks & Regards,
    Sudeepta.
    http://twitter.com/skganguly

  • September 3, 2009 at 2:37 am

    #1048580

    Sudeepta (9/3/2009)

    We found that for one DB (Say A), the t-log backup was failed. I have verified that the disk is having enough disk space and the T-log has 500 MB of free space.

    Did you check the job history and the SQL error log? Usually there'll be a reason for the failure in one of them at least

    My question is, if the T-log of database 'A' is full, then can we initiate a t-log backup of the Database?

    Usually, yes. The other thing to remember is that without a log backup the log won't suddenly gain free space unless it grows. So if the log had 500MB of free space, the failure's not likely to be due to a full log

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 3, 2009 at 3:18 am

    #1048600

    Thanks for your response, Gila. Unfortunately, the Job History and SQL Server Error log doesn't reflect much. The only information it gave that the backup log is failed with the error state number, which reflects an hot fix released by MS upon search.

    To my other question, if the t-log is completely full, then it is still possible to initiate a log backup of a user database? And during a log backup, is SQL Server writes the entries of log backup details in the userdb log or msdb log?

    I am a bit confused in this part. It will be great if you can write a post on your blog / SSC regarding the same.

    Thanks a lot Gil, for your answer.

    Thanks in advance.:-)

    Thanks & Regards,
    Sudeepta.
    http://twitter.com/skganguly

  • September 3, 2009 at 3:53 am

    #1048613

    Sudeepta (9/3/2009)

    Thanks for your response, Gila. Unfortunately, the Job History and SQL Server Error log doesn't reflect much. The only information it gave that the backup log is failed with the error state number, which reflects an hot fix released by MS upon search.

    Can you post the details?

    Does the hotfix apply to the version you have?

    To my other question, if the t-log is completely full, then it is still possible to initiate a log backup of a user database?

    As I said, usually yes. Depends just how full the log really is. Sometimes it's necessary to add a second log file or move the existing one to get a biot more space. Depends just how full it really is. Remember not all log records are the same size.

    And during a log backup, is SQL Server writes the entries of log backup details in the userdb log or msdb log?

    It writes the details of the backup into MSDB into one of the backup tables. Also marks that there was a log backup into the transaction log that it's backing up.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 3, 2009 at 4:09 am

    #1048626

    Thanks a lot, Gil. That will clarify the doubt. I will post the other details after going through the logs.

    Thanks & Regards,
    Sudeepta.
    http://twitter.com/skganguly

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply