Tag: instance

SQL Server : Instance Configuration and Management

Recycling Logs

By default SQL Server retains six logs are only recycles a log when the instance is restarted. As a result, the logs can grow really big, wasting a lot of space and making them really hard to open and read. You should probably consider recycling the logs on a regular basis and altering the number of logs retained.

In this example, we will recycle the logs every day and retain 30 days worth of logs.

  • Expand “Management”.
  • Right-click on the “SQL Server Logs” node and select the “Configure” menu option.
  • Check the Limit option and enter the maximum number of error log files to “30”.
  • Click the “OK” button.

Recycle the error log every day.

  • Expand “SQL Server Agent”.
  • Right-click on “Jobs”.
  • Select the “New Job…” menu option.
  • Set Name to “RecycleErrorLog” and Owner to “sa”.
  • Click on the “Steps” page and click the “New” button.
  • Set the “Step name” to “Step_1” and set the Command to “sp_cycle_errorlog”.
  • Click the “OK” button.
  • Click on the “Schedules” page and click the “New” button.
  • Set the Name to “DailyMidnight”.
  • Change “Occurs” to “Daily” and leave the default “Occurs once at” as “00:00:00”.
  • Click the “OK” button.
  • Click the “OK” button on the “New Job” dialog.

For more information see:

Patch Management

  • RTM – Release to Market : The initial release.
  • Hotfix : Specific fixes.
  • CU – Cumulative Update : Rollup hotfixes.
  • SP – Service Pack : A large number of patches combined together.

Check out the Update Center for Microsoft SQL Server.

Trace Flags

Where “n” is a trace flag number. To alter a trace flag at session level do the following.

dbcc traceon(n)

To alter the trace flag globally do the following. It will not persist past a restart of SQL Server.

dbcc traceon(n, -1)

To make a persistent change to a trace flag do the following.

  • Open “SQL Server Configuration Manager”.
  • Double-click on the “SQL Server Service”.
  • Click the “Startup Parameters” tab.
  • Add a startup parameter “-T3226” and click the “Add” button.
  • Click the “Apply” button and click “OK” on the warning message.

The trace flag will be applied on subsequent restarts.

For more information see: