SQL: sp_lock & sp_who2

Every morning during peak hours we get exceptions from our ASP.NET application saying it cannot find a table because the DB doesn't exist, network latency, or because the network name is no longer available.

While we're looking into other possible reasons for this, we wanted to know if there are any SQL server diagnostics that you can suggest. I tried running sp_lock and sp_who2, but it only shows LIVE information. The problem is that the exceptions only last for a very short period of time.

So what I'm looking for is something like sp_lock & sp_who2, something similar that I can just leave running or something. Then when I get email notifications that exceptions are happening, I can go and look at diagnostical data produced.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You need to intall some monitoring tools like red-gate sql monitor, idera sql doctor etc. or else you can implement this using Extended events in sql server.
pzozulkaAuthor Commented:
No simple stored procedures?
Aneesh RetnakaranDatabase AdministratorCommented:
unfortunately no.. if this is a temp situation, install the trial version, that should run for 14-30 days
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Eugene ZCommented:
try to use sql profiler


also based on your post
looks like your server needs some extra power

But you are going in the right direction:
to see what it is running

also check if you have regular DB maint jobs running there: reindex; update stats; etc

Check sql errors; configuration (max \min memory); MaxDop , etc

check Windows EV logs as well

Post\chck your server os\sql version \edition \sp

make sure you have latest ServPacks\pathes\firmware
No Antivirus is running during pick hours
check Taksmanager to see what is consuming  resources

try to run perfmon


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pzozulkaAuthor Commented:
Thanks for your suggestions. I don't think our server needs any extra power. And in fact, we believe the problem started because we added extra CPUs. There were no exceptions when this server (virtual machine) had only 2 processors. It started when we added 1 processor, and actually got worse when we added a 2nd processor to a total of 4 processors.

I never thought that a machine with more processors could be harmful, but then read this:
Eugene ZCommented:
it is strange

do you see any Event Viewer /VMware errors?
what is your VM ware server CPU/Memory RAM = page
what is set for Sql server?

how is your IO doing? how fast disks? NIC?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.