Hola lectores,
¡Buenas tardes!

En esta publicación demostraré cómo monitorear el crecimiento automático del espacio en una base de datos (crecimiento automático) en SQL Server, de modo que sea posible identificar el usuario que causó el crecimiento y cuándo ocurrieron los eventos de crecimiento automático en la base de datos.

Obtener estos resultados es posible gracias a la función del sistema ::fn_trace_gettable, que le permite consultar los eventos de seguimiento que se ejecutan de forma predeterminada en todas las instancias de SQL Server.

Creando un banco de pruebas

Antes de probar la consulta que nos muestra los eventos de AutoGrowth de la base de datos, creemos un banco de pruebas, con las opciones predeterminadas de SQL Server (tamaño inicial 5 MB y crecimiento automático cada 1 MB) e insertemos algunos registros para forzar que el espacio se agote y la base de datos tenga que crecer automáticamente.

CREATE DATABASE [AutoGrowth] 
GO

ALTER DATABASE [AutoGrowth] SET RECOVERY FULL
GO

USE [AutoGrowth] 
GO

IF (OBJECT_ID('dbo.Teste') IS NOT NULL) DROP TABLE dbo.Teste
CREATE TABLE dbo.Teste (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    Nome CHAR(8000),
    Profissao VARCHAR(8000)
)
GO


DECLARE @Contador INT = 1

WHILE (@Contador < 100000)
BEGIN

    INSERT INTO dbo.Teste(Nome, Profissao)
    VALUES ('Dirceu', 'DBA')

    SET @Contador = @Contador + 1

END

Su base de datos ahora debería tener 788 MB y haber crecido automáticamente en espacio cientos de veces. Averigüemos ahora cuándo ocurrieron estos eventos y quién provocó el aumento de espacio.

Para mejorar la prueba, sugiero abrir dos conexiones con diferentes usuarios e ingresar los datos al mismo tiempo.

Identificación de tipos de eventos de seguimiento

Usando la consulta a continuación, podemos identificar rápidamente todos los EventClass que podemos usar en la función ::fn_trace_gettable:

DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 )

SELECT DISTINCT
    eventid,
    name
FROM
    fn_trace_geteventinfo(@id) A
    JOIN sys.trace_events B ON A.eventid = B.trace_event_id

SQL Server - fn_trace_gettable EventClass
SQL Server - clase de evento fn_trace_gettable

Comprobando los eventos de crecimiento.

Ahora que nuestra base de pruebas está lista y se han insertado los datos, analicemos los eventos de crecimiento automático de la base de datos usando la siguiente consulta:

DECLARE 
    @Ds_Database sysname = 'AutoGrowth',
    @Ds_Arquivo_Trace VARCHAR(500) = (SELECT [path] FROM sys.traces WHERE is_default = 1)
    
DECLARE
    @Index INT = PATINDEX('%\%', REVERSE(@Ds_Arquivo_Trace))

DECLARE
    @Nm_Arquivo_Trace VARCHAR(500) = LEFT(@Ds_Arquivo_Trace, LEN(@Ds_Arquivo_Trace) - @Index) + '\log.trc'


SELECT
    A.DatabaseName,
    A.[Filename],
    ( A.Duration / 1000 ) AS 'Duration_ms',
    A.StartTime,
    A.EndTime,
    ( A.IntegerData * 8.0 / 1024 ) AS 'GrowthSize_MB',
    A.ApplicationName,
    A.HostName,
    A.LoginName
FROM
    ::fn_trace_gettable(@Nm_Arquivo_Trace, DEFAULT)		A
    LEFT JOIN sys.databases					B	ON ( B.name = @Ds_Database )
WHERE
    A.EventClass >= 92
    AND A.EventClass <= 95
    AND A.ServerName = @@servername 
    AND A.DatabaseName = @Ds_Database 
    AND B.create_date < EndTime
ORDER BY
    A.StartTime DESC

SQL Server - Monitor Autogrowth
SQL Server: supervisar el crecimiento automático

Con esto pudimos analizar qué base de datos sufrió el evento de autocrecimiento, qué archivo específico, cuánto tiempo tardó el servidor en aumentar el espacio, cuánto espacio se aumentó, qué software está procesando la consulta que provocó el aumento de espacio, nombre de host e inicio de sesión del usuario que está ejecutando esta consulta.

¡Eso es todo, amigos!
Espero que este consejo te ayude de alguna manera.

Un abrazo y nos vemos en el próximo post!