locked
Backup database and log files RRS feed

  • Question

  • Hello,

    I setup a new maintenance plan in my SQL 2008 R2. I selected the databases for the application. Besides, there are some other databases as master, ... Should I include these databases as well?

    I see the initial size of a log file is setup. I looked at the size of the log file and it really grows a lot larger. I think about the SHRINKFILE (for the log). I can use the command as "DBCC SHRINKFILE(DataBaseName_Log,0)". I wonder that should I reduce the log size to a size less than the initial setting?; For example, the initial size is 10000, can I reduce it to 1000?

    Thanks


    Madison
    Tuesday, September 20, 2011 5:12 PM

Answers

  • Hello,

    I setup a new maintenance plan in my SQL 2008 R2. I selected the databases for the application. Besides, there are some other databases as master, ... Should I include these databases as well?

    I see the initial size of a log file is setup. I looked at the size of the log file and it really grows a lot larger. I think about the SHRINKFILE (for the log). I can use the command as "DBCC SHRINKFILE(DataBaseName_Log,0)". I wonder that should I reduce the log size to a size less than the initial setting?; For example, the initial size is 10000, can I reduce it to 1000?

    Thanks


    Madison

    1. Master database backup would be small in size so it would not cause any harm if you add in Maintenance Plan. Normally DBAs keep backup using retantion period so taking periodic backup would not be an issue.

    2. For file growth you should refer http://support.microsoft.com/kb/317375 as it has common caused mentioned on transaction log growth. Most common cause based on my decade of experience of SQL Server is not taking t-log backup for database in full recovery model.

    Feel free to ask more questions or if you need any clarification.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Peja Tao Thursday, September 22, 2011 7:01 AM
    • Marked as answer by Peja Tao Thursday, September 22, 2011 7:02 AM
    Tuesday, September 20, 2011 5:38 PM
  • Hello,

    For your situation below are the recommendations.

    1. Assuming that your maintenance plan deals with database backups and in that case you need to take backups like

    Full Backup of both user databases and system databases(Master,Model and MSDB)

    Log file backup(For databases with full recovery model)

     

    2. Log file growth

    Ideally you should be monitoring log file growth and set a fixed acceptable size.For databases with full recover model,you can use tran log backups every 1 hour(According to requirement of point in time recovery) and this will clear log space.

    There are fixed boundaries for a log file to shrink and this is clearly mentioned under

    http://msdn.microsoft.com/en-us/library/ms189080.aspx

    Hope this helps.

     


    AnupSivaDas | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Tuesday, September 20, 2011 5:45 PM

All replies

  • Hello,

    I setup a new maintenance plan in my SQL 2008 R2. I selected the databases for the application. Besides, there are some other databases as master, ... Should I include these databases as well?

    I see the initial size of a log file is setup. I looked at the size of the log file and it really grows a lot larger. I think about the SHRINKFILE (for the log). I can use the command as "DBCC SHRINKFILE(DataBaseName_Log,0)". I wonder that should I reduce the log size to a size less than the initial setting?; For example, the initial size is 10000, can I reduce it to 1000?

    Thanks


    Madison

    1. Master database backup would be small in size so it would not cause any harm if you add in Maintenance Plan. Normally DBAs keep backup using retantion period so taking periodic backup would not be an issue.

    2. For file growth you should refer http://support.microsoft.com/kb/317375 as it has common caused mentioned on transaction log growth. Most common cause based on my decade of experience of SQL Server is not taking t-log backup for database in full recovery model.

    Feel free to ask more questions or if you need any clarification.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Peja Tao Thursday, September 22, 2011 7:01 AM
    • Marked as answer by Peja Tao Thursday, September 22, 2011 7:02 AM
    Tuesday, September 20, 2011 5:38 PM
  • Hello,

    For your situation below are the recommendations.

    1. Assuming that your maintenance plan deals with database backups and in that case you need to take backups like

    Full Backup of both user databases and system databases(Master,Model and MSDB)

    Log file backup(For databases with full recovery model)

     

    2. Log file growth

    Ideally you should be monitoring log file growth and set a fixed acceptable size.For databases with full recover model,you can use tran log backups every 1 hour(According to requirement of point in time recovery) and this will clear log space.

    There are fixed boundaries for a log file to shrink and this is clearly mentioned under

    http://msdn.microsoft.com/en-us/library/ms189080.aspx

    Hope this helps.

     


    AnupSivaDas | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Tuesday, September 20, 2011 5:45 PM
  • Thanks Balmukund. This is very helpful.
    Madison
    Wednesday, September 21, 2011 4:37 PM
  • Thanks AnupSivaDas, this helps me. I include the master, model and MSDB database along with the application database.

    Thanks


    Madison
    Wednesday, September 21, 2011 4:38 PM