SQL Server Performance

Hardware problem?

Discussion in 'Performance Tuning for Hardware Configurations' started by Luis Martin, Jul 1, 2007.

  1. Luis Martin Moderator

    Situation:

    HP Proliant, 2xXeon, 2GB RAM, 4x72GB 15000RPM with hardware mirror, so 2 logical disks.

    Plenty of disk space.

    Windows 2003 sp2 (Spanish) , SQL 2000 SP4 ( Spanish.)

    This server is running since November 2006 without problems.

    Now, this maintenance plan:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8BF64C9C-F4B3-44D3-AC60-659BE4ED6851 -Rpt "C:program FilesMicrosoft SQL ServerMSSQLLOGDB Maintenance Plan10.txt" -WriteHistory -RebldIdx 10 '

    Also is running since November 2006 without problem.

    Now this job hangs the server. The only way is to reboot the server.

    In event viewer the information is: Unexpected server stop.

    In log I can see:


    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'LUISMARTINDBA' as 'NT AUTHORITYSYSTEM' (trusted)
    Starting maintenance plan 'DB Maintenance Plan1' on 6/30/2007 9:49:47 PM
    [1] Database Diesel99A: Index Rebuild (leaving 10%% free space)...

    Rebuilding indexes for table 'ACUMULADORES'
    Rebuilding indexes for table 'ACUMULADORESDETALLE'
    Rebui


    Some times after more tables.

    My client calls HP Service and they update the BIOS.

    No luck. The job hangs the server.



    Now that is what I did:

    Backup the database and restore in my server.

    Pentium 2.8, 1GB RAM, IDE disks.

    Windows 2003, SP2 (English), SQL 2000 SP4 (English) hot fix 2187.

    Running the same job, fail but don#%92t hang the server.

    The Event viewer show:
    Error: 0, Severity: 19, State: 0.
    SqldumpExecptionHandler: Process 56 general fatal exception
    c00000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    In log:


    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'LUISMARTINDBA' as 'NT AUTHORITYSYSTEM' (trusted)
    Starting maintenance plan 'DB Maintenance Plan1' on 7/1/2007 3:23:28 PM
    [1] Database Diesel99A: Index Rebuild (leaving 10%% free space)...

    Rebuilding indexes for table 'ACUMULADORES'
    Rebuilding indexes for table 'ACUMULADORESDETALLE'
    Rebuilding indexes for table 'APLICACIONES'
    Rebuilding indexes for table 'ARMADO'
    Rebuilding indexes for table 'ARMADOMVS'
    Rebuilding indexes for table 'ATRIBUTOS'
    Rebuilding indexes for table 'ATRIBUTOSVAL'
    Rebuilding indexes for table 'AUTATRIBVALORES'
    Rebuilding indexes for table 'AWItemsAcumHistoricos'
    Rebuilding indexes for table 'AWLocks'
    Rebuilding indexes for table 'AWParametros'
    Rebuilding indexes for table 'AWRoles'
    Rebuilding indexes for table 'AWRubrosAgrup'
    Rebuilding indexes for table 'AWSpool'
    Rebuilding indexes for table 'AWUsuarios'
    Rebuilding indexes for table 'BANCOS'
    Rebuilding indexes for table 'BANCOSCOD'
    Rebuilding indexes for table 'BANCOSIB'
    Rebuilding indexes for table 'BANCOSREG'
    Rebuilding indexes for table 'BONIFDEF'
    Rebuilding indexes for table 'BONIFDET'
    Rebuilding indexes for table 'BONIFGEN'
    Rebuilding indexes for table 'BRTABLAS'
    Rebuilding indexes for table 'BRTABLASCOLS'
    Rebuilding indexes for table 'BRTABLASJOINS'
    Rebuilding indexes for table 'CAJAS'
    Rebuilding indexes for table 'CAJASCUENTAS'
    Rebuilding indexes for table 'CAJASREG'
    Rebuilding indexes for table 'CAMIONES'
    Rebuilding indexes for table 'CENTROSAP'
    Rebuilding indexes for table 'CENTROSAPGRUPO'
    Rebuilding indexes for table 'CHEQUERAS'
    Rebuilding indexes for table 'CHEQUES'
    Rebuilding indexes for table 'CMP1361'
    Rebuilding indexes for table 'CMPASOCIADOS'
    Rebuilding indexes for table 'CMPTABAUTORIZACION'
    Rebuilding indexes for table 'CMPTABLASAUT'
    Rebuilding indexes for table 'CMPTABLASAUTCONDI'
    Rebuilding indexes for table 'CMPTABLASAUTDEP'
    Rebuilding indexes for table 'CMPTABLASAUTGRUP'
    Rebuilding indexes for table 'CMPTALON'
    Rebuilding indexes for table 'CONCEPTOS'
    Rebuilding indexes for table 'CONCEPTOSPOSCNT'
    Rebuilding indexes for table 'CONDICIONES'
    Rebuilding indexes for table 'CONSULTAMODELOS'
    Rebuilding indexes for table 'CONSULTAS_CONTABLES'
    Rebuilding indexes for table 'CTACTES'
    Rebuilding indexes for table 'CTACTESATRIB'
    Rebuilding indexes for table 'CTACTESCMPS'
    Rebuilding indexes for table 'CTACTESCOMICOB'
    Rebuilding indexes for table 'CTACTESCOMIVEN'
    Rebuilding indexes for table 'CTACTESCOMIVENCOB'
    Rebuilding indexes for table 'CTACTESCONDICIONES'
    Rebuilding indexes for table 'CTACTESCONT'
    Rebuilding indexes for table 'CTACTESCUEN'
    Rebuilding indexes for table 'CTACTESEMP'
    Rebuilding indexes for table 'CTACTESIB'
    Rebuilding indexes for table 'CTACTESOBS'
    Rebuilding indexes for table 'CTACTESPORIB'
    Rebuilding indexes for table 'CUADROSDEF'
    Rebuilding indexes for table 'CUENTAS'
    Rebuilding indexes for table 'CUENTASATRIB'
    Rebuilding indexes for table 'CUENTASEMP'
    Rebuilding indexes for table 'CUENTASSALDOS'
    Rebuilding indexes for table 'CUENTASVISTAS'
    Rebuilding indexes for table 'CUPONES'
    Rebuilding indexes for table 'CURVASTALLES'
    Rebuilding indexes for table 'CURVASTALLESDET'
    Rebuilding indexes for table 'DATANETMOVS'
    Rebuilding indexes for table 'DEFLISPRECIOS'
    Rebuilding indexes for table 'DEPARTAMENTOS'
    Rebuilding indexes for table 'DEPOSITOS'
    Rebuilding indexes for table 'DEPTOSCATEGORIAS'
    Rebuilding indexes for table 'DESPACHOS'
    Rebuilding indexes for table 'DETALLEDEFPRECIOS'
    Rebuilding indexes for table 'DETGENLISFACTOR'
    Rebuilding indexes for table 'DETGENLISTAS'
    Rebuilding indexes for table 'DETGENLISTASDIN'
    Rebuilding indexes for table 'EMPRESAS'
    Rebuilding indexes for table 'FORMULAS'
    Rebuilding indexes for table 'FORMULASLIN'
    Rebuilding indexes for table 'GENLISTAPRECIOS'
    Rebuilding indexes for table 'HOJA'
    Rebuilding indexes for table 'HOJATAREAS'
    Rebuilding indexes for table 'icrREMITOS'
    Rebuilding indexes for table 'IDOBJETOS'
    Rebuilding indexes for table 'IMPUESTOS'
    Rebuilding indexes for table 'INDICES'
    Rebuilding indexes for table 'INVENTARIOS'
    Rebuilding indexes for table 'INVENTLIN'
    Rebuilding indexes for table 'ITEMS'
    Rebuilding indexes for table 'ITEMSACUM'
    Rebuilding indexes for table 'ITEMSALTERNATIVOS'
    Rebuilding indexes for table 'ITEMSATRIB'
    Rebuilding indexes for table 'ITEMSCMPS'
    Rebuilding indexes for table 'ITEMSDEPTOS'
    Rebuilding indexes for table 'ITEMSEMP'
    Rebuilding indexes for table 'ITEMSGRUPOS'
    Rebuilding indexes for table 'ITEMSGRUPUSO'
    Rebuilding indexes for table 'ITEMSMEDCOL'
    Rebuilding indexes for table 'ITEMSPOSCNT'
    Rebuilding indexes for table 'ITEMSUBICACIONES'
    Rebuilding indexes for table 'LINASIENTOS'
    [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 0: [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.


    ** Execution Time: 0 hrs, 10 mins, 12 secs **

    End of maintenance plan 'DB Maintenance Plan1' on 7/1/2007 3:33:41 PM
    SQLMAINT.EXE Process Exit Code: 1 (Failed)


    Same thing than HP Server (except with more information), but running several times, the step stop in different tables.


    Now actions I did (some really stupid):

    1)Uninstall Windows 2003 SP2. (No change at all).
    2)Using QA, I run:
    a.DBCC CHECKALLOC (No problem)
    b.DBCC CHECKDB (No problem)
    c.Run the following script:


    DECLARE @bErrors as bit

    BEGIN TRANSACTION
    SET @bErrors = 0

    DBCC DBREINDEX (Aplicaciones, '',80)

    IF( @bErrors = 0 )
    COMMIT TRANSACTION
    ELSE
    ROLLBACK TRANSACTION

    BEGIN TRANSACTION
    SET @bErrors = 0

    DBCC DBREINDEX (CmpAsociados, '',80)

    IF( @bErrors = 0 )
    COMMIT TRANSACTION
    ELSE
    ROLLBACK TRANSACTION

    BEGIN TRANSACTION
    SET @bErrors = 0

    DBCC DBREINDEX (Linasientos, '',80)

    IF( @bErrors = 0 )
    COMMIT TRANSACTION
    ELSE
    ROLLBACK TRANSACTION

    BEGIN TRANSACTION
    SET @bErrors = 0

    DBCC DBREINDEX (CtaCtes, '',80)

    IF( @bErrors = 0 )
    COMMIT TRANSACTION
    ELSE
    ROLLBACK TRANSACTION

    BEGIN TRANSACTION
    SET @bErrors = 0

    DBCC DBREINDEX (MvsItems, '',80)

    IF( @bErrors = 0 )
    COMMIT TRANSACTION
    ELSE
    ROLLBACK TRANSACTION


    Same error. Sometimes in the first table, sometimes in others.


    I read all in Microsoft, Google and in our forum.

    I really don#%92t care about my own server. My problem is with HP Server.

    Satya said in similar problem: Call MS.

    Any other idea?

    BTW: I also try with other database. Same thing.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


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



  2. Chappy New Member

    You have torn page detection enabled ?

    Depending on how big the database is, I think I would try to transfer the database from one server to another using transfer objects wizard. If that succeeds then run the same checks on the new database.

    I think you are inevitably looking at a call to MS though..
  3. satya Moderator

    Before going to PSS you can run SQLDiag and take all kinds of traces & logs for assesment within PSS. Also check what has been changed since last good completion of this particular job, say hotfix or any auto-updates to Windows too.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. Luis Martin Moderator

    The solution was: uninstall windows 2003 sp2.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


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



  5. Chappy New Member

    Thats quite worrying :|
  6. satya Moderator

    Is it SP2 for Windows?
    Absolutely worrying, we were able to resolve by reapplying the SP for SQL in this case and a thorough checkout on hardware found the resolution.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. Luis Martin Moderator

    Yes it was SP2 for Windows.

    Believed or not!



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


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



Share This Page