March 4, 2009 at 6:16 am
A developer rebooted one of our test servers yesterday. There is a service on this server that runs constantly that connects to one of the databases. When the server came back up this service failed and we had to manually restart it.
On investigation on the SQL logs I noticed that the database in question took 46 minutes to start. The database is only 5meg so I can`t work out what would cause such a time lapse.
Any thougts ?
March 4, 2009 at 6:36 am
Check the SQL error log. There should be details in there about what SQL was doing for that time. My guess, there was a rather large transaction that it had to roll back/forward and that's what took the time.
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
March 4, 2009 at 6:39 am
I had thought that. But there is no activity at all for 40 mins. The log goes from starting tempdb. Then 40 min gap, then starting next database.
March 4, 2009 at 6:44 am
Post that portion of the error log please?
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
March 4, 2009 at 7:09 am
See attachment
March 4, 2009 at 7:16 am
As text please. There's an entry there that says "SQL resumed execution after... Reason:" but it's cut off.
The error log's just a text file. You can open it in notepad and copy the interesting pieces out.
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
March 4, 2009 at 7:26 am
Text as requested.
03/03/2009 20:29:33,spid1s,Unknown,Server resumed execution after being idle 1791 seconds. Reason: timer event.
03/03/2009 19:44:28,spid1s,Unknown,Server resumed execution after being idle 1791 seconds. Reason: timer event.
03/03/2009 18:59:23,spid1s,Unknown,Server resumed execution after being idle 1793 seconds. Reason: timer event.
03/03/2009 18:13:58,spid51,Unknown,Starting up database 'ESOS'.
03/03/2009 18:08:08,spid1s,Unknown,Server resumed execution after being idle 1819 seconds. Reason: timer event.
03/03/2009 17:37:17,spid11s,Unknown,Service Broker manager has started.
03/03/2009 17:37:16,spid11s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
03/03/2009 17:37:16,spid11s,Unknown,The Service Broker protocol transport is disabled or not configured.
03/03/2009 17:37:16,spid5s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
03/03/2009 17:37:16,spid8s,Unknown,Starting up database 'tempdb'.
03/03/2009 17:37:15,Logon,Unknown,Login failed for user 'PublicUser'. [CLIENT: 128.100.50.11]
03/03/2009 17:37:15,Logon,Unknown,Error: 18456 State: 16.
03/03/2009 17:37:14,spid8s,Unknown,Clearing tempdb database.
March 4, 2009 at 7:50 am
Is this SQL Express?
Have a look at this discussion:
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
March 4, 2009 at 8:08 am
Thats great, explains whats going on.
Cheers for your help.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply