June 23, 2010 at 9:56 am
I'm no SQL Server DBA by any stretch of the imagination (a programmer by trade) and I'm pretty sure the issue I'm having could probably be found and corrected very quickly by a DBA. Unfortunately I don't have one to go to so I'm stuck trying to figure it out myself. So here goes:
I have a table that something seems to be wrong with. One of my applications is accessing it a pulling some information. It probably only has 6000 records in it so its pretty small.
If I go into SQL Mgmt Studio and just do a Select * from table, I get this exception:
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
Wasn't able to figure this out so I tried to just drop the table and recreate it.
Running a drop table statement just goes on forever. I hit the stop buttong after it reached 10 min.
If I manually attempt to delete the table by right clicking the table in the tree view anld clicking delete, it will fail and throw an exception saying something like, "Lock request timeout period exceeded"
I've seem this problem at a prior place of employment, but we had DBAs there. I told the DBA about it and he did something on his end and corrected it; I was then able to select from and delete the table again. Just don't know what he did. Don't know if it's the same issue but it sure looks like it is. On a side note this can be a high volume table meaning large amounts of data is added and removed daily.
I do have access to the SQL Server so I should be able to perform the DBA tasks you guys recommend to troubleshoot and correct the issue.
Thanks,
Strick
June 23, 2010 at 10:45 am
I would suggest going to Books Online and looking up DBCC CHECKTABLE and read up on how to run this system check statement. I'd run it with all errors and 'physical_only' options to display any and all errors found in the table.
Do NOT try to 'repair' the table/database without researching what the repair could possibly do to your data.
June 28, 2010 at 2:42 pm
Hi thanks for your response. Unfortunately this didn't work. Ran the check table with repair and still got the same error message.. Also ran check db with repair as well. Same result.
Thanks,
Strick
June 28, 2010 at 2:55 pm
Doesn't sound like corruption, sounds like blocking.
If you query the table then, in another query window query sys.dm_exec_requests. Find the row that refers to the query that you're running against the table, and check the value of blocking_session_id. Is it non-zero? If so, it refers to another running session.
If it is non-zero, check the properties of that session (fastest way is EXEC sp_who2), see where it comes from and what it's running,requests
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
June 28, 2010 at 2:56 pm
nelsonj-902869 (6/23/2010)
Do NOT try to 'repair' the table/database without researching what the repair could possibly do to your data.
stricknyn (6/28/2010)
Ran the check table with repair and still got the same error message.. Also ran check db with repair as well.
Hmmm....
It's a good thing that it's not corruption, or you could have lost important data by doing what you did.
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
June 28, 2010 at 3:25 pm
There are some applications, like Access, that can lock and hold an entire table. I would follow Gail's suggestion and check for blocking.
June 29, 2010 at 7:42 am
Some process could still stuck updating your Database table. Or at the very least that is what the database belives.
Use the Activity Manager and kill all processes attached to the DB that your table is in. Since you are the programer you should know where to stop any applications that will create new connections to the database.
After there are no more connections to the database in Activity Manager, take the Database offline. If you can take the DB offline and bring it back online you should be ready to work with your table.
Another thing to be mindfull of is that if you have no Primary Key column and an index on this problem table, a row with duplicate values will cause issues similar to what you have described.
If this is the case you will have to drop and re-create the table and it's data with no duplicate value rows. After that, make certain you set a column (or all columns) to be the Primary Key for the table or at least a Unique Constraint. May not be the prettiest solution, but it beats what you have now.
June 29, 2010 at 8:14 am
GilaMonster (6/28/2010)
Doesn't sound like corruption, sounds like blocking.If you query the table then, in another query window query sys.dm_exec_sessions. Find the row that refers to the query that you're running against the table, and check the value of blocking_session_id. Is it non-zero? If so, it refers to another running session.
If it is non-zero, check the properties of that session (fastest way is EXEC sp_who2), see where it comes from and what it's running,
Ok thanks, i'll give it a try.
Strick
June 29, 2010 at 8:16 am
GilaMonster (6/28/2010)
nelsonj-902869 (6/23/2010)
Do NOT try to 'repair' the table/database without researching what the repair could possibly do to your data.stricknyn (6/28/2010)
Ran the check table with repair and still got the same error message.. Also ran check db with repair as well.Hmmm....
It's a good thing that it's not corruption, or you could have lost important data by doing what you did.
Actually the data in the table is just testing dummy data. So no fear at all to lose anything. That's why I went ahead and ran the checks.
Thanks,
Strick
June 29, 2010 at 8:18 am
I'm always interested in troubleshooting techniques but when I run
select * from sys.dm_exec_sessions I don't get a column named blocking_session_id
on version 2005
June 29, 2010 at 9:17 am
Apologies, that should have read sys.dm_exec_requests. Post edited.
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
June 29, 2010 at 9:19 am
stricknyn (6/29/2010)
Actually the data in the table is just testing dummy data. So no fear at all to lose anything. That's why I went ahead and ran the checks.Thanks,
Strick
Just as a point, you should never run checkDB with repair as a default option. If you think you have corruption, run it with just the no_infomsgs option, then decide, based on the output of that, what you're going to do.
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
June 30, 2010 at 8:06 am
In cases like this it's a matter of trial & error to figure out exactly what your table is doing.
For starters try a Select Top 100 instead of a *. Check wether you get the same error. Trying to fetch everything from your table won't tell you much and this is safer.
From the description it looks like a process is executing an operation on your table which causes inaccessabilty.
A wannabe multi-user application like Access or Magic runtime is able to lockdown your table when one user is operating on the table directly (which isn't good practice) and another is trying to request data from or operate on the table.
Kill processes, disconnect shared drives if the DB is in a shared environment...just isolate it from external access.
Well that was rather radical. As a programmer I'd start looking for a loop in your app which executes an update statement which goes on forever.
July 2, 2010 at 10:56 am
Just to cover the basics, is the table on a remote machine? Can you query other tables? I am just wondering if the issue is not an errant windows process that may be taking up 99% of your cpu on that box.
----------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply