Empezamos Chequeando las Bases de Datos en nuestros SQL

Aqui va el primer remember..


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...!!!

ByTriki Return!! a Un Blog + de SQL Server !

Hola a tod@s.. despues de años que no actualizo este blog he decidido ir metiendo cosillas del día a día mas seguido ademas de ir subiendo cosas que he ido desarrollando por necesidad o investigación (adaptaciones de otros script de la red a mis necesidades..) :)


Pues nada empezamos..!!!!

Informacion de BBDD por linked Server

buff...la ardua labor de poder investigar.... me dijeron tio me puedes decir los tamaños ocupados y disponibles de las bbdd, dije osti... si claro.. exec sp_spaceused'mibbdd'...
bueno era facil intuirlo.. Pero claro se necesitaba la informacion de unas 30 instancias de SQL Server y como siempre pasa hay diferentes versiones.. SQL 2000, 2005 y 2008..
Buenos pues analizamos de donde salen los datos del sp_spaceused.. bufff (os dejo la duda para que investigueis...) pues a ver me sale este "truño" que eureka da la info que necesitamos de una lista de Linked server guardados en una tablita auxiliar..
--=======================================================
--== trabajito me ha costao...
--== :)
--=======================================================
BEGIN TRY
DECLARE @SRV varchar(16),@BUSCAsrv sysname,@ShBUSCAsrv sysname, @error int, @Sem varchar(6), @retval int, @dbname sysname
DECLARE @Maquina nvarchar(128),@Inst nvarchar(128),@vtver nvarchar(150),@vtVerBBDD tinyint,@tableHTML NVARCHAR(MAX)
DECLARE @Loop int, @Counter int, @sql NVARCHAR(900),@VSQL varchar(15)
DECLARE @Tbldbsize TABLE(
instancia varchar(100)
,nombbdd varchar(100)
,dbsize int
,logsize in)
DECLARE @TblUsado TABLE(
instancia varchar(100)
,nombbdd varchar(100)
,TotalPages int)
CREATE TABLE #TmpBBDD
(idtbl int identity(1,1),
dbname sysname)
--Calcula la Semana en Curso.
set @Sem = CONVERT(varchar(2),datepart(wk,getdate()))
If LEN(@sem) = 1
Begin
set @sem = CONVERT(varchar(4),datepart(yyyy,getdate())) + '0'+ @sem
End
Else Begin
set @sem = CONVERT(varchar(4),datepart(yyyy,getdate())) + @sem
End
declare @versionsql table
(servername sysname,ver varchar(10))
DECLARE BUSCAsrv CURSOR FOR
Select micadenaconexion from mitabladondeestaloslinkedserver
OPEN BUSCAsrv
FETCH NEXT FROM BUSCAsrv INTO @BUSCAsrv
WHILE @@FETCH_STATUS = 0
BEGIN
exec dbo.pr_TestLinkedServer @ShBuscasrv,@retval OUTPUT -- Este solo comprueba que la conexion con el linked es correcta
IF @retval = 0
BEGIN
insert into @versionSQL --// pa ver la version y diferencia las tablas de sistema
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,substring(@@version,21,6)'')')
select @VSQL = ver from @versionsql
where servername = @BUSCAsrv
set @VSQL = LTRIM(RTRIM(@VSQL))
-- ******************************
---PARA VERSIONES CON SQL 2000
-- ******************************
 IF @VSQL = '2000'
BEGIN
IF exists (select count(*)from #TmpBBDD)
 BEGIN
 truncate table #TmpBBDD
 If @@Error = 0
Print ('Limpieza de TempBBDD Correcta')
 insert into #TmpBBDD
exec ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
print ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
 select @Loop = count(*) from #TmpBBDD
 SET @Counter = 1
 WHILE @Loop >0 and @Counter < @Loop
 BEGIN
Select @dbname=dbname from #TmpBBDD
Where @Counter =idtbl
 Print ('sql 2000 ' )
 Insert into @Tbldbsize (instancia,nombbdd,dbsize,logsize)
 EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''',sum(case when status & 64 = 0 then size else 0 end)
 ,sum(case when status & 64 <> 0 then size else 0 end)
 from ['+@dbname+'].dbo.sysfiles'')')
 -- Insertando paginas usadas..
 insert into @TblUsado (instancia,nombbdd,TotalPages)
 EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''', sum(reserved)
 from ['+@dbname+'].dbo.sysindexes where indid in (0, 1, 255)'')')
 Set @Counter = @Counter + 1
 END END END
