Deadlock searches every 5 seconds

Rumor has it that a great way to wait for potential deadlock is to:
DBCC TRACEOn (3605, 1205, -1)
Ive done this for a while and have been please with the resaults. However I
just noticed in the Error Log that a Deadlock search is being done about
every 5 seconds. This is fine, but Im wondering if theres a way that the
search can write to the Error Log only if theres a Deadlock, not every time
it does a search? Also, if I ever needed to "Truncate" the Error Log , how
would I go about it?The behavior you're seeing is because you've turned on trace flag 1205. If
you turn on trace flag 1204, you'll only get error log output when we find a
deadlock. The overhead of -T1205 is pretty high, and the TF is
undocumented, so I would recommend turning it off and relying on the
documented -T1204 instead.
FYI, the 5 second deadlock search is by design. If we find a deadlock,
though, we will increase the frequency of searches temporarily.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Rumor has it that a great way to wait for potential deadlock is to:
> DBCC TRACEOn (3605, 1205, -1)
> Ive done this for a while and have been please with the resaults. However
> I just noticed in the Error Log that a Deadlock search is being done about
> every 5 seconds. This is fine, but Im wondering if theres a way that the
> search can write to the Error Log only if theres a Deadlock, not every
> time it does a search? Also, if I ever needed to "Truncate" the Error Log
> , how would I go about it?
>|||Perfect! Thanks.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> The behavior you're seeing is because you've turned on trace flag 1205.
> If you turn on trace flag 1204, you'll only get error log output when we
> find a deadlock. The overhead of -T1205 is pretty high, and the TF is
> undocumented, so I would recommend turning it off and relying on the
> documented -T1204 instead.
> FYI, the 5 second deadlock search is by design. If we find a deadlock,
> though, we will increase the frequency of searches temporarily.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
>|||To clarify, Traceon is a Server level setting, right?
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> The behavior you're seeing is because you've turned on trace flag 1205.
> If you turn on trace flag 1204, you'll only get error log output when we
> find a deadlock. The overhead of -T1205 is pretty high, and the TF is
> undocumented, so I would recommend turning it off and relying on the
> documented -T1204 instead.
> FYI, the 5 second deadlock search is by design. If we find a deadlock,
> though, we will increase the frequency of searches temporarily.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:egkCnvUNFHA.2748@.TK2MSFTNGP09.phx.gbl...
>|||Hi Chris
DBCC TRACEON is normally session level, but adding the -1 makes it take
effect for all sessions.
To start a fresh errorlog, you can run sp_cycle_errorlog.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uEkDybVNFHA.1392@.TK2MSFTNGP10.phx.gbl...
> To clarify, Traceon is a Server level setting, right?
>
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:uFJpQCVNFHA.1040@.TK2MSFTNGP12.phx.gbl...
>|||All sessions in the DB, or the Server?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23PcnYpZNFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Hi Chris
> DBCC TRACEON is normally session level, but adding the -1 makes it take
> effect for all sessions.
> To start a fresh errorlog, you can run sp_cycle_errorlog.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uEkDybVNFHA.1392@.TK2MSFTNGP10.phx.gbl...
>|||All sessions means all connections to the server.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uVGZi2hNFHA.2880@.TK2MSFTNGP10.phx.gbl...
> All sessions in the DB, or the Server?
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23PcnYpZNFHA.3668@.TK2MSFTNGP14.phx.gbl...
>