Ancient SQL 2000 Bug

Recently I’ve been running an automated data collection across a couple of hundred SQL Server instances. I hit an issue today when I ran a particular query on some servers running 8.00.760 (SQL 2000 SP3)

select filename 
from sysaltfiles
where name = 'tempdev'

Not very exciting – it returns the path as expected:

E:\MSSQL\DATA\tempdb.mdf

However when my script ran this slight variation of the query above, I got a really strange result:

declare @tmp varchar(255)
select @tmp =filename
from sysaltfiles
where name = 'tempdev'

print @tmp

E:\MSSQL\DATA\tempdb.mdfft SQL Server\MSSQL\DATA\tempdb.mdf

It turns out that when you move tempdb on a server running this build, there was a bug where it didn’t properly clean out the value stored in sysaltfiles. The original value is overwritten, and a null marks the end of the new string. Looks ok in the results pane, but when you print it, it wreaks havoc on your output.

I doubt anyone is still seriously running this build, but there you go. Old school dirty data.