May 2, 2007 at 3:21 pm
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
May 2, 2007 at 11:14 pm
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.
May 3, 2007 at 3:11 am
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
May 3, 2007 at 8:26 am
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
May 3, 2007 at 8:40 am
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.
May 3, 2007 at 10:01 am
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
May 3, 2007 at 10:17 am
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
May 3, 2007 at 11:48 am
Ok thanks for the suggestion!
July 21, 2007 at 8:30 am
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
July 21, 2007 at 10:16 am
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?
July 22, 2007 at 2:32 am
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.
July 23, 2007 at 9:00 am
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply