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:
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.