October 8, 2009 at 2:23 am
Hi All
I have a funny issue currently in my productions system (DELL quad code 2.5Ghz + 15Gb RAM + many disks). It is SQL 2005 SP3 (9.0.4226). It is in a VMWare ESX environment but is the only VM on that host.
Below is a sample of some TSQL that when it failed (or cancelled) it causes the sqlserver.exe to run between 98 to 99% cpu across all 4 cpu's. There is nothing in the SQL Logs nor is the specific session active in SQL server that run the TSQL.
The only way to resolve it is to restart SQL Server service!
BEGIN TRY
BEGIN TRAN
Many deletes + many inserts
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
DECLARE @errorMessage VARCHAR(1000)
SET @errorMessage = ERROR_MESSAGE()
RAISERROR(@errorMessage, xx, xx)
END CATCH
Additional info @ the time of high CPU
(1)
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824
(2)
Message
Memory Manager
VM Reserved = 1792544 KB
VM Committed = 199308 KB
AWE Allocated = 13828096 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
(3)
Memory node Id = 0
VM Reserved = 1788384 KB
VM Committed = 195300 KB
AWE Allocated = 13828096 KB
SinglePage Allocator = 151600 KB
MultiPage Allocator = 17848 KB
(4)
MEMORYCLERK_SQLGENERAL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 10352 KB
MultiPage Allocator = 5392 KB
(5)
There is insufficient system memory to run this query.
October 8, 2009 at 2:57 am
The quick question to this, is can you break up the inserts and deletes into smaller batchs/transactions.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 8, 2009 at 3:08 am
Thanks for the reply
That is my 1st plan to try and resolve this.. But what im not understanding when I simulate a failure in my Dev AND QA env it rolls back ok with none of the symptoms that appear on the live server.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply