tag:blogger.com,1999:blog-4149930.post3549935657978786318..comments2023-08-10T10:53:35.019-04:00Comments on SQL Soundings: Proc to Fix the Too Many Virtual Log Files ProblemLarry Leonardhttp://www.blogger.com/profile/01248746755565439137noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-4149930.post-54503359639978239142015-10-22T09:41:13.038-04:002015-10-22T09:41:13.038-04:00Hi Tim, I want to add square brackets around the D...Hi Tim, I want to add square brackets around the Database Name, where I can this in the code to do this?Rahim Shabbirhttps://www.blogger.com/profile/17826697298104617334noreply@blogger.comtag:blogger.com,1999:blog-4149930.post-79196556320104607342014-03-26T18:44:25.128-04:002014-03-26T18:44:25.128-04:00Tim's comment about harming the log backup cha...Tim's comment about harming the log backup chain I believe refers to issuing a "BACKUP LOG with TRUNCATE_ONLY" or "BACKUP LOG with NO_LOG", which I don't see in the script on this page. That said, the article by Andy Novak is more likely to actually shrink the log for an active database since it incorporates a "Backup Log" step as well as Checkpoint commands, in a loop. Also, remember that simply getting to some magical ~number~ of VLFs is not an ideal goal. It is possible to have too few _OR_ too many VLFs, as kt addresses in a later blog post: http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/Ehttps://www.blogger.com/profile/02914961490988345184noreply@blogger.comtag:blogger.com,1999:blog-4149930.post-25880003440038397392014-03-26T18:41:33.510-04:002014-03-26T18:41:33.510-04:00This comment has been removed by the author.Ehttps://www.blogger.com/profile/02914961490988345184noreply@blogger.comtag:blogger.com,1999:blog-4149930.post-31761296541950941022012-11-14T07:27:42.577-05:002012-11-14T07:27:42.577-05:00Be Careful about using this in production database...Be Careful about using this in production databases in FULL recovery mode. You will be trashing your LOG backup chain. In order to keep the LOG chain, run this to capture the script for growing the log file but use Andy Novak's procedure instead to shrink the log, by backing it up repeatedly. That way you preserve the log chain for restore purposes.<br /><br />http://www.mssqltips.com/sqlservertip/2097/how-to-shrink-the-transaction-log-file-in-sql-server-2008/Timnoreply@blogger.comtag:blogger.com,1999:blog-4149930.post-8528171472784921812012-11-13T00:13:34.199-05:002012-11-13T00:13:34.199-05:00My databases have two log files. The current scrip...My databases have two log files. The current script only shrinks the first log file.<br />I have modified the line to add a GROUP BY FILEID and that way both of my log files are corrected.<br /><br /> + ' SELECT DB_NAME(), MIN(FILE_NAME(FileID)), COUNT(*), SUM(FileSizeBytes) / 1024 / 1024 '<br /> + ' FROM #stage GROUP BY FILEID 'Timnoreply@blogger.comtag:blogger.com,1999:blog-4149930.post-34508711060983188462012-05-04T12:26:34.054-04:002012-05-04T12:26:34.054-04:00Thanks so much for posting, very useful for me.
M...Thanks so much for posting, very useful for me.<br /><br />My environment has database names with underscores and decimals. Use brackets to fix in the string passed to 'sp_msforeachdb':<br /><br />SET @Sql = 'USE [?] ' -- here<br /> + ''<br /> + 'INSERT INTO #stage '<br /> + ' EXEC sp_executesql N''DBCC LOGINFO ([?])'' ' -- here<br /> + ''<br /> + 'INSERT INTO #results '<br /> + ' SELECT DB_NAME(), MIN(FILE_NAME(FileID)), COUNT(*), SUM(FileSizeBytes) / 1024 / 1024 '<br /> + ' FROM #stage '<br /> + ' '<br /> + 'TRUNCATE TABLE #stage 'Dan Hhttp://www.expressnetsolutions.com/sqldchnoreply@blogger.comtag:blogger.com,1999:blog-4149930.post-89144472164568141162012-05-01T10:25:26.628-04:002012-05-01T10:25:26.628-04:00had to add recoveryunitld int to the beginning of ...had to add recoveryunitld int to the beginning of #stage for SS 2012<br />JimAnonymousnoreply@blogger.com