locked
Base de datos demasiado grande RRS feed

  • Pregunta

  • Hola a todos!!

    Tengo un problema que me está provocando demasiados problemas, espero que alguien por aquí tenga alguna solución.

    El problema se da en una base de datos de aproximadamente 40gb lo cual es demasiado para el contenido que tiene, por lo tanto ejecute un script que dice el tamaño de todas las tablas de la base de datos. 

    El tema es que cuando pongo los resultados en una planilla excel y sumo los tamaños de las tablas y los indices, no es ni remotamente cercano al tamaño que tiene la base de datos. La base como dije es de 40Gb y la suma de las tablas no llega a los 3Gb.

    Alguien sabe por que puede haber tanta diferencia?
    Estoy usando SQL 2005 en modo compatiblilidad con SQL 2000.


    Gracias por adelantado.
    • Editado JavierSc jueves, 26 de noviembre de 2009 14:03
    miércoles, 25 de noviembre de 2009 20:39

Respuestas

Todas las respuestas

  • El tamanio de la base incluyendo el log de transacciones?

    use midb;
    go
    EXEC sp_helpdb 'midb';
    go

    Muestranos los resultados, incluyendo el modelo de recuperacion que esta como parte de la columna "status" en el primer set.


    AMB 

    jueves, 26 de noviembre de 2009 2:36
  • Hola.

    Adicionalmente, sería útil saber el resultado de ejecutar:

    exec sp_spaceused

    Sobre la base de datos en cuestión. Es posible que haya un importante espacio reservado y no utilizado en la base de datos.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    jueves, 26 de noviembre de 2009 8:43
    Moderador
  • Hunchback y qwalgrande, muchas gracias por sus respuestas. 
    A continuación les envío los datos que me solicitaron en una imagen:


    En la primera consulta en la columna status, en la imagen no se ve todo el contenido, asi que lo copio a continuación:

    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AI, SQLSortOrder=54, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics

    Gracias nuevamente

    Javier
    jueves, 26 de noviembre de 2009 12:47
  • Pues no parece que haya mucho espacio libre y son los datos los que se llevan esos casi 40Gb. ¿Estás seguro que has sumado correctamente el tamaño de las tablas? Ejecuta el siguiente comando para saber el tamaño de cada una de las tablas

    exec sp_msforeachtable 'sp_spaceused ''?'''

     

    El siguiente script, por su parte, te sumaría el tamaño de las tablas. Es lo que deberías tener en el Excel

    create table #total (name sysname, rows int, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50))
    
    insert #total
    	exec sp_msforeachtable 'sp_spaceused ''?'''
    	
    update #total set reserved = replace(reserved, ' KB', '')
    	, unused = replace(unused, ' KB', ''), data = replace(data, ' KB', '')
    	, index_size = replace(index_size, ' KB', '')
    	
    select sum(cast(reserved as bigint)) / 1024 reserved_MB
    	, sum(cast(unused as bigint))/1024 unused_MB, sum(cast(data as bigint)) /1024 data_MB
    	, sum(cast(index_size as bigint))/1024 index_size_MB
    from #total
    
    
    DROP TABLE #total

     

     

     

    jueves, 26 de noviembre de 2009 13:32
  • Gracias por la respuesta Carlos.

    Ejecute esa consulta y puse los resultados en un excel.

    A la derecha de la hoja esta el resultado de tu script, luego puse el detalle de cada tabla a la izquierda por si sirve de algo.

    Saludos.-

    jueves, 26 de noviembre de 2009 14:17
  • No cuadra casi ningún dato, es extraño.

    Ejecuta el comando (para actualizar la información de espacio usado):
    DBCC UPDATEUSAGE (tuBD)
    Y también la información del tamaño del log de transacciones:

    DBCC SQLPERF (LOGSPACE)
    Y a continuación el procedimiento
    exec sp_spaceused
    Y nos cuentas...
    jueves, 26 de noviembre de 2009 14:36
  • Si, es muy extraño que no cuadre. Te envio la información que solicitaste:


    Además agrego otro excel con información de la fragmentación de los indices, veo que hay varios indices que estan bastante fragmentados, pero por algun motivo no son faciles de defragmentar. Ejecuté un script que realiza un rebuild de todos los indices de todas las tablas, sin embargo siguen existiendo muchos indices con alto porcentaje de fragmentación. El excel siguiente esta tomado, luego de haber hecho el rebuild de todos los indices:


    Al final dejo el script que ejecute para la defragmentación.

    Saludos



    DECLARE @Database VARCHAR(255)   
    DECLARE @Table VARCHAR(255)  
    DECLARE @cmd NVARCHAR(500)  
    DECLARE @fillfactor INT 

    SET @fillfactor = 90 

    DECLARE DatabaseCursor CURSOR FOR  
    SELECT name FROM master.dbo.sysdatabases   
    WHERE name IN ('armco')   
    ORDER BY 1  

    OPEN DatabaseCursor  

    FETCH NEXT FROM DatabaseCursor INTO @Database  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  

       SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName   
                        FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   

       -- create table cursor  
       EXEC (@cmd)  
       OPEN TableCursor   

       FETCH NEXT FROM TableCursor INTO @Table   
       WHILE @@FETCH_STATUS = 0   
       BEGIN   

           -- SQL 2000 command  
           --DBCC DBREINDEX(@Table,' ',@fillfactor)   
             
           -- SQL 2005 command  
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'  
           EXEC (@cmd)  

           FETCH NEXT FROM TableCursor INTO @Table   
       END   

       CLOSE TableCursor   
       DEALLOCATE TableCursor  

       FETCH NEXT FROM DatabaseCursor INTO @Database  
    END  
    CLOSE DatabaseCursor   
    DEALLOCATE DatabaseCursor 
    jueves, 26 de noviembre de 2009 15:00
  • ¿No tendrás vistas indexadas? ¿Puedes postear lo que muestra el informe "Disk Usage" de esa base de datos?

    Índices pequeños van a seguir apareciendo con una alta fragmentación aunque les reconstruyas, es algo normal. De hecho, lo suyo sería filtrar por índices que realmente van a verse beneficiados por la reconstrucción o reorganización para hacer el proceso de reindexación más ágil y eficiente. Es una de las razones por las que me gusta el script de mantenimiento de bases de datos de Ola Hallengren que publiqué en otra respuesta hace unos días (http://ola.hallengren.com/).

    jueves, 26 de noviembre de 2009 15:06
  • Posteo el informe disk usage a continuación, pero quiero hacer una aclaración importante. Este informe se saco sobre una base de prueba que no es la misma que la de producción de la cual saque las medidas anteriores. El problema es que las bases son SQL2005 con modo compatibilidad 80, y cuando intente sacar el reporte dio error indicando que no se puede por estar en modo compatibilidad 80. Como no puedo cambiar el modo de compatibilidad de la base de producción, lo cambie en la de prueba y saque el informe sobre esa. La base de prueba es una copia de la de producción sacada hace unos dias y tiene el mismo problema, ocupa 35Gb aprox.


    Lo que me resulto interesante del informe es que quedaron logueados algunos momentos en los que la base creció abruptamente. Pero todavia no entiendo el motivo de dicho creciemiento.

    Saludos.-


    jueves, 26 de noviembre de 2009 16:00
  • Hola.

    Según el Excel, las tablas que se llevan la mayoría del espacio son de Service Broker:

    sysdercv
    Partición nº Nº de registros
    1 31.580.971
    sysdesend
    Partición nº Nº de registros
    1 31.580.971
    sysconvgroup
    Partición nº Nº de registros
    1 31.558.548

    Te sugiero que revises este hilo, en el que se relata un caso parecido al tuyo: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/03180f45-cd83-4913-8f0e-3d8306f01f06


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    jueves, 26 de noviembre de 2009 18:33
    Moderador
  • Muchas gracias qwalgrande y a los demás colaboradores del foro!! Ese era el problema!!

    Les comento que nos dio muchos problemas el eliminar las conversations, al ser una tabla tan grande los scripts no terminaban nunca de ejecutar, y se eliminaban a un ritmo muy lento (15 mil por hora y la base tenia 31 millones). La forma que encontramos de achicar la base fue, traspasar la estructura de la tabla a una base nueva y luego exportar los datos.

    Saludos.-
    viernes, 27 de noviembre de 2009 18:00