none
SQL Server SharePoint_Config database Transaction log backup plan failed

    Question

  • As part of DR Plan for SharePoint Databases, I am doing regular backups of SQL DBs Transaction logs (http://technet.microsoft.com/en-us/library/cc811604.aspx). All the databases backups are working perfectly but SharePoint_Config database transaction log backup is failing with following error:
    Event Type:    Error
    Event Source:    MSSQLSERVER
    Event Category:    (2)
    Event ID:    18210
    Date:        20/10/2009
    Time:        11:43:03
    User:        xxxx
    Computer:    xxxxx
    Description:
    BackupMedium::ReportIoError: write failure on backup device 'E:\BACKUPS\SQL\TransactionLogs\SharePoint_Config\SharePoint_Config_backup_200910201120.trn'. Operating system error 112(error not found).

    SharePoint_Config Database Log file size has grown to 21.30 GB and free disk space on backup drive is 8.5 GB. Could this be issue why its not able to do transaction log backup?

    Please help.

    Thanks
    Pryank


    Pryank Rohilla MCTS, MCAD
    Tuesday, October 20, 2009 11:18 AM

Answers

  • Hello Pryank

    You are correct - you have run out of space for your transaaction log backup. You could back up the log to a UNC fileshare with sufficient storage, or (depending on the size of the database - ie if it is much less than 20GB) set recovery of the database to SIMPLE (which clears the log), backup the database, then set the recovery to FULL again (along with appropriate log backups)

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    • Marked as answer by pryank rohilla Wednesday, October 21, 2009 8:43 AM
    Tuesday, October 20, 2009 11:48 AM

All replies

  • Hello Pryank

    You are correct - you have run out of space for your transaaction log backup. You could back up the log to a UNC fileshare with sufficient storage, or (depending on the size of the database - ie if it is much less than 20GB) set recovery of the database to SIMPLE (which clears the log), backup the database, then set the recovery to FULL again (along with appropriate log backups)

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    • Marked as answer by pryank rohilla Wednesday, October 21, 2009 8:43 AM
    Tuesday, October 20, 2009 11:48 AM
  • Hi Ewan,
    Thanks for quick reply. Yes I looked at option of Simple recovery; for this I have another Maintenance plan to do full database backup which runs every night . This full DB backup will truncate logs automatically to remove unused virtual logs. But it seems it doesn't having much impact on log files!

    I will try to do transaction log backups on UNC filesshare and will update if that works.

    Cheers,
    Pryank
    Pryank Rohilla MCTS, MCAD
    Tuesday, October 20, 2009 12:00 PM
  • Hi Pryank

    A FULL backup does not truncate the log. To do that you must do a BACKUP LOG command.

    Or if you don't require log-level recovery, then set the database to SIMPLE permanently, which is what you require anyway from your description here.

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Tuesday, October 20, 2009 12:08 PM
  • <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    Hi Ewan,
    Configured backups on UNC drive worked. Very weird it wasn't straight forward; I had to give SQL Server Agent service account full access to UNC Drive.

    About "A FULL backup does not truncate the log"... I found info on http://technet.microsoft.com/en-us/library/ms191164.aspx..Which says "Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files". But I think I am wrong in understanding Simple Recovery. Please correct me- Simple recovery is regular full database backups...?. I have marked your earlier reply as correct answer.


    Wednesday, October 21, 2009 8:43 AM
  • Hi Pryank

    Simple recovery does not keep transaction log history - so you cannot take log backups to get point-in-time recovery. Full backups are all that is possible in this model, but is perfectly valid if this meets your requirements.

    Full recovery persists all data modifications in the transaction log, and are only removed when you perform a log backup.

    You should now be in a position to shrink your transaction log file using DBCC SHRINKFILE to an appropriate size, since I suspect your log file has grown to accommodate growth that will not recur under the simple recovery model.

    If anything is not clear, let me know.

    Ewan



    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, October 21, 2009 9:57 AM