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
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Server stalled

Author  Topic 

Kristen
Test

22859 Posts
Posted - 2004-11-10 : 07:30:10
Server stalled this morning. Serving web pages from one site was timing out (another site on same IIS/DB was fine).

I ran Nigel's sp_nrinfo SProc in case it would provide some data that someone more knowledgeable than I could interpret. http://www.nigelrivett.net/sp_nrinfo.html

When I ran the "info" version I get a number of

The specified SPID does not process input/output data streams.

and

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

messages, which I assume is OK.

The BLK (Blocking) comamnd is showing nothing.

The ACTIVE output is below (I stored some others like ALL/Sleeping and sp_nrinfo_02 if you need them)

Sorry, it will be a bit wide

spid status       loginame hostname   BlkBy DBName command        CPUTime DiskIO LastBatch               ProgramName                    spid buffercmd
---- ------------ -------- ---------- ----- ------ -------------- ------ ------- ----------------------- ------------------------------ ---- -----------------------
150  sleeping     IIS      MyServer         WEB    UPDATE         156    0       2004-11-10 11:30:33.763 Microsoft(R) Windows (R) 2000  150  exec kk_sm_SP_LogError Attemping to log timeout error
147  sleeping     IIS      MyServer         WEB    UPDATE         126    0       2004-11-10 11:30:09.890 Microsoft(R) Windows (R) 2000  147  exec kk_sm_SP_LogError ditto
142  sleeping     IIS      MyServer         WEB    UPDATE         265    0       2004-11-10 11:30:26.950 Microsoft(R) Windows (R) 2000  142  exec kk_sm_SP_LogError ditto
141  sleeping     IIS      MyServer         WEB    UPDATE         313    0       2004-11-10 11:30:17.903 Microsoft(R) Windows (R) 2000  141  exec kk_sm_SP_LogError ditto
134  sleeping     IIS      MyServer         WEB    UPDATE         94     0       2004-11-10 11:30:33.780 Microsoft(R) Windows (R) 2000  134  exec kk_sm_SP_LogError ditto
115  sleeping     IIS      MyServer         WEB    UPDATE         265    0       2004-11-10 11:30:38.217 Microsoft(R) Windows (R) 2000  115  rk_SP_PageLogInit;1 Attemping to get data for a web page
114  sleeping     IIS      MyServer         WEB    INSERT         125    0       2004-11-10 11:30:44.810 Microsoft(R) Windows (R) 2000  114  rk_SP_PageLogInit;1
113  sleeping     IIS      MyServer         WEB    INSERT         0      0       2004-11-10 11:30:45.950 Microsoft(R) Windows (R) 2000  113  rk_SP_PageLogInit;1
109  sleeping     IIS      MyServer         WEB    UPDATE         186    0       2004-11-10 11:30:33.763 Microsoft(R) Windows (R) 2000  109  exec kk_sm_SP_LogError ditto
108  sleeping     IIS      MyServer         WEB    UPDATE         436    3       2004-11-10 11:30:28.450 Microsoft(R) Windows (R) 2000  108  exec kk_sm_SP_LogError ditto
102  sleeping     IIS      MyServer         WEB    UPDATE         187    0       2004-11-10 11:30:36.217 Microsoft(R) Windows (R) 2000  102  exec kk_sm_SP_LogError ditto
96   sleeping     IIS      MyServer         WEB    UPDATE         125    0       2004-11-10 11:30:27.577 Microsoft(R) Windows (R) 2000  96   exec kk_sm_SP_LogError ditto
82   runnable     IIS      MyServer         WEB    INSERT         187    1       2004-11-10 11:30:30.700 Microsoft(R) Windows (R) 2000  82   rk_SP_PageLogInit;1
76   sleeping     IIS      MyServer         WEB    UPDATE         218    0       2004-11-10 11:30:37.840 Microsoft(R) Windows (R) 2000  76   rk_SP_PageLogInit;1
71   sleeping     IIS      MyServer         WEB    UPDATE         204    0       2004-11-10 11:30:23.153 Microsoft(R) Windows (R) 2000  71   exec kk_SP_LOC_PNF_404Handler  @404URL='404;http://www.MyDomain.com/SomeDuffURL.gif'
59   sleeping     IIS      MyServer         WEB    INSERT         16     0       2004-11-10 11:30:44.997 Microsoft(R) Windows (R) 2000  59   rk_SP_PageLogInit;1
57   sleeping     IIS      MyServer         WEB    INSERT         0      0       2004-11-10 11:30:41.093 Microsoft(R) Windows (R) 2000  57   rk_SP_PageLogInit;1
55   sleeping     IIS      MyServer         WEB    UPDATE         141    0       2004-11-10 11:30:37.480 Microsoft(R) Windows (R) 2000  55   rk_SP_PageLogInit;1
53   runnable     me       MyPC             ADMIN  SELECT         5375   329     2004-11-10 11:30:03.937 SQL Query Analyzer             53   exec sp_nrinfo 'active', 'info'||||
52   sleeping     IIS      MyServer         WEB    INSERT         31     0       2004-11-10 11:30:31.857 Microsoft(R) Windows (R) 2000  52   rk_SP_PageLogInit;1
51   sleeping     SQLAdmin MyServer         WEB    UPDATE         79     0       2004-11-10 11:30:00.420 SQLAgent - TSQL JobStep (Job 0 51   EXEC xx_SP_HourlyUpdate
17   background   sa                        WEB    GHOST CLEANUP  766    1808    2004-11-08 08:01:35.827                                17   NULL
15   background   sa                        master TASK MANAGER   0      0       2004-11-08 08:01:35.827                                15   NULL
14   background   sa                        master TASK MANAGER   0      0       2004-11-08 08:01:35.827                                14   NULL
13   background   sa                        master TASK MANAGER   0      124     2004-11-08 08:01:35.827                                13   NULL
12   background   sa                        master TASK MANAGER   0      0       2004-11-08 08:01:35.827                                12   NULL
11   background   sa                        master TASK MANAGER   0      966     2004-11-08 08:01:35.827                                11   NULL
10   background   sa                        master TASK MANAGER   0      0       2004-11-08 08:01:35.827                                10   NULL
9    background   sa                        master TASK MANAGER   0      0       2004-11-08 08:01:35.827                                9    NULL
8    background   sa                        master TASK MANAGER   0      405     2004-11-08 08:01:35.827                                8    NULL
6    background   sa                        master TASK MANAGER   0      0       2004-11-08 08:01:35.827                                6    NULL
5    background   sa                        master TASK MANAGER   0      1162    2004-11-08 08:01:35.827                                5    NULL
4    background   sa                        ADMIN  LOCK MONITOR   531    0       2004-11-08 08:01:35.827                                4    
3    background   sa                        master SIGNAL HANDLER 31     0       2004-11-08 08:01:35.827                                3    
2    sleeping     sa                        ADMIN  LOG WRITER     247969 0       2004-11-08 08:01:35.827                                2    
1    background   sa                        ADMIN  LAZY WRITER    797    0       2004-11-08 08:01:35.827                                1

Kristen

Kristen
Test

22859 Posts
Posted - 2004-11-10 : 14:08:44
<bump/>
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-11 : 13:55:43
It happened again (or something very much like it!)

I turned on the PerfMon trace for a while before restarting SQL and IIS
http://www.fbureau.co.uk/kbm/PerfMon041111_000003.blg
which seems to show very high Disk Write activity, and lots of connections. (All of which dip markedly at the point when I restarted SQL and IIS - so its the left half of the data that is possibly of interest)

I also saved the Enterprise Manager Process Info details (TAB delimited)
http://www.fbureau.co.uk/kbm/EM_ProcessInfo_041111b.txt

What's that "GHOST CLEANUP" doing?

The PerfMon is for the same server as I reported on
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42315
for which the BLG file is available at
http://www.fbureau.co.uk/kbm/PerfMon041109.zip

Thanks

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-11-11 : 15:38:38
I am confused by the blg file. Did you stop and start SQL Server while PerfMon was running? That's what it appears at least. Also, the first half of the file show heavy disk utilization but IIS and SQL Server were barely used during that time. Looks like you had something else occurring on the box related to files. Maybe a tape backup or Virus scanner?

Tara
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-12 : 04:34:15
Thanks Tara. Yup, SQL and ISS restarted about half way through the BLG.

There were lots of connections to SQL - I think way WAY more than normal (there are 10 connections just now, whereas during yesterday's problem there were about 100), so I didn't know whether they were creating the disk activity problem, or whether they were waiting patiently for the disk storm to subside.

CPU is running at tiny levels when this happens, and it is not at the same time of day each time - but I'll look into virus scan etc.

Could it be faulty disks? (retrying loads of times, or some other sort of disk housekeeping?)

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-20 : 13:43:46
Ghost Cleanup...


Never knew this....Why would it actually physically delete it?

I don't think DB2 does this. It only marks the rows and pages as deleted...not for a cleanup batch process to clean up

http://www.dbforums.com/t1165952.html



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-21 : 09:22:36
If anyone is interested...

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Has joined the discussion in the link above...



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-06-21 : 13:50:27
Very helpful Brett, thanks for linking back to this after so long.

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-06-22 : 11:02:40
Your welcome.

I just curious as to how many people have seen a ghost before?





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-06-22 : 12:19:23
I've never noticed it.

Tara
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

483 Posts
Posted - 2005-06-22 : 13:13:02
what is really cool is that he is answering other threads at dbforums today it looks like. i am a little hesitant to post over there today

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts
Posted - 2005-06-22 : 16:13:27
quote:
Originally posted by X002548

Ghost Cleanup...


Never knew this....Why would it actually physically delete it?

I don't think DB2 does this. It only marks the rows and pages as deleted...not for a cleanup batch process to clean up

http://www.dbforums.com/t1165952.html



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



We delete them when we no longer need them - no point having them there taking space is there? They're created when row-level locks are used during delete. As soon as the transaction has committed, they're no longer neede. You can stop them being created by using PAGLOCK or TABLOCK hints.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
Go to Top of Page

engel1
Starting Member

1 Post
Posted - 2006-01-04 : 18:33:35
Did anyone manage to fins a way to avoid / expedite "Ghost Cleanup" please?

quote:
Originally posted by X002548

Ghost Cleanup...


Never knew this....Why would it actually physically delete it?

I don't think DB2 does this. It only marks the rows and pages as deleted...not for a cleanup batch process to clean up

http://www.dbforums.com/t1165952.html



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site