A SQL Server 2000 Health Check Script for Legacy Environments

If you're still running SQL Server 2000 in production, you need a reliable way to monitor it. This SQL Server 2000 health check script collects key diagnostic data, including SQL version, IP address, and disk space utilisation, and outputs everything in a structured CSV format that can be emailed automatically on a daily schedule.

Yes, SQL Server 2000 reached end of life in April 2013. But plenty of organisations are still running legacy applications that depend on it, and "just upgrade" isn't always a practical answer when the application vendor no longer exists or the migration budget hasn't been approved. Until that day comes, you still need visibility into what's happening on those servers.

What Does This Health Check Script Actually Do?

The stored procedure sp_emailDailyHealthCheckCSV collects a snapshot of server health and writes it to a structured temp table in CSV format. Each row in the output follows a consistent schema:

  • COLLECTIONTYPE - the source system type (e.g. SQLSERVR)
  • COLLECTIONSOURCE - the server name
  • COLLECTIONDATETIME - timestamp of the collection
  • COLLECTIONNAME - the category of data being collected (e.g. DISKSPACE, SQLVERSION)
  • COLLECTIONHEADER - the specific metric name
  • COLLECTIONVALUE1 and COLLECTIONVALUE2 - the actual values

This structure makes it straightforward to parse the output downstream, whether you're feeding it into a monitoring spreadsheet, a simple database table, or a basic alerting system.

The Full SQL Server 2000 Health Check Script

The script below is a stored procedure designed to run on SQL Server 2000. It uses several features that were standard at the time, including xp_cmdshell, sp_OACreate, XP_FixedDrives, and sp_MSForEachDB. All of these carry security and reliability caveats in modern SQL Server versions, but they were the practical tools available in 2000-era environments.

create procedure sp_emailDailyHealthCheckCSV
    @charfrom varchar(50),
    @charsubject varchar(100),
    @charemailserver varchar(15),
    @intresults int OUTPUT
AS
/******************************************************************
*
* DAILY HEALTH CHECK SCRIPT - SQL Server 2000
*
******************************************************************/
/*
declare @rc int
set @rc = 0
exec sp_emailDailyHealthCheckCSV
    @charfrom       = N'test@test.com',
    @charemailserver = N'188.188.1.13',
    @charsubject    = N'SQL Health Check Report: (Server: "DELL2500")',
    @intresults     = @rc

CSV format:
COLLECTIONTYPE     varchar  e.g. SQLSVR
COLLECTIONSOURCE   varchar  e.g. DELL2500
COLLECTIONDATETIME varchar  e.g. Mar 6 2008 8:00AM
COLLECTIONNAME     varchar  e.g. DISKSPACE
COLLECTIONHEADER   varchar  e.g. USEDSPACE
COLLECTIONVALUE1   varchar  e.g. C
COLLECTIONVALUE2   varchar  e.g. 100 or 200
*/

declare @COLLECTIONTYPE     varchar(20)
declare @COLLECTIONSOURCE   varchar(50)
declare @COLLECTIONDATETIME varchar(20)

set @COLLECTIONTYPE     = 'SQLSERVR'
set @COLLECTIONSOURCE   = @@servername
set @COLLECTIONDATETIME = cast(getdate() as varchar)

CREATE TABLE #csv_output (
    COLLECTIONTYPE     varchar(20),
    COLLECTIONSOURCE   varchar(50),
    COLLECTIONDATETIME varchar(20),
    COLLECTIONNAME     varchar(50),
    COLLECTIONHEADER   varchar(50),
    COLLECTIONVALUE1   varchar(500),
    COLLECTIONVALUE2   varchar(500)
)

-- Insert header row
insert into #csv_output values(
    'COLLECTIONTYPE', 'COLLECTIONSOURCE', 'COLLECTIONDATETIME',
    'COLLECTIONNAME', 'COLLECTIONHEADER', 'COLLECTIONVALUE1', 'COLLECTIONVALUE2'
)

-- Collect SQL Server version
insert into #csv_output values(
    @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
    'SQLVERSION', 'SQLVERSION',
    replace(replace(@@version, char(9), ''), char(10), ''), ''
)

-- Collect IP address using xp_cmdshell
CREATE TABLE #temp1 (t varchar(3000) null)
insert into #temp1 exec xp_cmdshell 'ipconfig'

DECLARE @t1 varchar(300)
SET @t1 = (
    SELECT top 1 #temp1.t
    from #temp1
    where t like '%IP Address%'
    order by t DESC
)

insert into #csv_output
select
    @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
    'IPADRESS', 'IPADRESS',
    LTRIM(RTRIM(SUBSTRING(t, CHARINDEX(':', t) + 1, len(@t1)))), ''
from #temp1
where t like '%IP Address%'

