spWhatsRunning does just that. It tells you exactly what is executing on your server. By combining the output of the sp_who and dbcc inputbuffer, this script will tell you exactly whats being executed. DBCC INPUTBUFFER will tell you the same thing, but by the time you get the spid, the offending process may be gone. […]
2007-04-17 (first published: 2007-03-20)
2,573 reads
The following script will allow the user to get information from all spids that have a program name associated with them. That is event info out of dbcc inputbuffer. Additional columns may be added and used in the table through simple modifications of the script. I just found it useful for troubleshooting and setting up […]
2006-10-19 (first published: 2006-09-29)
2,175 reads
Troubleshooting intermitten connectivity issues can be difficult. One approach is to repeatedly test connecting to SQL Server outside of an existing application in order to verify if a server-wide intermitten issue is occurring. This script is used to test remote connectivity to a SQL Server. The script loops in one minute intervals (adjust as needed). […]
2006-09-04 (first published: 2006-08-08)
1,405 reads
This function returns the SQL Build Number as an int. i.e. 7, 8, or 9. @@Version is great, but super long, and the formatting of the string has changed over the years. This helps me find out what version of SQL I'm working on in a quick and efficient manner. The script includes the drop […]
2006-08-08 (first published: 2006-06-23)
1,212 reads
This function returns the SQL Build Number as a nvarchar(20) string. i.e. '8.00.760' @@Version is great, but super long, and the formatting of the string has changed over the years. This helps me find out what build of SQL I'm working on in a quick and efficient manner. The script includes the drop statement as […]
2006-08-09 (first published: 2006-06-23)
984 reads
This is a replacement for SP_who and modification of sp_who2. It will always list only active, nonsystem processes, and will list the number of seconds a transaction has been running for. Some transactions do not report a last batch time, so I forced a large value to display so you can see any issue with […]
2006-10-02 (first published: 2006-04-28)
2,153 reads
Our business accounting software is Microsoft Business Solutions Solomon IV.which is now being called Dynamics Solomon. You will see specific refernces to this software in this code. I originally developed this to gain faster view of issues we were having with Solomon. This procedure analyzes system tables and looks for blocks. This isfaster than using […]
2005-11-03 (first published: 2005-09-20)
398 reads
Examining the output of DBCC SQLPERF(UMSSTATS) helps in determining a CPU bottleneck. The output of the command is not handy for further investigation (from a table).This procedure performs a transformation of the results, so it is easy to query and store in a database.
2005-11-04 (first published: 2005-09-20)
1,925 reads
usp_ToggleDatabases is a simple procedure that will switch the names of two databases.One of the main problems with using sp_renamedb to switch databases is that the procedure will wait until the database is free before the rename can be completed. If the new live database is also in use then there is a likelyhood that […]
2005-08-04 (first published: 2005-07-28)
481 reads