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