April 21, 2011 at 10:41 am
Help!!!
I have a database that is normally accessed by a .Net application using stored procedures. A couple of times in the last week this application has stopped working. It appears that any of the stored procedures that write to a table fail. Those that only read from a table work fine. I tried writing to a table from SQL Server Manager and failed also.
To fix the problem I had to take the DB offline and then bring it back online.
How can I tell what is causing this? I have not been able to find anything in any of the logs.
Thanks for any help you can provide.
April 21, 2011 at 10:54 am
We can't do anything for you without the actual error message (sql side if possible).
April 21, 2011 at 11:38 am
Thanks for the quick response.
Yes, thats part of the problem.
On the application side I just get a timeout error and on the SQL side I don't get anything. It appears that the tables are locked but I don't know how to determine why.
I've looked in the SQL log files but can't find anything unusual. Because this is an operational system my initial focus was on getting the system back up and running. I didn't have a large amount of time for faultfinding. Hopefully next time this happens (probably within a couple of days) I will be able to check a few more things while the error is present.
What I need to know is suggestions on where to look and what to look for to solve this problem when it occurs next time.
Cheers,
April 21, 2011 at 11:42 am
sp_who2 comes to mind...
April 21, 2011 at 11:52 am
Post your sql errorlog file.
What's the db status ?
Is it online/offline/in recovery ?
Did you try sp_who_is_active to figure out what's going on ?
(b)locking, ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 21, 2011 at 2:01 pm
Thanks for the suggestions.
I think I may have finally figured it out. When I checked the log file settings I noticed that autogrowth was set to a limit of 2 TB which is far greater than is available on the disk. Is it possible that the autogrowth was failing and locking up the tables causing the SPs that write to the tables to timeout?
I shrunk the log file to a more appropriate size and adjusted the autogrowth to a more realistic maximum. Hopefully that will do the trick.
The SPs in question also make liberal use of the temp DB. Its currently set to the default setting of 10% and unrestricted growth but I dont think this should cause a problem.
Thanks for all the help and suggestions. I'll let you know if this has not solved the problem.
Cheers,:-)
April 21, 2011 at 11:46 pm
you would have walked a safer path just posting the requested info !
Your solution may have worked, but might have been avoidable.
If I'm correct 2TB is the default max size for log files. Chances are the file growth is still by a percentage ( default) and it (the engine) hasn't been able to extend in the timeout timespan.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 22, 2011 at 5:28 am
If your log is growing like crazy, are you in FULL recovery and if so, are you running log backups. Having databases in FULL recovery and not running log backups is a common issue in this area.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 22, 2011 at 6:25 am
tim.keen (4/21/2011)
I think I may have finally figured it out. When I checked the log file settings I noticed that autogrowth was set to a limit of 2 TB which is far greater than is available on the disk. Is it possible that the autogrowth was failing and locking up the tables causing the SPs that write to the tables to timeout?
Not at all.
The 2TB is just the max size that a log file can ever reach, it's not going to make it grow to that size. If autogrow failed, it'd write to the error log.
It's the autogrow increment that you need to be concerned about, not the max size.
I shrunk the log file to a more appropriate size and adjusted the autogrowth to a more realistic maximum. Hopefully that will do the trick.
Set the max back to the default, unless there's a really good reason, the files should not be limited in size.
When you say 'appropriate size', is that the size the log usually is during regular operation? If not, you should grow it to that size, otherwise it'll grow by itself.
The SPs in question also make liberal use of the temp DB. Its currently set to the default setting of 10% and unrestricted growth but I dont think this should cause a problem.
Bad setting.
TempDB should be set to a reasonable default size (depending how big it needs to be) and a fixed growth (not a %) so that growth is always the same amount and time.
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
April 22, 2011 at 6:41 am
I also think it maybe more to do with the TempDB not able to extend....as you have mentioned that a lot of temp tables are used..
and max size of the log file does not look like the possible problem here...the increment size can be..it is said that it is advisable to keep the increment in MB rather than %...maybe of a little higher size to enable one time growth of good size...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply