Tx Log Truncate and Deadlock

[New MSSQL DBA]

I have recently been assigned to take over several MSSQL environments
and found some of the existing practice confusing. As most of my
previous experiences are on Oracle and Unix platform so would like your
inputs and comments.

1) TX log truncate:
In the existing environment, there are scheduled jobs to truncate
transaction log of each database in the server and shrink them back to
a desired size, say 1G. These jobs are all scheduled BEFORE the daily
database FULL backup (about 1 hour before the start of daily backup).
There are no differential backup or transaction log backup. The codes
for the log truncate and shrikage are something like:
checkpoint
go
backup log Northwind with truncate_only
go
DBCC SHRINKFILE (Northwind_Log, 1000)
go

I am wondering is this a good practice? I am wondering why not just run
the DBCC SHRINKFILE command after the daily full backup. And also doing
log truncate before database full backup would risk data lost and
unrecoverable scenario.

2) Deadlock and blocking lock
I am a bit confused whether these are of the same definition as in
Oracle. For my understanding is that, blocking lock can persist and
the parties involved in a blocking lock can be waiting forever, and for
deadlock, once it occurred, it will be detected by MSSQL automatically
and one of the involving parties will be terminated to resolve it. I
was asked to monitor "DEADLOCK" in the database server. I originally
proposed to use SQL Profiler (as suggested in BOL) but was rejected
because of performance overhead and deemed unnecessary. I was told
that I can use, say a 15 minute interval, to monitor for blocking lock
and thus be able to detect deadlock. Well, I am not really sure about
that so would like your input as well.

Thanks a lot

[Greg D. Moore \(Strider\)]

"New MSSQL DBA" <bo*******@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I have recently been assigned to take over several MSSQL environments
and found some of the existing practice confusing. As most of my
previous experiences are on Oracle and Unix platform so would like your
inputs and comments.

1) TX log truncate:
In the existing environment, there are scheduled jobs to truncate
transaction log of each database in the server and shrink them back to
a desired size, say 1G. These jobs are all scheduled BEFORE the daily
database FULL backup (about 1 hour before the start of daily backup).
There are no differential backup or transaction log backup. The codes
for the log truncate and shrikage are something like:
checkpoint
go
backup log Northwind with truncate_only
go
DBCC SHRINKFILE (Northwind_Log, 1000)
go

I am wondering is this a good practice? I am wondering why not just run
the DBCC SHRINKFILE command after the daily full backup. And also doing
log truncate before database full backup would risk data lost and
unrecoverable scenario.
I'd call this a bad practice.

First, you really don't have a great amount of disaster recovery. Sure, you
may be able to back up the tail of the log if it fails before you do the
truncate, but what happens if it occurs 5 minutes after you truncate it?

Also, constantly shrinking the physical file and letting it grow again can
lead to on-disk fragmentation. You're better off keeping it a fixed size.

Personally, if nothing else I'd do at least ONE transaction log backup a day
(ideally more) and eliminate the truncate. This alone will make your DR
plans easier.

2) Deadlock and blocking lock
I am a bit confused whether these are of the same definition as in
Oracle. For my understanding is that, blocking lock can persist and
the parties involved in a blocking lock can be waiting forever, and for
deadlock, once it occurred, it will be detected by MSSQL automatically
and one of the involving parties will be terminated to resolve it. I
was asked to monitor "DEADLOCK" in the database server. I originally
proposed to use SQL Profiler (as suggested in BOL) but was rejected
because of performance overhead and deemed unnecessary. I was told
that I can use, say a 15 minute interval, to monitor for blocking lock
and thus be able to detect deadlock. Well, I am not really sure about
that so would like your input as well.

You're correct, deadlocks and blocking are different. Deadlocks HAVE to be
resolved (since by definition they will never resolve w/o an outside agent)
so SQL server flips a coin and kills a thread. Blocking though, in theory
will resolve on its own, if given enough time.

However, I'd say that in a properly designed system, blocking should be a
minor issue. It's not one I generally monitor in a production system unless
I'm looking for specific problems. You could do some sort of scheduled task
running every 15 minutes that dumps data to a table and compares from the
previous run 15 minutes ago. But then you have to be careful that they
really are the same threads, etc.. not different ones with the same spid,
etc.

Thanks a lot
Hope this helps. I'm a bit overtired :-)