When executed against a database in which blocking occur, below script will report lockType, Object waited for and current Wait times. The script requires access to master..systables. The script queries syslockInfo (as does sp_lock), but further joins sysprocesses with an interpretation of waitresource matching SQL Server 7.0 and SQL Server 2000.
2002-01-14
2,617 reads
This Script reports locking on a particular database either to the console or to a database table. It also allows filtering based on a minimum locking level (say Page or Table and higher). Included is the CREATE TABLE statement to build the reporting table. This table can reside in any database but needs to be […]
2002-01-06
1,042 reads
Problems with error 512 more than 1 result returned due to the operator outside of the query. Found this when tested with multiple blocks going on at once. It worked fine when there was only 1 block. Depending on the type of server there can be a "whole lot of blocking going on "Replaced with […]
2002-01-01
837 reads
This script allows the capture of the locks to a table. After the capture, you can see the commands and the moment of the lock.
2001-12-13
3,021 reads
This script will detect and alert the DBA of blocking processes. The DBA can adjust the length of time the block has exisited before sending the net send message.
2001-08-29
5,235 reads
This script will search all logged in users and return the spid along with the number of seconds since that spids last batch was executed.
2001-08-22
1,020 reads
This gives a quick check of the current activity on a SQL Server. I developed this after getting continually frustrated with "Current Activity" in SQL/EM getting blocked by processes when I needed to see blocking issues on the server. It has been immensely valuable to me in quickly identifying production-stopping issues on the database. It […]
2001-08-13
4,903 reads
This script will kill all users in a given database. Before disconnecting, it can optionally send the user a NET SEND Message giving them a warning to disconnect. To run, simply call it like usp_killusers 'dbname'. Make sure the @dbname parameter is not the master database.
2001-08-08
4,520 reads
Redshift keeps its SYS_* monitoring views for seven days in cluster, which is fine...
By Steve Jones
I was updating my session for the Data API Builder (DAB) recently, mostly to...
I am excited to host T-SQL Tuesday for the first time. I want to...
Hi we're a bit behind on versioning ssrs. I think we have over 1600...
Working with SQL Server can involve more than just writing queries. Understanding database design,...
Comments posted to this topic are about the item Measuring Productivity
I have 3 instances of a brand new long running query that are executing on a SQL Server 2025 instance. Query store is enabled. One of the queries is killed by the administrator, one is cancelled by the user. The other runs to completion. How many rows are in Query Store for this query?
See possible answers