English version is available at Technet Gallery: http://gallery.technet.microsoft.com/How-to-check-Last-known-0f93b4fa
Az utóbbi időben igen sokszor van szerencsém a DBCC parancsokkal dolgozni. Most egy érdekes kérdést próbálok megválaszolni: hogyan tudjuk Policy-Based Management segítségével az adatbázisaink utolsó jó DBCC CHECKDB futását ellenőrizni? Ezt a kérdést ma egy fórumon tette fel valaki, majd az MCM tanulócsoport is rákapott 
Az alábbi megoldás született.
A probléma
Az SQL Server 2008+ verzióban található PBM segítségével meg akarjuk állapítani, hogy az adatbázison 30 napon belül a DBCC CHECKDB lefutott e hiba nélkül. A PBM jelenleg nem engedi a saját Facet létrehozását.
A megoldás
Létre kellett hozni egy olyan Condition-t, ami azt vizsgálja, hogy az adott időszakon belül lefutott e a DBCC parancs hiba nélkül. Majd ebből hoztam létre a Policy-t. A Condition az ExecuteSql() függvényt használja az alábbi lekérdezésekkel:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] = 'upDbccLastKnownGood')
BEGIN
--Stored procedure létrehozása
EXEC ('CREATE PROCEDURE dbo.upDbccLastKnownGood
@Days int,
@IsLastKnownOk bit OUTPUT
AS
DECLARE @LastKnownDate datetime;
CREATE TABLE #DBCC ([ParentObject] nvarchar(max), [Object] nvarchar(max), [Field] nvarchar(max), [Value] nvarchar(max));
INSERT INTO #DBCC ([ParentObject], [Object], [Field], [Value]) EXEC (''DBCC DBINFO() WITH TABLERESULTS;'');
SELECT TOP 1 @LastKnownDate = CAST([Value] AS [datetime]) FROM #DBCC WHERE [Field] = ''dbi_dbccLastKnownGood'';
IF (DATEADD(DAY, -@Days, GETDATE()) <= @LastKnownDate)
SET @IsLastKnownOk = 1
ELSE
SET @IsLastKnownOk = 0
')
END
-- keresett adat lekérdezése
DECLARE @RetVal bit;
EXEC dbo.upDbccLastKnownGood 30, @RetVal OUTPUT
SELECT @RetVal;
--cleanup
DROP PROCEDURE upDbccLastKnownGood;
Na ezt a fenti kódot ha beillesztem, már meg is vagyok: ellenőrzi, hogy az adott adatbázisban 30 napnál nem régebbi a keresett információ.
A Condition az alábbi script segítségével hozható létre:
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'DBCCLastRun', @description=N'', @facet=N'Database', @expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>NE</OpType>
<Count>2</Count>
<Function>
<TypeClass>Numeric</TypeClass>
<FunctionType>ExecuteSql</FunctionType>
<ReturnType>Numeric</ReturnType>
<Count>2</Count>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>Numeric</Value>
</Constant>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value><?char 13?>
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] = ''''upDbccLastKnownGood'''')<?char 13?>
BEGIN<?char 13?>
EXEC (''''CREATE PROCEDURE dbo.upDbccLastKnownGood<?char 13?>
@Days int,<?char 13?>
@IsLastKnownOk bit OUTPUT<?char 13?>
AS<?char 13?>
<?char 13?>
DECLARE @LastKnownDate datetime;<?char 13?>
CREATE TABLE #DBCC ([ParentObject] nvarchar(max), [Object] nvarchar(max), [Field] nvarchar(max), [Value] nvarchar(max));<?char 13?>
INSERT INTO #DBCC ([ParentObject], [Object], [Field], [Value]) EXEC (''''''''DBCC DBINFO() WITH TABLERESULTS;'''''''');<?char 13?>
SELECT TOP 1 @LastKnownDate = CAST([Value] AS [datetime]) FROM #DBCC WHERE [Field] = ''''''''dbi_dbccLastKnownGood'''''''';<?char 13?>
<?char 13?>
IF (DATEADD(DAY, -@Days, GETDATE()) <= @LastKnownDate)<?char 13?>
SET @IsLastKnownOk = 1<?char 13?>
ELSE<?char 13?>
SET @IsLastKnownOk = 0<?char 13?>
'''')<?char 13?>
END<?char 13?>
<?char 13?>
DECLARE @RetVal bit;<?char 13?>
EXEC dbo.upDbccLastKnownGood 30, @RetVal OUTPUT<?char 13?>
SELECT @RetVal;<?char 13?>
DROP PROCEDURE upDbccLastKnownGood;</Value>
</Constant>
</Function>
<Constant>
<TypeClass>Numeric</TypeClass>
<ObjType>System.Double</ObjType>
<Value>0</Value>
</Constant>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO
Majd a Policy:
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'DBCC_ObjectSet', @facet=N'Database', @object_set_id=@object_set_id OUTPUT
Select @object_set_id
Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'DBCC_ObjectSet', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'DBCC', @condition_name=N'DBCCLastRun', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'DBCC_ObjectSet'
Select @policy_id
GO
Mivel a Condition-nál a Facet Database-re lett állítva, így a lekérdezésünk minden adatbázison le fog futni, amelyek meg vannak adva az Against Targets beállításnál
Jogosultság
Az így létrehozott Policy futtatásához az alábbi jogosultságokra van szükség:
- Az ellenőrzött adatbázisban CREATE/DROP PROCEDURE, EXEC a dbo schema-n,
- a DBCC DBINFO nem dokumentált parancs futtatásának lehetősége (ez szerintem db_owner jogot szeretne, de nem ellenőriztem)
Eredmény
Amikor lefuttatom az ellenőrzést, a teszt szerveremen az alábbi ereményt kapom: a zöld a jó, piros a rossz.
