Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cybernig
Starting Member
3 Posts |
 Posted - 2007-10-17 : 07:03:43
|
| Hi.We have problems with our application in that it is occasionally hanging. I suspect that there may be a locking issue but would expect to find some record of locking problems in the error logs.The error logs contain system start up information, database backup information but precious few errors.Admittedly, there's not much error handling in the application but I would expect database problems such as locking to be recorded.Can anyone advise? |
|
|
AnimalMagic
Starting Member
28 Posts |
Posted - 2007-10-17 : 07:11:36
|
| try running...select * from master..sysprocesses where blocked > 0 ...on the database in question and see if there are any locks with large wait times. If its not happening all the time try and see if there is a pattern occuring as to when it does happen (maybe using profiler etc)You could schedule the above to run and maybe input into a table, you may be able to see which SPIDs are causing the locks. |
 |
|
|
cybernig
Starting Member
3 Posts |
Posted - 2007-10-17 : 07:45:38
|
| Thanks AnimalMagic...Its finding the pattern that's the difficulty.I can't see us running trace (in PROFILER) for any length of time - it's just capturing too much infoand we may get our problems once a week.I had hoped that locking problems were logged in the Error log. Is there any way to configureSQL Server to ensure deadlocks (for example) are captured in the Error logs? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 08:02:17
|
| "it's just capturing too much info and we may get our problems once a week"I think you can have it start a new file based on filesize ...... then perhaps you could have something delete "old" files after, say, 2 or 3 hours. Then provided you STOP that process immediately after a repeat occurrence you'll have just the recent log files available.Dunno if that helps at all though ...Kristen |
|
|
|
AnimalMagic
Starting Member
28 Posts |
Posted - 2007-10-17 : 08:05:35
|
| you can do it in sql 2000 pretty easily, you can amend sysmessages (i think deadlock is 1205) and specify the with log.sp_altermessage @message_id = 1205, @parameter = 'with_log', @parameter_value = 'true'in 2005 its a bit more difficult as you cant amend the system defined messagesThere is an article on it here http://www.sqlservercentral.com/articles/Administration/3243/ |
|
|
|
cybernig
Starting Member
3 Posts |
Posted - 2007-10-17 : 08:59:44
|
Thanks Guys.I had a read of the article.Seems that the deadlock will write to the Windows Event log rather than the SQL Server ERRORLOG and there are problems for us with this solution.a. Our SQL Server is hosted and we don't have access to the filesystem that the database runs on. So I can't see how I can see the Windows Event log?b. The solution in the article discusses the use of alerts - we can't get email notification because our hosting provider hasnot set up mail capability.c. I don't know whether the logged record will include details of the deadlocked resources.AS far as running a trace goes, the other downside of the trace is application performance degradation.So thanks for the suggestions..... but I'm still looking!Regards |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-20 : 01:24:34
|
| Have to turn on couple of trace flag to log deadlock details in sql server log, looks like you need to work with hosting company to troubleshoot the issue. |
|
|
|
|
|
|
SQL Server 2000 Error Logs