Meltdown of SQL Server 2005 Service

We are using SQL Server 64 bit edition and windows Server 2003 Server. Sometimes, the SQL server takes most of the CPU time (99 to 100 %) and memory (7 + GB out of 8 GB RAM) and would not respond to user request. At this point of time, we would not be able to connect to SQL server/execute any query using the existing connection or start/stop service. This continues for 15 mins to 4 hours and after that it started working nicely. I could not find any information in the event viewer. When this happened, I have checked our processing log that none of the job/queries related with the application is running on our SQL Server. This happens once in 3 weeks or sometime once in 2 weeks.

In one of our application log, at point of meltdown I’ve got the below message.

[Microsoft][ODBC SQL Server Driver][SQL Server]Time-out occurred while waiting for buffer latch type 3 for page (1:647040), database ID

Since this our production server, users across the world are getting frustrated.

I couldn’t find any information related to his in any website

Can you anyone help me?

With regards

Ganeshkumar S

Can you confirm how you have your datafile/logfiles growth settings? Timeouts on page latches could be as a result of waiting for the growth of a file.

Also are you doing lots of bulk inserts?

|||

Did you try connecting when the DAC when this happens? Have you set up any monitoring on your disks, I/O activity? Do you have any non-default configurations of your server settings?

-Sue

|||Could it be this scenario? http://support.microsoft.com/kb/940942/en-us|||

Yes We are doing lot of bulk insert on one of the databases. The growth settings is as follows..

Autogrowth property of Data file is By 1 MB, unrestricted growth

Autogrowth property of log file is By 10 percent, unrestricted growth

Can you please advise

-Ganeshkumar

|||Check out this article:

http://support.microsoft.com/default.aspx/kb/310834

Consider changing the autogrow value for the datafile to be bigger than 1MB, say 100MB? On top of this, manually grow the file to an appropriate size so it doesn't have to grow at all. The same goes for the transaction log.

HTH!
|||

What build of SQL 2005 (SP1, SP2, RTM, Hotfix?) and which 64-bit (X64 or IA-64)?

There are also some known hardware driver bugs which might cause problems like this and you should make sure you have the most recent drivers for your platform.