SQL 2005 - Weird Problems 1

[Ovatvvon]

We have a SQL 2005 server that started having weird things happen, just out of the blue.

[ul]
[li]The associated client application was not able to connect / interact with the database any more.[/li]
[li]The database took a long time to recover during system start-up. It is a 200 GB database, but would normally come online within several seconds. During this time, it would take between 5 and 10 minutes at least. (Note: this is a 64bit environment)[/li]
[li]Received an error (below) when attempting to shrink the database.[/li]
[li]Also could not detach the database[/li]
[li]When I tried to run jobs while the database was still recovering, I would receive a message that jobs could not be run while SQL Agent was starting up. The SQL Agent displayed as having been started, however, as well as was listed in the server Services as having been started.
[li]Ran a CheckDB on the database, and after 13 hours, attempted to cancel it, but it remained in killed/rollback status. It had ended up blocking itself prior to killing/ending the process ID. Has to restart the system, as a SQL Server recycle would not even work.[/li]
[/ul]

Here is the error that was generated when I tried to shrink the database...

[COLOR=red]Shrink failed for Database '{database name}'. (Microsoft.SqlServer.Smo)

[b]Additional information:[/b]
An exception occurred while executing a Transact-SQL statement or batch.
[i](Microsoft.SqlServer.ConnectionInfo)[/i]
A Severe error occurred on the current command.  The results, if any, should be discarded.
[i](Microsoft SQL Server)[/i][/color]

I also saw hundreds of of these errors from before the system reboots:

[COLOR=red]Date		10/16/2007 11:33:19 AM
Log		SQL Server (Archive #2 - 10/16/2007 11:33:00 AM)

Source		spid55

Message
The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x0000044c2a0000 in file with handle 0x000000000000075C. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.[/color]

After I had rebooted several times, a System Admin took a look at the box, to check if power management was enabled. (Based on a MS KB article found at:

http://support.microsoft.com/kb/931279).

It was set so the CPU's would always be at max power, but he enabled and disabled it again, just to give it a try, and when the system started back up, everything became operational again, without problems.

Has anyone seen this before, or know what might've happened, hardware or software wise? Any ideas? I'm at a loss.



-Ovatvvon :-Q

 

[mrdenny]

Sounds like the CPUs flipped into power saving mode then flipped back out.

This is why I hate the fact that they build that power saving crap onto server boards.

When I tried to run jobs while the database was still recovering, I would receive a message that jobs could not be run while SQL Agent was starting up. The SQL Agent displayed as having been started, however, as well as was listed in the server Services as having been started.

This is normal. The service will show as started, but SQL Agent stay's in a limbo state until all databases have been recovered.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

Except the server had power management turned off in the BIOS, so wouldn't that prevent the CPU's from going into power saving mode?


-Ovatvvon :-Q

 

[mrdenny]

In theory. Could be a BIOS bug or a bug in the CPU.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

I think that must have been the case; I cannot find another cause. I was actually wondering that before addressing the issue in this forum, but others cringed at the thought of passing it off as a blip of some sorts.

Thanks for confirming my thoughts with your own!


-Ovatvvon :-Q

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog