Enabling a Trace Flag

In SQL Server 2000 setting a trace flag was really obvious. In Enterprise Manager you could right click on a Server and select properties and then add a Startup Parameter. In SQL Server 2005 the process to set a trace flag is not quite as obvious, as the Startup Parameters are not set in SSMS but in SQL Server Configuration Manager.

To set a trace flag in SQL Server 2005 you need to follow the steps below:

  1. Open SQL Server Configuration Manager
  2. Select SQL Server 2005 Services
  3. Right click the SQL Server service and select Properties
  4. Select the Advanced tab
  5. Edit the Startup Parameters property

The thing to note is that you need to make sure that you separate the Trace Flag from other startup parameters with a semicolon (;) and don’t leave a space.


… mastlog.ldf;-T1204

The following registry key lists all of the SQL Server Startup Parameters that have been set:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLServer\Parameters

March WARDY IT Community Dinner

The WARDY IT Community Dinner is a great opportunity to network with other IT professionals in order to share knowledge, information and experiences. The March WARDY IT Community Dinner is being held on Monday the 19th of March. To RSVP to be at this event and find out what other people are doing and how they are doing it send an email to peter @ wardyit.com.

Note: The WARDY IT Community Dinner is typically held on the second Monday of the Month, however as I will be presenting at SQL Server Open World the March Community Dinner is one week later.

WHERE New Farm Bistro | Cnr Lamington & Welsby Street | New Farm | http://www.newfarmbistro.com.au

WHEN Monday 19th March | 6:30PM

RSVP Friday 16th March

Please note that you are responsible for the cost of your own dining.

Add this event to your Outlook Calendar

SQL Server 2005 SP2 RTM Released

SQL Server 2005 Service Pack 2 is now available for download from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx#EZB.  Included with the Service Pack is an updated version of Books Online as well as the Feature Pack. Also Data Mining Add-ins for Microsoft Office 2007 is also available. For a complete list of new feature in this Service Pack refer to http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/WhatsNewSQL2005SP2.htm. For a list of bugs fixed refer to

Perth SQL Server UG Resources

Last night I delivered the presentation Engineering 101 for the SQL DBA (the one with the ‘legendary’ USB key RAID demo) at the Perth SQL Server User Group. This presentation focuses on how the I/O subsystem can affect the performance of SQL Server. This presentation is also the same presentation that I will be delivering in Denmark next month at SQL Server Open World.

The PowerPoint deck and demo code from this presentation can be downloaded from http://www.wardyit.com/presentations/PSSUGEngineering101fortheSQLDBA.zip.

The presentation was also recorded using Camtasia and I have made a screencast and mp3 available for download.

The screencast can be downloaded from http://www.wardyit.com/presentations/PSSUGEngineering101fortheSQLDBA.wmv (~62MB).

The mp3 can be downloaded from http://www.wardyit.com/presentations/PSSUGEngineering101fortheSQLDBA.mp3 (~35MB).

SSMS Export Maintenance Plan

When you connect using the Server type Database Engine in SQL Server Management Studio there is no way to export a Maintenance Plan. However if you connect using the server type Integration Services you are able to export a Maintenance Plan using the steps below:

  1. Open SQL Server Management Studio
  2. Select File | Connect Object Explorer
  3. Change the Server Type to Integration Services and connect to the server
  4. Expand Servername (Integration Services) | Stored Packages | MSDB | Maintenance Plans
  5. Right Click on the Maintenance Plan and select Export

SQL Server High Availability

In the latest edition of .Net Rocks Allan Hirt discusses high availability in SQL Server 2005. This episode provides a great insight to the things that you need to be aware of when developing solutions for high availability. The show also provides a great overview of the different features that can be used with SQL Server for high availability such as clustering and database mirroring. The show can be downloaded from here.