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
viernes, 19 de noviembre de 2010
martes, 19 de octubre de 2010
domingo, 10 de octubre de 2010
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.
“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...
Suscribirse a:
Entradas (Atom)