How many transactions has been commited in my database?
Just a beta code. Then, as you like you can schedule that stored procedure in a job
It uses internal esctructures such as LOP_BEGIN_XACT and are not documented in public forums.
CREATE TABLE [dbo].[TRA_TRANSACCIONESMENSUAL]
( [ID] [int] IDENTITY (1, 1) NOT NULL ,
[OPERATION] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOTAL] [int] NULL,
[BASE_DATOS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FECHA] [datetime] NULL )
ON [PRIMARY]GO
CREATE PROCEDURE TRA_ListaTransaccionesMensuales
DECLARE @BD AS CHAR(25)
DECLARE @sql2 AS CHAR(500)
DECLARE cursorbd CURSOR FAST_FORWARD FOR
SELECT NAME FROM master.dbo.SYSDATABASES
OPEN cursorbd
FETCH NEXT FROM cursorbd INTO @BD
WHILE @@fetch_status = 0
BEGIN
SET @sql2= ('USE ' + @BD + ' INSERT INTO MASTER.DBO.TRA_TRANSACCIONESMENSUAL(OPERATION,TOTAL,FECHA) SELECT Operation, COUNT(*), GETDATE() FROM ::fn_dblog(null,null) WHERE Operation IN (''LOP_BEGIN_XACT'', ''LOP_COMMIT_XACT'') GROUP BY OPERATION UPDATE MASTER.DBO.TRA_TRANSACCIONESMENSUAL SET BASE_DATOS = ''' + UPPER(ltrim(Rtrim(@BD))) + ''' WHERE [id] = (SELECT SCOPE_IDENTITY()) OR [id] = (SELECT SCOPE_IDENTITY() -1 )')
-- print @sql2
EXEC (@sql2)
FETCH NEXT FROM cursorbd INTO @BD
END
CLOSE cursorbd
DEALLOCATE cursorbd
It uses internal esctructures such as LOP_BEGIN_XACT and are not documented in public forums.
CREATE TABLE [dbo].[TRA_TRANSACCIONESMENSUAL]
( [ID] [int] IDENTITY (1, 1) NOT NULL ,
[OPERATION] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOTAL] [int] NULL,
[BASE_DATOS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FECHA] [datetime] NULL )
ON [PRIMARY]GO
CREATE PROCEDURE TRA_ListaTransaccionesMensuales
DECLARE @BD AS CHAR(25)
DECLARE @sql2 AS CHAR(500)
DECLARE cursorbd CURSOR FAST_FORWARD FOR
SELECT NAME FROM master.dbo.SYSDATABASES
OPEN cursorbd
FETCH NEXT FROM cursorbd INTO @BD
WHILE @@fetch_status = 0
BEGIN
SET @sql2= ('USE ' + @BD + ' INSERT INTO MASTER.DBO.TRA_TRANSACCIONESMENSUAL(OPERATION,TOTAL,FECHA) SELECT Operation, COUNT(*), GETDATE() FROM ::fn_dblog(null,null) WHERE Operation IN (''LOP_BEGIN_XACT'', ''LOP_COMMIT_XACT'') GROUP BY OPERATION UPDATE MASTER.DBO.TRA_TRANSACCIONESMENSUAL SET BASE_DATOS = ''' + UPPER(ltrim(Rtrim(@BD))) + ''' WHERE [id] = (SELECT SCOPE_IDENTITY()) OR [id] = (SELECT SCOPE_IDENTITY() -1 )')
-- print @sql2
EXEC (@sql2)
FETCH NEXT FROM cursorbd INTO @BD
END
CLOSE cursorbd
DEALLOCATE cursorbd
0 Comments:
Post a Comment
<< Home