November 18, 2008 at 2:58 am
Hi all
I am just new to this sql thing..Im a Juinior DBA for few months now. One of my client 05 Database is freezing which happens to be the major DB in the Server. They called me dis morning that when they use some application it gives a runtime error,Then they tried using the app on anthr Database it worked fine but in this database still get errors, The users reaports that when he tries to save it just freeze and show a blank screen. I did check the size of the Log File it was gttng full,so i back it up and Truncate and shrink.. But still gives problem...Does any one has an idea what i should check?
Plaese Help!
November 18, 2008 at 3:46 am
Hello,
Try executing an sp_who2 at them time when a “freeze” occurs and look if there are any sessions blocking each other.
Also, do you have any entries in either the Windows Event Logs or SQL Server Log, at the time when the error occurred?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 18, 2008 at 4:12 am
In adition to the above, you can also caputre SQL Profiler from remote machine to capture the activity on the affected server which help you to narrow down the problem 🙂
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 18, 2008 at 4:55 am
Hi Zoma
What run time error is it throwing out, can you post the error 🙂
November 19, 2008 at 8:56 pm
Basically you need to troubleshoot by starting wide and narrowing in to what the problem may be.
Start with looking the sql server error logs for such issues as space running out or other db failures/errors.
Run sp_who2 as suggested (or look in activity monitor) to see if any spids are blocking others. look at where they are coming from and how long they have been there. look for open transactions that have been there for a while.
Capture code if posible of offending spid for further analysis. (eg to run through optimiser or show plan.)
If you find something causing an issue one option is to kill it , however you need to consider implications of doing this from an app / user POV and if your management processes allow for this.
If you still can't identify the problem, use sql profiler to capture activity on DB. you may need to set it to run when the system is ok and hope it is running when the issue occurs.
Remember BOL is your friend and also read blogs/ articles for other ideas.
November 23, 2008 at 3:17 pm
I'd go with John Marsh's suggestion. If you see processes blocking each other, then that's probably your culprit. I had this happen on a SQL 2000 system where some know-best idiot changed the default memory settings (Min/Max). The result was deal-locks. When I changed the settings so that SQL Server used whatever it wanted and then stopped and re-started SQL Server, all of the dead lock problems vanished. Apparently the lock manager suffered when SQL Server ran out of memory.
I don't know if this has been improved in 2005 or not.
Todd Fifield
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply