SQLChicken.com

This morning on Twitter my friend (and as per request, “handsome moron”, but trust me he’s no moron) Jeremiah Peschka (Twitter) asked about scheduled traces. I suggested he do a server-side trace. Unfortunately when you Google how to do this it just tells you how to do this via T-SQL but to my dismay I didn’t see an article on how to create/set this up from SQL Server Profiler. So this will be a quick rundown on how to create your trace using the GUI of SQL Server Profiler and schedule your trace to run at a time of your choosing (so that way you don’t have to wake up at 4 am to kick off a trace).

1. Cut a hole in the box…oh wait, wrong instruction set. Open up SQL Server Profiler and create a new trace. To open Profiler you can either launch it from within SSMS (SQL Server Management Studio) by going to the Tools menu and selecting SQL Server Profiler. The other way to launch it is by clicking on Start button, going to Programs, Microsoft SQL Server 2008 (or 2005), Performance Tools and you should see the shortcut for Profiler there.

2. Check your save to options. Since this will be running on the server itself you’ll have to make a decision here. If you save to table try to save to a database that is NOT the monitored server, preferably into a separate database/table you create for this purpose. You can create a separate database for this purpose and either keep the database for reporting purposes or drop it when you’re done with your analysis. If you don’t want to create a whole new database you can create a new table in an existing database. The advantages of saving to a table are that you can use native SQL tools to do your analysis or even Excel. Preferably you’ll want to save trace data to a file as its faster but for this demo the save to database method is the one we will use.

a. When you select this option you will be prompted to connect to a SQL instance of your choosing. Select the server you’ll be doing your trace on.

b. Here you can name your new table (creates table for you, assuming you have permission to do so).

c. Your other option is to save to file. This will create a series of .TRC files wherever you specify. The advantage to using this option is that your results become portable in that you can move and import those files wherever you need them to do your analysis (i.e. copy them to your local PC and do analysis locally).

3. Configure your trace’s stop time. This is important as you want to make sure you have a consistent slice of time measured. This can later be modified within the script if you’d like.

4. At this point we can customize our trace. Click on the Events Selection tab. Here you can select the events and filters you’d like to trace on. Be careful as to not choose too many options/filters as this can bog down the trace and cause performance problems on the server itself since you’re trying to log so many things at once. For purposes of this demo I’ve chosen the Standard (default) template and default settings.

5. Run your trace…and then stop it immediately. This sounds a little strange but keep in mind we’re not running our trace right now, we just want the script that makes up this trace. You start the trace by clicking the Run button at the previous screen. Once the trace starts you stop it by clicking on the Stop button or by going to the File Menu and selecting Stop Trace.

6. Collect your trace script. Once you’ve stopped your trace go to the File menu, Export, Script Trace Definition, select For SQL Server 2005-2008. Select a location to save your script (.SQL) file.

7. To see your script, open it up in SSMS. In the script you’ll see all the options you chose in the Profiler GUI. The @DateTime variable is the option for when to stop your trace so modify that as needed. Of note, check the number after @TraceID ouput (in screenshot it’s 0). If you leave it at 0 the trace file will fill up and not rollover. To avoid this, replace that with 2. You can read all the options on Books Online.

UPDATE: One of the unfortunate parts of this is that the scripted trace only supports writing trace to a flat file, so you can’t specify SQL table like in the GUI version. In this trace file make sure you specify location for your trace file location as well.

8. Now to schedule your newly created script. In SSMS connect to the server you want to trace on. Go to your SQL Server Agent and expand it so you can see the Jobs folder. Right-click the Jobs folder and select New Job.

9. In the New Job Step dialog, click on the Open button. Browse to where you saved your trace script file, select it and click Open. It might prompt you twice, just repeat. Once you’ve loaded it you should see the contents of your script file in the Command window. NOTE: At this point you can also choose what account to run this script as by selecting an account from the dropdown menu under Run as. Click OK when you’re done.

10. Next we need to schedule your trace. Click on the Schedules link on the side menu. You can pick an existing schedule or create a new one. Here you’ll want to create a New schedule. Give your schedule an easily identifiable name. Since we’re creating a custom schedule you can do a one-time job or you can choose to run this on a recurring schedule (i.e. once a month). The recurring schedule would be advantageous if you’re looking to monitor your SQL performance on a regular basis and want to make sure you trace the same actions every time. One thing to note is that you want to set your start time here under the ‘Occurs once at’ section. Remember, you’ve already scheduled your stop time for the trace within your script. Once you’re done customizing your schedule click OK when you’re ready to proceed.

11. Click OK to finish creating your new job. To see your newly created job look under the Jobs folder in SQL Server Agent. Congratulations, you’ve now created an automated server-side trace using SQL Server Profiler!