之前在SQL 2008 R2上用
dbcc traceon(3604,1200,1211)
begin tran
select * from table...
commit tran
在查询结果的信息选项卡里,可看到事务加锁/释放锁的过程,但现在再用就没有了,
只提示"(1 row(s) affected)". 请问该怎么设置.
dbcc traceon(3604,1200,1211)
begin tran
select * from table...
commit tran
在查询结果的信息选项卡里,可看到事务加锁/释放锁的过程,但现在再用就没有了,
只提示"(1 row(s) affected)". 请问该怎么设置.
DBCC常用跟踪标记260:输出有关扩展存储过程动态链接库(DLL)的版本信息1204:返回参与死锁的锁的类型以及当前受影响的命令2528:通过DBCC CHECKDB、DBCC CHECKFILEGROUP和DBCC CHECKTABLE禁用对象的并行检查3205:禁用磁带驱动程序的硬件压缩3604:将跟踪结果输出的屏幕3605:将跟踪结果输出的SQL SERVER2000的错误日志文件8602:停止索引提示功能8722:停止最优化提示功能8755:停止锁提示功能
dbcc traceon(3604,1200,1211,-1)
begin tran
select * from dbo.errorlog1
commit tran
dbcc traceoff(3604,1200,1211,-1)
之前我用dbcc traceon(3604,1200,1211)是可以的,在查询分析器里能看到锁的申请/释放过程.
但忘了是SQL2000还是SQL2008了, 不知是SQL2008没这个1200跟踪标志还是哪里没设置好?
2.你是直接读取系统日志的方式,跟xp_readerrorlog效果是一样的.
你在sql2000上执行是有的05,08这个1200 trace flag被取消了。 so...
Process 52 acquiring S lock on PAGE: 5:1:2012 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2011Process 52 acquiring S lock on PAGE: 5:1:2013 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2012Process 52 acquiring S lock on PAGE: 5:1:2014 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2013Process 52 acquiring S lock on PAGE: 5:1:2015 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2014Process 52 acquiring S lock on PAGE: 5:1:2016 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2015Process 52 acquiring S lock on PAGE: 5:1:2017 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2016Process 52 acquiring S lock on PAGE: 5:1:2018 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2017Process 52 acquiring S lock on PAGE: 5:1:2019 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2018Process 52 acquiring S lock on PAGE: 5:1:2020 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2019Process 52 acquiring S lock on PAGE: 5:1:2021 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2020Process 52 acquiring S lock on PAGE: 5:1:2022 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2021Process 52 acquiring S lock on PAGE: 5:1:2023 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2022Process 52 acquiring S lock on PAGE: 5:1:2024 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2023Process 52 acquiring S lock on PAGE: 5:1:2025 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2024Process 52 acquiring S lock on PAGE: 5:1:2026 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2025Process 52 acquiring S lock on PAGE: 5:1:2027 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2026Process 52 acquiring S lock on PAGE: 5:1:2028 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2027Process 52 acquiring S lock on PAGE: 5:1:2029 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2028Process 52 acquiring S lock on PAGE: 5:1:2030 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2029Process 52 acquiring S lock on PAGE: 5:1:2031 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2030Process 52 acquiring S lock on PAGE: 5:1:2032 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2031Process 52 acquiring S lock on PAGE: 5:1:2033 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2032Process 52 acquiring S lock on PAGE: 5:1:2034 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2033Process 52 acquiring S lock on PAGE: 5:1:2035 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2034Process 52 acquiring S lock on PAGE: 5:1:2036 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2035Process 52 acquiring S lock on PAGE: 5:1:2037 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2036Process 52 acquiring S lock on PAGE: 5:1:2038 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2037Process 52 acquiring S lock on PAGE: 5:1:2039 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2038Process 52 acquiring S lock on PAGE: 5:1:2040 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2039Process 52 acquiring S lock on PAGE: 5:1:2041 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2040Process 52 acquiring S lock on PAGE: 5:1:2042 (class bit0 ref1) result: OKProcess 52 releasing lock on PAGE: 5:1:2041Process 52 releasing lock on PAGE: 5:1:2042
(20000 row(s) affected)
Process 52 releasing lock on OBJECT: 5:85575343:0 ---------------------------------------------------------
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) R2不行么?
但使用-1代价太大,改变的是全局的设置.造成系统日志(errorlog)数据量很大.
我只要当前连线有这个功能就行呢?去掉-1,就没有输出了.
说的对,2005,2008都要加-1才行。SQL2005/2008将跟踪标志1204\3604的行为改变了。
说的对,2005,2008都要加-1才行。SQL2005/2008将跟踪标志1204\3604等的行为改变了。