DATABASE_SNAPSHOT_CREATION on MSDB won't go away.

  • When looking at the log_reuse_wait_desc on sys.database I see DATABASE_SNAPSHOT_CREATION active on the MSDB database.

    It has been like this for 12 hours now. There are no snapshots being taken and log files are truncating fine etc. Checkdb ran last night as planned, but is not still running. I am not sure why this is showing or if it is anything to worry about.

    Would be grateful for any pointers as to if this is a problem or not.

    thanks.

  • Maddave (6/20/2014)


    There are no snapshots being taken and log files are truncating fine etc. Checkdb ran last night as planned, but is not still running.

    You could check these.

    url1: worrying-cause-log-growth-log_reuse_wait_desc[/url]

    url2: Factors That Can Delay Log Truncation

    In this scenario, log doesn't get truncated. You may check log space , for msdb,in 2 min interval using below code

    -- Find the database file space usage

    -- no harm in running on production server

    use msdb;

    SELECT

    [Time] = CURRENT_TIMESTAMP ,DbName=DB_NAME(), file_id

    ,[Status]=state_desc

    ,LogicalFileName=name

    ,FileSizeAllocated_MB= CONVERT(DECIMAL(8,2),size/128.0)

    ,[File_UsedSpace_MB] = CONVERT(DECIMAL(8,2),FILEPROPERTY(name, 'spaceused')/128.0)

    ,Maxsize_MB= CASE

    WHEN max_size < 0 THEN 'UNLIMITED'

    WHEN max_size = 0 THEN 'NO GROWTH, FILE SIZE IS FIXED'

    WHEN max_size > 0 THEN CONVERT(VARCHAR(15),max_size/128)

    END

    ,FileGrowth = CASE

    WHEN growth = 0 THEN 'No Autogrow option Set'

    WHEN growth > 0 AND is_percent_growth = 0

    THEN CONVERT(VARCHAR(15),CONVERT(DECIMAL(10,2),growth/128.0)) + ' MB'

    WHEN growth > 0 AND is_percent_growth = 1 THEN CONVERT(VARCHAR(15),growth) + ' %'

    END

    ,[ReadOnly] = CASE

    WHEN is_read_only = 1 THEN 'READ ONLY'

    WHEN is_read_only = 0 THEN 'READ WRITE'

    END

    FROM sys.database_files

    WHERE file_id = 2

    waitfor delay '00:02:00' -- pass 2 mins time

    use msdb;

    SELECT

    [Time] = CURRENT_TIMESTAMP ,DbName=DB_NAME(), file_id

    ,[Status]=state_desc

    ,LogicalFileName=name

    ,FileSizeAllocated_MB= CONVERT(DECIMAL(8,2),size/128.0)

    ,[File_UsedSpace_MB] = CONVERT(DECIMAL(8,2),FILEPROPERTY(name, 'spaceused')/128.0)

    ,Maxsize_MB= CASE

    WHEN max_size < 0 THEN 'UNLIMITED'

    WHEN max_size = 0 THEN 'NO GROWTH, FILE SIZE IS FIXED'

    WHEN max_size > 0 THEN CONVERT(VARCHAR(15),max_size/128)

    END

    ,FileGrowth = CASE

    WHEN growth = 0 THEN 'No Autogrow option Set'

    WHEN growth > 0 AND is_percent_growth = 0

    THEN CONVERT(VARCHAR(15),CONVERT(DECIMAL(10,2),growth/128.0)) + ' MB'

    WHEN growth > 0 AND is_percent_growth = 1 THEN CONVERT(VARCHAR(15),growth) + ' %'

    END

    ,[ReadOnly] = CASE

    WHEN is_read_only = 1 THEN 'READ ONLY'

    WHEN is_read_only = 0 THEN 'READ WRITE'

    END

    FROM sys.database_files

    WHERE file_id = 2

    once it is resolved, Could you post the actions taken.

    - Suneel

  • I was seeing the same issue, I just shrink the log file to get it away.

Viewing 3 posts - 1 through 2 (of 2 total)

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