Pues esto empezo con la necesidad de buscar una alternativa a ejecutar un mantenimiento de BBDD sin utilizar DTSx SSIS y poder personalizar y automatizar dichos procesos guardando lo que hace el comando de CHECKDB, y ademas gurdar desde cuando se ha realizado el ultimo checkdb de la BBDD.
Bueno pues se me ocurrió hacerlo así..
Paso 1: Creamos las tablas donde guardaremos la información del checkdb.
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQL Server", ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 1: Creacion Tablas para LOG de Ejecuciones
-- @ByTriki
--####################################################################################
use MSDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_Mantenance_InfoCheckdb')
DROP TABLE [dbo].[tbl_Mantenance_InfoCheckdb]
GO
CREATE TABLE [dbo].[tbl_Mantenance_InfoCheckdb](
[NomInstancia] [sysname] NOT NULL,
[NomBaseDatos] [sysname] NOT NULL,
[Ultimo_Check] [datetime] NULL
) ON [PRIMARY]
GO
IF EXISTS (select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_Mantenance_HistoryCheckdb')
DROP TABLE [dbo].[tbl_Mantenance_HistoryCheckdb]
GO
CREATE TABLE [dbo].[tbl_Mantenance_HistoryCheckdb](
[ServerName] [varchar](100) NULL CONSTRAINT [DF_tbl_Mantenance_HistoryCheckdb_ServerName] DEFAULT (@@servername),
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [varchar](100) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [bigint] NULL,
[IndId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[Fecha_Ejecucion] [smalldatetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]
Paso 2: Creacion del procedimiento almacenado para su despliegue.
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQLServer" , ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 2: Creacion Procedimiento Almacenado - ejecucion sentencias T-SQL
-- @ByTriki
--####################################################################################
USE [msdb]
GO
GO
CREATE PROC [dbo].[pr_Mantenance_CheckbdBBDD](@dbmore20Gb char(2))
AS
SET @dbmore20Gb = UPPER(@dbmore20Gb)
DECLARE @dbname sysname, @SQL nvarchar(1000), @sub varchar(200)
DECLARE @HistoryCheckdb TABLE
( [Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] varchar(100) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [bigint] NULL,
[IndId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL
)
DECLARE @InfoChequeoBD TABLE
( ParObject nvarchar(1000) NULL,
NomObject nvarchar(1000) NULL,
Registro nvarchar(1000) NULL,
Valor nvarchar(1000) NULL,
dbname nvarchar(1000) NULL
)
DECLARE @TBLdbname TABLE
(
dbname1 sysname,
tamBBDD int
)
INSERT INTO @TBLdbname
SELECT sd.name ,(sum(sm.size)*8)/1024 as [TamMB]FROM sys.databases as sd
INNER JOIN SYS.MASTER_FILES as sm
ON sd.database_id = sm.database_id
WHERE sd.name NOT IN ('master','model','msdb','tempdb')
AND sd.state_desc = 'ONLINE'
AND sd.source_database_id IS NULL
AND sd.is_read_only = 0
group by sd.name
order by [TamMB] desc
IF @dbmore20Gb = 'SI'
BEGIN
DECLARE Puntero CURSOR READ_ONLY FOR
select dbname1 from @TBLdbname
where tamBBDD > 20000
OPEN Puntero
FETCH NEXT FROM Puntero INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbname
-- Se guarda informacion del proceso CHECKDB en una tabla de la BBDD.
SET @SQL = 'DBCC CHECKDB ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @HistoryCheckdb
EXEC (@SQL)
PRINT @@ERROR
IF @@ERROR <> 0
BEGIN
Select @@ROWCOUNT
Print ('Fallo en la ejecucion "DBCC CHECKDB --> '+@dbname + '')
END
ELSE Begin
SET @SQL = 'DBCC DBINFO ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @InfoChequeoBD(ParObject, NomObject, Registro, Valor)
EXEC(@SQL)
---- ******************************************************************
---- VOLCADO DE INFORMACION a TABLAS ***************
---- ******************************************************************
INSERT INTO dbo.tbl_Mantenance_HistoryCheckdb
SELECT @@SERVERNAME,[Error],[Level]
,[State],[MessageText],[RepairLevel],[Status]
,[DbId],[Id],[IndId],[PartitionId],[AllocUnitId]
,[File],[Page],[Slot],[RefFile],[RefPage],[RefSlot]
,[Allocation],CAST(getdate() AS datetime)
FROM @HistoryCheckdb
INSERT INTO dbo.tbl_Mantenance_InfoCheckdb(NomInstancia, NomBaseDatos, Ultimo_Check)
SELECT DISTINCT @@servername,@dbname , CAST(Valor AS smalldatetime) AS Ultimo_chequeo
FROM @InfoChequeoBD
WHERE Registro = 'dbi_dbccLastKnownGood'
End
waitfor delay '00:00:05'
DELETE FROM @InfoChequeoBD
DELETE FROM @HistoryCheckdb
FETCH NEXT FROM Puntero INTO @dbname
END
CLOSE Puntero
DEALLOCATE Puntero
END
ELSE BEGIN
IF @dbmore20Gb = 'NO'
BEGIN
DECLARE Puntero CURSOR READ_ONLY FOR
select dbname1 from @TBLdbname
where tamBBDD < 20000
OPEN Puntero
FETCH NEXT FROM Puntero INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbname
-- Se guarda informacion del proceso CHECKDB en una tabla de la BBDD.
SET @SQL = 'DBCC CHECKDB ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @HistoryCheckdb
EXEC (@SQL)
PRINT @@ERROR
IF @@ERROR <> 0
BEGIN
Select @@ROWCOUNT
Print ('Fallo en la ejecucion "DBCC CHECKDB --> '+@dbname + '')
END
ELSE Begin
SET @SQL = 'DBCC DBINFO ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @InfoChequeoBD(ParObject, NomObject, Registro, Valor)
EXEC(@SQL)
---- ******************************************************************
---- VOLCADO DE INFORMACION a TABLAS ***************
---- ******************************************************************
INSERT INTO dbo.tbl_Mantenance_HistoryCheckdb
SELECT @@SERVERNAME,[Error],[Level]
,[State],[MessageText],[RepairLevel],[Status]
,[DbId],[Id],[IndId],[PartitionId],[AllocUnitId]
,[File],[Page],[Slot],[RefFile],[RefPage],[RefSlot]
,[Allocation],CAST(getdate() AS datetime)
FROM @HistoryCheckdb
INSERT INTO dbo.tbl_Mantenance_InfoCheckdb(NomInstancia, NomBaseDatos, Ultimo_Check)
SELECT DISTINCT @@servername,@dbname , CAST(Valor AS smalldatetime) AS Ultimo_chequeo
FROM @InfoChequeoBD
WHERE Registro = 'dbi_dbccLastKnownGood'
End
waitfor delay '00:00:05'
DELETE FROM @InfoChequeoBD
DELETE FROM @HistoryCheckdb
FETCH NEXT FROM Puntero INTO @dbname
END
CLOSE Puntero
DEALLOCATE Puntero
END
END
Paso 3: Y por último como lo ejecutamos.
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQLServer" , ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 3: Creacion Job Programado - Schedule ** Sunday 14:30 horas.
-- @ByTriki
--####################################################################################
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ByTriki_Mant_Instancia_db20Gb')
EXEC msdb.dbo.sp_delete_job @job_name='ByTriki_Mant_Instancia_db20Gb', @delete_unused_schedule=1
GO
USE [msdb]
GO
BEGIN TRANSACTION
declare @patherrlog varchar(max)
set @patherrlog = convert(sysname,serverproperty('errorlogfilename'))
select @patherrlog = replace(@patherrlog,'\ERRORLOG','\')
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Procesos ByTriki' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Procesos ByTriki'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
set @patherrlog = ''+@patherrlog+'Log_ByTriki_Mant_Instancia_db20Gb.log'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ByTriki_Mant_Instancia_db20Gb',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Procesos ByTriki',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Integridad de BBDD',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [dbo].[pr_Mantenance_CheckbdBBDD] ''SI''',
@database_name=N'msdb',
@output_file_name=@patherrlog,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch-Mantenimiento',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110519,
@active_end_date=99991231,
@active_start_time=143000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-- ******************************************************+
-- Job Para BBDD menor de 20Gb
-- ******************************************************+
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ByTriki_Mant_Instancia_db-20Gb')
EXEC msdb.dbo.sp_delete_job @job_name = N'ByTriki_Mant_Instancia_db-20Gb', @delete_unused_schedule=1
GO
USE [msdb]
GO
BEGIN TRANSACTION
declare @patherrlog2 varchar(max)
set @patherrlog2 = convert(sysname,serverproperty('errorlogfilename'))
select @patherrlog2 = replace(@patherrlog2,'\ERRORLOG','\')
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Procesos ByTriki' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Procesos ByTriki'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ByTriki_Mant_Instancia_db-20Gb',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Procesos ByTriki',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Integridad de BBDD] Script Date: 01/20/2012 14:13:57 ******/
set @patherrlog2 = ''+@patherrlog2+'Log_ByTriki_Mant_Instancia_db-20Gb.log'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Integridad de BBDD',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [dbo].[pr_Mantenance_CheckbdBBDD] ''NO''',
@database_name=N'msdb',
@output_file_name=@patherrlog2,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch-Mantenimiento',
@enabled=1,
@freq_type=8,
@freq_interval=64,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110519,
@active_end_date=99991231,
@active_start_time=143000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ByTriki_MantenimientoInstancia')
EXEC msdb.dbo.sp_delete_job @job_name='ByTriki_MantenimientoInstancia', @delete_unused_schedule=1
GO
Pues lo que os quedara al final de todo esto es una "tablitas" creada en el paso 1 donde almacena siempre todo el resultado del checkdb donde podeis explotarlas para Reportes y demás.. solo esta contemplado para BBDD de Usuario no las de Sistema..
Personalmente lo mas importante de todo esto es chequear el campo que os indico con la flecha para comprobar que las bbdd estan bien de salud :D
Buenos pues nadad chic@s que lo disfruteis.. espero ir subiendo mas cositas...
hasta la próxima...!!!
No hay comentarios:
Publicar un comentario