June 20, 2014 at 8:21 am
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.
June 20, 2014 at 1:26 pm
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
February 2, 2017 at 5:35 am
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