SQL server 2000 always hang - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL server 2000 always hang

Author

Topic

azri5979
Starting Member

6 Posts

Posted - 2014-12-02 : 22:02:49

Dear admin,

First i dont know where to place this issue ( Many thanks in advance if you can put me in the right channel).

I'm actually new in Microsoft SQL. FYI, my company is using MSSQL Server 2000 enterprise. My SQL server intermittent hang during working hour.
for the temp solution. we just restart the sql services and sql back online. Windows and server are running ok, only sql hang.

fyi i'm using windows server 2003 enterprise.

recently. we just upgrade the sql from 2000 to sql 2014 but the problem still persist.

please help me...

many thanks to those who willing to help me.

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-12-03 : 03:23:24

when you say your SQL server is hanging, do you mean SSMS?

Hema Sunder

azri5979
Starting Member

6 Posts

Posted - 2014-12-03 : 08:26:00

quote:
Originally posted by sunder.bugatha

when you say your SQL server is hanging, do you mean SSMS?

Hema Sunder

I believe sql 2000 have no ssms.. during problem
- i'm still able to open sql enterprise manager.
- telnet to port 1433... ok
- the problem can be happened in anytime ( no specific time).. sometime once a day.. sometime twice a day.. sometime not happened for 2 to 3 days.

the problem only application from client got error.. server not responding.

- everything will back online after restart the sql services.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 12:11:30

Sounds like out-of-date statistics, bad execution plans, blocking or a combination of those. Restarting SQL Server is not a solution and could cause extended downtime if a large transaction was running when SQL was restarted.

When the issue happens again, check for blocking. If there's no blocking, proceed to the next step. Identify the slow queries via DMV queries, Extended events, Profiler, etc. Recompile problematic stored procs. Update stats on impacted tables. Etc. But whatever you do, don't restart SQL. That is not a solution to this problem, even if it is resolving the issue temporarily. You need to troubleshoot this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

azri5979
Starting Member

6 Posts

Posted - 2014-12-03 : 21:38:49

Thank tkizer,

Yes, restart the sql services actually is my last option, FYI, i'm using quest sql spotlinght to monitor sql process/ transaction.. during sql hang... sql spotlight detected ( refer to below sql process). this is the lead of the blocking process, after killed all the victim process are gone from the blocking screen.

SPID Wait Time Type Command SQL User CPU I/O Host Name Status Blocking SPID Session SQL
94 0 MISCELLANEOUS AWAITING COMMAND appusr 16 2 sleeping and blocking 0 sp_unprepare;1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 21:48:46

Next time it occurs, run DBCC INPUTBUFFER(spidgoeshere) for the lead blocker to get the query it is running. Then figure out why it was blocking for so long.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 21:49:50

Oh I think "sp_unprepare" is the input buffer. Unfortunately that makes it harder as that's from a prepared statement, where the queries are in the application and not in a stored procedure. Run a Profiler trace to get the actual queries.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

azri5979
Starting Member

6 Posts

Posted - 2014-12-04 : 02:50:55

dear all,

how about "cursor cose on commit" if i turn it on. is there any performance issue that can help my problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-04 : 11:58:28

Why?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-12-05 : 06:32:32

Can you provide detailed logs when you face the issue. Also please check auto close for databases are set to false. Do you use Virtual machine and is there snapshot backup going on virtual machine. If so that can also cause it to hang. I am just giving you reasons to check.

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

azri5979
Starting Member

6 Posts

Posted - 2014-12-10 : 02:15:15

tq shanky ,

already checked on auto close setting (untick). FYI, No virtual machine.

azri5979
Starting Member

6 Posts

Posted - 2014-12-10 : 02:22:56

hi shanky , is there any posible way for me to mail the log file.

many thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources