Urgt Pb recuperer taille des data sur toutes les bases d'1 serveur
2 réponses
gayoux
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)
--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
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' ;
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Romelard Fabrice [MVP]
Bonjour,
Sur quelle version de SQL Server travaillez vous ?
-- Cordialement
Romelard Fabrice [MVP]
"gayoux" wrote in message news:
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)
--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
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' ;
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)
--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
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' ;
Sur quelle version de SQL Server travaillez vous ?
-- Cordialement
Romelard Fabrice [MVP]
"gayoux" wrote in message news:
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)
--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
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' ;