tailieunhanh - SQL Server Tacklebox- P41

SQL Server Tacklebox- P41: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 7 Securing access to SQL Server Set up a user with read write permissions on the central storage database to be used for the linked server credentials. Use the new user credentials you have just made to setup a linked server on the SQL Server that you want to run an automated trace on. In the usp_startTrace and usp_stopTrace scripts locate the calls all noted in the comments that point to a generic linked server myserver123 and modify them to reflect the name of your central trace data repository server. Run the create scripts on each of the servers that will be performing the traces. Once these stored procedures are installed you can begin starting and stopping traces via a query window query analyzer or through SQL Agent jobs. Testing Here is a quick example that demonstrates how to find the cause of all this grief for our beloved program manager. Create a new SQL Agent job to kick off at 5 30 PM after business hours involving only one step as shown in Figure . In this step execute the start trace procedure with the parameters needed to gather only the data relevant to the issue at hand. Figure Create a SQL Agent job to run usp_startTrace. 200 7 Securing access to SQL Server This will produce a trace named TruncateTrace. The trace file will be stored in the root of the C drive. The maximum space the trace file should take is 10 MB and we will place a filter on the first column text data looking for any instances of the word truncate . The last three parameters are optional and will be defaulted to 5 trace file size in MB 0 no trace column and NULL no filter keyword respectively. If you do not specify these parameters then a bare bones trace will be created with a maximum file size of 5 MB and it will perform no filtering so you will get all available data from the trace events. Alternatively create another job to run at 6 00 AM calling the usp_stopTrace giving the same trace name as shown in Figure . Figure Create a SQL Agent job to run .