none
MS Project Server 2013: SQL Transaction Log Full RRS feed

  • Question

  • The SQL transaction log of the production server is growing and filled up the disk.

    What's the best way to manage/control the log size and what's the impact on recovery?

    Monday, September 7, 2015 2:32 AM

Answers

  • Hey there Gonzague,

       This growing log file problem is a sign that your SQL backup/recovery strategy is not setup correctly.

    You need to consider the backup/recovery strategy that addresses:

     - Recovery Point Objective

     - Recovery Time Objective.

    In the short term I would advise you to perform a full backup, set the database recovery model to SIMPLE, and then Shrink the Database Files.  A quick way to do this is to execute this on the database server, with results to text:

    USE master 

    SELECT 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE WITH NO_WAIT' frommaster..sysdatabases where name not in ('master','model','msdb','tempdb') 

    SELECT 'EXEC sp_MSForEachDB @Command1 = N''DBCC SHRINKDATABASE (?)'', @replacechar = ''?'''

    Take resulting text and execute it, this will set the recovery mode to simple and shrink the DBs fixing the disk space problem.

    After you have a full backup "in-the-bag" you can then ascertain how you want to achieve your backup/recovery objectives above, start here.

    Hope that helps,

       James.


    James Boman BSc. MCP:EAD -

    Monday, September 7, 2015 3:22 AM
  • Do a TLog backup regularly (every 15 mins). Takes 2 minutes to setup, 2 minutes ot document and has no impact in recovery.
    Thursday, September 10, 2015 9:01 AM

All replies

  • Hey there Gonzague,

       This growing log file problem is a sign that your SQL backup/recovery strategy is not setup correctly.

    You need to consider the backup/recovery strategy that addresses:

     - Recovery Point Objective

     - Recovery Time Objective.

    In the short term I would advise you to perform a full backup, set the database recovery model to SIMPLE, and then Shrink the Database Files.  A quick way to do this is to execute this on the database server, with results to text:

    USE master 

    SELECT 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE WITH NO_WAIT' frommaster..sysdatabases where name not in ('master','model','msdb','tempdb') 

    SELECT 'EXEC sp_MSForEachDB @Command1 = N''DBCC SHRINKDATABASE (?)'', @replacechar = ''?'''

    Take resulting text and execute it, this will set the recovery mode to simple and shrink the DBs fixing the disk space problem.

    After you have a full backup "in-the-bag" you can then ascertain how you want to achieve your backup/recovery objectives above, start here.

    Hope that helps,

       James.


    James Boman BSc. MCP:EAD -

    Monday, September 7, 2015 3:22 AM
  • Do a TLog backup regularly (every 15 mins). Takes 2 minutes to setup, 2 minutes ot document and has no impact in recovery.
    Thursday, September 10, 2015 9:01 AM