Promazávání starých databází na SQL Serveru

Před nějakou dobou jsem narazil na problém se specifickými uživateli MS SQL Serveru. Specifické bylo v tomto případě to, že z důvodu své pracovní náplně vytvářeli tito uživatelé velké množství databází na SQL Serveru, které vždy dočasně použili, ale poté samozřejmě nesmazali. Vzhledem k tomu, že nad jednotlivými databázemi spolupracovalo více lidí a doba použití byla mezi jedním dnem až zhruba třemi měsíci, nebylo možné uživatele administrativně donutit po sobě “uklízet”. Na druhou stranu každá databáze si brala nějaké prostředky databázové serveru a tomu se to při počtu několika desítek databází moc nezamlouvalo.

Po zvážení způsobu práce bylo tedy navrženo, aby se databáze po 30 dnech převedla do stavu OFFLINE a po 90 dnech od založení se definitivně smazala.

Jak to tedy vyřešit, aby nebylo nutné se o promazávání starat.

Nejprve si vytvoříme tabulku, kam budeme zapisovat jakýsi log.
Tabulku můžeme vytvořit přímo v master databázi, i když to není úplně správně, ale nemusíme pro ni vytvářet další databázi.

USE master
CREATE TABLE dbo.db_mazani
(
datum date NOT NULL,
database_name nvarchar(128) NOT NULL,
stat nchar(15) NOT NULL
) ON [PRIMARY]
GO

Dále vytvoříme sql skripty, kterými budeme databáze procházet a podle data vytvoření a stavu je budeme přepínat do offline režimu nebo mazat.

Nejprve tedy skript pro přepnutí databáze do offline. Databáze ve stavu offline nezabírá prostředky databázového serveru, ale je možné ji snadno opět přepnout do normálního stavu. Nebudeme zde rozebírat jednotlivé příkazy, ale podíváme se jen na celkový způsob provedení.
Pomocí cursoru budeme procházet jednotlivé databáze a zjišťovat jejich stáří, resp. jestli je mezi aktuálním datem a datem vytvoření určitý čas.
Abychom mohli provést alter database s proměnným parametrem názvu databáze, musíme použít konstrukci s vytvořením zápisu příkazu a jeho následným spuštěním pomocí sp_executesql.
Pomocí insert into zapisujeme do logovací tabulky.

DECLARE @dname nvarchar(517),
@stat char(100),
@datum datetime;
DECLARE @Statement NVARCHAR(300);
DECLARE db_curs CURSOR FOR
SELECT [name]
FROM sys.databases with (nolock) where (DATEDIFF(day, create_date, GETDATE()) >= 30)
and name not in ('master', 'model', 'msdb', 'tempdb')
and state_desc='ONLINE'
order by DATEDIFF(day, create_date, GETDATE()) desc

BEGIN
select @stat='SET OFFLINE'
select @datum = GETDATE()
OPEN db_curs
FETCH NEXT FROM db_curs into @dname

WHILE (@@FETCH_STATUS = 0)
BEGIN
insert into master.dbo.db_mazani (datum, database_name, stat) values (@datum, @dname, @stat)
SET @Statement = N'alter database [' + @dname + N']' + N'set offline WITH ROLLBACK IMMEDIATE';
EXEC sp_executesql @Statement;
FETCH NEXT FROM db_curs into @dname
END
CLOSE db_curs

END
DEALLOCATE db_curs
Go

SQL skript pro mazání databází je prakticky totožný, jen dochází místo změny stavu na offline, ke smazání databáze. Pozor však musíme dát na jednu drobnost a tou je, že SQL server sice umí smazat databázi ve stavu offline , ale v takovém případě smaže jen definici databáze, avšak soubory databáze (MDF a LDF) ponechá na disku, což není úplně v našem případě úplně vhodné. Proto musíme každou databázi před smazáním převést do stavu online a poté ji teprve smazat.

DECLARE @dname nvarchar(517),
@stat char(100),
@datum datetime;
DECLARE @Statement NVARCHAR(300),
@Statement1 NVARCHAR(300);
DECLARE db_curs CURSOR FOR
SELECT [name]
FROM sys.databases with (nolock) where (DATEDIFF(day, create_date, GETDATE()) >= 90)
and name not in ('master', 'model', 'msdb', 'tempdb') and state_desc='OFFLINE'
order by DATEDIFF(day, create_date, GETDATE()) desc

BEGIN
select @stat='DELETED'
select @datum = GETDATE()
OPEN db_curs
FETCH NEXT FROM db_curs into @dname

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Statement1 = N'alter database [' + @dname + N']' + N'set online';
EXEC sp_executesql @Statement1;
insert into master.dbo.db_mazani (datum, database_name, stat) values (@datum, @dname, @stat)
SET @Statement = N'drop database [' + @dname + N']' ;
EXEC sp_executesql @Statement;
FETCH NEXT FROM db_curs into @dname
END
CLOSE db_curs

END
DEALLOCATE db_curs
Go

Jakmile máme připraveny uvedené skripty stačí již pouze založit joby pro SQL Agenta, který bude v zadaných intervalech skripty provádět.

Komentáře nejsou povoleny.