Post reply

Shrink log files via a stored procedure

  • June 4, 2008 at 7:11 am

    #71427

    I need to periodically shrink my log files using a stored procedure

    here is the current code i use, running via SQL Server Analyzer

    USE Service_TimeSheets;

    GO

    ALTER DATABASE Service_TimeSheets

    SET RECOVERY SIMPLE;

    GO

    DBCC SHRINKFILE (Service_TimeSheets_Log, 200);

    GO

    ALTER DATABASE Service_TimeSheets

    SET RECOVERY FULL;

    GO

    placing the same syntax in a sp fails - evidently the USE statement is a problem, as well as some other syntax issues.

    Can anyone assist?

    thanks,

  • June 4, 2008 at 8:14 am

    #824205

    GO is not a T-Sql construct. It is only recognized by SQL Server utilities such as QA. See BOL for explanation.

    It's not recommended that you force a shrink on the log file. Are you backing up the transaction log on a regular basis? If so, this will empty (truncate) the log file but not shrink the file size. Shrinking the log file size could result in performance degradation because it forces the database to grow the file. Backing up the log file on a regular basis should keep it at a fairly constant size.

    If you have to shrink the file, you can create a job with your code without the GO's.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply