database recovering... please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

database recovering... please help

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-23 : 20:31:18

what does it mean if the logs say that database is recovering?

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-24 : 03:39:02

Means it is trying to bring the database online.
Probably trying to complete roll forward/back of transactions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-24 : 22:52:48

i understand this will be normal for shutdowns/restart of services,
but what if there are no restarts made? can a network hiccup cause the database to recover? not individual processes?

--------------------
keeping it simple...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-25 : 11:15:22

Is there anything in the log about the database being restarted automatically? Under some circumstances SQL Server will restart a database (e.g. if it ran out of log space during a transaction rollback)

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-25 : 11:24:54

Is the database set to auto close? Look at the options.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-25 : 11:44:21

quote:
Originally posted by nr

Is the database set to auto close? Look at the options.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

An auto-close database shouldn't have to recover on startup as its always shutdown cleanly.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-26 : 01:02:36

Thanks Paul...
not sure what you mean about restarting the database but there are
no automatic or manual restarts done but the logs says it is

1. clearing the tempdb
2. starting database [dbname] - is this what you mean?

So what you're saying is that if the tempdb runs out of logspace, it's supposed to 'restart' the database? How is it able to do it or is that normal?

Are there instances that it will fail to 'restart' and this may have caused transactions to 'hang'?

Can you give me some pointers to check? As of date, I'm doing some tune-ups and optimizations and so far we haven't had the problem, but since I was not able to pinpoint the problem, my worry is that it's going to happen again.

thanks in advance

quote:
Originally posted by paulrandal

Is there anything in the log about the database being restarted automatically? Under some circumstances SQL Server will restart a database (e.g. if it ran out of log space during a transaction rollback)

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-26 : 01:03:45

No autoclose is disabled, should i turn it on?

quote:
Originally posted by nr

Is the database set to auto close? Look at the options.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-11-26 : 05:05:53

"autoclose is disabled, should i turn it on?"

Absolutely not!!

(Well, it would be OK on a laptop running MSDE ...)

Has the database been restored? This is what I use to check restore history (but I think its in the Logs too)


SELECT DISTINCT TOP 100
--	RH.destination_database_name,
	RH.restore_history_id,
--  	BF.file_number,
	RF.file_number,
	RH.restore_date,

	RF.file_number,
	RF.destination_phys_drive,
	RF.destination_phys_name,

	RG.filegroup_name,

	[Backup Set Name]=BS.name,
	BS.description,
	BS.database_creation_date,
	BS.backup_start_date,
	BS.database_name,
	BS.server_name
FROM	msdb.dbo.restorehistory AS RH
	LEFT OUTER JOIN msdb.dbo.restorefile AS RF
		ON RF.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
		ON RG.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.backupset AS BS
		ON BS.backup_set_id = RH.backup_set_id
WHERE	    RH.destination_database_name = N'MyDBName'	-- SELECT DB_NAME()
ORDER BY RH.restore_date DESC
	, RF.file_number DESC

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-26 : 11:03:29

Jen - can you post the relevant portion of the errorlog please?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-28 : 04:50:58

The database was not restored Kristen, it was a fresh build.

I'll post the errorlog when I get back to office on Wednesday Paul.

It's safe for me to take the day's off (local holiday) coz it's Monday

, I love Mondays...(personally, I wish everyday is Monday

)

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-30 : 04:26:27

Hi Paul, here's the set of log entries that you requested...
I've modified certain information such as ip addresses and db names

quote:

2005-11-23 22:43:40.11 spid55 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure
2005-11-23 22:43:33.15 spid53 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
2005-11-23 22:43:15.60 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure
2005-11-23 22:43:13.15 spid4 Launched startup procedure 'sp_MSrepl_startup'
2005-11-23 22:43:13.14 spid4 SQL global counter collection task is created.
2005-11-23 22:43:13.14 spid4 Recovery complete.
2005-11-23 22:43:12.68 spid12 Recovery is checkpointing database 'DBName' (18)
2005-11-23 22:43:12.68 spid12 6 transactions rolled back in database 'DBName' (18).
2005-11-23 22:43:12.65 spid12 Recovery of database 'DBName' (18) is 100% complete (approximately 0 more sec
2005-11-23 22:43:12.64 spid5 Starting up database 'tempdb'.
2005-11-23 22:43:12.64 spid12 Recovery of database 'DBName' (18) is 99% complete (approximately 0 more seco
2005-11-23 22:43:12.29 spid12 Recovery of database 'DBName' (18) is 99% complete (approximately 0 more seco
2005-11-23 22:43:12.26 spid12 Recovery of database 'DBName' (18) is 0% complete (approximately 9 more secon
2005-11-23 22:43:12.15 spid12 Analysis of database 'DBName' (18) is 100% complete (approximately 0 more sec
2005-11-23 22:43:11.46 spid5 Clearing tempdb database.
2005-11-23 22:43:11.40 server SQL Server is ready for client connections
2005-11-23 22:43:11.39 server SQL server listening on TCP, Named Pipes.
2005-11-23 22:43:11.04 spid12 Starting up database 'DBName'.
2005-11-23 22:43:11.00 spid11 Starting up database 'DBName2'.
2005-11-23 22:43:11.00 server SQL server listening on 127.0.0.1: 1433.
2005-11-23 22:43:11.00 server SQL server listening on xxx.xxx.xxx.xxx: 1433.
2005-11-23 22:43:10.93 spid10 Starting up database 'Maintenance'.
2005-11-23 22:43:10.86 spid9 Starting up database 'distribution'.
2005-11-23 22:43:10.86 spid8 Starting up database 'msdb'.
2005-11-23 22:43:10.82 spid4 Server name is 'ServerName'.
2005-11-23 22:43:10.73 spid5 Starting up database 'model'.
2005-11-23 22:43:10.73 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-11-23 22:43:10.57 spid4 Starting up database 'master'.
2005-11-23 22:43:08.96 server Attempting to initialize Distributed Transaction Coordinator.
2005-11-23 22:43:08.86 server SQL Server configured for thread mode processing.

Thanks in advance

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-11-30 : 08:28:43

This looks "normal" to me Jen - assuming that the server (or the SQL Service) was restared shortly before "2005-11-23 22:43:08.86"

But I imagine it must have been shut down "untidily" for the amount of "recovery" effort that then went on. My understanding is that is just the normal startup processing of the TLogs to rollback / forward / whatever the transactions that were "in progress" when the DB shut down.

What I don't see is the "MS SQL Version ..." stuff from a normal startup, so maybe this is NOT the start of the log file, and there was NOT a shutdown as such (i.e. just before "2005-11-23 22:43:08.86")?

But I'm only typing out-loud, more knowledgeable people will be able to interpret this more usefully I'm sure.

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-30 : 22:01:25

you're correct Kristen, I did not include those information

I was thinking it was normal but this type of entries occur also in between without any restarts on the services or machine

--------------------
keeping it simple...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-12-01 : 00:07:05

I agree with Kristen - this is normal. Can you post the pertinent sections of errorlog for when it starts recovering with no shutdown?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-12-01 : 01:33:35

remaining entries not included:

sql server terminating because of system shutdown
then
the recovery (same as to the one posted, like it was restarting from a restart or shutdown)
then
WARNING: EC 23af9550, 0 waited 300 sec. on latch 19e13e70. Not a BUF latch. (when we tried to start issuing transactions)

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 01:39:36

Which log are you looking at Jen - Event [Application] log or SQL log?

'Coz if its SQL Log I thought it started a new file at each restart - so might be odd given that your message is in the middle of a SQL Log file.

"sql server terminating because of system shutdown"

There wasn't a "system shutdown" right? Event Log [System] doesn't have an entry for:

Source = eventlog
Type = Information
Message = "The Event log service was started"

around that time (I'm figuring that's the best way of knowing that the box was restarted)

Automatic Windows Update that did an automatic reboot?

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-12-01 : 02:09:27

automatic update is disabled
the event logs shows there was an event log restart 2 minutes before the system shutdown in the sql log

I'll review all of the event logs and will keep you posted

thanks for the reminder Kristen, I'm guilty of forgetting to check the event logs (bad Jen)

, got so used to sql logs giving me the information I need

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 02:21:06

"I'm guilty of forgetting to check the event logs"

You'd remember if you'd turned the machine off though, wouldn't you? <bg>

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-12-01 : 02:34:34

yes, ofcourse

the purpose of this post actually is that i'm trying to rule out several possibilities like virus infection and malicious intentions

i'll relax only when i'm sure it was something that the netadmins did and they did not inform us...again

right now, nobody is admitting anything

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 03:19:20

Do you see an orderly shutdown in Event log?

For a Win2003 server there should be a comment put by the Operator (for a disorderly one the next person to TS into that server would be asked for the reason of the previous shutdown IIRC)

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources