Urgt Pb recuperer taille des data sur toutes les bases d'1 serveur

Bonjour,

J'ai crée 1 sp pour recuperer des infos sur la taille des fichiers
data/log
par base par serveur. Mon souci est que ça marche uniquement sur une seule
base. Ma procédure n'arrive pas boucler sur toutes les bases du serveur.
Pourriez-vous m'aider, je n'ai plus d'idée !!!!!
SP1 :
ALTER PROCEDURE [dbo].[Inventaire_database] @dbname varchar(100) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql varchar(1000)
DECLARE cu CURSOR
FOR
select name as 'nom base' from sys.databases
-- SELECT s.name + '.' + t.name
-- FROM sys.tables AS t
-- JOIN sys.schemas AS s ON s.schema_id = t.schema_id;
OPEN cu;
FETCH NEXT FROM cu INTO @dbname;
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec Inventory_dba3 @dbname= @dbname

--RETURN @dbname
FETCH NEXT FROM cu INTO @dbname;
END;
CLOSE cu;
DEALLOCATE cu;

END


SP 2 :
ALTER PROCEDURE [dbo].[Inventory_dba3] @dbname varchar(100)

AS
BEGIN
declare @dbsize bigint
declare @logsize bigint
declare @database_size_mb float
declare @unallocated_space_mb float
declare @reserved_mb float
declare @data_mb float
declare @index_mb float
declare @unused_mb float
declare @reservedpages bigint
declare @pages bigint
declare @usedpages bigint
declare @sql varchar(1000)
--declare @table22 sysname
--select @table22= @dbname+ '.dbo.sysfiles'


--set @table22 = @dbname+ '.dbo.sysfiles'
exec ('Use '+ @dbname +';')
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size
else
0 end))
,@logsize = sum(convert(bigint,case when status & 64 != 0 then size
else 0 end))
from @dbname.dbo.sysfiles

exec ('Use '+ @dbname +';')

select @reservedpages = sum(a.total_pages)
,@usedpages = sum(a.used_pages)
,@pages = sum(CASE
WHEN it.internal_type IN (202,204) THEN 0
WHEN a.type != 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END)
from sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id

