SQL Server Express Edition : Schedulare jobs

Ok, SQL Server Express non ha il SQL Server Agent, ma questo non è una buona scusa per non implementare backup e attività di manutenzione regolari dei Database.

Anche senza SQL Server Agent è possibile, con un pizzico di lavoro in più, schedulare tutte le attività necessarie al mantenimento dei DB.
Come ? Usando lo Schedule Task del sistema operativo e un poco di sano T-SQL !!

Immaginiamo di voler fare un backup ed il rebuild degli indici del Db collegato a Sql Express

Creo un DB di test di cui effettuare la manutenzione

Use Master
Go

Create database TestJob
Go

Use TestJob
Go


create table TestTab
(
 id int
 ,campo varchar(25)
)

create clustered index idx on TestTab(id)
Go

;with cte as (Select 1 Riga union all Select 1)
 ,cte4 as (Select A.Riga From cte A, cte B)
 ,cte16 as (Select A.Riga From cte4 A, cte4 B)
 ,cte256 as(Select A.Riga From cte16 A, cte16 B)
 ,cte65536 as(Select A.Riga From cte256 A, cte256 B)
 ,results as(Select Row_number()Over(Order By Riga)Riga from cte65536)
Insert into TestTab 
Select Riga,riga from Results
Go 2

Verifichiamo subito se c'è frammentazione per avere un raffronto post manutenzione.

Use TestJob
Go
select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,'Detailed')
Go

Ok.... Frammentazione al 98% nella tabella...

Mi serve poi un DB di Log all'interno del quale salvare gli esiti delle operazioni pianificate.

Use Master
Go

create database LogJob
Go

Use LogJob
Go

Create table TabLogJob
(
 Data datetime default getdate()
 ,Errore bit
 ,Messaggio varchar(max)
)
Go

Preparo uno Script di backup e di Rebuild degli indici.
Ovviamente il backup deve essere implementato post Check d'integrità del database.
Quindi gli step sono
1: Check Integrity
2: Backup
3: Rebuild

In caso d'errore log nella tabella TabLogJob del DB LogJob

Use TestJob
Go

CREATE TABLE #DBCC_CHECKDB 
(   
 Error int NULL,
 [Level] int NULL,
 State int NULL,
 MessageText nvarchar(2048) NULL,
 RepairLevel nvarchar(22) NULL,
 Status int NULL,
 DbId int NULL,
 DbFragId int NULL,
 ObjectId int NULL,
 IndexId int NULL,
 PartitionId bigint NULL,
 AllocUnitId bigint NULL,
 RidDbId smallint NULL,
 RidPruId smallint NULL,
 [File] smallint NULL,
 Page int NULL,
 Slot int NULL,
 RefDbId smallint NULL,
 RefPruId smallint NULL,
 RefFile smallint NULL,
 RefPage int NULL,
 RefSlot int NULL,
 Allocation smallint NULL 
)    

INSERT INTO #DBCC_CHECKDB 
(
 Error,
 [Level], 
 State,
 MessageText,
 RepairLevel,
 Status,
 DbId,
 DbFragId,
 ObjectId,
 IndexId,
 PartitionId,
 AllocUnitId,
 RidDbId,
 RidPruId,
 [File],
 Page,
 Slot,
 RefDbId,
 RefPruId,
 RefFile,
 RefPage,
 RefSlot,
 Allocation
) 
EXEC('DBCC CHECKDB(TestJob) WITH TABLERESULTS, ALL_ERRORMSGS ')   
If ( Select count(*) from #DBCC_CHECKDB Where Error >= 8000 and level >= 16 ) = 0  
Begin 
 
 begin try

  backup Database TestJob to disk = 'C:\SQL 2012\Backup\Full.bak'
  Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
          values(0,'Backup Effettuato Correttamente')

  exec sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD'
  Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
   values(0,'Rebuild Effettuata Correttamente')

 end try
 begin catch

         Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
   Select 1,ERROR_MESSAGE()

 end catch
end
Else 
begin
 
 Insert into LogJob.dbo.TabLogJob (Errore,Messaggio)
 Select 1,MessageText from #DBCC_CHECKDB Where Error >= 8000 and level >= 16
 
end 
 
Drop table #DBCC_CHECKDB

Salvo lo script e mi preparo ad eseguirlo con l'utility sqlcmd.exe.
Creo un file Batch contenente il seguente comando :

sqlcmd.exe -Slocalhost\SqlExpress -E -i"C:\SQL 2012\Backup\Maintenance.sql"

Provo il file e verifico se la frammentazione è calata e se nella tabella di Log è presente l'avvenuta esecuzione

Use TestJob
Go
select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,'Detailed')
Go

Frammentazione inesistente....

Verifico il Backup

Backup effettuato....

Verifico il Log

Logging corretto !!!

Perfetto !! Funziona tutto....

Ora non mi resta che schedularlo per essere eseguito ogni giorno alle 16:45

Step1 Creo il Task

Step2 Tipo di schedulazione

Step3 Seleziono l'attività

Step4 Seleziono il batch

Step5 Imposto l'ora di esecuzione

Step6 Finish con apertura eventuale delle proprietà per i setting relativi alla Security

Non mi resta che attendere le 16 e 45 per vedere se ha funzionato....

tic tac... tic tac.... ;-)

Ok... 16 e 45... Vediamo cosa è successo....

Ok, backup effettuato in append al file preesistente.....

Verifico il Log

Log ok...

Benissimo... I nostri DB ora saranno contenti !!!

Immaginiamo però che a causa di errori del disco vi sia una datapage corrotta.
Ne danneggio una a caso....

Alter Database TestJob set single_user with rollback immediate 
Go 
Dbcc writepage(TestJob,1,125,1,1,0x66,1) 
Go 
Alter Database TestJob set multi_user 
Go

Cosa accade se parte il batch ?
bhe... Parte la Dbcc checkdb() che restituisce errore,il backup e la rebuild non vengono implementati, e viene eseguito il log.

Con poco sforzo si può modificare lo script in modo che crei un file di backup giornaliero e si può modificare il batch in modo che elimini i backup che non si desidera più mantenere on line....

OVVIAMENTE FARE IL BACKUP SULLA STESSA MACCHINA COME NEL MIO ESEMPIO NON E' UNA BEST PRACTICE.
FARE IL BACKUP SULLA STESSA MACCHINA = NON FARE IL BACKUP !!!!

Ciao

Luca