August 29, 2006 at 10:49 am
Our application is written in Java. We use MSDE 2000 with service pack 4 on Windows XP.........On ceratin client machine, the MSDB database is starting after 5 to 6 minutes after our database starts. Following is the snippet of the SQL logs :
2006-08-28 13:19:28.00 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
2006-08-28 13:19:28.04 server Copyright (C) 1988-2002 Microsoft Corporation.
2006-08-28 13:19:28.04 server All rights reserved.
2006-08-28 13:19:28.04 server Server Process ID is 1576.
2006-08-28 13:19:28.04 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2006-08-28 13:19:28.15 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2006-08-28 13:19:29.06 server SQL Server configured for thread mode processing.
2006-08-28 13:19:29.06 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2006-08-28 13:19:29.09 spid3 Starting up database 'master'.
2006-08-28 13:19:29.95 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2006-08-28 13:19:29.95 spid5 Starting up database 'model'.
2006-08-28 13:19:30.14 spid3 Server name is 'SITCOM300-1'.
2006-08-28 13:19:30.14 spid3 Skipping startup of clean database id 4
2006-08-28 13:19:30.14 spid3 Starting up database 'backoffice'.
2006-08-28 13:19:30.17 server SQL server listening on 153.71.63.189: 1433.
2006-08-28 13:19:30.17 server SQL server listening on 127.0.0.1: 1433.
2006-08-28 13:19:30.17 server SQL server listening on TCP, Shared Memory, Named Pipes.
2006-08-28 13:19:30.17 server SQL Server is ready for client connections
2006-08-28 13:19:31.75 spid5 Clearing tempdb database.
2006-08-28 13:19:38.29 spid5 Starting up database 'tempdb'.
2006-08-28 13:19:38.46 spid3 Recovery complete.
2006-08-28 13:19:38.46 spid3 SQL global counter collection task is created.
2006-08-28 13:19:57.54 spid51 Starting up database 'backoffice'.
2006-08-28 13:25:40.93 spid57 Starting up database 'msdb'.
If you see, our database, backoffice (our database) starts at 13:19:57.54 and MSDB database starts at 13:25:40.93 . This is also not reproducable on all machines......I have also checked that both the database MSDB and master are growable database..........Could any one tell me, what could cause this delay..........
Thanks...
Souvik....
September 1, 2006 at 2:34 am
Souvik
It looks as if the backoffice database is taking about 6 minutes to start. Perhaps there are a lot of transactions to roll forwards and back in that database. Does this happen every time? What do you get if you run the following?
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'recovery interval'
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
John
September 1, 2006 at 9:02 am
Thanks John.......
From the logs it shows that the MSDB database is taking 6 minutes to start.......The database size is about 110 MB so i think yeah there are some batches in our database........But this also happens on fresh machines...........This happpens everytime on the machine where there is this issue......
Following are the output after executing your queris :
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
Output : Configuration option 'show advanced option' changed from 0 to 1.
Command sucessful.
EXEC sp_configure 'recovery interval'
output :
name min max config value run-value
recovery interval 0 32767 0 0
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
output : sucessful....
I hope this feedback helps you diagnose my problem......
September 4, 2006 at 8:12 pm
I think you may want to look into the reason for the backoffice DB starting up twice... that should not happen...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply