March 21, 2011 at 2:51 pm
I hope I've chosen the right forum to post this one in. I know this is more of an operating system error, but maybe some of you have seen this one before. I haven't had much luck with Yahoo or Google.
Backups taken on SQL 2005 SP3 running on Windows Server 2003 R2 (32-bit). Restores being run on SQL Server 2008 SP1 on Windows Server 2008 R2 (64-bit) active-passive cluster. One node on the cluster was recently upgraded from 48gb to 96gb of RAM. (So, N01 has 48gb, N02 has 96gb). All the services have been running on N02 for the last week or two. We have an automated process that refreshes 4 databases on the cluster nightly. Two databases are small ( < 10gb) and are over 100gb. The small ones load fine. However, the large ones I consistently receive the following error:
Msg 3203, Level 16, State 1, Line 1
Read on "\\SERVER\SQL_BAK\DBs\DB_backup_201103210302.bak" failed: 1130(Not enough server storage is available to process this command.)
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
From what I've researched, this is clearly an o/s error. What I was able to do to get around it most of the time was reduce the amount of memory dedicated to SQL Server. Sometimes it was as low as 8gb (down from the 40gb configured in SQL), but it worked. Now, we can't get it to load at all, even with 8gb configured on N02. I've bounced all heavy traffic out of SQL. We've verified nothing CPU or memory intensive is running on the boxes during the restore. The pagefile is sized properly. We have seen other mention the changing of an IRQStack or ChimneyStack (or something like that) property at the o/s level, but our sysadmins aren't convinced that's going to fix it.
At this point it's worth trying, but we're at a loss. I've brought up another server - same o/s, same SQL version - as the source, and I'm probably going to modify the process to point to there and then utilize differential backups to speed up the process and make up for it being a less powerful machine to what we have been running on.
So, has anyone out there seen this error, had any experience troubleshooting this error in a similar scenario to what I'm in? I'm curious to know how you got around it. What I found on Yahoo/Google had some similar aspects, but I never found a close enough match to feel comfortable.
thanks....
Peter
March 21, 2011 at 3:22 pm
If you copy the backup locally and then try and restore do you get the same errors?
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 21, 2011 at 3:51 pm
Yes, we do. I forgot to mention that.
March 21, 2011 at 7:25 pm
Are you running Standard or Enterprise Edition?
Do you have lock pages in memory right set for the service account?
Do you have perform volume maintenance right set for the service account?
If you are running standard edition, try apply CU4 and enabling the option to lock pages in memory.
Since this is 32-bit, you would have to have lock pages in memory right set and AWE enabled to access the additional memory. Do you have the option of switching to x64 and running the 64-bit version instead?
Just a couple of things I would look at.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 22, 2011 at 6:42 am
Jeffrey Williams-493691 (3/21/2011)
Are you running Standard or Enterprise Edition?Do you have lock pages in memory right set for the service account?
Do you have perform volume maintenance right set for the service account?
If you are running standard edition, try apply CU4 and enabling the option to lock pages in memory.
Since this is 32-bit, you would have to have lock pages in memory right set and AWE enabled to access the additional memory. Do you have the option of switching to x64 and running the 64-bit version instead?
Just a couple of things I would look at.
I posted my question while also trying to get out the door, so your follow-up questions are helping me realize I forgot a couple of pieces of info. This restore had been working almost all the time. I would not have any issues from Monday thru Friday. It would fail on Saturday almost on cue. So, that brings up the question "what's happening on Saturdays?" To the best of my knowledge, nothing during the window I perform the restores. And wouldn't you know it, 2 Saturdays ago it succeeded.
So, that brings us to this past weekend, where it failed Saturday, Sunday, Monday and again this morning. All weekend and Monday it failed on both databases. Today it only failed on the larger of the two, so I'm hold out hope.
But, to go back and answer your questions......
Are you running Standard or Enterprise Edition? - Enterprise
Do you have lock pages in memory right set for the service account? - No, we do not, but I can add this easily
Do you have perform volume maintenance right set for the service account? - not sure I understand the question here. Are you asking if the service account has proper permissions on the disks?
And this is actually 64-bit. The source of the databases is x86, but the server we are doing the load on, the one throwing the error, is 64-bit. We did fire up a VM for further testing, though, that's 32-bit, SQL 2005 and we saw the same error.
Our sysadmin's are looking into some stuff with TCPChimneyStack, IRPStackSize and something else involving the buffers on the iSCSI cards.
March 22, 2011 at 8:46 am
GilaMonster (3/21/2011)
If you copy the backup locally and then try and restore do you get the same errors?
pveilleux (3/21/2011)
Yes, we do. I forgot to mention that.
Quick update:
In the past, the copy had always failed with the same not enough storage message, so we were never able to load the databases. This morning, though, even though the restore failed, I was able to copy the database backup files over and am loading them up now. Hopefully this does not result in an error.
We'll see.....
March 22, 2011 at 11:34 am
The perform volume maintenance right is needed to enable instant initialization - probably not your issue though.
You are already looking at the things I would focus on.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply