Sunday, June 10, 2012

Display All System-Named Constraints in All User Databases

In my previous post, I mentioned that system-named objects get arbitrary names (like, "PK__Executio__05F5D74515DA3E5D"), and that this makes comparing database schemas difficult. To fix this problem, you have to rename the offending constraints, and to do that, you have to find them. So, this script displays all the system-named constraints in all the databases.

-- Display all the system-named constraints in all user databases. 
DECLARE @Sql NVARCHAR(MAX) 

SET @Sql = 'USE [?] '                                                                     + CHAR(10)
         +                                                                                  CHAR(10)
         + 'IF DB_ID() > 4 '                                                              + CHAR(10)     
         + '   AND EXISTS (SELECT * FROM sys.key_constraints WHERE is_system_named = 1) ' + CHAR(10)    
         +                                                                                  CHAR(10)
         + '    SELECT ''?''                           AS ''Database'' '                  + CHAR(10)
         + '         , OBJECT_NAME(parent_object_id)   AS ''Table'' '                     + CHAR(10)
         + '         , name                            AS ''Constraint'' '                + CHAR(10)
         + '      FROM sys.key_constraints '                                              + CHAR(10)
         + '     WHERE is_system_named = 1'
         
PRINT @Sql
EXEC sp_MSforeachdb @sql

The results will look something like this:

Database        Table                  Constraint 
ReportServer    ExecutionLogStorage    PK__Executio__05F5D74515DA3E5D 

Saturday, June 9, 2012

Script to Drop and Re-Create Column Statistics

If you're like me, you use a third-party tool to compare SQL Server database schemas. No matter how hard we try to keep schemas in synch, some little change always creeps in, and being able to confirm that two database schemas are exactly alike helps us sleep at night.

Schemas can differ in important ways (who added that stored procedure?) or in seemingly trivial ways (okay, there's an extra space at the end of the column, I can live with that). One difference that may seem trivial, but isn't, is statistics, specifically column statistics. I learned that the hard way years ago, when I published an upgrade script that tried to drop a column from a table: the script failed for some users, because that column happened to have a statistics object on it. Turns out you can't drop a column with statistics: you have to drop the statistics first. Didn't know that.

Another annoying thing about statistics is that if the system creates them, they'll end up with random (sorry, arbitrary) names, and of course the names will be different between databases. This becomes an issue when you're trying to prove that two schemas are the same, and the screen is cluttered with these bogus differences. Yeah, I could disable checking statistics names entirely in my third-party tool, but that's just... No.

So, here's a script that will drop all the column statistics on all the tables in the current database, and then manually re-creates them. Not something you'd want to do on a production machine during peak hours, but very handy in a development environment.

-- Drop all column statistics on all tables.  We join sys.tables to ensure 
-- these are user (not system) tables.  Note that statistics for indexes 
-- cannot be dropped; the index itself has to be dropped to get rid of its 
-- statistics. 
DECLARE @Sql        NVARCHAR(MAX)       SET @Sql       = ''
DECLARE @TableName  sysname             SET @TableName = ''
DECLARE @StatsName  sysname             SET @StatsName = ''

DECLARE cur CURSOR LOCAL FOR
SELECT OBJECT_NAME(s.object_id)   AS 'TableName'
     , s.name                     AS 'StatsName'
  FROM sys.stats     s 
  JOIN sys.tables    t
    ON s.object_id = t.object_id
 WHERE s.object_id > 100
   AND s.name NOT IN 
         (SELECT name FROM sys.indexes WHERE object_id = s.object_id)

OPEN cur
FETCH NEXT FROM cur INTO @TableName, @StatsName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = 'DROP STATISTICS ' + QUOTENAME(@TableName) + '.' + QUOTENAME(@StatsName)
    PRINT @Sql
    EXEC sp_executesql @Sql
    FETCH NEXT FROM cur INTO @TableName, @StatsName
END

CLOSE cur 
DEALLOCATE cur 

-- Create the statistics for all tables and columns in the database that don't 
-- already exist.  This is much easier than using CREATE STATISTICS on each 
-- table, as you can only do 16 columns at a time, and have to name them each. 
RAISERROR('Creating statistics on all tables and columns that are missing them', 10, 1) WITH NOWAIT, LOG
EXEC sp_createstats @indexonly = 'NO', @fullscan = 'FULLSCAN', @norecompute ='NO'

-- Set the automatic UPDATE STATISTICS setting to 'ON' for all indexes and 
-- statistics for all tables and indexed views in the database. 
RAISERROR('Running sp_autostats for all tables...', 10, 1) WITH NOWAIT, LOG
EXEC sp_MSforeachtable '  PRINT ''?''   EXEC sp_autostats ''?'', @flagc = ''ON'' '

-- Display the new names of the column indexes. 
SELECT OBJECT_NAME(s.object_id)   AS 'TableName'
     , s.name                     AS 'StatsName'
  FROM sys.stats     s 
  JOIN sys.tables    t
    ON s.object_id = t.object_id
 WHERE s.object_id > 100
   AND s.name NOT IN 
         (SELECT name FROM sys.indexes WHERE object_id = s.object_id)

What can you expect from this script? Well, I ran this against my local ReportServer database (I'm not recommending that you do that, mind you), and got the following results:

DROP STATISTICS [ConfigurationInfo].[_WA_Sys_00000003_0425A276]
DROP STATISTICS [Catalog].[_WA_Sys_00000014_060DEAE8]
DROP STATISTICS [Users].[_WA_Sys_00000004_1273C1CD]
DROP STATISTICS [Users].[_WA_Sys_00000003_1273C1CD]
DROP STATISTICS [Users].[_WA_Sys_00000005_1273C1CD]
DROP STATISTICS [Policies].[_WA_Sys_00000002_173876EA]
DROP STATISTICS [ModelItemPolicy].[_WA_Sys_00000004_1920BF5C]
DROP STATISTICS [SecData].[_WA_Sys_00000003_1B0907CE]
DROP STATISTICS [Event].[_WA_Sys_00000007_2A4B4B5E]
DROP STATISTICS [Event].[_WA_Sys_00000006_2A4B4B5E]
DROP STATISTICS [Subscriptions].[_WA_Sys_00000010_2D27B809]
DROP STATISTICS [SnapshotData].[_WA_Sys_00000009_34C8D9D1]
DROP STATISTICS [SnapshotData].[_WA_Sys_0000000A_34C8D9D1]
DROP STATISTICS [ChunkData].[_WA_Sys_00000005_36B12243]
DROP STATISTICS [ChunkData].[_WA_Sys_00000004_36B12243]
DROP STATISTICS [Notifications].[_WA_Sys_00000012_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_0000000C_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_00000011_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_0000000F_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_00000004_3A81B327]
DROP STATISTICS [RunningJobs].[_WA_Sys_0000000A_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000006_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000008_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000002_48CFD27E]
DROP STATISTICS [DBUpgradeHistory].[_WA_Sys_00000002_731B1205]
DROP STATISTICS [Keys].[_WA_Sys_00000004_7E6CC920]
DROP STATISTICS [Keys].[_WA_Sys_00000006_7E6CC920]
Creating statistics on all tables and columns that are missing them
Table 'ReportServer.dbo.History': Creating statistics for the following columns:
     SnapshotDate
Table 'ReportServer.dbo.ConfigurationInfo': Creating statistics for the following columns:
     Value
Table 'ReportServer.dbo.Catalog': Creating statistics for the following columns:
     Name
     Content
     Intermediate
     Property
     Description
     Hidden
     CreatedByID
     CreationDate
     ModifiedByID
     ModifiedDate
     MimeType
     SnapshotLimit
     Parameter
     PolicyID
     PolicyRoot
     ExecutionFlag
     ExecutionTime
     SubType
     ComponentID
Table 'ReportServer.dbo.UpgradeInfo': Creating statistics for the following columns:
     Status
Table 'ReportServer.dbo.SubscriptionsBeingDeleted': Creating statistics for the following columns:
     CreationDate
Table 'ReportServer.dbo.ModelDrill': Creating statistics for the following columns:
     ReportID
     ModelItemID
     Type
Table 'ReportServer.dbo.Segment': Creating statistics for the following columns:
     Content
Table 'ReportServer.dbo.ChunkSegmentMapping': Creating statistics for the following columns:
     StartByte
     LogicalByteCount
     ActualByteCount
Table 'ReportServer.dbo.ModelPerspective': Creating statistics for the following columns:
     ID
     PerspectiveID
     PerspectiveName
     PerspectiveDescription
Table 'ReportServer.dbo.CachePolicy': Creating statistics for the following columns:
     ExpirationFlags
     CacheExpiration
Table 'ReportServer.dbo.SegmentedChunk': Creating statistics for the following columns:
     ChunkFlags
     ChunkName
     ChunkType
     Version
     MimeType
Table 'ReportServer.dbo.Users': Creating statistics for the following columns:
     UserType
     AuthType
     UserName
Table 'ReportServer.dbo.ExecutionLogStorage': Creating statistics for the following columns:
     InstanceName
     ReportID
     UserName
     ExecutionId
     RequestType
     Format
     Parameters
     ReportAction
     TimeEnd
     TimeDataRetrieval
     TimeProcessing
     TimeRendering
     Source
     Status
     ByteCount
     RowCount
Table 'ReportServer.dbo.DataSource': Creating statistics for the following columns:
     Name
     Extension
     Link
     CredentialRetrieval
     Prompt
     ConnectionString
     OriginalConnectionString
     OriginalConnectStringExpressionBased
     UserName
     Password
     Flags
     Version
Table 'ReportServer.dbo.Policies': Creating statistics for the following columns:
     PolicyFlag
Table 'ReportServer.dbo.ModelItemPolicy': Creating statistics for the following columns:
     ModelItemID
     PolicyID
Table 'ReportServer.dbo.SecData': Creating statistics for the following columns:
     AuthType
     XmlDescription
     NtSecDescPrimary
     NtSecDescSecondary
Table 'ReportServer.dbo.Roles': Creating statistics for the following columns:
     Description
     TaskMask
     RoleFlags
Table 'ReportServer.dbo.PolicyUserRole': Creating statistics for the following columns:
     UserID
     PolicyID
Table 'ReportServer.dbo.Event': Creating statistics for the following columns:
     EventType
     EventData
     ProcessHeartbeat
     BatchID
Table 'ReportServer.dbo.Subscriptions': Creating statistics for the following columns:
     OwnerID
     Report_OID
     Locale
     InactiveFlags
     ExtensionSettings
     ModifiedByID
     ModifiedDate
     Description
     LastStatus
     EventType
     MatchData
     LastRunTime
     Parameters
     DataSettings
     DeliveryExtension
     Version
     ReportZone
Table 'ReportServer.dbo.ActiveSubscriptions': Creating statistics for the following columns:
     SubscriptionID
     TotalNotifications
     TotalSuccesses
     TotalFailures
Table 'ReportServer.dbo.SnapshotData': Creating statistics for the following columns:
     CreatedDate
     ParamsHash
     QueryParams
     EffectiveParams
     Description
     DependsOnUser
     TransientRefcount
     ExpirationDate
     PageCount
     HasDocMap
     PaginationMode
     ProcessingFlags
Table 'ReportServer.dbo.ChunkData': Creating statistics for the following columns:
     ChunkFlags
     ChunkName
     ChunkType
     Version
     MimeType
     Content
Table 'ReportServer.dbo.Notifications': Creating statistics for the following columns:
     SubscriptionID
     ActivationID
     ReportID
     SnapShotDate
     ExtensionSettings
     Locale
     Parameters
     Attempt
     SubscriptionLastRunTime
     DeliveryExtension
     SubscriptionOwnerID
     IsDataDriven
     BatchID
     ProcessHeartbeat
     Version
     ReportZone
Table 'ReportServer.dbo.Batch': Creating statistics for the following columns:
     Action
     Item
     Parent
     Param
     BoolParam
     Content
     Properties
Table 'ReportServer.dbo.Schedule': Creating statistics for the following columns:
     StartDate
     Flags
     NextRunTime
     LastRunTime
     EndDate
     RecurrenceType
     MinutesInterval
     DaysInterval
     WeeksInterval
     DaysOfWeek
     DaysOfMonth
     Month
     MonthlyWeek
     State
     LastRunStatus
     ScheduledRunTimeout
     CreatedById
     EventType
     EventData
     Type
     ConsistancyCheck
     Path
Table 'ReportServer.dbo.ReportSchedule': Creating statistics for the following columns:
     ReportAction
Table 'ReportServer.dbo.RunningJobs': Creating statistics for the following columns:
     StartDate
     RequestName
     RequestPath
     UserId
     Description
     Timeout
     JobAction
     JobType
     JobStatus
Table 'ReportServer.dbo.ServerParametersInstance': Creating statistics for the following columns:
     ParentID
     Path
     CreateDate
     ModifiedDate
     Timeout
     ParametersValues
Table 'ReportServer.dbo.DBUpgradeHistory': Creating statistics for the following columns:
     DbVersion
     User
     DateTime
Table 'ReportServer.sys.queue_messages_1977058079': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.sys.queue_messages_2009058193': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.dbo.DataSets': Creating statistics for the following columns:
     Name
Table 'ReportServer.sys.queue_messages_2041058307': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.sys.filestream_tombstone_2073058421': Creating statistics for the following columns:
     oplsn_bOffset
     oplsn_slotid
     rowset_guid
     column_guid
     filestream_value_name
     transaction_sequence_num
     status
Table 'ReportServer.sys.syscommittab': Creating statistics for the following columns:
     commit_lbn
     commit_csn
     commit_time
     dbfragid
Table 'ReportServer.dbo.Keys': Creating statistics for the following columns:
     MachineName
     InstanceName
     Client
     PublicKey
     SymmetricKey
Table 'ReportServer.dbo.ServerUpgradeHistory': Creating statistics for the following columns:
     ServerVersion
     User
     DateTime
 
Statistics have been created for the 253 listed columns of the above tables.
Running sp_autostats for all tables...
[dbo].[History]
[dbo].[ConfigurationInfo]
[dbo].[Catalog]
[dbo].[UpgradeInfo]
[dbo].[SubscriptionsBeingDeleted]
[dbo].[ModelDrill]
[dbo].[Segment]
[dbo].[ChunkSegmentMapping]
[dbo].[ModelPerspective]
[dbo].[CachePolicy]
[dbo].[SegmentedChunk]
[dbo].[Users]
[dbo].[ExecutionLogStorage]
[dbo].[DataSource]
[dbo].[Policies]
[dbo].[ModelItemPolicy]
[dbo].[SecData]
[dbo].[Roles]
[dbo].[PolicyUserRole]
[dbo].[Event]
[dbo].[Subscriptions]
[dbo].[ActiveSubscriptions]
[dbo].[SnapshotData]
[dbo].[ChunkData]
[dbo].[Notifications]
[dbo].[Batch]
[dbo].[Schedule]
[dbo].[ReportSchedule]
[dbo].[RunningJobs]
[dbo].[ServerParametersInstance]
[dbo].[DBUpgradeHistory]
[dbo].[DataSets]
[dbo].[Keys]
[dbo].[ServerUpgradeHistory]

TableName          StatsName
History                  SnapshotDate
ConfigurationInfo        Value
Catalog        Name
Catalog        Content
Catalog        Intermediate
Catalog        Property
Catalog        Description
Catalog        Hidden
Catalog        CreatedByID
Catalog        CreationDate
Catalog        ModifiedByID
Catalog        ModifiedDate
Catalog        MimeType
Catalog        SnapshotLimit
Catalog        Parameter
Catalog        PolicyID
Catalog        PolicyRoot
Catalog        ExecutionFlag
Catalog        ExecutionTime
Catalog        SubType
Catalog        ComponentID
UpgradeInfo        Status
SubscriptionsBeingDeleted        CreationDate
ModelDrill        ReportID
ModelDrill        ModelItemID
ModelDrill        Type
Segment        Content
ChunkSegmentMapping        StartByte
ChunkSegmentMapping        LogicalByteCount
ChunkSegmentMapping        ActualByteCount
ModelPerspective        ID
ModelPerspective        PerspectiveID
ModelPerspective        PerspectiveName
ModelPerspective        PerspectiveDescription
CachePolicy        ExpirationFlags
CachePolicy        CacheExpiration
SegmentedChunk        ChunkFlags
SegmentedChunk        ChunkName
SegmentedChunk        ChunkType
SegmentedChunk        Version
SegmentedChunk        MimeType
Users        UserType
Users        AuthType
Users        UserName
ExecutionLogStorage        InstanceName
ExecutionLogStorage        ReportID
ExecutionLogStorage        UserName
ExecutionLogStorage        ExecutionId
ExecutionLogStorage        RequestType
ExecutionLogStorage        Format
ExecutionLogStorage        Parameters
ExecutionLogStorage        ReportAction
ExecutionLogStorage        TimeEnd
ExecutionLogStorage        TimeDataRetrieval
ExecutionLogStorage        TimeProcessing
ExecutionLogStorage        TimeRendering
ExecutionLogStorage        Source
ExecutionLogStorage        Status
ExecutionLogStorage        ByteCount
ExecutionLogStorage        RowCount
DataSource        Name
DataSource        Extension
DataSource        Link
DataSource        CredentialRetrieval
DataSource        Prompt
DataSource        ConnectionString
DataSource        OriginalConnectionString
DataSource        OriginalConnectStringExpressionBased
DataSource        UserName
DataSource        Password
DataSource        Flags
DataSource        Version
Policies        PolicyFlag
ModelItemPolicy        ModelItemID
ModelItemPolicy        PolicyID
SecData        AuthType
SecData        XmlDescription
SecData        NtSecDescPrimary
SecData        NtSecDescSecondary
Roles        Description
Roles        TaskMask
Roles        RoleFlags
PolicyUserRole        UserID
PolicyUserRole        PolicyID
Event        EventType
Event        EventData
Event        ProcessHeartbeat
Event        BatchID
Subscriptions        OwnerID
Subscriptions        Report_OID
Subscriptions        Locale
Subscriptions        InactiveFlags
Subscriptions        ExtensionSettings
Subscriptions        ModifiedByID
Subscriptions        ModifiedDate
Subscriptions        Description
Subscriptions        LastStatus
Subscriptions        EventType
Subscriptions        MatchData
Subscriptions        LastRunTime
Subscriptions        Parameters
Subscriptions        DataSettings
Subscriptions        DeliveryExtension
Subscriptions        Version
Subscriptions        ReportZone
ActiveSubscriptions        SubscriptionID
ActiveSubscriptions        TotalNotifications
ActiveSubscriptions        TotalSuccesses
ActiveSubscriptions        TotalFailures
SnapshotData        CreatedDate
SnapshotData        ParamsHash
SnapshotData        QueryParams
SnapshotData        EffectiveParams
SnapshotData        Description
SnapshotData        DependsOnUser
SnapshotData        TransientRefcount
SnapshotData        ExpirationDate
SnapshotData        PageCount
SnapshotData        HasDocMap
SnapshotData        PaginationMode
SnapshotData        ProcessingFlags
ChunkData        ChunkFlags
ChunkData        ChunkName
ChunkData        ChunkType
ChunkData        Version
ChunkData        MimeType
ChunkData        Content
Notifications        SubscriptionID
Notifications        ActivationID
Notifications        ReportID
Notifications        SnapShotDate
Notifications        ExtensionSettings
Notifications        Locale
Notifications        Parameters
Notifications        Attempt
Notifications        SubscriptionLastRunTime
Notifications        DeliveryExtension
Notifications        SubscriptionOwnerID
Notifications        IsDataDriven
Notifications        BatchID
Notifications        ProcessHeartbeat
Notifications        Version
Notifications        ReportZone
Batch        Action
Batch        Item
Batch        Parent
Batch        Param
Batch        BoolParam
Batch        Content
Batch        Properties
Schedule        StartDate
Schedule        Flags
Schedule        NextRunTime
Schedule        LastRunTime
Schedule        EndDate
Schedule        RecurrenceType
Schedule        MinutesInterval
Schedule        DaysInterval
Schedule        WeeksInterval
Schedule        DaysOfWeek
Schedule        DaysOfMonth
Schedule        Month
Schedule        MonthlyWeek
Schedule        State
Schedule        LastRunStatus
Schedule        ScheduledRunTimeout
Schedule        CreatedById
Schedule        EventType
Schedule        EventData
Schedule        Type
Schedule        ConsistancyCheck
Schedule        Path
RunningJobs        StartDate
RunningJobs        RequestName
RunningJobs        RequestPath
RunningJobs        UserId
RunningJobs        Description
RunningJobs        Timeout
RunningJobs        JobAction
RunningJobs        JobType
RunningJobs        JobStatus
ServerParametersInstance        ParentID
ServerParametersInstance        Path
ServerParametersInstance        CreateDate
ServerParametersInstance        ModifiedDate
ServerParametersInstance        Timeout
ServerParametersInstance        ParametersValues
DBUpgradeHistory        DbVersion
DBUpgradeHistory        User
DBUpgradeHistory        DateTime
DataSets        Name
Keys        MachineName
Keys        InstanceName
Keys        Client
Keys        PublicKey
Keys        SymmetricKey
ServerUpgradeHistory        ServerVersion
ServerUpgradeHistory        User
ServerUpgradeHistory        DateTime

Note that the arbitrarily named column statistics such as [ConfigurationInfo].[_WA_Sys_00000003_0425A276] got renamed to the more standard (and thus, more easily-compared) name [ConfigurationInfo].[Value].

Saturday, April 28, 2012

Proc to Fix the Too Many Virtual Log Files Problem

There could be a beast lurking in your log files, robbing you of performance, and you might not even know its name. But fear not - I'll give you a weapon to kill the beast.

If you've never heard of "virtual log files" (VLFs), read these articles: they'll well-written, short, and extremely informative. You owe it to yourself to read the originals, but I'll sum them up briefly here.


VLFs: A Quick Overview

What we think of as a log file is actually a bunch of interconnected of disk space chunks called VLFs. A log file can consist of a few, or hundreds, or even thousands of VLFs, and a new one is created every time the log file needs to expand. In a perfect world, you could create your log file to be the biggest it would ever need to be; since the log file would never need to expand, there would be no new VLFs created. Problem solved.

Unfortunately, if you're reading this, you don't live in a perfect world. Very likely, you're responsible for databases that have too many VLFs, and having too many VLFs is bad, especially for performance. So, your first step is to check your databases' log files to see how many VLFs they have. Then, if they have too many VLFs, you can fix the problem with a few simple commands. Fortunately for you, I've created a stored procedure that does both steps, so you can spend your time on more interesting things.

(How many VLFs is "too many"? It depends. In this blog, our bias is toward providing tools to get things done; I leave theoretical questions to people far more qualified. I picked "50" because Kimberly said so, and that's good enough for me.)


The Weapon

The script below will create a stored procedure that will examine and, if necessary, fix, the VLF problem in all the databases in your instance. Usually, you'll only need to run this once in a very long while, but again, it depends. Note the "@ExecuteFix" argument - setting this flag to zero allows you to see what T-SQL code would be executed; setting it to one actually executes that T-SQL.

-- Drop if it already exists. 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_FixExcessiveLogFileVlfs]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------------------
-- Display, logs, and fixes all databases with more than 50 Virtual Log Files. 
--                                                                             
-- See: http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/
-- See: http://sqlskills.com/blogs/kimberly/post/8-steps-to-better-transaction-log-throughput.aspx
-- See: http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx
--                                                                             
-- Created 2012-04-28 - Larry Leonard - http://SqlSoundings.blogspot.com/      
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs]  @ExecuteFix INT = 0
AS
BEGIN
    SET NOCOUNT ON

    -- Set up temp tables. 
    IF OBJECT_ID('tempdb..#stage') IS NOT NULL
        DROP TABLE #stage
        
    CREATE TABLE #stage
    (
        FileID          INT
      , FileSizeBytes   BIGINT
      , StartOffset     BIGINT
      , FSeqNo          BIGINT
      , [Status]        BIGINT
      , Parity          BIGINT
      , CreateLSN       NUMERIC(38)
    )
     
    IF OBJECT_ID('tempdb..#results') IS NOT NULL
        DROP TABLE #results

    CREATE TABLE #results
    (
        DatabaseName    sysname
      , LogFileName     sysname
      , VlfCount        INT
      , LogFileSizeMB   INT 
    )
     
    -- Gather the log file information into the temp tables. 
    DECLARE @Sql NVARCHAR(MAX)

    SET @Sql = 'USE [?] '
             + ''
             + 'INSERT INTO #stage '
             + '  EXEC sp_executesql N''DBCC LOGINFO ([?])'' '
             + ''
             + 'INSERT INTO #results '
             + '    SELECT DB_NAME(), MIN(FILE_NAME(FileID)), COUNT(*), SUM(FileSizeBytes) / 1024 / 1024 '
             + '      FROM #stage '
             + ' '
             + 'TRUNCATE TABLE #stage '
             
    EXEC sp_msforeachdb @Sql

    -- Log the results. 
    DECLARE @DatabaseName   sysname
    DECLARE @LogFileName    sysname
    DECLARE @VlfCount       INT 
    DECLARE @LogFileSizeMB  INT

    DECLARE cur CURSOR LOCAL FOR
        SELECT DatabaseName 
             , VlfCount
             , LogFileSizeMB
          FROM #results
      ORDER BY VlfCount DESC

    OPEN cur
    FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        RAISERROR('Database: %25s  -  Virtual Log Files: %4d  -  Size: %5d MB', 10, 1, @DatabaseName, @VlfCount, @LogFileSizeMB) WITH NOWAIT, LOG
        FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB
    END
          
    CLOSE cur
    DEALLOCATE cur

    -- Display the results. 
    RAISERROR(' ', 10, 1) WITH NOWAIT
    
    SELECT *
      FROM #results
     ORDER BY VlfCount DESC

    -- Fix the log files with too many VLFs.  We add two MB to the size because 
    -- ALTER DATABASE requires that we make the log larger.  Adding one doesn't 
    -- work, because of rounding issues when dividing, I think. 
    DECLARE cur CURSOR LOCAL FOR
        SELECT DatabaseName 
             , LogFileName
             , VlfCount
             , LogFileSizeMB
          FROM #results
         WHERE VlfCount > 50
      ORDER BY VlfCount DESC

    OPEN cur
    FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Sql = 'USE ' + @DatabaseName                                          + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'CHECKPOINT '                                                   + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'DBCC SHRINKFILE (' + @LogFileName + ', 0, TRUNCATEONLY) '      + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'ALTER DATABASE ' + @DatabaseName                               + CHAR(10)
                 + '   MODIFY FILE '                                               + CHAR(10)
                 + '( '                                                            + CHAR(10)
                 + '      NAME = ' + @LogFileName                                  + CHAR(10)
                 + '    , SIZE = ' + CAST(@LogFileSizeMB + 2 AS NVARCHAR) + ' MB ' + CHAR(10)
                 + ') '                                                            + CHAR(10)

        IF @ExecuteFix = 0
        BEGIN
            RAISERROR('-- Proposed T-SQL code for database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT, LOG
        END
        ELSE
        BEGIN
            RAISERROR('Processing database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG
            EXEC sp_executesql @Sql
        END
        
        FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB
    END
          
    CLOSE cur
    DEALLOCATE cur

    -- Done. 
    IF OBJECT_ID('tempdb..#stage') IS NOT NULL
        DROP TABLE #stage

    IF OBJECT_ID('tempdb..#results') IS NOT NULL
        DROP TABLE #results
END

/* Testbed. 
 
 EXEC dbo.dba_FixExcessiveLogFileVlfs @ExecuteFix = 0 
 
 */

GO