SQL Server shutdown error

  • kwitzell

    SSCrazy

    Points: 2462

    We have a SQL 2000 SP3a database on a dual processor non-clustered server with 4 GB of ram. Just today the application analyst told me that SQL server had shut itself down. I had never heard of this and upon checking the error logs found the following:

    2007-05-02 15:29:25.04 spid201   Stack Signature for the dump is 0xD1F283AA

    2007-05-02 15:29:26.87 spid201   Error: 3314, Severity: 21, State: 4

    2007-05-02 15:29:26.87 spid201   Error while undoing logged operation in database 'tempdb'.  Error at log record ID (47:15496:339)..

    2007-05-02 15:29:26.87 spid201   Error: 3449, Severity: 21, State: 1

    2007-05-02 15:29:26.87 spid201   An error has occurred that requires SQL Server to shut down  so that recovery can be performed on database ID .. 2

    2007-05-02 15:29:24.29 spid201   Stack Signature for the dump is 0xC07D52BA

    2007-05-02 15:29:24.34 spid201   Error: 3448, Severity: 21, State: 1

    2007-05-02 15:29:24.34 spid201   Could not undo log record (47:15496:339), for transaction ID (0:87990),  on page (3:21958),database 'tempdb' (database ID 2). Page information: LSN = (43:24736:1), type = 7. Log information: OpCode = 2, context 1..

    2007-05-02 15:29:24.34 spid201   ex_raise2: Exception raised, major=34, minor=48, severity=25,  attempting to create symptom dump

    2007-05-02 15:29:24.34 spid201   Using 'dbghelp.dll' version '4.0.5'

    *Dump thread - spid = 201, PSS = 0x38d15808, EC = 0x38d15b38

    *

    * User initiated stack dump.  This is not a server exception dump.

    *

    *Stack Dump being sent to g:\PerformanceData\MSSQL\log\SQLDump0221.txt

    * *******************************************************************************

    *

    * BEGIN STACK DUMP:

    *   05/02/07 15:29:24 spid 201

    *

    * ex_raise2: Exception raised, major=34, minor=48, severity=25

    The system is back up and running with no apparent problems, but this is the second time this has occured in the last couple of days. Has anyone ever seen anything like this? If so what is the problem and how did you correct it? Any help will be greatlly appreciated! Thanks

    Ken

  • Randolph West

    Default port

    Points: 1458

    I'm just guessing here, but it sounds like the transaction log is getting corrupted, or you're running out of space on the drive where tempdb is stored. Another suggestion is to check the growth limits on tempdb and its log file.

    Check the Event Viewer for other errors in the Application and System logs.

  • EdVassie

    SSC Guru

    Points: 60266

    Definitely check that tempdb has enough space to grow.

    If you are sure that tempdb can get all the space it needs, then I would report this problem to Microsoft.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • kwitzell

    SSCrazy

    Points: 2462

    Thanks for the feed back, the tempdb is on a drive with about 1.5 TB of freespace, so I don't think the disk is filling, I will give Microsoft a call. Thanks again!

    Ken

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715401

    I agree with the call PSS advice. If it's not something simple like space, which it doesn't look like.

    Might try shutting down SQL and defragging the OS drive as well. It might move the tempdb files and mark bad sectors for you.

  • kwitzell

    SSCrazy

    Points: 2462

    Thanks Steve,

    We had been tossing around the idea of a hardware issue, this might be something to look at. While looking at the problem, I found that someone had added another tempdb file on another disk. So we now have two. The disk the second file is on has 45GB free. If one file fills will SQL Server switch to another avaliable file? Just wondering..

    Ken

  • Hans Lindgren

    SSChampion

    Points: 10494

    I would say turn off any AV on your system. You could look in your dumpfile if this is wrecking havoc to your SQL Server process space. (Is likely depending on your AV vendor)

     

    Hanslindgren

  • kwitzell

    SSCrazy

    Points: 2462

    Ok thanks for the suggestion!

  • Kobus-467674

    SSC Enthusiast

    Points: 183

    Hi Ken,

    Did you manage to solve this problem? What is the solution?

    I have the same problems, but mine started after a hard drive failure on my RAID 5, a corrupted System Registry Hive and a few other problems in between.

    When I finally had everything restored/fixed up so that I can boot into the server, everything was working besides SQL.

    In the mean time I'll try the chkdsk again and the defrag option, as I had some problems with the file system during this whole process.

    Is there a way of restoring another temp db using another sql server instance to replace the one on the server that is currently experiencing problems?

    Running Windows 2003 R2, SP2.

    SQL Server 2000 MSDE SP4 (Version: 8.00.2039)

    Any help would be appreciated.

    Thank you.

    Kobus

  • Kobus-467674

    SSC Enthusiast

    Points: 183

    An update:

    Neither the Defrag or chkdsk produced any results.

    If only I could run the database consistency checks somehow to make sure that the tempdb is OK.

    Came across this error as well. Could this have an influence, especially if the Local System Account is used for the service account?

    Event Type: Error

    Event Source: Userenv

    Event Category: None

    Event ID: 1508

    Date: 2007/07/21

    Time: 17:19:21

    User: NT AUTHORITY\SYSTEM

    Computer: ServerName

    Description:

    Windows was unable to load the registry. This is often caused by insufficient memory or insufficient security rights.

    DETAIL - The configuration registry database is corrupt. for C:\Documents and Settings\LocalService\ntuser.dat

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Event Type: Error

    Event Source: Userenv

    Event Category: None

    Event ID: 1510

    Date: 2007/07/21

    Time: 17:19:21

    User: NT AUTHORITY\LOCAL SERVICE

    Computer: ServerName

    Description:

    Windows cannot load your profile because it may be corrupted. Contact your administrator.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    I changed the logon account credentials, and SQL started up and shut down again as before.

    As you can see, I am beginnig to become desperate.

    If all else fails, I guess I'll have to re-install SQL?

  • Kobus-467674

    SSC Enthusiast

    Points: 183

    I managed to restore the NTUser.dat file from die Windows\Repair directory (booted into safe mode, copied the file to the LocalService profile and rebooted the machine) which solved the last (at hindsight, it seems unrelated) problem mentioned.

    Then I got SQL to work again the hard way....

    I copied all my database files from the Data directory to make sure I've got a backup of them if they are deleted.

    So, I uninstalled MSDE, re-installed it again and reattached the original DB's. SQL is working fine!!

    Remember that you will loose your DB Maintenance plans etc, security logins and rights on the DB's when uninstalling and re-installing MSDE again.

    I'm trying to figure out if there is a way of restoring them from the backups I have. Setting these all up won't be taking too long as I've exported the DB Maintenance plans to sql scripts. And luckily, I don't have that many DB's to re-configure access and security on them.

  • kwitzell

    SSCrazy

    Points: 2462

    Kobus,

    Microsoft suggested we look into a hardware problem. We ran diagnostics on our disks and found one maybe two to be 'going bad' after replacing the disks and adding the extra Temp db file, we have had no further recurrence of the problem. So I guess the lesson here is, even though a disk may not inidcate failure, that does not mean it is not faulty in some way!

    Ken

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply