SQL Server Performance

Stored Procedure to shrink log files

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by choideyoung, Nov 9, 2006.

  1. choideyoung New Member

    I am trying to create a store procedure to truncate and shrink the transaction log of all our databases (10)

    I was trying to use the script:
    Create procedure dbo.sp_truncate_resize_log
    as
    backup log (database name) with truncate_only
    DBCC Shrinkfile (database name_log,2)

    ---Repeating this for each database—

    But I get the message saying could not locate file (database_name_log) in sysfiles

    So then I tried to include the line

    Use (database_name) but then get a message saying can not include “Use” in a procedure

    What I want to do is to set up a job to run nightly to truncate and shrink the log file. We use simple recovery so I am not concerned with the info in the log file.

    What is the best way to accomplish what I need?

    Thank you
  2. vaddi New Member

    May be you can use :

    USE [databasename]
    GO
    backup log 'databasename' with truncate_only
    USE [databasename]
    GO
    DBCC SHRINKFILE (N'databasename_log' , 5)
    GO


    Thanks
  3. satya Moderator

    I would say why you are trying to stress the SQL server by shrinking the log & data on a regular basis.

    First of all you need to ascertain the log space usage by considering all the process & scheduled jobs, then set the value which is required to accomodate the above processess that will save your time to worry about log space usage and further if there is no issue in free disk space issues then need not worry.

    SIMPLE recovery model takes care of log space by truncating and when you have AUTOGROW enabled you are not helping the system.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  4. gurucb New Member

    Try this if it works.. I have not checked if it is working...
    sp_msforeachdb 'use ? select db_name() backup log ? with truncate_only'
  5. mmarovic Active Member

Share This Page