Here is a routine for a DOS batch file that checks tempdb. See the maintenance batch script for an example of validating command line arguments. 


  echo Verify tempdb size
 isql -U%1 -P%2 -S%3 -d%4 -i cktempdb.sql -o %5\cktempdb.out -n
if NOT ERRORLEVEL 1 goto :tempdb_checked  
  echo:
  echo An error occurred while checking the size of tempdb!
  goto :end 
:tempdb_checked
 find "tempdb is OK" %5\cktempdb.out
if NOT ERRORLEVEL 1 goto :verified_tempdb
  echo:
  echo The tempdb database is less than 50M in size.
  goto :end 
:verified_tempdb

echo hurray!

:end
Here is the isql script the batch file calls. 
PRINT "Check the size of tempdb"
SELECT getdate()
GO
SET NOCOUNT ON
DECLARE @size VARCHAR(20),
        @message VARCHAR(50) 

SELECT @size = (SELECT STR(SUM(CONVERT(DEC(15),u.size))*(SELECT low 
                                    FROM master.dbo.spt_values 
                                    WHERE type = 'E' 
                                    AND number = 1)/1048576,10,2) 
                 FROM master.dbo.sysdatabases d, master.dbo.sysusages u 
                 WHERE d.dbid = u.dbid 
                 AND d.name = 'tempdb')
SELECT @message = "tempdb is " + LTRIM(@size) + " M-bytes"
PRINT @message
IF CONVERT(FLOAT,@size) < 50
  PRINT  "tempdb is less than 50 Meg!"
ELSE
  PRINT "tempdb is OK"
?bill's home