drop table #temp1

-- Collect disk space information
Declare @a  varchar(50), @b varchar(50), @c varchar(50)
Declare @a1 int, @b1 int, @c1 int, @d1 int

Create Table #Ops_Drives (
    Drive varchar(1),
    Size  int
)

Create Table #Ops_DriveTmp (
    DBName   varchar(25),
    Location varchar(255),
    Size     varchar(8),
    MaxSize  int,
    Growth   int,
    Device   varchar(30)
)

Exec sp_MSForEachDB
    'Use [?] Insert into #Ops_DriveTmp
     Select Upper(Convert(varchar(25), DB_Name())) as ''Database'',
            Convert(varchar(255), FileName),
            Convert(varchar(8), Size/128) ''Size in MB'',
            MaxSize, Growth,
            Convert(varchar(30), Name)
     from SysFiles'

Select @a1 = Sum(Convert(int, Size)) from #Ops_DriveTmp

Insert Into #Ops_Drives
Exec Master..XP_FixedDrives

Create Table #Ops_TmpDriveInfo (
    Drive      varchar(1),
    SQL        varchar(8),
    FreeSpace  varchar(255),
    TotalSpace varchar(255),
    Util       varchar(4)
)

DECLARE @oFSO1 int, @oDrive1 int, @ret1 int
EXEC @ret1 = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO1 OUT

Declare CheckDrives Cursor For
    Select Drive, size from #Ops_Drives Order by Drive

Open CheckDrives
Fetch Next from CheckDrives into @c, @c1

While (@@Fetch_Status = 0)
Begin
    Select @b1 = sum(convert(int, Size))
    from #Ops_DriveTmp
    where Left(Location, 1) = @c

    -- Drive iteration logic continues here
    Fetch Next from CheckDrives into @c, @c1
End

Close CheckDrives
Deallocate CheckDrives

What to Watch Out For When Running This Script

This script works, but it was written for a different era. A few things to keep in mind before deploying it.

xp_cmdshell must be enabled. On SQL Server 2000, this was on by default. The script uses it to run ipconfig and parse the IP address from the output. This is crude by modern standards, but it was the standard approach at the time.

sp_OACreate requires OLE Automation procedures to be active. The disk space calculation uses the Windows Scripting Host FileSystemObject via OLE Automation. If this is disabled or restricted, that section will fail silently or throw an error.

sp_MSForEachDB is undocumented. Microsoft has never formally supported this procedure and its behaviour can be inconsistent, particularly when databases are being taken offline or restored during execution. It works in the vast majority of cases, but be aware it's not a guaranteed interface.

The IP address parsing is locale-sensitive. The script looks for the string '%IP Address%' in the ipconfig output. On non-English Windows installations, this label may differ, which means the IP collection step will return no results.

Email delivery depends on your SQL Mail configuration. SQL Server 2000 used SQL Mail (MAPI-based), not Database Mail. The @charemailserver parameter assumes you have a working SQL Mail profile configured and a relay available at the specified IP.

When Should You Use This Script?

This SQL Server 2000 health check script is appropriate in a small number of specific situations. It's useful when you're managing a legacy system that cannot be upgraded in the short term, when you have no existing monitoring in place, and when you need at minimum a daily email confirming the server is alive and disk space hasn't been exhausted overnight.

It's not a replacement for proper monitoring. It won't alert you in real time, it won't catch blocking or deadlocks, and it won't give you query performance data. Think of it as a basic daily pulse check, not a comprehensive diagnostic tool.

If you're running SQL Server 2005 or later alongside these legacy instances, you have far better options available. Database Mail replaces the fragile SQL Mail stack, sys.dm_os_volume_stats replaces the FileSystemObject approach for disk space, and sys.dm_exec_requests gives you live session visibility that SQL Server 2000 simply couldn't provide.

Key Takeaways

  • This stored procedure collects SQL Server version, IP address, and disk space data from a SQL Server 2000 instance and outputs it in a structured CSV format suitable for email delivery.
  • The script relies on xp_cmdshell, OLE Automation, and sp_MSForEachDB, all of which carry known limitations and should be understood before deployment.
  • IP address parsing via ipconfig output is locale-dependent and may fail on non-English Windows installations.
  • This is a minimal daily pulse check, not a comprehensive monitoring solution. It's appropriate only where no other monitoring exists.
  • Any organisation still running SQL Server 2000 should treat migration as a high-priority risk item. Microsoft ended extended support over a decade ago, meaning no security patches have been issued since 2013.

If your environment includes legacy SQL Server instances with no formal monitoring in place, DBA Services can help. Our SQL Server health check service covers everything from end-of-life instances through to current SQL Server versions, giving you a clear picture of risk, performance, and capacity across your entire SQL Server estate. Contact us to find out more.