1.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 table local to the 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. For this demo this is the method 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.
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!
