一个可以跟踪数据库情况的存储过程
来源:互联网 发布:龙霸网络电视手机版 编辑:程序博客网 时间:2024/06/09 14:19
跟踪数据库的情况
SET NOCOUNT ON;
USE master;
GO
IF OBJECT_ID('dbo.PerfworkloadTraceStart', 'P') IS NOT NULL
DROP PROC dbo.PerfworkloadTraceStart;
GO
CREATE PROC dbo.PerfworkloadTraceStart
@dbid AS INT,
@tracefile AS NVARCHAR(245),
@traceid AS INT OUTPUT
AS
-- Create a Queue
DECLARE @rc AS INT;
DECLARE @maxfilesize AS BIGINT;
SET @maxfilesize = 5;
EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL
IF (@rc != 0) GOTO error;
-- Set the events
DECLARE @on AS BIT;
SET @on = 1;
-- RPC:Completed
exec sp_trace_setevent @traceid, 10, 15, @on;
exec sp_trace_setevent @traceid, 10, 8, @on;
exec sp_trace_setevent @traceid, 10, 16, @on;
exec sp_trace_setevent @traceid, 10, 48, @on;
exec sp_trace_setevent @traceid, 10, 1, @on;
exec sp_trace_setevent @traceid, 10, 17, @on;
exec sp_trace_setevent @traceid, 10, 10, @on;
exec sp_trace_setevent @traceid, 10, 18, @on;
exec sp_trace_setevent @traceid, 10, 11, @on;
exec sp_trace_setevent @traceid, 10, 12, @on;
exec sp_trace_setevent @traceid, 10, 13, @on;
exec sp_trace_setevent @traceid, 10, 6, @on;
exec sp_trace_setevent @traceid, 10, 14, @on;
-- SP:Completed
exec sp_trace_setevent @traceid, 43, 15, @on;
exec sp_trace_setevent @traceid, 43, 8, @on;
exec sp_trace_setevent @traceid, 43, 48, @on;
exec sp_trace_setevent @traceid, 43, 1, @on;
exec sp_trace_setevent @traceid, 43, 10, @on;
exec sp_trace_setevent @traceid, 43, 11, @on;
exec sp_trace_setevent @traceid, 43, 12, @on;
exec sp_trace_setevent @traceid, 43, 13, @on;
exec sp_trace_setevent @traceid, 43, 6, @on;
exec sp_trace_setevent @traceid, 43, 14, @on;
-- SP:StmtCompleted
exec sp_trace_setevent @traceid, 45, 8, @on;
exec sp_trace_setevent @traceid, 45, 16, @on;
exec sp_trace_setevent @traceid, 45, 48, @on;
exec sp_trace_setevent @traceid, 45, 1, @on;
exec sp_trace_setevent @traceid, 45, 17, @on;
exec sp_trace_setevent @traceid, 45, 10, @on;
exec sp_trace_setevent @traceid, 45, 18, @on;
exec sp_trace_setevent @traceid, 45, 11, @on;
exec sp_trace_setevent @traceid, 45, 12, @on;
exec sp_trace_setevent @traceid, 45, 13, @on;
exec sp_trace_setevent @traceid, 45, 6, @on;
exec sp_trace_setevent @traceid, 45, 14, @on;
exec sp_trace_setevent @traceid, 45, 15, @on;
-- SQL:BatchCompleted
exec sp_trace_setevent @traceid, 12, 15, @on;
exec sp_trace_setevent @traceid, 12, 8, @on;
exec sp_trace_setevent @traceid, 12, 16, @on;
exec sp_trace_setevent @traceid, 12, 48, @on;
exec sp_trace_setevent @traceid, 12, 1, @on;
exec sp_trace_setevent @traceid, 12, 17, @on;
exec sp_trace_setevent @traceid, 12, 6, @on;
exec sp_trace_setevent @traceid, 12, 10, @on;
exec sp_trace_setevent @traceid, 12, 14, @on;
exec sp_trace_setevent @traceid, 12, 18, @on;
exec sp_trace_setevent @traceid, 12, 11, @on;
exec sp_trace_setevent @traceid, 12, 12, @on;
exec sp_trace_setevent @traceid, 12, 13, @on;
-- SQL:StmtCompleted
exec sp_trace_setevent @traceid, 41, 15, @on;
exec sp_trace_setevent @traceid, 41, 8, @on;
exec sp_trace_setevent @traceid, 41, 16, @on;
exec sp_trace_setevent @traceid, 41, 48, @on;
exec sp_trace_setevent @traceid, 41, 1, @on;
exec sp_trace_setevent @traceid, 41, 17, @on;
exec sp_trace_setevent @traceid, 41, 10, @on;
exec sp_trace_setevent @traceid, 41, 18, @on;
exec sp_trace_setevent @traceid, 41, 11, @on;
exec sp_trace_setevent @traceid, 41, 12, @on;
exec sp_trace_setevent @traceid, 41, 13, @on;
exec sp_trace_setevent @traceid, 41, 6, @on;
exec sp_trace_setevent @traceid, 41, 14, @on;
-- Set the Filters
-- Application name filter
EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';
-- Database ID filter
EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;
-- Set the trace status to start
EXEC sp_trace_setstatus @traceid, 1;
-- Print trace id and file name for future references
PRINT 'Trace ID: ' + CAST(@traceid AS VARCHAR(10))
+ ', Trace File: ''' + @tracefile + '.trc''';
GOTO finish;
error:
PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));
finish:
GO
---------------------------------------------执行存储过程--------------------------------------------------------------------------
DECLARE @dbid AS INT, @traceid AS INT;
SET @dbid = DB_ID('Performance'); --这里是跟踪'Performance'数据库的运行情况
EXEC dbo.PerfworkloadTraceStart
@dbid = @dbid,
@tracefile = 'Z:\Perfworkload 20090212.trc',
@traceid = @traceid OUTPUT;
这里要记住 @traceid 这个跟踪的ID
EXEC sp_trace_setstatus 2, 0;
EXEC sp_trace_setstatus 2, 2;
可以对这个跟踪进行停止(0) 和关闭(2)
- 一个可以跟踪数据库情况的存储过程
- 可以自定义导出数据库存储过程(含加密过程)的一个存储过程
- 查看数据库死锁情况的存储过程
- 可以自定义导出数据库的表结构(视图)的一个存储过程
- 写了一个针对一个的所有表进行的存储过程!可以方便的清空数据库!
- 一个数据库解锁的存储过程sp_who_lock
- 一个数据库解锁的存储过程sp_who_lock
- JDBC可以调用数据库提供的存储过程/函数
- 一个可以将汉字转为拼音的存储过程
- 可以终止的存储过程
- 数据库的存储过程
- 数据库的存储过程。
- 数据库的存储过程
- 数据库的存储过程
- 数据库的存储过程
- 数据库的存储过程
- 数据库 的存储过程
- 数据库的存储过程
- HTML5之表单详解
- Delphi编程驿站,网络交流的空间站!
- 12个ajax弹出层效果
- 类中static的使用
- Ajax Toolkit 中的CalendarExtender控件
- 一个可以跟踪数据库情况的存储过程
- 推荐8个很酷很有用的 HTML5 应用程序
- 关于c#中的Timer控件的简单用法
- javascript面试题
- RogerWang
- 什么是“文档类”、“主类”、“启动类”?
- Java创建线程的两种方法
- java.sql.SQLException: 索引中丢失 IN 或 OUT 参数::
- 为什么应该选择Java而不是C#?