Comment surveiller SQL Server Croissance de la base de données à l'aide de TSQL et Excel

La surveillance de la croissance de la base de données est un facteur clé dans la planification des ressources d'un serveur. Utilisez ce script et créez un rapport de croissance de base de données facile à comprendre

Pourquoi vous devez surveiller la croissance de la base de données

Si vous êtes un administrateur de base de données SQL et si vous n'avez pas "SQL Server planification de la capacité de la base de données » dans votre liste de tâches clés, vous pouvez être sûr que les bases de données sur votre serveur rempliront bientôt votre espace disque. Le suivi de la taille et de la croissance des bases de données SQL est l'une des tâches principales de la planification de la capacité. Cela garantit également qu'il y a suffisamment d'espace sur le disque pour que vos bases de données se développent.

Bricolage via SQL Script et Excel

Dans cet article, nous utiliserons SQL et Excel pour capturer la taille des bases de données SQL avec le modèle. Cela nous permettra de planifier les besoins d'espace imminents et nous aidera également à comprendre la chronologie pendant laquelle il y a un volume important.

Ce processus est subdivisé en quatre étapes différentes afin que vous puissiez suivre sans aucun problème.

Étape 1 : Exécutez ce script dans une nouvelle fenêtre de requête.

La sortie aura des noms de base de données comme en-têtes de ligne et Mois comme en-têtes de colonne. Les valeurs affichées dans la sortie correspondent à la taille de la base de données en Go.

declare @v_count as integer, @do_count as integer, @v_month as varchar(20),@sql as varchar(5000)

create table [t_databases]
(
Database_Name varchar(200),
January       float(3),
February      float(3),
March  float(3),
April  float(3),
May    float(3),
June   float(3),
July   float(3),
August float(3),
September     float(3),
October       float(3),
November      float(3),
December      float(3),
)
create table t_databases_gateway
(
Database_Name varchar(200),
Database_Size float(3)
)
set @v_count = (select COUNT(*) from t_databases ) 
if @v_count = 0 
begin
insert into t_databases(Database_Name)
select name from sysdatabases 
 end
if @v_count <> 0 
 begin 
--this script captures the size of all databases. You can add a where clause to capture the size of specific database name 
 INSERT t_databases (Database_Name) 
 SELECT DISTINCT Name FROM sysdatabases cr LEFT JOIN t_databases c ON cr.Name = c.Database_Name WHERE c.Database_Name IS NULL 
 end 
 --select * from master.dbo.t_databases 
 --drop table t_databases 
 set @do_count = 1 
 while (@do_count <=12) 
 begin 
 set @v_month = DATENAME(m, str(@do_count) + '/1/2016') –change the year to 2017 or other year as per your requirement 
 truncate table master.dbo.t_databases_gateway 
 insert into master.dbo.t_databases_gateway select distinct
(msdb.dbo.backupset.[database_name]),max(msdb.dbo.backupset.[Backup_Size]/1073741824)
from msdb.dbo.backupset inner join master.dbo.t_databases on msdb.dbo.backupset.[database_name] = master.dbo.t_databases.[Database_Name] where
datepart(m,msdb.dbo.backupset.[backup_finish_date]) =  @do_count and datepart(yyyy,msdb.dbo.backupset.[backup_finish_date] ) = 2015 group by msdb.dbo.backupset.[database_name]
set @sql = 'update master.dbo.t_databases set ' + @v_month + ' = (select Database_Size from master.dbo.t_databases_gateway where master.dbo.t_databases.Database_Name = master.dbo.t_databases_gateway.Database_Name)'
exec (@sql)
set @do_count = @do_count + 1
end
select * from t_databases
drop table t_databases_gateway
drop table t_databases

Sortie:

Étape 2 : créez une nouvelle instance d'Excel et copiez la sortie de l'étape précédente dans votre nouvelle feuille Excel.

Étape 3 : Sélectionnez maintenant les deux premières lignes sur la feuille et insérez un graphique en courbes 2D.

Cela créera le graphique de tendance pour la base de données sélectionnée. Dans le graphique, l'axe X indiquera les mois et l'axe Y indiquera la taille de la base de données en Go.

Étape 4 : Sélectionnez l'intégralité des données en faisant glisser le symbole "plus" pour l'envelopper.

Cela montrera la tendance de toutes les bases de données sur le graphique.

Si vous ignorez l'étape 3 et insérez un graphique linéaire 2D en sélectionnant l'intégralité du tableau, vous obtiendrez toujours un graphique, mais l'axe X indiquera les noms de base de données au lieu du mois, ce n'est pas la sortie requise.

La table de sortie peut avoir des valeurs NULL. Cela se produit car l'historique de sauvegarde de la base de données peut ne contenir aucun enregistrement pour la base de données pour ce mois particulier. Cela implique également que le script se réfère à la taille de la sauvegarde dans le tableau de l'historique des sauvegardes pour suivre la tendance de croissance. Si l'une de vos bases de données n'est pas incluse dans le plan de sauvegarde, la table de sortie contiendra toujours le nom de la base de données mais les valeurs pour tous les mois seront NULL

Correction d'une base de données surdimensionnée

Si vous n'avez pas suivi la stratégie ci-dessus dans le passé et que votre base de données est surdimensionnée, cela peut entraîner divers problèmes. Dans un tel cas, mieux vaut trouver un SQL Server outil de récupération de fichiers pour résoudre le problème de manière efficace et efficiente.

Introduction de l'auteur:

Neil Varley est un expert en récupération de données dans DataNumen, Inc., qui est le leader mondial des technologies de récupération de données, y compris réparer le problème d'Outlook et des produits logiciels de récupération Excel. Pour plus d'informations, visitez www.datanumen.com