桃子视频

This morning on Twitter my friend (and as per request, 鈥渉andsome moron鈥�, but trust me he鈥檚 no moron) Jeremiah Peschka () 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鈥檛 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鈥檛 have to wake up at 4 am to kick off a trace).

1. 鈥�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鈥檒l 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鈥檙e done with your analysis. If you don鈥檛 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鈥檒l 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鈥檚 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鈥檇 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鈥檇 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鈥檙e trying to log so many things at once. For purposes of this demo I鈥檝e 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鈥檙e 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鈥檝e 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鈥檒l 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.

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鈥檝e 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鈥檙e 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鈥檒l want to create a New schedule. Give your schedule an easily identifiable name. Since we鈥檙e 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鈥檙e 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 鈥極ccurs once at鈥� section. Remember, you鈥檝e already scheduled your stop time for the trace within your script. Once you鈥檙e done customizing your schedule click OK when you鈥檙e 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鈥檝e now created an automated server-side trace using SQL Server Profiler!