select @database_size_mb = (convert(dec (15,2),@dbsize) +
convert(dec(15,2),@logsize)) * 8192 / 1048576
select @unallocated_space_mb =(case
when @dbsize >= @reservedpages then
(convert
(dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 /
1048576
else 0
end)

select @reserved_mb = @reservedpages * 8192 / 1048576.0
select @data_mb = @pages * 8192 / 1048576.0
select @index_mb = (@usedpages - @pages) * 8192 / 1048576.0
select @unused_mb = (@reservedpages - @usedpages) * 8192 / 1048576.0

insert into DBATECH.dbo.TEST select @@servername as 'serveur',@@version
as
'version',@dbname as 'nom base', @database_size_mb as 'database_size'
, cast (@unallocated_space_mb*100.0/@database_size_mb as
decimal(10,2))as 'unallocated'
, cast (@reserved_mb*100/@database_size_mb as decimal(10,2))as
'reserved'
, cast(@data_mb*100/@database_size_mb as decimal(10,2))as 'data'
, cast(@index_mb*100/@database_size_mb as decimal(10,2)) as
'index_1'
, cast(@unused_mb*100/@database_size_mb as decimal(10,2))as 'unused'
, cast((@database_size_mb - @unallocated_space_mb -
@reserved_mb)*100/@database_size_mb as decimal (10,2))as 'TransactionLog'
, (@database_size_mb - @unallocated_space_mb - @reserved_mb) as
'TransactionLogSize'
, cast((@unallocated_space_mb + @reserved_mb)*100/@database_size_mb
as
decimal (10,2)) as 'DataFiles' ;


print @@servername
print @@version
print @dbname
print @database_size_mb
print @unallocated_space_mb*100.0/@database_size_mb
print @reserved_mb*100/@database_size_mb
print @data_mb*100/@database_size_mb
print @index_mb*100/@database_size_mb
print @unused_mb*100/@database_size_mb
print (@database_size_mb - @unallocated_space_mb -
@reserved_mb)*100/@database_size_mb
print ( @database_size_mb - @unallocated_space_mb - @reserved_mb)
print ( @unallocated_space_mb + @reserved_mb)*100/@database_size_mb


end


J'obtiens bien les noms de bases mais les tailles des fichiers sont
identiques pour toutes !!!!!

Please help !!!!
PS : avec la procedure exec sp_MSforeachdb 'exec Inventory_dba4' j'obtiens
la même chose !!!

Bonjour,

Sur quelle version de SQL Server travaillez vous ?

--
Cordialement

Romelard Fabrice [MVP]

"gayoux" <glivignac@hotmail.com> wrote in message
news:868C7E15-7B69-46C0-9B43-674396C05B8E@microsoft.com...
> Bonjour,
>
> J'ai crée 1 sp pour recuperer des infos sur la taille des fichiers
> data/log
> par base par serveur. Mon souci est que ça marche uniquement sur une seule
> base. Ma procédure n'arrive pas boucler sur toutes les bases du serveur.
> Pourriez-vous m'aider, je n'ai plus d'idée !!!!!
> SP1 :
> ALTER PROCEDURE [dbo].[Inventaire_database] @dbname varchar(100) OUTPUT
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> declare @sql varchar(1000)
> DECLARE cu CURSOR
> FOR
> select name as 'nom base' from sys.databases
> -- SELECT s.name + '.' + t.name
> -- FROM sys.tables AS t
> -- JOIN sys.schemas AS s ON s.schema_id = t.schema_id;
> OPEN cu;
> FETCH NEXT FROM cu INTO @dbname;
> WHILE (@@FETCH_STATUS = 0)
> BEGIN
> exec Inventory_dba3 @dbname= @dbname
>
> --RETURN @dbname
> FETCH NEXT FROM cu INTO @dbname;
> END;
> CLOSE cu;
> DEALLOCATE cu;
>
> END
>
>
> SP 2 :
> ALTER PROCEDURE [dbo].[Inventory_dba3] @dbname varchar(100)
>
> AS
> BEGIN
> declare @dbsize bigint
> declare @logsize bigint
> declare @database_size_mb float
> declare @unallocated_space_mb float
> declare @reserved_mb float
> declare @data_mb float
> declare @index_mb float
> declare @unused_mb float
> declare @reservedpages bigint
> declare @pages bigint
> declare @usedpages bigint
> declare @sql varchar(1000)
> --declare @table22 sysname
> --select @table22= @dbname+ '.dbo.sysfiles'
>
>
> --set @table22 = @dbname+ '.dbo.sysfiles'
> exec ('Use '+ @dbname +';')
> select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size
> else
> 0 end))
> ,@logsize = sum(convert(bigint,case when status & 64 != 0 then size
> else 0 end))
> from @dbname.dbo.sysfiles
>
> exec ('Use '+ @dbname +';')
>
> select @reservedpages = sum(a.total_pages)
> ,@usedpages = sum(a.used_pages)
> ,@pages = sum(CASE
> WHEN it.internal_type IN (202,204) THEN 0
> WHEN a.type != 1 THEN a.used_pages
> WHEN p.index_id < 2 THEN a.data_pages
> ELSE 0
> END)
> from sys.partitions p
> join sys.allocation_units a on p.partition_id = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
>
> select @database_size_mb = (convert(dec (15,2),@dbsize) +
> convert(dec(15,2),@logsize)) * 8192 / 1048576
> select @unallocated_space_mb =(case
> when @dbsize >= @reservedpages then
> (convert
> (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 /
> 1048576
> else 0
> end)
>
> select @reserved_mb = @reservedpages * 8192 / 1048576.0
> select @data_mb = @pages * 8192 / 1048576.0
> select @index_mb = (@usedpages - @pages) * 8192 / 1048576.0
> select @unused_mb = (@reservedpages - @usedpages) * 8192 / 1048576.0
>
> insert into DBATECH.dbo.TEST select @@servername as 'serveur',@@version
> as
> 'version',@dbname as 'nom base', @database_size_mb as 'database_size'
> , cast (@unallocated_space_mb*100.0/@database_size_mb as
> decimal(10,2))as 'unallocated'
> , cast (@reserved_mb*100/@database_size_mb as decimal(10,2))as
> 'reserved'
> , cast(@data_mb*100/@database_size_mb as decimal(10,2))as 'data'
> , cast(@index_mb*100/@database_size_mb as decimal(10,2)) as
> 'index_1'
> , cast(@unused_mb*100/@database_size_mb as decimal(10,2))as 'unused'
> , cast((@database_size_mb - @unallocated_space_mb -
> @reserved_mb)*100/@database_size_mb as decimal (10,2))as 'TransactionLog'
> , (@database_size_mb - @unallocated_space_mb - @reserved_mb) as
> 'TransactionLogSize'
> , cast((@unallocated_space_mb + @reserved_mb)*100/@database_size_mb
> as
> decimal (10,2)) as 'DataFiles' ;
>
>
> print @@servername
> print @@version
> print @dbname
> print @database_size_mb
> print @unallocated_space_mb*100.0/@database_size_mb
> print @reserved_mb*100/@database_size_mb
> print @data_mb*100/@database_size_mb
> print @index_mb*100/@database_size_mb
> print @unused_mb*100/@database_size_mb
> print (@database_size_mb - @unallocated_space_mb -
> @reserved_mb)*100/@database_size_mb
> print ( @database_size_mb - @unallocated_space_mb - @reserved_mb)
> print ( @unallocated_space_mb + @reserved_mb)*100/@database_size_mb
>
>
> end
>
>
> J'obtiens bien les noms de bases mais les tailles des fichiers sont
> identiques pour toutes !!!!!
>
> Please help !!!!
> PS : avec la procedure exec sp_MSforeachdb 'exec Inventory_dba4' j'obtiens
> la même chose !!!
>
>
>
>