April 1, 2009 at 1:49 pm
Hi All,
I am facing a situation where my SQL server gets restarted often. There are 2 drives in the server and the System team had run check disk against each of them including the C: (OS drive) and said that the drives are fine and there are no issues. E: drive hosts both the datafile and logfile of the database. The size of the only user database that exists is around 230 GB.
Server Specifications : Microsoft windows server 2003 , Standard edition, SP2, 3.81GB of RAM
SQL specifications: SQL2000 server,Enterprise Edition, SP4
When we ran DBCC CHECKTABLE statements against certain specific tables in the db the server gets restarted.
Could anyone kindly let me know what could be the issue, as we dont want to use the repair table option as it can lead to data loss. Please advice.
Regards,
Eben
April 1, 2009 at 2:00 pm
Do you know that you have corruption? If so, post details, if not, why are you considering 'allow_data_loss'?
What's the last few entries in the SQL error before the shutdown?
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
April 1, 2009 at 2:18 pm
ebenraja (4/1/2009)
Hi All,When we ran DBCC CHECKTABLE statements against certain specific tables in the db the server gets restarted.
Yes as Gail suggested, Why are you running this anyway? Is it a part of your SLA's or have you identified any corruption? and also please post the error details in SQL Server Error log?
April 1, 2009 at 3:17 pm
Guys,
I am not able to find anything abnormal with the SQL erorlogs and the System eventlogs. SQL logs are normal
I am not certain on the details of the table but they are pretty big also my application owner says that the table cant be dropped or backed up from production as the data is different.
Error log :
2009-03-31 02:59:53.43 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2009-03-31 02:59:53.43 server Copyright (C) 1988-2002 Microsoft Corporation.
2009-03-31 02:59:53.43 server All rights reserved.
2009-03-31 02:59:53.43 server Server Process ID is 1644.
2009-03-31 02:59:53.43 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2009-03-31 02:59:53.46 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
2009-03-31 02:59:53.56 server SQL Server configured for thread mode processing.
2009-03-31 02:59:53.57 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2009-03-31 02:59:53.65 server Attempting to initialize Distributed Transaction Coordinator.
2009-03-31 02:59:53.89 spid3 Starting up database 'master'.
2009-03-31 02:59:54.24 spid3 0 transactions rolled back in database 'master' (1).
2009-03-31 02:59:54.24 spid3 Recovery is checkpointing database 'master' (1)
2009-03-31 02:59:54.56 spid3 Server name is 'USCPSPDS003'.
2009-03-31 02:59:54.56 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2009-03-31 02:59:54.56 spid5 Starting up database 'model'.
2009-03-31 02:59:54.56 spid8 Starting up database 'msdb'.
2009-03-31 02:59:54.56 spid9 Starting up database 'pubs'.
2009-03-31 02:59:54.56 spid10 Starting up database 'Northwind'.
2009-03-31 02:59:54.59 spid12 Starting up database 'DBA_Admin'.
2009-03-31 02:59:54.59 spid14 Starting up database 'x'.
2009-03-31 02:59:54.59 spid13 Starting up database 'x'.
2009-03-31 02:59:54.59 spid15 Starting up database 'x'.
2009-03-31 02:59:54.61 spid11 Starting up database 'x'.
2009-03-31 02:59:54.61 spid16 Starting up database 'x'_Q2_2008'.
2009-03-31 02:59:55.00 spid13 Analysis of database 'x'' (9) is 100% complete (approximately 0 more seconds)
2009-03-31 02:59:55.06 spid16 1 transactions rolled forward in database 'Datalink_Q2_2008' (12).
2009-03-31 02:59:55.12 spid16 0 transactions rolled back in database 'Datalink_Q2_2008' (12).
2009-03-31 02:59:55.15 spid8 5 transactions rolled forward in database 'msdb' (4).
2009-03-31 02:59:55.17 server SQL server listening on 10.93.10.10: 1433.
2009-03-31 02:59:55.17 server SQL server listening on 127.0.0.1: 1433.
2009-03-31 02:59:55.23 spid5 Clearing tempdb database.
2009-03-31 02:59:55.23 spid16 Recovery is checkpointing database 'Datalink_Q2_2008' (12)
2009-03-31 02:59:55.26 server SQL server listening on TCP, Shared Memory.
2009-03-31 02:59:55.26 server SQL Server is ready for client connections
2009-03-31 02:59:55.65 spid8 0 transactions rolled back in database 'msdb' (4).
2009-03-31 02:59:55.67 spid8 Recovery is checkpointing database 'msdb' (4)
2009-03-31 02:59:57.04 spid5 Starting up database 'tempdb'.
2009-03-31 02:59:57.48 spid15 45 transactions rolled forward in database 'x' (11).
2009-03-31 02:59:58.60 spid15 0 transactions rolled back in database 'x' (11).
2009-03-31 02:59:58.60 spid15 Recovery is checkpointing database 'x' (11)
2009-03-31 02:59:59.14 spid3 Recovery complete.
2009-03-31 02:59:59.14 spid3 SQL global counter collection task is created.
2009-03-31 03:00:00.18 spid51 Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
2009-03-31 03:01:05.84 spid53 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'sp_MSgetversion'.
2009-03-31 03:23:48.42 spid56 Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_msver'.
2009-03-31 03:24:10.07 spid56 Using 'odsole70.dll' version '2000.80.2039' to execute extended stored procedure 'sp_OACreate'.
April 1, 2009 at 3:27 pm
ebenraja (4/1/2009)
Guys,I am not able to find anything abnormal with the SQL erorlogs and the System eventlogs. SQL logs are normal
What you posted there is a startup portion of the log. I'm not interested in that. What I want is the last few events before SQL shut down, so the end of the previous error log.
Also, as I asked before, do you know you have corruption? If so, post any errors you have. If not, why are you contemplating running checkDB with a repair option.
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
April 1, 2009 at 3:33 pm
Also gail...
yes i cant lose data and so i cant use data loss option.
Let me know the ways to find if the tables are corrupted as if i run the check command the server restarts.
Server gets restarted once every week and I need to ascertain the reason, System team says that disks are fine, so i need to find out fropm db side so i ran check table statements in the user db.
No Its not an SLA...howver i have reindexing job configured to run during weekends which also i feel causes the restart.
April 1, 2009 at 4:19 pm
Rather than the following, there is no clue to anything as it comes again to the startup of the Log.
SQL agent logs :
2009-03-30 21:36:28 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2009-03-30 21:36:28 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2009-03-30 21:36:28 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2009-03-30 23:59:56 - ! [LOG] Unable to read local eventlog (reason: The event log file has changed between read operations)
2009-03-30 23:59:56 - + [LOG] Successfully re-opened the local eventlog - NOTE: Some events may have been missed
I can also see the following in the erorlog betwee the chek table execution
Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_msver'.
Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_servicecontrol'.
Using 'odsole70.dll' version '2000.80.2039' to execute extended stored procedure 'sp_OACreate'.
April 1, 2009 at 4:36 pm
Go to errrorlog.1 and post the last 20-30 lines.
April 1, 2009 at 4:50 pm
ebenraja (4/1/2009)
Let me know the ways to find if the tables are corrupted as if i run the check command the server restarts.
What is the exact command that you are running?
SQL agent logs :
I'm not asking for the SQL agent logs. SQL by default keeps 6 log files. What I need is the last few lines of one of the previous logs, one where this odd shutdown occurred. There should be an indication in the log why SQL is shutting down, that's what I need to see.
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
April 2, 2009 at 2:57 am
Hi,
You can get the previous error log using the following command in master database. Also can you check the event viewer of the server. There might be some other process that are trying to stop or restart sql server.
xp_readerrorlog 1
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 2, 2009 at 1:49 pm
Guys,
This is my error log when i ran the check table statement
command used is : DBCC CHECKTABLE ('tablename')
Ran this command individually against each of the tables.
-------------------------------------------------
2009-03-30 20:48:58.40 spid54 DBCC CHECKTABLE (Chemmate.dbo.GenPrint) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 20:49:09.39 spid54 DBCC CHECKTABLE (Chemmate.dbo.Genprntitms) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 2 seconds.
2009-03-30 20:49:24.45 spid54 DBCC CHECKTABLE (Chemmate.dbo.Genprodanx) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
-------------------------------------------------------------
Then the server got restarted when i ran the check table command for a table and the error log again is as follows
--------------------------------------
2009-03-30 20:54:48.93 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2009-03-30 20:54:48.93 server Copyright (C) 1988-2002 Microsoft Corporation.
2009-03-30 20:54:48.93 server All rights reserved.
2009-03-30 20:54:48.93 server Server Process ID is 1660.
2009-03-30 20:54:48.93 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2009-03-30 20:54:48.96 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
2009-03-30 20:54:49.06 server SQL Server configured for thread mode processing.
2009-03-30 20:54:49.09 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2009-03-30 20:54:49.17 server Attempting to initialize Distributed Transaction Coordinator.
2009-03-30 20:54:50.39 spid3 Starting up database 'master'.
2009-03-30 20:54:50.81 spid3 0 transactions rolled back in database 'master' (1).
2009-03-30 20:54:50.81 spid3 Recovery is checkpointing database 'master' (1)
2009-03-30 20:54:51.14 spid3 Server name is 'USCPSPDS003'.
2009-03-30 20:54:51.14 spid5 Starting up database 'model'.
2009-03-30 20:54:51.14 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2009-03-30 20:54:51.14 spid8 Starting up database 'msdb'.
2009-03-30 20:54:51.14 spid9 Starting up database 'pubs'.
2009-03-30 20:54:51.14 spid11 Starting up database 'x'.
2009-03-30 20:54:51.14 spid10 Starting up database 'Northwind'.
2009-03-30 20:54:51.14 spid12 Starting up database 'DBA_Admin'.
2009-03-30 20:54:51.15 spid13 Starting up database 'x'.
2009-03-30 20:54:51.17 spid14 Starting up database 'x'.
2009-03-30 20:54:51.17 spid15 Starting up database 'x'.
2009-03-30 20:54:51.39 spid13 Analysis of database 'x' (9) is 100% complete (approximately 0 more seconds)
2009-03-30 20:54:51.49 spid5 Clearing tempdb database.
2009-03-30 20:54:51.64 server SQL server listening on 10.93.10.10: 1433.
2009-03-30 20:54:51.64 server SQL server listening on 127.0.0.1: 1433.
2009-03-30 20:54:51.71 server SQL server listening on TCP, Shared Memory.
2009-03-30 20:54:51.71 server SQL Server is ready for client connections
2009-03-30 20:54:52.35 spid5 Starting up database 'tempdb'.
2009-03-30 20:54:53.81 spid3 Recovery complete.
2009-03-30 20:54:53.81 spid3 SQL global counter collection task is created.
2009-03-30 20:54:55.17 spid51 Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
----------------------------------------------------------------------
April 2, 2009 at 2:20 pm
ebenraja (4/2/2009)
Then the server got restarted when i ran the check table command for a table and the error log again is as follows
Again that is the startup messages. Those are useless, there's no indication what happened. What I need to see are the last 10 or so entries from the previous error log.
If you're working via management studio then, when you browse the logs via object explorer you will see several logs. View the second one, find the last few entries in it and post them here.
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
April 3, 2009 at 8:42 am
Hi find out the last log details in previous error log....
2009-03-30 21:24:19.84 spid53 DBCC CHECKTABLE (Chemmate.dbo.MAINVALIDATION_UNSUPPORTED) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:24:28.28 spid53 DBCC CHECKTABLE (Chemmate.dbo.MainValidationanx) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:24:50.59 spid53 DBCC CHECKTABLE (Chemmate.dbo.MAINVIEW) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:24:58.48 spid53 DBCC CHECKTABLE (Chemmate.dbo.MainWIA) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:25:06.30 spid53 DBCC CHECKTABLE (Chemmate.dbo.MainWIAL) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:25:15.76 spid53 DBCC CHECKTABLE (Chemmate.dbo.mig_TableToProcess) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:25:25.22 spid53 DBCC CHECKTABLE (Chemmate.dbo.mig_ToProcess) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:25:40.87 spid53 DBCC CHECKTABLE (Chemmate.Chemmate.Product) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:25:54.32 spid53 DBCC CHECKTABLE (Chemmate.Chemmate.Product_compos) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:26:07.99 spid53 DBCC CHECKTABLE (Chemmate.Chemmate.Products_EU) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:26:14.37 spid53 DBCC CHECKTABLE (Chemmate.dbo.sc_Atrion_FG1) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:26:28.87 spid53 DBCC CHECKTABLE (Chemmate.dbo.sc_Atrion_FG2) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:26:37.79 spid53 DBCC CHECKTABLE (Chemmate.dbo.SentExtraInfosentnces) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:26:47.23 spid53 DBCC CHECKTABLE (Chemmate.dbo.SentExtraInfosentnces) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:26:59.76 spid53 DBCC CHECKTABLE (Chemmate.dbo.SentExtraInfoTranssentnces) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
2009-03-30 21:27:07.07 spid53 DBCC CHECKTABLE (Chemmate.dbo.SentExtraInfoTranssentnces) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:27:20.03 spid53 DBCC CHECKTABLE (Chemmate.Chemmate.SentExtraInfoTransunsupported) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:27:24.07 spid53 DBCC CHECKTABLE (Chemmate.dbo.SentExtraInfoTranssentnces) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:27:37.89 spid53 DBCC CHECKTABLE (Chemmate.Chemmate.SentExtraInfounsupported) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:28:56.10 spid56 DBCC CHECKTABLE (Chemmate.dbo.WhereUsedCodes) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:29:12.77 spid56 DBCC CHECKTABLE (Chemmate.dbo.TranslationsEx) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
2009-03-30 21:29:27.47 spid56 DBCC CHECKTABLE (Chemmate.dbo.TranslationsEx) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:29:35.50 spid56 DBCC CHECKTABLE (Chemmate.dbo.test1) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:29:44.46 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_TSCA_Report) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
2009-03-30 21:29:54.33 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_TSCA_History) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
2009-03-30 21:30:07.01 spid56 DBCC CHECKTABLE (Chemmate.dbo.Sun_TransMethod) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:30:16.07 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_SHIPFROM_ADDRESS) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:30:27.18 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_ROLLOUT_TEMP) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:30:36.47 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_Products_No_MSDS) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:30:43.75 spid56 DBCC CHECKTABLE (Chemmate.dbo.Sun_Plant_No_MSDS) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:30:53.48 spid56 DBCC CHECKTABLE (Chemmate.dbo.Sun_New_Products) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
2009-03-30 21:30:59.85 spid56 DBCC CHECKTABLE (Chemmate.dbo.Sun_Language) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:31:07.13 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_DACH_REGION) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
2009-03-30 21:31:14.51 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_DACH_ADDRESS) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:31:24.01 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_Countries_Mapping) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2009-03-30 21:32:09.66 spid56 DBCC CHECKTABLE (Chemmate.dbo.SUN_BARCODE_PREFIX) executed by sifydba found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
April 3, 2009 at 8:43 am
i didnt find any specified error in error log....
April 3, 2009 at 8:57 am
Strange. No stack dumps? (.mdmp files in the SQL log directory)
What happens if you run a full checkDB rather than the individual check tables?
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply