Dynamic Shrink Log file on server

  • Comments posted to this topic are about the item Dynamic Shrink Log file on server

    Regards,
    Mitesh OSwal
    +918698619998

  • Why do one want to shrink logfiles on a schedule, only to find that it probably is growing back to the same size the day after?

    The only thing you end up with is fragmented disks which WILL slow down your performance.

    Use shrinking with caution. There is a reason your logfile is big, find the cause and adjust.

    More logfile backups, setting BULKLOGGED if heavily used while importing data and so on...

    Investigate before using script like these on a regular basis.

  • What's proposed here is a really poor practice. You can't just arbitrarily shrink a SQL db log with no analysis because you could just be forcing it to be reallocated, which will cause huge wasted overhead.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Besides the blatant shrink is the lack of care for the recovery model and ensuring the recovery objectives are still in place.

    If this script is going to force a change to simple recovery, then it should perform a full backup as well. But placing a database in simple is not recommended for shrinking the log file. Nor is it possible in certain configurations (such as mirroring).

    I'd recommend against the use of a script such as this on a production server in most cases.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I agree with others who've posted that this approach, as listed, is bad practice.

    Instead, I would direct readers to the links below, which explain how to shrink a log file in stages after deciding (1) that it is unavoidable and (2) that it will be done not automatically but as part of a careful and approved change request. As I understand it, this kind of log management should be a last resort for a situation where the log file or disk space usage is out of control, and a stopgap measure is needed. Otherwise, the log file size needs to be planned as carefully as possible ahead of time, including growth increments, and never made part of a continuous shrink plan.

    http://www.sqlservercentral.com/Forums/Topic617680-146-1.aspx

    http://www.sqlservercentral.com/Forums/Attachment1915.aspx

    Good luck,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Hi,

    i understand all your replies and reasons not to use this script on a regular base.

    But if you know what you're doing in a case of an "incident" then this script is really helpful...

    Just to perform a shrink on all logfiles of all databases in an instance to "clean up" disk just for one time... (instead of building a new script for each database or using the gui...)

    I've found something strange in the script it can't handle database names which contains - (minus)

    Therefor i've added some ' to the @Filenname

    'DBCC SHRINKFILE('''+@FileName+''',1)'

    instead of

    'DBCC SHRINKFILE('+@FileName+',1)'

    so whole script must be...

    DECLARE @tbl TABLE

    (

    ID INT IDENTITY(1,1),

    DBNAME NVARCHAR(1000),

    [FileName] NVARCHAR(1000)

    )

    INSERT INTO @tbl

    SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles ST INNER JOIN Sys.databases SB on ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

    DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

    SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

    WHILE(@MinID <=@MaxID)

    BEGIN

    SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

    SELECT@RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

    SELECT@SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

    +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

    +N'DBCC SHRINKFILE('''+@FileName+''',1)'+CHAR(10)

    +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

    +CHAR(10)

    PRINT @SQL

    EXEC SP_EXECUTESQL @SQL

    SELECT @MinID = @MinID +1

    END

Viewing 6 posts - 1 through 5 (of 5 total)

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