En esta publicación del blog, discutiremos cómo monitorear el espacio en disco para sus bases de datos de SQL Server. Es importante verificar regularmente el espacio en disco para asegurarse de que haya suficiente espacio disponible para que sus bases de datos funcionen correctamente.

Procedimiento almacenado para recopilar el espacio en disco libre

El primer paso es crear un procedimiento almacenado que recopilará la información del espacio en disco libre para sus bases de datos. Este procedimiento almacenado recuperará la información del espacio en disco utilizando el comando “wmic” y la almacenará en una tabla temporal. Aquí hay un ejemplo del procedimiento almacenado:

CREATE PROCEDURE [Monitoring].[disk_space] 
AS
BEGIN
   SET NOCOUNT ON;
 
   DECLARE @query VARCHAR(MAX);
   DECLARE @threshold CHAR(3);
 
   SET @threshold = (SELECT warning_value FROM Monitoring.Thresholds WHERE item = 'disk_space');
 
   SET @query = '
   DECLARE @xp_cmdshell bit
   DECLARE @flipped bit
 
   SET @flipped = 0
 
   /* Verificar si xp_cmdshell está habilitado o no */
   SELECT @xp_cmdshell = (CONVERT(INT, ISNULL(value, value_in_use)))
   FROM  sys.configurations
   WHERE  name = ''xp_cmdshell'';
 
   IF @xp_cmdshell = 0
   BEGIN
      EXEC SP_CONFIGURE ''show advanced options'', 1
      RECONFIGURE 
      EXEC SP_CONFIGURE ''xp_cmdshell'', 1
      RECONFIGURE
      SET @flipped = 1
   END
 
   DECLARE @SQL NVARCHAR(1000)
 
   CREATE TABLE #DrvLetter (Drive VARCHAR(500))
   CREATE TABLE #DrvInfo (
      Drive VARCHAR(500) null,
      [MB free] DECIMAL(20,2),
      [MB TotalSize] DECIMAL(20,2),
      [Volume Name] VARCHAR(64)
     )
 
   INSERT INTO #DrvLetter
   EXEC xp_cmdshell ''wmic volume where drivetype="3" get caption, freespace, capacity, label''
   DELETE FROM #DrvLetter WHERE drive IS NULL OR len(drive) < 4 OR Drive LIKE ''%Capacity%'' OR Drive LIKE  ''%\\%\Volume%''
 
   DECLARE @STRLine VARCHAR(8000)
   DECLARE @Drive varchar(500)
   DECLARE @TotalSize REAL
   DECLARE @Freesize REAL
   DECLARE @VolumeName VARCHAR(64)
   
   WHILE EXISTS(SELECT 1 FROM #DrvLetter)
   BEGIN
      SET ROWCOUNT 1
      SELECT @STRLine = drive FROM #DrvLetter
      
      /* Obtener TotalSize */
      SET @TotalSize= CAST(LEFT(@STRLine,CHARINDEX('' '',@STRLine)) AS REAL)/1024/1024
   
      /* Eliminar Total Size */
      SET @STRLine = REPLACE(@STRLine, LEFT(@STRLine,CHARINDEX('' '',@STRLine)),'''')
      SET @Drive = LEFT(LTRIM(@STRLine),CHARINDEX('' '',LTRIM(@STRLine)))
      SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX('' '',LTRIM(@STRLine))),'''')))
      SET @Freesize = LEFT(LTRIM(@STRLine),CHARINDEX('' '',LTRIM(@STRLine)))
      SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX('' '',LTRIM(@STRLine))),'''')))
      SET @VolumeName = @STRLine
      
      INSERT INTO #DrvInfo
      SELECT @Drive, @Freesize/1024/1024 , @TotalSize, @VolumeName
 
      DELETE FROM #DrvLetter
      END
 
      SET ROWCOUNT 0
 
      /* POBLAR TABLA TEMPORAL CON DISCOS LÓGICOS */
      SET @SQL =''wmic /FailFast:ON logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename  /Format:csv''
      
      if object_id('tempdb..#output1') is not null drop table #output1
      CREATE TABLE #output1 (Col1 VARCHAR(2048))
      INSERT INTO #output1
      EXEC master..xp_cmdshell @SQL
      
      DELETE #output1 where ltrim(col1) is null or len(col1) = 1 or Col1 like ''Node,DeviceID,VolumeName%''
 
      if object_id('tempdb..#logicaldisk') is not null drop table #logicaldisk
      CREATE TABLE #logicaldisk (DeviceID varchar(128),VolumeName varchar(256))
 
      DECLARE @NodeName varchar(128)
      SET @NodeName = (SELECT TOP 1 LEFT(Col1, CHARINDEX('','',Col1)) FROM #output1)
 
      /* Limpiar el nombre del servidor */
      UPDATE #output1 SET Col1 = REPLACE(Col1, @NodeName, '''')
 
      INSERT INTO #logicaldisk
      SELECT LEFT(Col1, CHARINDEX('','',Col1)-2),  SUBSTRING(COL1, CHARINDEX('','',Col1)+1, LEN(col1))
      FROM #output1
 
      UPDATE dr
      SET dr.[Volume Name] = ld.VolumeName
      FROM #DrvInfo dr RIGHT OUTER JOIN #logicaldisk ld ON left(dr.Drive,1) = ld.DeviceID
      WHERE LEN([Volume Name]) = 1
 
      CREATE TABLE #DBInfo2 ( 
      ServerName VARCHAR(100),  
      DatabaseName VARCHAR(100),  
      FileSizeMB INT,  
      LogicalFileName sysname,  
      PhysicalFileName NVARCHAR(520),  
      Status sysname,  
      Updateability sysname,  
      RecoveryMode sysname,  
      FreeSpaceMB INT,  
      FreeSpacePct VARCHAR(7),  
      FreeSpacePages INT,  
      PollDate datetime)  
 
      DECLARE @command VARCHAR(5000)  
      
      DECLARE @instance VARCHAR(100)
      SELECT @instance = CONVERT(VARCHAR(100),SERVERPROPERTY('ServerName'))
      
      SELECT @command = ''Use [?] 
      SELECT  
      ''+@instance+'' AS ServerName,  
      ''?'' AS DatabaseName,
      CAST(sysfiles.size/128.0 AS int) AS FileSize,
      sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
      CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
      CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
      CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
      CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ''SpaceUsed'') AS int)/128.0 AS int) AS FreeSpaceMB,
      CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ''SpaceUsed'') AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ''%'' AS FreeSpacePct
      FROM dbo.sysfiles'' + ''
   
      INSERT INTO #DBInfo2 (
      ServerName,  
        DatabaseName,  
        FileSizeMB,  
        LogicalFileName,  
        PhysicalFileName,  
        Status,  
        Updateability,  
        RecoveryMode,  
        FreeSpaceMB,  
        FreeSpacePct)  
 
      EXEC sp_MSForEachDB @command  
 
      SELECT  
         db.ServerName Instance,
         db.DatabaseName AS DBName,  
         db.PhysicalFileName AS PhysicalFileLocation, 
           CASE
            WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)+ ':\'
            ELSE dr.drive+ ':\'
             END AS Drive, 
             db.FileSizeMB AS DBFileSizeMB,
             dr.[MB TotalSize] AS TotalSpaceInMB,
             dr.[MB free] AS FreeSpaceInMB,  
             CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) AS PercentFreeSpace
      FROM #DBInfo2 db
      JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) =  LEFT(dr.drive,LEN(dr.drive)) 
      WHERE db.DatabaseName not in (
      SELECT DatabaseName
      FROM #DBInfo2 DB
      JOIN (SELECT drive FROM #DrvInfo WHERE LEN(drive) > 3) DR 
        ON LEFT(db.PhysicalFileName, LEN(drive)) = DR.drive) 
       AND CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) < '+@threshold+'
      UNION ALL
      SELECT  
         db.ServerName Instance,
           db.DatabaseName AS DBName,  
           db.PhysicalFileName AS PhysicalFileLocation, 
           CASE
            WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)+ ':\'
            ELSE dr.drive+ ':\'
           END AS Drive, 
           db.FileSizeMB AS DBFileSizeMB,
           dr.[MB TotalSize] AS TotalSpaceInMB,
           dr.[MB free] AS FreeSpaceInMB,  
           CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) AS PercentFreeSpace
      FROM #DBInfo2 db
      JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) =  LEFT(dr.drive,LEN(dr.drive))
      WHERE LEN(dr.drive) > 3 AND CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) < '+@threshold+'
   
      DROP TABLE #DBInfo2
      DROP TABLE #logicaldisk
      DROP TABLE #DrvLetter
      DROP TABLE #DrvInfo
 
      IF @flipped = 1
      BEGIN
         EXEC SP_CONFIGURE ''xp_cmdshell'', 0
         RECONFIGURE
      END
 
      EXEC SP_CONFIGURE ''show advanced options'', 0
      GO
      RECONFIGURE
      GO';
 
   SELECT @query AS tsql;
END

Script de PowerShell para recopilar información del espacio en disco

A continuación, crearemos un script de PowerShell que invocará el procedimiento almacenado y recopilará la información del espacio en disco para todas las instancias de SQL Server. El script insertará la información recopilada en una base de datos centralizada. Aquí hay un ejemplo del script de PowerShell:

$server = "XXX"
$inventoryDB = "XXX"
 
#Crear la tabla DiskSpace si no existe en su instancia centralizada
#Puede eliminar el esquema "Monitoring" si lo desea.
$diskSpaceTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'DiskSpace' AND xtype = 'U')
CREATE TABLE [Monitoring].[DiskSpace](
   [Instance] [nvarchar](50) NULL,
   [DBName] [nvarchar](255) NULL,
   [PhysicalFileLocation] [nvarchar](500) NULL,
   [Drive] [nvarchar](50) NULL,
   [DBFileSizeMB] [int] NULL,
   [TotalSpaceInMB] [int] NULL,
   [FreeSpaceInMB] [int] NULL,
   [PercentFreeSpace] [float] NULL
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $diskSpaceTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
#Limpiar la tabla DiskSpace
Invoke-Sqlcmd -Query "TRUNCATE TABLE Monitoring.DiskSpace" -Database $inventoryDB -ServerInstance $server
 
#Crear la tabla thresholds si no existe en su instancia centralizada
$thresholdsTableCreationQuery = " 
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'thresholds' AND xtype = 'U')
CREATE TABLE [Monitoring].[thresholds](
   [id] [tinyint] IDENTITY(1,1) NOT NULL,
   [item] [varchar](25) NOT NULL,
   [warning_value] [tinyint] NOT NULL,
   [critical_value] [tinyint] NOT NULL,
 CONSTRAINT [PK_thresholds] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $thresholdsTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
#Insertar el valor de umbral para establecer el límite superior que el SP utilizará para limitar el conjunto de resultados (el valor no se insertará si ya existe).
#Esto está diseñado de esta manera para que pueda utilizar esta tabla para almacenar valores de umbral para otros propósitos (CPU, RAM, etc.)
$thresholdValueInsertQuery = "
IF NOT EXISTS (SELECT item FROM Monitoring.thresholds 
               WHERE item = 'disk_space')
BEGIN
   INSERT INTO Monitoring.thresholds VALUES ('disk_space', 50, 10)
END
"
Invoke-Sqlcmd -Query $thresholdvalueInsertQuery -Database $inventoryDB -ServerInstance $server
 
#Obtener todas las instancias con la respectiva versión de SQL Server
$instancesTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'instances' AND xtype = 'U')
CREATE TABLE instances(
   [name] [nvarchar](128) NULL,
   [instance] [nvarchar](128) NULL
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $instancesTableCreationQuery -Database $inventoryDB -ServerInstance $server
 
$instanceLookupQuery = "SELECT name, instance FROM instances"
 
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
 
#Para cada instancia, obtener la información del espacio en disco
foreach ($instance in $instances){
   $diskSpaceQuery = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query "EXEC Monitoring.disk_space" -MaxCharLength 8000
   
   #Obtener la información de los discos para la instancia
   Write-Host "Obteniendo información del disco para la instancia" $instance.instance
    $results = Invoke-Sqlcmd -Query $diskSpaceQuery.tsql -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30
 
   #Realizar la inserción en la tabla DiskSpace solo si devuelve al menos 1 fila
   if($results.Length -ne 0){
      #Construir la instrucción de inserción
      $insert = "INSERT INTO Monitoring.DiskSpace VALUES"
      foreach($result in $results){
         $insert += "
         (
         '"+$result.Instance+"',
         '"+$result.DBName+"',
         '"+$result.PhysicalFileLocation+"',
         '"+$result.Drive+"',
         "+$result.DBFileSizeMB+",
         "+$result.TotalSpaceInMB+",
         "+$result.FreeSpaceInMB+",
         "+$result.PercentFreeSpace+"
         ),
         "
      }
 
      #Almacenar los resultados en la tabla DiskSpace local en su instancia central
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
   } 
}
Write-Host "¡Hecho!"

Después de ejecutar los scripts anteriores, tendrá una base de datos centralizada que contiene la información del espacio en disco para todas sus bases de datos de SQL Server. Luego, puede utilizar esta información para monitorear el espacio en disco y tomar las acciones necesarias si el espacio libre cae por debajo de un umbral determinado.

Es importante tener en cuenta que es posible que deba modificar los scripts para adaptarlos a su entorno específico. Asegúrese de reemplazar “XXX” con los valores apropiados para su servidor y base de datos.

Al monitorear regularmente el espacio en disco para sus bases de datos de SQL Server, puede asegurarse de que sus bases de datos tengan suficiente espacio para funcionar de manera eficiente y evitar cualquier problema potencial relacionado con las limitaciones de espacio en disco.

Si tiene alguna pregunta o encuentra algún problema con los scripts, no dude en dejar un comentario a continuación.