Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    26

    Unanswered: Truncating Log files daily ..

    Hello -

    How can I truncate log files of all Databases daily automatically.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Either set the Recovery-Model to Simple or u may run this for each db:

    --TAKE A LOG BACKUP TO EMPTY OLD TRANSACTIONS
    BACKUP LOG [dbname] TO DISK = N'path' WITH NOINIT , NOUNLOAD , NAME = N'mybackup', NOSKIP , STATS = 10, NOFORMAT
    --TRUNCATE THE LOG
    DBCC SHRINKFILE(mbdb_log , TRUNCATEONLY)

    See BOL for further details.


    Howdy!

  3. #3
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    "mbdb_log" it's the logical file name i have for my db.
    u can check the file name using:
    sp_helpfile for the current db

  4. #4
    Join Date
    Jun 2003
    Posts
    26
    Is there is any script which we can schedule which will daily shrink and truncate transaction log every midnight.

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You will not gain any performance by shrinking the Tlog on daily basis, rather follow as suggested by Talat to keep the database recovery model to SIMPLE and perform reglular full backups.

    Refer to books online about database recovery models for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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