This is a strange occurance but is happening a lot.
If I go into Management->Current Activity->Process Info
There always seems to be at least one process linked to a table listed in "Locks/Object" that has a "Host" name that is not on the network.
Yet, the process belongs to a computer that is but has a different "Host" name.
How does SQL Server do this? It makes trying to find computers locking tables very difficult when SQL Server is showing the wrong host name this is especially annoying as the "Host" name in question belongs to a computer that has not been on the network in two weeks!
How do I fix this and is there some way from SQL Server of getting the IP address of a listed Process/Host??
The thing that confuses me is why when the server is rebooted on a Friday night and I check the Process Info on Monday there are no process other than the servers, BUT later on in the week existing computers on the network are listed under Process Info as having "host" names of PCs that have not been connected to the server that week!??
So where does it get "Host" names from it certainly isn't the network if it can use names that have not been attached since a reboot...
The host name (at least on SQL 2000 and above) is passed to the server at the time of connection. The connecting machine essentially says 'I am <machine name>'. So either you have some app spoofing machine names, or those machines really did make the connections.
Actually I think I have narrowed the issue down to a possible cause being as in depth study has revealed only two host names are being randomly given to computers by SQL Server.
I use virtual machines which I use to develop software for our business, the two host names SQL Server incorrectly gives to physical machines on the network (this morning I discovered it had given two different physical computers THE SAME HOST NAME despite NT4 correctly listing the real computers network names) are the names of the virtual machines!
So is it the virtualisation software causing SQL Server to believe these machines are connected to the network when they are not and give physical machines at random a host name of the unattached virtual ones?
As NT4 network manager and Windows Explorer correctly show that these virtual machines are connected or not connected to the network but SQL Server seems to get its network connection client names from a different place to the OS!
Interestingly when I went on holiday for a week (and thus no virtual machines were used) when I came back despite being rebooted during that period SQL Server was still saying live process were attached to unattached virtual machines.
ADDITIONAL: if I kill the process attached to the phantom host names it really does kill processes on a physical machine!
No other computers on the network have the same name as the virtual machines as we have a distinct naming convention for network attached machines and the virtual ones have silly names like "LAPTOP".