Posted by Greg D. Moore \(Strider\) on 06/01/05 07:25

"New MSSQL DBA" <boscong88@gmail.com> wrote in message
news:1117598656.303098.141870@g49g2000cwa.googlegroups.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 :-)


>

[Back to original message]