-- ******************************************************
 ---PARA VERSIONES CON SQL 2005 y SQL 2008 y SQL 2008R2
 -- ******************************************************
 If @VSQL = '2005' or @VSQL = '2008'
 BEGIN
 IF exists (select count(*)from #TmpBBDD)
 BEGIN
 truncate table #TmpBBDD
 If @@Error = 0
 Print ('Limpieza de TempBBDD Correcta')
 insert into #TmpBBDD
 exec ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
 print ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
 select @Loop = count(*) from #TmpBBDD
 SET @Counter = 1
 WHILE @Loop > 0 and @Counter <= @Loop
 BEGIN
 Select @dbname=dbname from #TmpBBDD
 Where @Counter =idtbl
 Insert into @Tbldbsize (instancia,nombbdd,dbsize,logsize)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''',sum(case when status & 64 = 0 then size else 0 end)
,sum(case when status & 64 <> 0 then size else 0 end)
from ['+@dbname+'].dbo.sysfiles'')')
-- Insertando paginas usadas..
insert into @TblUsado (instancia,nombbdd,TotalPages)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''', sum(a.total_pages)
from ['+@dbname+'].sys.partitions p join ['+@dbname+'].sys.allocation_units a on p.partition_id =a.container_id left join ['+@dbname+'].sys.internal_tables it on p.object_id = it.object_id'')')
Set @Counter = @Counter + 1
END END END END
ELSE BEGIN
Print ('Fallo Conexion Servidor '+@BUSCAsrv+ ':comprobar comunicaciones..')
Print @@ROWCOUNT
END
FETCH NEXT FROM BUSCAsrv INTO @BUSCAsrv
END
CLOSE BUSCAsrv
DEALLOCATE BUSCAsrv
--******************************
--*** Resultados de consulta
--*** Aqui pa que salga bonito.... :)
--******************************
declare @pagesperMB dec(15,2)
select @pagesperMB = 8.0/1024.0
Select a.instancia
,b.nombbdd
,convert(dec(15,2),(a.dbsize + a.logsize))/128 as [Tamaño]
,convert(dec(15,2),a.logsize)/128 as LogSize
,convert(dec(15,2),a.dbsize - b.totalpages)/128 as Disponible
from @Tbldbsize as a
JOIN @TblUsado as b
on a.nombbdd = b.nombbdd
and a.instancia = b.instancia
drop table #TmpBBDD
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
drop table #TmpBBDD
END CATCH

Ahi queda esoo.... espero os ayude..
ByTriki

Fast Track Data Warehouse 2.0 Architecture

Revisando Fast Track 2.0 ...http://technet.microsoft.com/en-us/library/dd459178(SQL.100).aspx

Comprobando Estado de ficheros de BD en SQL Server

Objetivo:  Necesitaría comprobar el estado de la BD y el estado del archivo de BD al inicio de una Instancia, si los archivos de BD no son los correctos o están dañados que los muestre.

Cuando inicias una instancia y SQL Server no localiza los archivos o no son los correctos.

Marca la BD como SUSPECT pero los archivos los mantiene como ONLINE.

La consulta es la siguientes:

                 use master

go

select convert(nvarchar(128),SERVERPROPERTY('SERVERNAME'))

                  ,convert(varchar(100),DB_NAME(b.database_id))

                  ,b.name

                  ,b.physical_name

                  ,convert(varchar(50),databasepropertyex(DB_NAME(b.database_id),'UserAccess'))

                  ,convert(varchar(50),databasepropertyex(DB_NAME(b.database_id),'status'))

                  ,b.state_desc

            from sys.master_files as b

Ejemplo:

En este caso la BD pr1, esta creado en un disco USB, que después de crearla desconecto el disco USB y reinicio SQL Server, y ocurre el escenario:

-      Intentamos actualizar las tablas de sistema con DBCC UPDATEUSAGE, da error de acceso a los discos:

      Msg 945, Level 14, State 2, Line 1  

      Database 'pr1' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

-   La vista de catálogo sys.database_files no nos sirve ya que si una BD esta en OFFLINE, la query da error.

-   Leyendo en BOL, http://msdn.microsoft.com/en-us/library/ms190241.aspx, esto: 

     “In SQL Server, the state of a database file is maintained independently from the state of the database”

Llegando al Objetivo...

El informe a lo que nos gustaría llegar seria que cuando inicie SQL Server y no encuentre el archivo este en estado OFFLINE, y la BD en SUSPECT.

USE MASTER

Go

DECLARE @VerSQL char(3), @tblFiles varchar(200),@CmpFile sysname,@CmpID char(11),@CmpSts char(10),@CmpFile1 sysname

DECLARE @Loop int, @Counter int, @NomBD sysname

DECLARE @result int,@cmd sysname

If Exists (select name from master..sysobjects where name = 'InformeBBDD')

Begin

       Drop table InformeBBDD

End

create table master..InformeBBDD

       (

             NomInstancia sysname,

             NomBBDD      sysname,

             NomFichero   varchar(400),      

             RutaFichero varchar(500),

             ConfiguracionBBDD   varchar(50),

             EstadoBBDD   varchar(40),

             EstadoFichero nvarchar(60)

       )

Select @VerSQL = substring(convert(varchar(128),SERVERPROPERTY('ProductVersion')),1,3)

SET @tblFiles =

       case

             When @VerSQL like '8%' THEN 'dbo.sysaltfiles'

             When @VerSQL like '9%' THEN 'sys.master_files'

             When @VerSQL like '10%' THEN 'sys.master_files'

       End

SET @CmpFile =

case

             When @VerSQL like '8%' THEN 'filename'

             When @VerSQL like '9%' THEN 'physical_name'

             When @VerSQL like '10%' THEN 'physical_name'

       End   

SET @CmpID =

case

             When @VerSQL like '8%' THEN 'dbid'

              When @VerSQL like '9%' THEN 'database_id'

             When @VerSQL like '10%' THEN 'database_id'

       End

DECLARE @tblQuery TABLE

(      id int Identity(1,1),

       NomBD  sysname,

       CmpFile      nvarchar(128)

)

INSERT INTO @tblQuery

EXEC('SELECT DB_NAME('+@CmpId+'),'+@CmpFile+' from '+@TblFiles+'')

SELECT @Loop = COUNT(*) from @tblQuery

SET @Counter = 1

WHILE @Loop > 0 and @Counter <= @Loop

BEGIN

       Select @NomBD = NomBD,@CmpFile1 = CmpFile from @tblQuery

             Where @Counter = id

       SET @CmpFile1 = '"'+RTRIM(LTRIM(@CmpFile1))+'"'

       SET @cmd = 'dir '+@CmpFile1+''

       EXEC @result = master..xp_cmdshell @cmd,NO_OUTPUT;

       IF (@result = 0)

       Begin

             SET @CmpSts = 'ONLINE'

             Print @CmpSts

       End

       Else Begin

             SET @CmpSts = 'OFFLINE'

             Print @CmpSts

       End   

       INSERT INTO master..InformeBBDD

       EXEC('select convert(nvarchar(128),SERVERPROPERTY(''SERVERNAME''))

             ,convert(varchar(100),DB_NAME('+@CmpID+'))

             ,b.name

             ,b.'+@CmpFile+'

             ,convert(varchar(50),databasepropertyex(DB_NAME('+@CmpID+'),''UserAccess''))

             ,convert(varchar(50),databasepropertyex(DB_NAME('+@CmpID+'),''status''))

             ,'''+@CmpSts+'''

             from '+@TblFiles+' as b Where b.name = '''+@NomBD+'''')

       SET @Counter = @Counter + 1      

END

Select * from master..InformeBBDD

Disco USB – Desconetado

Disco USB - Conectado

Para poder realizar la comprobacion de esta informacion es necesario utilizar xp_cmdshell para poder recuperar la informacion de los archivos desde SQL Server a ..sistema operativo.

hasta la proxima...