February 6, 2008 at 9:32 am
Hello,
My company has a SQL server (x64) that has been crashing once a month since last October. The crashes don't happen on the same day, but they do happen at the same time and they seem to co-inside with the system database backup job (at 00:30). We normally get a message in the event log the afternoon before:
Could not find an entry for table or index with partition ID 489800837038080 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
.....and then about 10 seconds after the system backup job begins, we get:
The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
......and then it goes:
SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
......It then restarts itself, rolls back/forward any loose transactions and runs a CHECKDB on all the databases.
The closest thing I have found is knowledgebase article 916086 which says it should be fixed with the latest service pack - but we have all the latest service packs on it. It also suggests a workaround of disabling auto create/update statistics on the tempdb, but we have done this as well to no avail :crazy:
Has anybody else seen this and found a solution?
Thanks,
Stu
February 6, 2008 at 10:09 am
Have you run dbcc checkdb on your system databases? (not tempdb) and if so what information did you get?
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 6, 2008 at 11:52 am
Din't you find anything with the error logs. they should have said something more than what youhave said here.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 6, 2008 at 12:39 pm
dbid 2 = msdb
Perform DBCC checkdatabase for msdb !
You can even post the results within this thread. :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 6, 2008 at 1:34 pm
SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
Run one on Master also (dbid = 1)
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 6, 2008 at 8:26 pm
Actually if you have possible corruption on your system database, I wouldn't mess around. We might get you past it, but something else could happen.
Call PSS and work through this with them.
February 12, 2008 at 3:54 am
SQL Server ran DBCC checkDB on all the databases when it came back up, but reported no problems. Error log posted below:
2008-02-06 03:12:12.48 Server (c) 2005 Microsoft Corporation.
2008-02-06 03:12:12.48 Server All rights reserved.
2008-02-06 03:12:12.48 Server Server process ID is 2376.
2008-02-06 03:12:12.48 Server Authentication mode is MIXED.
2008-02-06 03:12:12.48 Server Logging SQL Server messages in file 'G:\MSSQL\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-02-06 03:12:12.48 Server This instance of SQL Server last reported using a process ID of 8696 at 06/02/2008 00:30:09 (local) 06/02/2008 00:30:09 (UTC). This is an informational message only; no user action is required.
2008-02-06 03:12:12.48 Server Registry startup parameters:
2008-02-06 03:12:12.48 Server -d G:\MSSQL\MSSQL.1\MSSQL\DATA\master.mdf
2008-02-06 03:12:12.48 Server -e G:\MSSQL\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-02-06 03:12:12.48 Server -l G:\MSSQL\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-02-06 03:12:12.50 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2008-02-06 03:12:12.50 Server Detected 8 CPUs. This is an informational message; no user action is required.
2008-02-06 03:12:12.81 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2008-02-06 03:12:12.90 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-02-06 03:12:12.98 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 37820, committed (KB): 105592, memory utilization: 35%.
2008-02-06 03:12:13.93 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-02-06 03:12:13.93 Server Database mirroring has been enabled on this instance of SQL Server.
2008-02-06 03:12:13.98 spid3s Starting up database 'master'.
2008-02-06 03:12:14.04 spid3s 5 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2008-02-06 03:12:14.07 spid3s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2008-02-06 03:12:14.07 spid3s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2008-02-06 03:12:14.12 spid3s CHECKDB for database 'master' finished without errors on 2007-10-12 15:53:14.257 (local time). This is an informational message only; no user action is required.
2008-02-06 03:12:14.15 spid3s SQL Trace ID 1 was started by login "sa".
2008-02-06 03:12:14.17 spid3s Starting up database 'mssqlsystemresource'.
2008-02-06 03:12:14.18 spid3s The resource database build version is 9.00.3152. This is an informational message only. No user action is required.
2008-02-06 03:12:14.40 spid3s Server name is 'OHG20'. This is an informational message only. No user action is required.
2008-02-06 03:12:14.40 spid9s Starting up database 'model'.
2008-02-06 03:12:14.68 spid9s 1 transactions rolled forward in database 'model' (3). This is an informational message only. No user action is required.
2008-02-06 03:12:14.70 spid9s 0 transactions rolled back in database 'model' (3). This is an informational message only. No user action is required.
2008-02-06 03:12:14.70 spid9s Recovery is writing a checkpoint in database 'model' (3). This is an informational message only. No user action is required.
2008-02-06 03:12:14.71 spid9s CHECKDB for database 'model' finished without errors on 2007-12-03 15:47:33.460 (local time). This is an informational message only; no user action is required.
2008-02-06 03:12:14.71 spid9s Clearing tempdb database.
2008-02-06 03:12:14.82 Server A self-generated certificate was successfully loaded for encryption.
2008-02-06 03:12:14.84 Server Server is listening on [ 'any' 1433].
2008-02-06 03:12:14.85 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2008-02-06 03:12:14.85 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2008-02-06 03:12:14.85 Server Server is listening on [ 127.0.0.1 1434].
2008-02-06 03:12:14.85 Server Dedicated admin connection support was established for listening locally on port 1434.
2008-02-06 03:12:14.87 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2008-02-06 03:12:14.87 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-02-06 03:12:14.95 Logon Error: 18456, Severity: 14, State: 16.
2008-02-06 03:12:14.95 Logon Login failed for user 'OHG20\OHGStaff'. [CLIENT: 192.168.26.56]
2008-02-06 03:12:14.98 spid12s Starting up database 'msdb'.
2008-02-06 03:12:14.98 spid13s Starting up database 'ASPState'.
2008-02-06 03:12:14.98 spid14s Starting up database 'OHG'.
2008-02-06 03:12:15.00 spid9s Starting up database 'tempdb'.
2008-02-06 03:12:15.04 spid9s CHECKDB for database 'tempdb' finished without errors on 2007-12-03 15:47:33.460 (local time). This is an informational message only; no user action is required.
2008-02-06 03:12:15.09 spid15s The Service Broker protocol transport is disabled or not configured.
2008-02-06 03:12:15.09 spid15s The Database Mirroring protocol transport is disabled or not configured.
2008-02-06 03:12:15.10 spid15s Service Broker manager has started.
2008-02-06 03:12:15.20 spid12s 20 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required.
2008-02-06 03:12:15.42 spid12s 1 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required.
2008-02-06 03:12:15.42 spid12s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2008-02-06 03:12:15.71 spid12s CHECKDB for database 'msdb' finished without errors on 2007-10-12 17:07:03.663 (local time). This is an informational message only; no user action is required.
2008-02-06 03:12:16.23 spid52s Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_sysmail_activate'. This is an informational message only; no user action is required.
2008-02-06 03:12:16.82 spid13s 484 transactions rolled forward in database 'ASPState' (5). This is an informational message only. No user action is required.
2008-02-06 03:12:16.85 spid13s 0 transactions rolled back in database 'ASPState' (5). This is an informational message only. No user action is required.
2008-02-06 03:12:16.85 spid13s Recovery is writing a checkpoint in database 'ASPState' (5). This is an informational message only. No user action is required.
2008-02-06 03:12:19.54 spid14s Analysis of database 'OHG' (6) is 32% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2008-02-06 03:12:19.60 spid14s Analysis of database 'OHG' (6) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2008-02-06 03:12:19.62 spid14s Recovery of database 'OHG' (6) is 0% complete (approximately 17 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2008-02-06 03:12:20.93 spid14s Recovery of database 'OHG' (6) is 21% complete (approximately 4 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2008-02-06 03:12:21.23 Logon Error: 18456, Severity: 14, State: 16.
2008-02-06 03:12:21.23 Logon Login failed for user 'OHG20\Softwire'. [CLIENT: ]
2008-02-06 03:12:22.14 spid14s Recovery of database 'OHG' (6) is 96% complete (approximately 0 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2008-02-06 03:12:22.14 spid14s 3142 transactions rolled forward in database 'OHG' (6). This is an informational message only. No user action is required.
2008-02-06 03:12:22.82 spid14s Recovery of database 'OHG' (6) is 96% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2008-02-06 03:12:23.06 spid14s Recovery of database 'OHG' (6) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2008-02-06 03:12:23.06 spid14s 0 transactions rolled back in database 'OHG' (6). This is an informational message only. No user action is required.
2008-02-06 03:12:23.06 spid14s Recovery is writing a checkpoint in database 'OHG' (6). This is an informational message only. No user action is required.
2008-02-06 03:12:24.65 spid14s CHECKDB for database 'OHG' finished without errors on 2007-10-16 07:13:02.950 (local time). This is an informational message only; no user action is required.
2008-02-06 03:12:24.67 spid3s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2008-02-06 03:12:24.67 spid3s Recovery is complete. This is an informational message only. No user action is required.
2008-02-06 03:12:24.92 spid3s Launched startup procedure 'sp_MSrepl_startup'.
***** Then it does a Stack Dump (omitted for brevity) *****
The error information has been submitted to Watson error reporting.
2008-02-06 19:07:28.70 spid130 Error: 17066, Severity: 16, State: 1.
2008-02-06 19:07:28.70 spid130 SQL Server Assertion: File: , line=18180 Failed Assertion = 'LCK_SUCCESS (result)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2008-02-06 19:07:28.75 spid130 Error: 3624, Severity: 20, State: 1.
2008-02-06 19:07:28.75 spid130 A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
February 12, 2008 at 9:07 am
The checkDBs that SQL runs on startup are not complete consistency checks of the entire database. I suspect it's just a check of the system objects.
DBID 2 is tempDB, but since TempDB gets recreated every time the server starts, it's probably not the source of the problem.
I would suggest you run the following on all your user databases (to be safe) and also on master, model and msdb
DBCC CheckDB ('DatabaseName') WITH NO_INFOMSGS
If it give errors, you can post them here for interpretation if you're not sure.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 37820, committed (KB): 105592, memory utilization: 35%.
This is concerning, especially so soon after server start. What's the memory available on the box, what's SQL's max memory set to? What edition of SQL are you running? (Enterprise, standard, ...)
Check the event log, see if you have any other errors (hardware or OS) around the same time.
I would also suggest you contact PSS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2008 at 9:30 am
It's running SQL 2005 (9.00.3152) Standard Ed (x64) build 3190 with SP2. The box has got 16GB physical memory plus 4GB swap. SQL Max memory is set to 14GB.
There is nothing else in the event log at the time other than what I've posted here already, however, a few days ago I got a couple of these:
SQL Server Assertion: File: , line=18180 Failed Assertion = 'LCK_SUCCESS (result)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
I'm starting to get slightly worried with the amount of errors that are coming up 🙁
Sorry to be a complete noob, but what (or who) is PSS?
February 12, 2008 at 9:56 am
Hello,
PSS stands for Microsoft Product Support Services.
Thanks
Lucky
February 12, 2008 at 10:05 am
Sorry, I was thinking PSS was common.
If you're having possible corruption issues, I'd worry about hardware. There could be issues somewhere with the underlying hardware, always a little scary. I'd call PSS. At $250 it's a bargain and that cost could disappear immediately with a small amount of data corruption.
February 12, 2008 at 10:09 am
I thought it must be something like that, thanks.
I've run the CheckDB on all the system DB's and there are no errors. The check on the user DB will take a bit more time 😉
February 13, 2008 at 3:42 am
I notice you are doing replication. Are you using CLR proceduers within your replication process. There is a known bug in using CLR within replication on 64-bit boxes that PSS have not yet published.
You should also look at why you get the storage paged out message at startup. This should not happen if you have set SQL max memory to take into account the other users of memory on your system.
Finally, I repeat the advice of others - you NEED to get PSS involved in this. SQL should not be crashing.
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
February 13, 2008 at 11:36 am
Another (at this time minor) point: 2008-02-06 03:12:12.98 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 37820, committed (KB): 105592, memory utilization: 35%.
Looks like you have some memory allocation issues too. Do you have a max setting for sql memory? Are there lots of other stuff running on the box eating ram?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2008 at 1:55 am
- can post the results of sp_configure ?
- does your server's windows eventlog mention (memory)bank issues ?
- do you have other issues with this server ?
-What's the minimum server memory configuration ?
- is this the only instance for that box ?
btw as Gail stated db2 is tempdb, but I think there are other issues ..
dbname db_id
master 1
tempdb 2
model 3
msdb 4
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply