[SQL Server] Afficher la taille de toutes les bases
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 20/05/2020
-- Description: Afficher la taille des bases de données sur toutes les instance SQL Server, la taille des disque et la taille disponible de disque
-- =============================================
/*
Le traitement de ce script utilise les vues systèmes indiquées ci-dessous :
sysaltfiles
sys.database_files
sys.dm_os_volume_stats
sys.master_files
*/
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_LiesTemp]'))
BEGIN
DROP TABLE #Serveurs_LiesTemp
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_Lies]'))
BEGIN
DROP TABLE #Serveurs_Lies
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))
BEGIN
DROP TABLE #Resultat
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#liste_bases]'))
BEGIN
DROP TABLE #liste_bases
END
CREATE TABLE #Serveurs_LiesTemp
(
SRV_NAME VARCHAR(150),
SRV_PROVIDERNAME VARCHAR(150),
SRV_PRODUCT VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
SRV_PROVIDERSTRING VARCHAR(150),
SRV_LOCATION VARCHAR(150),
SRV_CAT VARCHAR(150)
)
CREATE TABLE #Serveurs_Lies
(
Num_Lig INT IDENTITY (1,1),
SRV_NAME VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
ServerName VARCHAR(150) NULL
)
CREATE NONCLUSTERED INDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies
(
ServerName ASC
)
CREATE TABLE #Resultat
(
Num_Lig INT IDENTITY (1,1),
Serveur_Lie VARCHAR(150) NULL,
server_name VARCHAR(150) NULL,
Environnement VARCHAR(100) NULL,
nom_base VARCHAR(150) NULL,
[application] NVARCHAR(100) NULL,
[type_desc] VARCHAR(35) NULL,
physical_name NVARCHAR(500) NULL,
DB_Size_in_MB DECIMAL(25,2) NULL,
volume_mount_point NVARCHAR(512) NULL,
logical_volume_name NVARCHAR(512) NULL,
file_system_type NVARCHAR(512) NULL,
CapacityDisque_MB DECIMAL(25,2) NULL,
FreeDisque_MB DECIMAL(25,2) NULL,
collect_date DATETIME NULL,
Requete NVARCHAR(MAX) NULL,
Message_erreur NVARCHAR(MAX) NULL
)
CREATE TABLE #liste_bases
(
Num_Base INT IDENTITY (1,1),
name SYSNAME
)
DECLARE @Num_Lig AS INT
,@Nbre_Lig AS INT
,@Num_Base AS INT
,@Nbre_Base AS INT
,@Serveur_Lie AS VARCHAR(150)
,@ServerName AS NVARCHAR(4000)
,@Environnement AS VARCHAR(35)
,@Requete AS NVARCHAR(MAX)
,@LocalHot AS VARCHAR(50)
BEGIN
SELECT @LocalHot = @@SERVERNAME
INSERT INTO #Serveurs_LiesTemp
EXEC sp_linkedservers
INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)
SELECT DISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp
SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies
WHILE @Num_Lig <= @Nbre_Lig
BEGIN
BEGIN TRY
SELECT @ServerName=NULL
,@Environnement=NULL
SELECT @Serveur_Lie =SRV_DATASOURCE,@ServerName = NULL FROM #Serveurs_Lies WHERE Num_Lig=@Num_Lig
--Récupérer le nom du serveur
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT @ServerName_Out=ServerName FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + N',''SELECT @@SERVERNAME AS ServerName'')' ELSE 'SELECT @@SERVERNAME AS ServerName' END
EXECUTE sp_executesql @Requete,N'@ServerName_Out VARCHAR(150) OUTPUT',@SERVERNAME_Out=@ServerName OUTPUT
--Récupérer l'environnement du serveur
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT @Environnement_Out=Environnement FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + N',''' ELSE '' END
+ 'SELECT Environnement= CASE WHEN CommandBackup LIKE ''''%BCK_PROD%'''' THEN ''''PRODUCTION''''
WHEN CommandBackup LIKE ''''%BCK_HOM%'''' THEN ''''HOMOLOGATION''''
WHEN CommandBackup LIKE ''''%BCK_DEV%'''' THEN ''''DEVELOPPEMENT''''
ELSE ''''INCONNU''''
END
FROM (
SELECT TOP 1 command AS CommandBackup
FROM [msdb].[dbo].[sysjobsteps]
WHERE command LIKE ''''%LOG_BCK%'''' OR command LIKE ''''%DIFF_BCK%'''' OR command LIKE ''''%FULL_BCK%''''
) T1 '
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END
EXECUTE sp_executesql @Requete,N'@Environnement_Out VARCHAR(35) OUTPUT',@Environnement_Out=@Environnement OUTPUT
IF NOT EXISTS (SELECT ServerName FROM #Serveurs_Lies WHERE ISNULL(ServerName,'')=@ServerName)
BEGIN
UPDATE #Serveurs_Lies
SET
ServerName=@ServerName
WHERE Num_Lig=@Num_Lig
--Récupérer la liste des bases sur l'instance concernée
TRUNCATE TABLE #liste_bases
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END
SELECT @Requete= @Requete + N'SELECT name FROM sys.databases'
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END
INSERT INTO #liste_bases (name)
EXECUTE (@Requete)
SELECT @Num_Base=1 , @Nbre_Base=COUNT(name) FROM #liste_bases
WHILE @Num_Base<=@Nbre_Base
BEGIN
BEGIN TRY
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END
SELECT @Requete= @Requete + ' SELECT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie,@@SERVERNAME AS server_name, Environnement,nom_base,[type_desc],physical_name,SUM(DB_Size_in_MB) DB_Size_in_MB,
volume_mount_point,logical_volume_name,file_system_type,CAST(CAST(total_bytes AS NUMERIC(20,2))/(1024*1024) AS NUMERIC(12,2)) AS CapacityDisque_MB,CAST(CAST(available_bytes AS DECIMAL(25,2))/(1024*1024) AS DECIMAL(25,2)) AS FreeDisque_MB
,collect_date FROM (
SELECT ''''' + name + ''''' AS nom_base,@@SERVERNAME AS Instance,[type_desc],physical_name,CAST((size * 8.00) / 1024 as NUMERIC(12,2)) AS DB_Size_in_MB
,Environnement= CASE WHEN CommandBackup LIKE ''''%BCK_PROD%'''' THEN ''''PRODUCTION''''
WHEN CommandBackup LIKE ''''%BCK_HOM%'''' THEN ''''HOMOLOGATION''''
WHEN CommandBackup LIKE ''''%BCK_DEV%'''' THEN ''''DEVELOPPEMENT''''
ELSE ''''INCONNU''''
END
,S2.volume_mount_point,S2.logical_volume_name,S2.file_system_type,S2.total_bytes,S2.available_bytes
,CONVERT(NVARCHAR(8), GETDATE(), 112) AS collect_date
FROM ' + QUOTENAME(name) + '.sys.database_files S1 with (nolock)'
+ ' CROSS APPLY sys.dm_os_volume_stats(DB_ID(''''' + name + '''''), s1.file_id) S2
CROSS JOIN (
SELECT TOP 1 command AS CommandBackup
FROM [msdb].[dbo].[sysjobsteps] with (nolock)
WHERE command LIKE ''''%COMP_LOG_BACKUP%'''' OR command LIKE ''''%COMP_DIFF_BACKUP%'''' OR command LIKE ''''%COMP_FULL_BACKUP%''''
) S3
) T1
GROUP BY Environnement,Instance,nom_base,[type_desc],physical_name,volume_mount_point,logical_volume_name,file_system_type,total_bytes,available_bytes,collect_date UNION' FROM #liste_bases WHERE Num_Base=@Num_Base
SELECT @Requete = REVERSE(SUBSTRING(REVERSE(@Requete),6,LEN(@Requete)))
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END
INSERT INTO #Resultat (Serveur_Lie, server_name,Environnement,nom_base,type_desc,physical_name,DB_Size_in_MB,volume_mount_point,logical_volume_name,file_system_type,CapacityDisque_MB,FreeDisque_MB,collect_date)
EXECUTE (@Requete)
UPDATE #Resultat
SET
Requete=@Requete
WHERE Requete IS NULL
END TRY
BEGIN CATCH
BEGIN TRY
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END
SELECT @Requete= @Requete + ' SELECT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie,@@SERVERNAME AS server_name, Environnement,nom_base,[type_desc],physical_name,SUM(DB_Size_in_MB) DB_Size_in_MB,
volume_mount_point,logical_volume_name,file_system_type,CAST(CAST(total_bytes AS NUMERIC(20,2))/(1024*1024) AS NUMERIC(12,2)) AS CapacityDisque_MB,CAST(CAST(available_bytes AS DECIMAL(25,2))/(1024*1024) AS DECIMAL(25,2)) AS FreeDisque_MB
,collect_date FROM (
SELECT ''''' + name + ''''' AS nom_base,@@SERVERNAME AS Instance,[type_desc],physical_name,CAST((size * 8.00) / 1024 as NUMERIC(12,2)) AS DB_Size_in_MB
,Environnement= CASE WHEN CommandBackup LIKE ''''%BCK_PROD%'''' THEN ''''PRODUCTION''''
WHEN CommandBackup LIKE ''''%BCK_HOM%'''' THEN ''''HOMOLOGATION''''
WHEN CommandBackup LIKE ''''%BCK_DEV%'''' THEN ''''DEVELOPPEMENT''''
ELSE ''''INCONNU''''
END
,S2.volume_mount_point,S2.logical_volume_name,S2.file_system_type,S2.total_bytes,S2.available_bytes
,CONVERT(NVARCHAR(8), GETDATE(), 112) AS collect_date
FROM sys.master_files S1 with (nolock)'
+ ' CROSS APPLY sys.dm_os_volume_stats(S1.database_id, S1.file_id) S2
CROSS JOIN (
SELECT TOP 1 command AS CommandBackup
FROM [msdb].[dbo].[sysjobsteps] with (nolock)
WHERE command LIKE ''''%COMP_LOG_BACKUP%'''' OR command LIKE ''''%COMP_DIFF_BACKUP%'''' OR command LIKE ''''%COMP_FULL_BACKUP%''''
) S3 WHERE DB_NAME(S1.database_id)=''''' + name + '''''
) T1 ' + ' GROUP BY Environnement,Instance,nom_base,[type_desc],physical_name,volume_mount_point,logical_volume_name,file_system_type,total_bytes,available_bytes,collect_date UNION' FROM #liste_bases WHERE Num_Base=@Num_Base
SELECT @Requete = REVERSE(SUBSTRING(REVERSE(@Requete),6,LEN(@Requete)))
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END
INSERT INTO #Resultat (Serveur_Lie, server_name,Environnement,nom_base,type_desc,physical_name,DB_Size_in_MB,volume_mount_point,logical_volume_name,file_system_type,CapacityDisque_MB,FreeDisque_MB,collect_date)
EXECUTE (@Requete)
UPDATE #Resultat
SET
Requete=@Requete
WHERE Requete IS NULL
END TRY
BEGIN CATCH
INSERT INTO #Resultat (Serveur_Lie, server_name,Environnement,nom_base,type_desc,physical_name,DB_Size_in_MB,collect_date,Requete,Message_erreur)
VALUES (@Serveur_Lie,@ServerName,@Environnement, (SELECT TOP 1 name FROM #liste_bases WHERE Num_Base=@Num_Base),NULL,NULL,NULL,GETDATE(),@Requete,ERROR_MESSAGE())
END CATCH
END CATCH
SELECT @Num_Base = @Num_Base + 1
END
END
END TRY
BEGIN CATCH
INSERT INTO #Resultat (Serveur_Lie, server_name,Environnement,nom_base,type_desc,physical_name,DB_Size_in_MB,CapacityDisque_MB,FreeDisque_MB,collect_date,Requete,Message_erreur)
VALUES (@Serveur_Lie,@ServerName,@Environnement, NULL,NULL,NULL,NULL,NULL,NULL,GETDATE(),@Requete,ERROR_MESSAGE())
END CATCH
SELECT @Num_Lig = @Num_Lig + 1
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[BasesTailles]'))
BEGIN
DROP TABLE [tempdb]..[BasesTailles]
END
UPDATE #Resultat
SET
[application] = CASE WHEN [nom_base] LIKE 'Base1%' THEN 'APPLICATION_1'
WHEN [nom_base] LIKE 'Base2%' THEN 'Application_2'
WHEN [nom_base] LIKE 'Base3%' THEN 'Application_3'
ELSE 'INCONNU' END
SELECT * INTO [tempdb]..[BasesTailles] FROM #Resultat
DROP TABLE #Serveurs_LiesTemp
DROP TABLE #Serveurs_Lies
DROP TABLE #liste_bases
DROP TABLE #Resultat
END
END
GO