ADSM-L

Re: TDP SQL Transaction log not truncating

2004-04-07 00:34:18
Subject: Re: TDP SQL Transaction log not truncating
From: Chris Leonard <christopher-leonard AT UIOWA DOT EDU>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Tue, 6 Apr 2004 23:33:44 -0500
Is your DBA talking about shrinking the log file or truncating the log?  One
thing to look out for here is the fact that these are two different things,
and lots of SQL people can get confused about this.  Truncating a SQL Server
log is not the same as shrinking the physical file that houses the log
(which is a logical construct, not a physical construct).  When a log backup
(backup log dbname to ...) is run, the log is truncated (as long as
NO_TRUNCATE or NO_LOG is not specified).  This means that space in the log
file that is no longer needed for crash recovery is available for reuse.  It
does not, however, mean that the log file will shrink even one byte.  To
physically shrink the log file, you need to run a command like DBCC
SHRINKFILE.

Good information about this is available in the SQL Server Books Online
(sp3) in an article entitled "Shrinking the Transaction Log."  This is *not*
the same article that is online at microsoft.com, by the way.

It may be that everybody at your shop is already clear on this, but I
thought it was worth pointing out just in case.  And by the way, TSM just
does standard SQL backup calls - your DBAs can see this if they watch
sessions in the SQL Profiler.  The biggest problem with the TDP SQL client,
and in my opinion it is a fairly glaring one, is that they give you no way
to run RESTORE VERIFYONLY.  This is a nearly crippling problem with respect
to verifying backups in some environments - does anybody know if there are
any plans to add this functionality in the future?

Cheers,
Chris

_________________________________

Chris Leonard
MCSE, MCDBA, OCP, CIW
_________________________________

The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com
_________________________________