December 22, 2014 at 3:48 am
All I only use Maintenance Plans for CHECKDB.
I recently noticed that although the job on certain servers where being executed with success. When I query SQL the last good checkdb is not current to the last successful job execution.
Some databases are up to date and have been check but, some will have older date stamps. Again the job reports success.
What could be causing this.
T-SQL used
CREATE TABLE #temp (
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[VALUE] VARCHAR(255)
)
CREATE TABLE #DBCCRes (
Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,
DBName sysname ,
dbccLastKnownGood DATETIME,
RowNumINT
)
DECLARE
@DBName SYSNAME,
@SQL VARCHAR(512);
DECLARE dbccpage CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT name
FROM sys.databases
WHERE 1 = 1
AND state = 0
--And name NOT IN ('tempdb')
;
OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@Fetch_Status = 0
BEGIN
SET @SQL = 'Use [' + @DBName +'];' + CHAR(10)+ CHAR(13)
SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' + CHAR(10)+ CHAR(13)
INSERT INTO #temp
EXECUTE (@SQL);
SET @SQL = ''
INSERT INTO #DBCCRes
( DBName, dbccLastKnownGood,RowNum )
SELECT @DBName, VALUE
, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
FROM #temp
WHERE Field = 'dbi_dbccLastKnownGood';
TRUNCATE TABLE #temp;
FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;
SELECT DBName,dbccLastKnownGood
FROM #DBCCRes
WHERE RowNum = 1;
DROP TABLE #temp
DROP TABLE #DBCCRes
December 22, 2014 at 4:51 am
Have you checked that the job is doing what it's supposed to do? If say someone changed it to just run CheckDB on master, then it will execute with success, but not do what it's intended to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 8:06 am
Ah......
Yes on one server, I only set it up a couple weekends ago and someone has gone in and changed it to selected dbs. Thought I had checked that already.My bad!!. If the db is read-only then set it to read only with the relevant CRs and sign off.
FRUSTRATING!!!
However on a couple of 2005 servers all dbs are selected. The job runs for a minute. Then reports success. All dbs report that they have had no dbcc checkdb executed. I have 3 servers that do this all 2005. I have recreated the MP, with the same results and changed the run times.
All servers are 9.0.3042 SP2. I am aware that many bugs were fixed in SP3. Is this one.
I'll look to get these on SP4 asap. Never had issues with CHECKDB MP so I've never seen a reason to change it for change sake.
Doing some house cleaning in a new environment.
December 22, 2014 at 8:30 am
A trivial google search (bug SQL Server 2005 CheckDB maintenance plan) turns up http://www.sqlskills.com/blogs/paul/sql-2005-sp2-maintenance-plan-bug-masking-corruption/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply