Advertisement
Guest User

find open transacton

a guest
Apr 7th, 2017
273
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. SET NOCOUNT ON;
  3.  
  4. IF OBJECT_ID(N'tempdb..#OpenTranStatus', N'U') IS NOT NULL
  5. DROP TABLE #OpenTranStatus;
  6. GO
  7. IF OBJECT_ID(N'tempdb..#OpenTranStatusStg', N'U') IS NOT NULL
  8. DROP TABLE #OpenTranStatusStg;
  9. GO
  10.  
  11. -- Create the temporary table to accept the results.
  12. CREATE TABLE #OpenTranStatus
  13. (
  14. -- dbname NVARCHAR(40),
  15. dbname varchar(100),
  16. ActiveTransaction VARCHAR(25) ,
  17. Details SQL_VARIANT
  18. );
  19.  
  20. CREATE TABLE #OpenTranStatusStg
  21. (
  22. -- dbname NVARCHAR(40),
  23. ActiveTransaction VARCHAR(25) ,
  24. Details SQL_VARIANT
  25. );
  26.  
  27. DECLARE @dbid int, @dbname nvarchar(50), @SQLStmt nvarchar(1000);
  28.  
  29. DECLARE db_cursor CURSOR FOR
  30. select database_id dbid, name dbname from sys.databases where state_desc ='ONLINE'
  31. ORDER BY name;
  32.  
  33. OPEN db_cursor
  34.  
  35. FETCH NEXT FROM db_cursor
  36. INTO @dbid, @dbname
  37.  
  38. WHILE @@FETCH_STATUS = 0
  39. BEGIN
  40. TRUNCATE TABLE #OpenTranStatusStg
  41. SET @SQLStmt = 'DBCC OPENTRAN ('+cast(@dbid as nvarchar(100))+') WITH TABLERESULTS, NO_INFOMSGS'
  42. INSERT INTO #OpenTranStatusStg
  43. EXEC ( @SQLStmt)
  44. INSERT INTO #OpenTranStatus SELECT @dbname,* FROM #OpenTranStatusStg
  45.  
  46. -- Get the next vendor.
  47. FETCH NEXT FROM db_cursor
  48. INTO @dbid, @dbname
  49. END
  50. CLOSE db_cursor;
  51. DEALLOCATE db_cursor;
  52.  
  53.  
  54. select * from #OpenTranStatus
Advertisement
RAW Paste Data Copied
Advertisement