[AccessD] Re: SQL Server transaction log size

Brett and Debbie,

Here is a stored procedure you can run to do what you need to do.

CREATE PROCEDURE [DBO].[usp_Truncate_Log] AS
BEGIN
      EXEC sp_dboption 'Your DB Name' , 'trunc. log on chkpt.','TRUE'
END
BEGIN
      CHECKPOINT
END
BEGIN
      EXEC sp_dboption 'Your DB Name' , 'trunc. log on chkpt.','FALSE'
END
BEGIN
      DBCC SHRINKFILE(Your Log File Name,1)
END

GO

Note that this does not backup the log.  It simple sets a checkpoint
and then truncates the log and recovers the space.

Robert

At 04:29 PM 2/4/2004 -0600, you wrote:
>Date: Wed, 4 Feb 2004 16:15:40 -0600
>From: Brett Barabash <BBarabash at TappeConstruction.com>
>Subject: RE: [AccessD] OT - SQL Server transaction log size
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <426071E0B0A6D311B3C0006008B0AB23AFE325 at TAPPEEXCH01>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>Thanks Debbie,
>One thing confuses me, though:
>
> >From what I've seen, it looks like when you issue a BACKUP LOG statement in
>SQL, it automatically truncates the log (which is why there is a NO_TRUNCATE
>option available).  However, our DB Maintenance plan that backs up the log
>file doesn't seem to truncate it.  There isn't even a truncate option.
>
>I was thinking about adding a step to the job, to execute a BACKUP LOG WITH
>TRUNCATE_ONLY command, followed by a DBCC_SHRINKFILE command to truncate the
>log and reclaim unused space.  Am I going about this the right way?
>
>Thanks again for all your help.
>Brett