Dynamic Shrink Log file on server

Question

Dynamic Shrink Log file on server

Mitesh Oswal

SSCrazy

Points: 2798
More actions
August 9, 2014 at 11:52 am

#289693

Comments posted to this topic are about the item Dynamic Shrink Log file on server
Regards,
Mitesh OSwal
+918698619998

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

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

webtomte Ten Centuries Points: 1135 More actions · Answer 1

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.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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

webrunner SSC-Dedicated Points: 31862 More actions · Answer 4

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

SQL aus HH SSC Eights! Points: 833 More actions · Answer 5

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