SQL Server Performance Forum – Threads Archive

Stopping SQL service Hangs

There are 2 instances of SQL 2000 on a server. The main instance is fine, the other has occasional problems. Generally what I do is check the intranet program that uses the problematic instance, and if there is a problem, I stop all of the program related services, and then the sql service (this instance only). Then I restart everything and that usually fixes the problem. Today I got to the step where I stop the SQL service (in the services screen or in EM) and the service is stuck in ‘stopping’ status. I have no way to do anything with it – stopping or restarting or starting is not available. In EM, the instance appears stopped. In task manager, I can see 2 instances of sqlservr, but I cannot tell which one is which to try to stop it there. That also rules out trying to stop it using the kill command in DOS, since I am not sure which PID is the one to use. At this point, the only choice is to reboot the server, but that is not an option during the workday. Fortunately, this program is used rarely – but today probably 50 people will want to use it. Calling the vendor is not an option since their support is horrendous. Any other ideas?
Thanks!!
Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

Why do you have 2 instances? Tom Pullen
DBA, Oxfam GB

Thomas – sorry to not have answered earlier, I did not get notice that I had a response. 2 instances were not by my design, but the design of the software using that SQL server. This has been resolved, simply by waiting it out. The service finally went from "stopping" to, "stopped." Thanks! Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

What is your hardware configuration?. Both instances same sp?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi Luis, Yes, both have same sp. Configuration? Do you mean the hw, or do you mean sql server? Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

HW. Just to know capabilities for 2 instances.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

HW – Too much info is better then too little! Sorry to make you read soooo much! The available memory is an issue at this moment – I think that the memory used went up when I stopped and restarted the services. System Information report written at: 12/03/2004 03:40:14 PM
[System Information] [ Following are sub-categories of this main category ] [System Summary] ItemValue
OS NameMicrosoft Windows 2000 Server
Version5.0.2195 Service Pack 4 Build 2195
OS ManufacturerMicrosoft Corporation
System NameHGINT10
System ManufacturerCompaq
System ModelProLiant ML530
System TypeX86-based PC
Processorx86 Family 6 Model 8 Stepping 6 GenuineIntel ~996 Mhz
Processorx86 Family 6 Model 8 Stepping 6 GenuineIntel ~996 Mhz
BIOS Version08/04/01
Windows DirectoryC:WINNT
System DirectoryC:WINNTsystem32
Boot DeviceNot Available
LocaleUnited States
User NameHGIDOMAINadministrator
Time ZoneEastern Standard Time
Total Physical Memory2,227,740 KB
Available Physical Memory248,616 KB
Total Virtual Memory7,884,404 KB
Available Virtual Memory3,796,016 KB
Page File Space5,656,664 KB
Page FileC

agefile.sys
Page FileD

agefile.sys
Page FileE

agefile.sys [Storage] [ Following are sub-categories of this main category ] [Drives]
DriveC:
DescriptionLocal Fixed Disk
CompressedFalse
File SystemNTFS
Size3.91 GB (4,194,627,584 bytes)
Free Space1.25 GB (1,347,198,976 bytes)
Volume Name
Volume Serial NumberD0AC20FE
PartitionDisk #0, Partition #1
Partition Size3.91 GB (4,194,631,680 bytes)
Starting Offset37601280 bytes
Drive DescriptionDisk drive
Drive Manufacturer(Standard disk drives)
Drive ModelCOMPAQ LOGICAL VOLUME SCSI Disk Device
Drive BytesPerSector512
Drive MediaLoadedTrue
Drive MediaTypeFixed hard disk media
Drive Partitions4
Drive SCSIBus4
Drive SCSILogicalUnit0
Drive SCSIPort4
Drive SCSITargetId0
Drive SectorsPerTrack32
Drive Size18207375360 bytes
Drive TotalCylinders4358
Drive TotalSectors35561280
Drive TotalTracks1111290
Drive TracksPerCylinder255 DriveD:
DescriptionLocal Fixed Disk
CompressedFalse
File SystemNTFS
Size13.01 GB (13,970,960,384 bytes)
Free Space9.96 GB (10,693,488,640 bytes)
Volume NameLocal Disk
Volume Serial Number0C5A6D4A
PartitionDisk #0, Partition #2
Partition Size13.01 GB (13,970,964,480 bytes)
Starting OffsetNot Available
Drive DescriptionDisk drive
Drive Manufacturer(Standard disk drives)
Drive ModelCOMPAQ LOGICAL VOLUME SCSI Disk Device
Drive BytesPerSector512
Drive MediaLoadedTrue
Drive MediaTypeFixed hard disk media
Drive Partitions4
Drive SCSIBus4
Drive SCSILogicalUnit0
Drive SCSIPort4
Drive SCSITargetId0
Drive SectorsPerTrack32
Drive Size18207375360 bytes
Drive TotalCylinders4358
Drive TotalSectors35561280
Drive TotalTracks1111290
Drive TracksPerCylinder255 DriveE:
DescriptionLocal Fixed Disk
CompressedFalse
File SystemNTFS
Size135.67 GB (145,674,465,280 bytes)
Free Space39.15 GB (42,032,173,056 bytes)
Volume Name
Volume Serial Number905E70A0
PartitionDisk #1, Partition #0
Partition Size135.67 GB (145,675,698,176 bytes)
Starting Offset16384 bytes
Drive DescriptionDisk drive
Drive Manufacturer(Standard disk drives)
Drive ModelCOMPAQ LOGICAL VOLUME SCSI Disk Device
Drive BytesPerSector512
Drive MediaLoadedTrue
Drive MediaTypeFixed hard disk media
Drive Partitions1
Drive SCSIBus4
Drive SCSILogicalUnit0
Drive SCSIPort4
Drive SCSITargetId1
Drive SectorsPerTrack32
Drive Size145675714560 bytes
Drive TotalCylinders34868
Drive TotalSectors284522880
Drive TotalTracks8891340
Drive TracksPerCylinder255
[Summary] ItemValue
Page Refresh TypeAutomatic
Temporary Internet Files FolderC

ocuments and SettingskritcheyLocal SettingsTemporary Internet Files
Total Disk Space4000 MB
Available Disk Space1284 MB
Maximum Cache Size125 MB
Available Cache Size125 MB Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten

A shot in the dark: May be you are lack of space in C:. Tempdb is in C<img src=’/community/emoticons/emotion-7.gif’ alt=’:?’ />, if yes I think this is the problem. One thing to test (if my think is OK), is to reduce pagefile on C: and see what’s goin on. HTH Luis Martin Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>