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: