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