SQL Server Performance

ARCHIVO LOG QUE CRECE BASTANTE Y PROCESOS ....

Discussion in 'Preguntas sobre SQL Server en Español.' started by aliciacarrillo, Sep 6, 2005.

  1. aliciacarrillo New Member

    [<img src='/community/emoticons/emotion-6.gif' alt=':(' />] Hola, tengo una duda, no programo en SQL solo manejo BDD por Enterprise Manager<br />el sistema que tengo ahorita.... corre un proceso de "Cálculo de Nómina" que <br />siempre se queda colgado, es decir, como si estuviera trabajando pero si reviso el<br />profiler no está haciendo nada.<br /><br />Crece de tamaño el archivo LOG y tengo que estar dando truncate, shrink, bajar la <br />consola de componentes, bajar los servicios de sql, reinicar o apagra el equipo, etc.<br /><br />Es hasta despuès de 5 o más veces cuando ya responde bien el cálculo.<br /><br />Lo que he monitoreado es que el archivo LOG crece todos los dìas bastante, ejemplo <br />si la BDD mide 1.8 GB el log llega a crecer en 4 o 5 dìas mas de 1 GB..hasta que hago todo<br />el rpocedimiento que les menciono.<br /><br />En los planes de MTTO... está generando un error SQL, esto es en las carpeta LOG de SQL<br /><br />El equipo tiene más de 15 GB en DD y memoria de 1 GB ...<br /><br />Alguien me puede ayudar por favor<br /><br />Muchas Gracias
  2. amayral New Member

    Hola Alicia,

    Nos tendrías que dar más pistas. Por ejemplo, dices que: "corre un proceso de cálculo de nómina" y en cambio dices que "no programas en SQL". No lo entiendo, este proceso, corre en a través de una aplicación o en un stored procedure ?

  3. Luis Martin Moderator

    Qué tipo de modelo de recuperación tiene la base? (Administrador Corporativo, Bases de Datos, botón derecho sobre tu bases, propiedades, solapa opciones),

    Si el modelo es FULL, qué tipo de backup hacen de la base?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  4. aliciacarrillo New Member

    Hola, mira...digo que corre un proceso de nómina... <img src='/community/emoticons/emotion-1.gif' alt=':)' /> por que el sistema es de Nómina para la empresa, pero es un SP lo que ejecuta. <br /><br />Revisé la pestaña que me indican de opciones y tengo:<br /><br />Restrict Access está desactivado<br /><br />Read Only está desactivado<br /><br />Recovery Model: Full<br /><br />Settings: activado tengo las casillas AUTO UPDATE STATICS, TOM PAGE DETECTION, AUTO CLOSE, AUTO SHRINK, AUTO CREATE STATICS.<br /><br />EN COMPATIBILITY TENGO LA OPCIÓN DATABASE COMPATIBILITY LEVEL 80<br />---------------------------------------------------------------------------------------<br />---------------------------------------------------------------------------------------<br />y ESTOS SON LOS ERRORES QUE ME MUESTRA EL ARCHIVO TXT<br /><br />Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'PARLASA02' as 'CIPSAPARLASA02$' (trusted)<br />Starting maintenance plan 'Mantenimiento a Lobo_RH' on 8/8/2005 1:00:05 AM<br />[1] Database Lobo_RH: Check Data and Index Linkage...<br />[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8968: [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: SinglePage page (0:131920) (object ID 0, index ID 0) is out of the range of this database.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Extent (0:131920) object ID 1700544178, index ID 0 is beyond the range of this database.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: IAM page (1:11811) (object ID 1700544178, index ID 0) is out of the range of this database.<br />[Microsoft][ODBC SQL Server Driver][SQL Server] The repair level on the DBCC statement caused this repair to be bypassed.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.<br />[Microsoft][ODBC SQL Server Driver][SQL Server] The repair level on the DBCC statement caused this repair to be bypassed.<br />[Microsoft][ODBC SQL Server Driver][SQL Server] The repair level on the DBCC statement caused this repair to be bypassed.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation errors and 0 consistency errors in table '(Object ID 170054417<img src='/community/emoticons/emotion-11.gif' alt='8)' />' (object ID 170054417<img src='/community/emoticons/emotion-11.gif' alt='8)' />.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 3 allocation errors and 0 consistency errors in database 'Lobo_RH'.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Lobo_RH repair_fast).<br /><br /> The following errors were found:<br /><br />[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: SinglePage page (0:131920) (object ID 0, index ID 0) is out of the range of this database.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Extent (0:131920) object ID 1700544178, index ID 0 is beyond the range of this database.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: IAM page (1:11811) (object ID 1700544178, index ID 0) is out of the range of this database.<br />[Microsoft][ODBC SQL Server Driver][SQL Server] The repair level on the DBCC statement caused this repair to be bypassed.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.<br />[Microsoft][ODBC SQL Server Driver][SQL Server] The repair level on the DBCC statement caused this repair to be bypassed.<br />[Microsoft][ODBC SQL Server Driver][SQL Server] The repair level on the DBCC statement caused this repair to be bypassed.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 2 allocation errors and 0 consistency errors in table '(Object ID 170054417<img src='/community/emoticons/emotion-11.gif' alt='8)' />' (object ID 170054417<img src='/community/emoticons/emotion-11.gif' alt='8)' />.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 3 allocation errors and 0 consistency errors in database 'Lobo_RH'.<br />[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Lobo_RH repair_fast).<br /> ** Execution Time: 0 hrs, 2 mins, 52 secs **<br /><br />Deleting old text reports... 1 file(s) deleted.<br /><br />End of maintenance plan 'Mantenimiento a Lobo_RH' on 8/8/2005 1:02:58 AM<br />SQLMAINT.EXE Process Exit Code: 1 (Failed)<br /><br />---------------------------------------------------<br /><br /><br /><br /><br />
  5. Luis Martin Moderator

    Ok. Al ser Full el modelo de recuperación es de suponer que tu sistema de backup es Diferencial y/o transaccional.

    Ahora si el backup se realiza una vez por día en forma completa, no tiene sentido que el modelo sea FULL, porque hace crecer el log y solo se reduce con backps del log, lo cual sería el modelo de backup antes mencionado.

    En conclusión, si realizas un backup diario, cambia el modelo a simple y se acabará el problema de crecimiento del log.

    Para arreglar los problemas que muestra el texto tendrás que ejecutar DBCC CHECKDB con las opciones necesarias (consulta el libro on line) para tratar de solucionarlo.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  6. aliciacarrillo New Member

    Hola, gracias por tu respuesta, voy a hacer el cambio de FULL....a la otra opción.

    respecto a la consulta de Libro on line, es en esta misma página?...me puedes indicar por favor por que opción entro?

    Muchas Gracias, te estaré informando sobre como me vá con esto

    Saludos
  7. Luis Martin Moderator

    DBCC CHECKDB
    Checks the allocation and structural integrity of all the objects in the specified database.

    Syntax
    DBCC CHECKDB
    ( 'database_name'
    [ , NOINDEX
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
    ) [ WITH { [ ALL_ERRORMSGS ]
    [ , [ NO_INFOMSGS ] ]
    [ , [ TABLOCK ] ]
    [ , [ ESTIMATEONLY ] ]
    [ , [ PHYSICAL_ONLY ] ]
    }
    ]

    Arguments
    'database_name'

    Is the database for which to check all object allocation and structural integrity. If not specified, the default is the current database. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.

    NOINDEX

    Specifies that nonclustered indexes for nonsystem tables should not be checked. NOINDEX decreases the overall execution time because it does not check nonclustered indexes for user-defined tables. NOINDEX has no effect on system tables, because DBCC CHECKDB always checks all system table indexes.

    REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REBUILD

    Specifies that DBCC CHECKDB repair the found errors. The given database_name must be in single-user mode to use a repair option and can be one of the following.

    Value Description
    REPAIR_ALLOW_DATA_LOSS Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database.
    REPAIR_FAST Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.
    REPAIR_REBUILD Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.


    WITH

    Specifies options on the number of error messages returned, locks obtained, or estimating tempdb requirements.

    ALL_ERRORMSGS

    Displays an unlimited number of errors per object. If ALL_ERRORMSGS is not specified, displays up to 200 error messages for each object. Error messages are sorted by object ID, except for those messages generated from tempdb.

    NO_INFOMSGS

    Suppresses all informational messages (Severity 10) and the report of space used.

    TABLOCK

    Causes DBCC CHECKDB to obtain shared table locks. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.

    ESTIMATE ONLY

    Displays the estimated amount of tempdb space needed to run DBCC CHECKDB with all of the other specified options. The check is not performed.

    PHYSICAL_ONLY

    Limits the checking to the integrity of the physical structure of the page and record headers, and to the consistency between the pages' object ID and index ID and the allocation structures. Designed to provide a low overhead check of the physical consistency of the database, this check also detects torn pages and common hardware failures that can compromise a user's data. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any of the repair options.

    Remarks
    DBCC CHECKDB performs a physical consistency check on indexed views. The NOINDEX option, used only for backward compatibility, also applies to any secondary indexes on indexed views.

    DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to repair these errors. However, to ensure that all errors, including allocation errors, are properly repaired, execute DBCC CHECKDB with a repair option rather than DBCC CHECKALLOC with a repair option.

    DBCC CHECKDB validates the integrity of everything in a database. There is no need to run DBCC CHECKALLOC or DBCC CHECKTABLE if DBCC CHECKDB either is currently or has been recently executed.

    DBCC CHECKDB performs the same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE statement were executed for each table in the database.

    DBCC CHECKDB does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data. The schema locks acquired will prevent the user from getting an exclusive table lock required to build a clustered index, drop any index, or truncate the table.

    The DBCC statement collects information, and then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.

    When the TABLOCK option is specified, DBCC CHECKDB acquires shared table locks. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data. The TABLOCK option will not block the truncation of the log and will allow the command to run faster.

    DBCC CHECKDB checks the linkages and sizes of text, ntext, and image pages for each table, and the allocation of all the pages in the database.

    For each table in the database, DBCC CHECKDB checks that:

    Index and data pages are correctly linked.


    Indexes are in their proper sort order.


    Pointers are consistent.


    The data on each page is reasonable.


    Page offsets are reasonable.
    Errors indicate potential problems in the database and should be corrected immediately.

    By default, DBCC CHECKDB performs parallel checking of objects. The degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. Use the sp_configure system stored procedure to restrict the maximum number of processors available for DBCC checking. For more information, see max degree of parallelism Option.

    Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags.

    Result Sets
    Whether or not any options (except for the NO_INFOMSGS or NOINDEX options) are specified, DBCC CHECKDB returns this result set for the current database, if no database is specified (values may vary):

    DBCC results for 'master'.
    DBCC results for 'sysobjects'.
    There are 862 rows in 13 pages for object 'sysobjects'.
    DBCC results for 'sysindexes'.
    There are 80 rows in 3 pages for object 'sysindexes'.
    '...'
    DBCC results for 'spt_provider_types'.
    There are 23 rows in 1 pages for object 'spt_provider_types'.
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    IF the NO_INFOMSGS option is specified, DBCC CHECKDB returns this result set (message):

    The command(s) completed successfully.

    DBCC CHECKDB returns this result set when the ESTIMATEONLY option is specified.

    Estimated TEMPDB space needed for CHECKALLOC (KB)
    -------------------------------------------------
    13

    (1 row(s) affected)

    Estimated TEMPDB space needed for CHECKTABLES (KB)
    --------------------------------------------------
    57

    (1 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Permissions
    DBCC CHECKDB permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.

    Examples
    A. Check both the current and the pubs database
    This example executes DBCC CHECKDB for the current database and for the pubs database.

    -- Check the current database.
    DBCC CHECKDB
    GO
    -- Check the pubs database without nonclustered indexes.
    DBCC CHECKDB ('pubs', NOINDEX)
    GO

    B. Check the current database, suppressing informational messages
    This example checks the current database and suppresses all informational messages.

    DBCC CHECKDB WITH NO_INFOMSGS
    GO



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  8. aliciacarrillo New Member

    Muchas Gracias, voy a revisar las instrucciones que me envìas y cualquier cosa vuelvo a escribir

    Saludos

Share This Page