SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Shrink Log file on server


Dynamic Shrink Log file on server

Author
Message
Mitesh Oswal
Mitesh Oswal
SSC Eights!
SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)

Group: General Forum Members
Points: 968 Visits: 653
Comments posted to this topic are about the item Dynamic Shrink Log file on server

Regards,
Mitesh OSwal
+918698619998
webtomte
webtomte
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 226
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
ScottPletcher
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10911 Visits: 7248
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41197 Visits: 18565
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

webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4962 Visits: 3934
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
NorthernDBA
NorthernDBA
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 47429
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search