none
Reducir registros y tamaño de base de datos msdb SQL SERVER 2017 ENTERPRISE RRS feed

  • Pregunta

  • hola amigos, tengo el siguiente inconveniente con la base de datos msdb debido que su tamaño esta superando el tamaño del disco y la consulta que debo realizar para reducir el tamaño de la base de datos msdb.

    espero su ayuda.

    gracias.

    domingo, 14 de junio de 2020 1:13

Respuestas

  • Hola Diego JQS:

    En la base de datos MSDB se aloja la información del agente, alertas trabajos, copias de seguridad, etc

    Yo creo que lo primero sería saber en que tablas tienes tanta info, para que el mdf tenga ese tamaño.

    DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
     
    --Cursor to get the name of all user tables from the sysobjects listing
    DECLARE tableCursor CURSOR
    FOR
    select [name]
    from dbo.sysobjects
    where  OBJECTPROPERTY(id, N'IsUserTable') = 1
    FOR READ ONLY
     
    --A procedure level temp table to store the results
    CREATE TABLE #TempTable
    (
        tableName varchar(100),
        numberofRows varchar(100),
        reservedSize varchar(50),
        dataSize varchar(50),
        indexSize varchar(50),
        unusedSize varchar(50)
    )
     
    --Open the cursor
    OPEN tableCursor
     
    --Get the first table name from the cursor
    FETCH NEXT FROM tableCursor INTO @TableName
     
    --Loop until the cursor was not able to fetch
    WHILE (@@Fetch_Status = 0)
    BEGIN
        --Dump the results of the sp_spaceused query to the temp table
        INSERT  #TempTable
            EXEC sp_spaceused @TableName
     
        --Get the next table name
        FETCH NEXT FROM tableCursor INTO @TableName
    END
     
    --Get rid of the cursor
    CLOSE tableCursor
    DEALLOCATE tableCursor
     
    --Select all records so we can use the reults
    SELECT *
    FROM #TempTable
     
    --Final cleanup!
    DROP TABLE #TempTable
    
    

    A partir de ahí, ya puedes ir realizando deletes. También es posible, que simplemente tengas que realizar una reducción de la misma.

    USE [msdb]
    GO
    DBCC SHRINKDATABASE(N'msdb' )
    GO
    

    • Marcado como respuesta Diego JQS lunes, 15 de junio de 2020 3:41
    domingo, 14 de junio de 2020 4:34
  • Hola Diego JQS:

    Mejor:

    exec msdb.dbo.sp_maintplan_delete_log @oldest_time='20200101'

    Donde la fecha puede ser la de hoy. (20200615) yyyyMMdd

    O desde el management studio, Explorador de objetos, Administración, Registros de Sql , Planes de mantenimiento. Y dispondrás de un botón de delete con una marca de borrar toda la historia.


    lunes, 15 de junio de 2020 3:52
  • ¿Alguna novedad sobre la consulta realizada? ¿Han sido útiles las respuestas proporcionadas?  
    • Marcado como respuesta Diego JQS martes, 16 de junio de 2020 1:35
    lunes, 15 de junio de 2020 20:44
    Moderador
  • Haz un backup de las bases de datos del sistema, y luego un truncate table.

    Luego realizas el reducir tamaño.

    • Marcado como respuesta Diego JQS martes, 16 de junio de 2020 4:15
    martes, 16 de junio de 2020 4:08

