Results 1 to 3 of 3

Thread: Truncate Log

  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Unanswered: Truncate Log

    Hi there,

    I have a simple stored procedure that will truncate the log file but I now need to make this dynamic so that this proc can be run with any DB name. I know you can look up the logical filename in sysfiles but I'm not too sure how to write this procedure so that it will simply truncate the log file of the currently selected DB.

    This is what I have so far which as you can see is hardcoded:

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO


    CREATE PROCEDURE TruncateLog AS

    BACKUP LOG [FICaches] with TRUNCATE_ONLY


    DBCC SHRINKFILE (FICaches_log, 50)


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    Any help would be greatly appreciated!

    Thanks

    NewToSql

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Why to execute a seperate SP when you can control the log by keeping database in SIMPLE recovery model and shrink using DBCC SHRINKFILE in regular intervals.

    You can schedule a job to do so.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    Mainly because I call the tuncate log stored proc from perl scripts that are used within our software and the log file is truncated on an ad hoc basis after some specific updates on the DB have run from the perl scripts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •