Anthony Brown

script to find out what a spid is doing

antxxxx — Thu, 30 Nov 2006 13:43:00 GMT

If a server is behaving badly, or there is a long running process and you want to find out what it is actually doing, you might find these scripts useful. They can be used to show the query or stored procedure that is executing and the text of the statement that is executing within that procedure.

This gives a more complete picture of what is happening than by just using dbcc inputbuffer which just displays the last statement sent by the client to sql server.

For example say have a stored procedure, uspProcedure1, which calls another procedure, uspProcedure2, inside it, and uspProcedure2 takes a long time to complete. If a client executes uspProcedure1 and you run dbcc inputbuffer on that spid, it will just show uspProcedure1 executing, even though the statement currently executing is in uspProcedure2

Both scripts create a procedure which does the same thing - sp_dba_showline is for sql 2000 with sp3 or greater installed and sp_dba_showline2005 is for sql 2005. The reason for the difference is the sql 2000 version uses fn_get_sql (a function introduced in sp3) which is being depreciated in favor of sys.dm_exec_sql_text (available in sql 2005). The sql 2005 version also uses a cross apply join to get the data.

The procedure takes 1 parameter, @spid, and the results returned are:

the results of dbcc inputbuffer to see what query or procedure was called from the client

the results of sp_who2 to show general process information

the first 100 lines of the procedure or query being executed (this can be useful to find the procedure name being executed)

the text of the statement actually being executed.

If you create these on the master database as dbo, then they should be accessible from all user database

Running a report against several servers in RS2005

antxxxx — Fri, 24 Nov 2006 14:47:00 GMT

Whilst I was looking at building custom reports in management studio, I finally got round to looking at reporting services in sql 2005.

One really neat feature I found is the ability to use an expression to build a connection string. Using this, you can dynamically set which server the report connects to when it runs. You can then embed this report as a subreport on another report and so get it to run against several servers all from one report. If you look after several servers, this can give you a nice easy way to check all your servers are OK.

To do this, first create the report you want to run against each server. Don't worry about the data source while you are designing it - just connect to any machine and make sure it is designed how want.

When you are happy with it, add a parameter to the report called ServerName. This should be a string datatype and the ‘allow blank value’ checkbox should be unchecked.

Next you need to change the datasource to use an expression.(one thing to note is you can’t use an expression on a shared datasource, so you may need to create a new datasource for the report). Edit the datasource and click the fx button next to the connection string and change the expression to

="Data Source=" & Parameters!ServerName.Value

If you now try and preview the report, you need to enter the servername parameter and it runs against whichever server you specify.

Now for the second report. First create a dataset that will return all the names of the servers you want to run the report against.

On the layout tab, add a table and bind it to that dataset. Then drag a subreport into the detail part of the table. Right click on the subreport and select properties and in the subreport drop down box, select the first report you created. Finally go to the parameters tab and in the parameter name column select ServerName and in the parameter value column select the column from the dataset that contains the servername.

In the download on this entry are couple of reports to show how this all works.

The first report is SingleServerInfo that just shows some information that it connects to. You can change which server it connects to using the ServerName parameter.

The second report is MultipleServerInfo and has a table with SingleServerInfo as subreport in one of the cells. This means it is called for each server returned by the dataset on this report (which needs a table called ServerNames which can be created using the script in the zip file)

These reports will only run against sql 2005 servers as they use some of the dynamic management views available on there. However, there is no reason why you cant use this technique to run a report against several sql 2000 servers (or a mixture of versions)

Please make sure you look at the queries these reports run and are happy with them before running the reports against any of your servers.

documentation on custom reports

antxxxx — Wed, 22 Nov 2006 11:12:00 GMT

Following on from my last post, Microsoft have now published some documentation on creating custom reports in management studio. Its still a draft, but is available at http://blogs.msdn.com/sqlrem/archive/2006/11/20/custom-reports-in-management-studio.aspx if you want to read it

Custom reports in management studio

antxxxx — Wed, 15 Nov 2006 14:35:00 GMT

One of the things I really like in sql 2005 is the management reports available from within management studio. In sp2, there is the ability to add your own report. However the documentation seems a bit sparse on how to do this, so here is a quick overview...(read more)