Why @@servername is null

[aalmeida]

Today I find out that the Select @@ServerName was returning null, went to Select * from sysservers and sure enogh the srvid was 1 instead of 0, I run sp_addserver and restart SQL server Service and it is back to normal.
My problem now is, this is the third time it happens in one year, and I have no clue on how to identify the "Bad Boy" on this situation.
How or where to find who or what is causing this anomaly?
Any Clue will be welcome
Thanks AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[kim1]

Hi,

The only thing that come in my head is the default server name at the time of installation has been changed.

I don't know if that could lead you to some point.

Kim

 

[tlbroadbent]

Al,

Sorry, I don't have an answer. I've not encountered this problem and can't understand why the server name would change or disappear.

A common problem is adding a sever but not indicating it is local. However, you say the server name is there after adding but disappears later. Obvously, someone could be running sp_dropserver. How many people can admininster the server? Could someone be doing this unknowingly (or even knowingly) as part of another process? Just guessing! ;-) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[grahamm]

Does this server participate in a replicated environment? We had a similar problem in our replicated environment. If I remember correctly, the replication set up sp's manipulate the sysservers table, and this caused problems... Sorry I couldn't be more specific, but it was about 1 yr ago.

 

[aalmeida]

Well but that's the thing if someone is doing that how can I catch it? could I create an alert? you see some one is doing that and the worst is that I'm the DBA and they are trying to point at me as the one doing that.
This server is a SQL 7 does not have another instance with the same name.
My question is how to catch whos doing it? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[tlbroadbent]

1) You could set up a trace and filter the TextData on sp_addserver, sp_dropserver and sysservers. This might be overkill for such an infrequent problem.

2) You might be able to extract info from the transaction log using DBCC LOG in a stored procedure that is scheduled to run regularly and check if the name is still correct. DBCC LOG is undocumented. Read about it at one of these links.

http://www.swynk.com/friends/achigrik/DBCC2000.asp#part_2_8

http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp

3) There are also third party tools for auditing SQL Server. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.