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 |
|
phrankbooth
Posting Yak Master
162 Posts |
 Posted - 2007-03-29 : 14:39:23
|
| Hello,Wondering if anyone might have a guess about this.I have a small 4 table DB. it's got several stored proc's and it's accessed through .NET to fill it in and get data from it.It's been working just fine. But this morning, while it was doing its thing, I experimented with it by adding then deleting a View (through the View Wizard.)Then later I started noticing that my .Net calls had slowed to a crawl (I hadn't made any code changes)and even making direct queries through Query Analyzer had slowed too.My question is: being that there were no network issues, could the View create/delete have caused the DB to come to a halt or perhaps a table lock-up?While I'm at it, is there anything that I can put in stored proc's or other places to prevent locking issues (if that's what happened here.)I already use Begin/Commit Tran pairs.And sorry if this post doesn't read like a SQL beginner, but believe me, I am.Thanks for your advice!!--PhB |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-03-29 : 14:57:19
|
| No, the view would not do that.did you do sp_who2 to see if there is any blocking?How many sprocs do you have?You could also use sql profiler to see whaat's going on.You could also add sproc logging to a sproc_log table to capture the length of each transactionIs your transaction log set to autogrow and its a small %Have you taken a full backup lately?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
phrankbooth
Posting Yak Master
162 Posts |
Posted - 2007-03-29 : 15:03:26
|
| Thanks for replying!No I did not know about sp_who2, I will keep that in mind. There's a total of 6 SP's.I have not made a backup lately. But would not doing a BKU cause this behaviour?I'll look into your other suggestions.Thanks again!--PhB |
|
|
|
|
|
|
DB lock-up??!!