Todas las respuestas

  • Hola Diego JQS:

    En la base de datos MSDB se aloja la información del agente, alertas trabajos, copias de seguridad, etc

    Yo creo que lo primero sería saber en que tablas tienes tanta info, para que el mdf tenga ese tamaño.

    DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
     
    --Cursor to get the name of all user tables from the sysobjects listing
    DECLARE tableCursor CURSOR
    FOR
    select [name]
    from dbo.sysobjects
    where  OBJECTPROPERTY(id, N'IsUserTable') = 1
    FOR READ ONLY
     
    --A procedure level temp table to store the results
    CREATE TABLE #TempTable
    (
        tableName varchar(100),
        numberofRows varchar(100),
        reservedSize varchar(50),
        dataSize varchar(50),
        indexSize varchar(50),
        unusedSize varchar(50)
    )
     
    --Open the cursor
    OPEN tableCursor
     
    --Get the first table name from the cursor
    FETCH NEXT FROM tableCursor INTO @TableName
     
    --Loop until the cursor was not able to fetch
    WHILE (@@Fetch_Status = 0)
    BEGIN
        --Dump the results of the sp_spaceused query to the temp table
        INSERT  #TempTable
            EXEC sp_spaceused @TableName
     
        --Get the next table name
        FETCH NEXT FROM tableCursor INTO @TableName
    END
     
    --Get rid of the cursor
    CLOSE tableCursor
    DEALLOCATE tableCursor
     
    --Select all records so we can use the reults
    SELECT *
    FROM #TempTable
     
    --Final cleanup!
    DROP TABLE #TempTable
    
    

    A partir de ahí, ya puedes ir realizando deletes. También es posible, que simplemente tengas que realizar una reducción de la misma.

    USE [msdb]
    GO
    DBCC SHRINKDATABASE(N'msdb' )
    GO
    

    • Marcado como respuesta Diego JQS lunes, 15 de junio de 2020 3:41
    domingo, 14 de junio de 2020 4:34
  •  hola Javi Fernández F comentarte que tengo el siguiente detalle sobre la base de datos

    no tendría ningún problema si realizo un delete a toda la tabla ???

    existiria algún efecto colateral ??

    gracias.


    Diego JQS

    lunes, 15 de junio de 2020 3:43
  • Hola Diego JQS:

    Mejor:

    exec msdb.dbo.sp_maintplan_delete_log @oldest_time='20200101'

    Donde la fecha puede ser la de hoy. (20200615) yyyyMMdd

    O desde el management studio, Explorador de objetos, Administración, Registros de Sql , Planes de mantenimiento. Y dispondrás de un botón de delete con una marca de borrar toda la historia.


    lunes, 15 de junio de 2020 3:52
  • ¿Alguna novedad sobre la consulta realizada? ¿Han sido útiles las respuestas proporcionadas?  
    • Marcado como respuesta Diego JQS martes, 16 de junio de 2020 1:35
    lunes, 15 de junio de 2020 20:44
    Moderador
  • hola Javi Fernández F comentarte que realice la operación sugerida y tengo el siguiente mensaje, mencionar también que la base de datos se encuentra en Modelo de recuperación simple, 

    que sugerencia debería seguir para realizar el proceso de eliminación de los registros ??

    gracias


    Diego JQS

    martes, 16 de junio de 2020 1:34
  • Haz un backup de las bases de datos del sistema, y luego un truncate table.

    Luego realizas el reducir tamaño.

    • Marcado como respuesta Diego JQS martes, 16 de junio de 2020 4:15
    martes, 16 de junio de 2020 4:08
  • hola Javi Fernández F te comento que resulto la sugerencia, realice un truncate a la tabla, luego se realizo la reducción de la base de datos ahora todo se encuentra en un estado correcto.

    existirá alguna forma de evitar que esto suceda en un futuro, tener alguna tarea programa que realice tal proceso.

    muchas gracias por al ayuda 


    Diego JQS

    martes, 16 de junio de 2020 4:20
  • hola Óscar Ignacio Navarro López pues informarte que la ayuda solicitada se culmino correctamente, y se agradece por la colaboración a Javi Fernández F.

    Gracias.

    Saludos.


    Diego JQS

    martes, 16 de junio de 2020 4:22
  • Eso te ha ocurrido porque tienes algún plan de mantenimiento que va anotando en su historial lo que hace.

    Lo suyo es crear un trabajo que te vaya borrando los históricos del plan de mantenimiento para que no crezcan a límites preocupantes. 

    Haces un job del agente que ejecute un delete contra esa tabla where fecha < dateadd(year,-1,getdate()) o algo del estilo, y ya tienes tu mantenedor de las tablas históricas. Es posible que el propio plan de mantenimiento tenga algo para que se configure el borrado, pero eso ya depende mucho de la versión, y del plan que fuese.

    martes, 16 de junio de 2020 5:14