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
