Policy-Based Management és DBCC CHECKDB
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 SmileAz 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:
1IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] = 'upDbccLastKnownGood')
2BEGIN
3--Stored procedure létrehozása
4EXEC ('CREATE PROCEDURE dbo.upDbccLastKnownGood
5@Days int,
6@IsLastKnownOk bit OUTPUT
7AS
8
9DECLARE @LastKnownDate datetime;
10CREATE TABLE #DBCC ([ParentObject] nvarchar(max), [Object] nvarchar(max), [Field] nvarchar(max), [Value] nvarchar(max));
11INSERT INTO #DBCC ([ParentObject], [Object], [Field], [Value]) EXEC (''DBCC DBINFO() WITH TABLERESULTS;'');
12SELECT TOP 1 @LastKnownDate = CAST([Value] AS [datetime]) FROM #DBCC WHERE [Field] = ''dbi_dbccLastKnownGood'';
13
14IF (DATEADD(DAY, -@Days, GETDATE()) <= @LastKnownDate)
15 SET @IsLastKnownOk = 1
16ELSE
17 SET @IsLastKnownOk = 0
18')
19END
20-- keresett adat lekérdezése
21DECLARE @RetVal bit;
22EXEC dbo.upDbccLastKnownGood 30, @RetVal OUTPUT
23SELECT @RetVal;
24--cleanup
25DROP 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:
1Declare @condition_id int
2EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'DBCCLastRun', @description=N'', @facet=N'Database', @expression=N'<Operator>
3 <TypeClass>Bool</TypeClass>
4 <OpType>NE</OpType>
5 <Count>2</Count>
6 <Function>
7 <TypeClass>Numeric</TypeClass>
8 <FunctionType>ExecuteSql</FunctionType>
9 <ReturnType>Numeric</ReturnType>
10 <Count>2</Count>
11 <Constant>
12 <TypeClass>String</TypeClass>
13 <ObjType>System.String</ObjType>
14 <Value>Numeric</Value>
15 </Constant>
16 <Constant>
17 <TypeClass>String</TypeClass>
18 <ObjType>System.String</ObjType>
19 <Value><?char 13?>
20IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] = ''''upDbccLastKnownGood'''')<?char 13?>
21BEGIN<?char 13?>
22EXEC (''''CREATE PROCEDURE dbo.upDbccLastKnownGood<?char 13?>
23@Days int,<?char 13?>
24@IsLastKnownOk bit OUTPUT<?char 13?>
25AS<?char 13?>
26<?char 13?>
27DECLARE @LastKnownDate datetime;<?char 13?>
28CREATE TABLE #DBCC ([ParentObject] nvarchar(max), [Object] nvarchar(max), [Field] nvarchar(max), [Value] nvarchar(max));<?char 13?>
29INSERT INTO #DBCC ([ParentObject], [Object], [Field], [Value]) EXEC (''''''''DBCC DBINFO() WITH TABLERESULTS;'''''''');<?char 13?>
30SELECT TOP 1 @LastKnownDate = CAST([Value] AS [datetime]) FROM #DBCC WHERE [Field] = ''''''''dbi_dbccLastKnownGood'''''''';<?char 13?>
31<?char 13?>
32IF (DATEADD(DAY, -@Days, GETDATE()) <= @LastKnownDate)<?char 13?>
33 SET @IsLastKnownOk = 1<?char 13?>
34ELSE<?char 13?>
35 SET @IsLastKnownOk = 0<?char 13?>
36'''')<?char 13?>
37END<?char 13?>
38<?char 13?>
39DECLARE @RetVal bit;<?char 13?>
40EXEC dbo.upDbccLastKnownGood 30, @RetVal OUTPUT<?char 13?>
41SELECT @RetVal;<?char 13?>
42DROP PROCEDURE upDbccLastKnownGood;</Value>
43 </Constant>
44 </Function>
45 <Constant>
46 <TypeClass>Numeric</TypeClass>
47 <ObjType>System.Double</ObjType>
48 <Value>0</Value>
49 </Constant>
50</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
51Select @condition_id
52
53GO
Majd a Policy:
1Declare @object_set_id int
2EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'DBCC_ObjectSet', @facet=N'Database', @object_set_id=@object_set_id OUTPUT
3Select @object_set_id
4
5Declare @target_set_id int
6EXEC 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
7Select @target_set_id
8
9EXEC 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
10
11
12GO
13
14Declare @policy_id int
15EXEC 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'
16Select @policy_id
17GO
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 zöld a jó, piros a rossz. 😉