What Was I Thinking?

Follies & Foils of .NET Development
posts - 95 , comments - 352 , trackbacks - 0

December 2012 Entries

Troubleshooting Blocked Transaction in SQL Server
While troubleshooting a blocked transaction issue recently, I found this code online. My apologies in not citing its source, but its lost in my browse history some where. While the transaction is executing and blocked, open a connection to the database containing the transaction and run the following to return both the SQL statement blocked (the Victim), as well as the statement that’s causing the block (the Culprit) -- prepare a table so that we can filter out sp_who2 results DECLARE @who TABLE(BlockedId ......

Posted On Friday, December 7, 2012 4:45 PM | Comments (0) | Filed Under [ SQL ]

More useful Sql Server Serivce Broker Queries
SELECT 'Checking Broker Service Status...' IF (select Top 1 is_broker_enabled from sys.databases where name = 'NWMESSAGE')=1 SELECT ' Broker Service IS Enabled' -- Should return a 1. ELSE SELECT '** Broker Service IS DISABLED ***' /* If Is_Broker_enabled returns 0, uncomment and run this code ALTER DATABASE NWMESSAGE SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO Alter Database NWMESSAGE Set enable_broker GO ALTER DATABASE NWDataChannel SET MULTI_USER GO */ SELECT 'Checking For Disabled Queues....' -- ......

Posted On Monday, December 3, 2012 4:50 PM | Comments (0) |

Powered by: