@echo off rem ******************************************************** rem Maintenance for a database rem ******************************************************** rem Do some basic stuff to validate the command line rem verify that two arguments are given if "%1" == "" goto :badcall if "%3" == "" goto :badcall if "%4" == "" goto :badcall echo SQL SERVER DATABASE MAINTENANCE echo database: %4 echo server: %3 echo: rem Capture the path of the caller. Later we will send control rem back to that directory rem create a two line bach file that sets an environment var to rem the second command line arg echo @echo off> directory.bat echo set olddir=%%2>> directory.bat rem create a one line batch file that contains 'Directory of ' dir | find "Directory"> go.bat rem go.bat calls Directory.bat call go rem cleanup if exist directory.bat del directory.bat if exist go.bat del go.bat rem create a batch file to handle the outfile delete/archive rem you can define the aging level for you maintenance output files here. rem if you want to keep three days instead of two, add logic for .002 rem the archive.bat is recreated each time this script runs echo find "%%current%%.001" dir.lst> archive.bat echo if errorlevel 1 goto :no_001>> archive.bat echo del %%current%%.001>> archive.bat echo :no_001>> archive.bat echo find "%%current%%.out" dir.lst>> archive.bat echo if errorlevel 1 goto :end>> archive.bat echo ren %%current%%.out %%current%%.001>> archive.bat echo :end>> archive.bat rem set environment variables for the output targets rem %~dp0 returns the drive and path from the cmd line arg 0 rem %~n0 returns the exe name from cmd line arg 0 set outpath=%~dp0%4\ set outdir=%~dp0%4 set outfile=%~n0.out rem make sure the output directory exists if exist %outdir% goto :dir_exists md %outdir% rem point this thing at the output directory :dir_exists cd %outdir% rem figure out what day of the week it is and stuff it in an environment variable date /T>currdate.txt set currdate=Mon find "%currdate%" currdate.txt>nul if errorlevel 1 set currdate=Tue find "%currdate%" currdate.txt>nul if errorlevel 1 set currdate=Wed find "%currdate%" currdate.txt>nul if errorlevel 1 set currdate=Thu find "%currdate%" currdate.txt>nul if errorlevel 1 set currdate=Fri find "%currdate%" currdate.txt>nul if errorlevel 1 set currdate=Sat find "%currdate%" currdate.txt>nul if errorlevel 1 set currdate=Sun rem dump the output directory to a file for use in delete/archive rem of the maintenance output files dir>dir.lst rem initialize an outfile for this run set current=maint call %olddir%\archive>nul echo Daily Maintenance for database %4 on %3 started>%outfile% date /T>>%outfile% time /T>>%outfile% echo Run by %USERNAME% from %COMPUTERNAME%>>%outfile% echo:>>%outfile% if %4==master goto :master_db rem Update statistics frequently. Less frequently, yet regularly, rebuild rem indexes. Rebuilding indexes includes an update to the statistics page. rem Here Friday is rebuild day, other days are update statistics days. if %currdate%==Fri goto :weekly rem see the index maintenance document at this web site for mysp_updatestats set current=mysp_updatestats echo %current% %4 call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "%current%" -o %current%.out -e>>%outfile% if errorlevel 1 echo A problem with stored procedure %current%. It may not exist in the database. echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% goto :daily :weekly rem see the index maintenance document at this web site for mysp_reindex set current=mysp_reindex echo %current% %4 call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "%current%" -o %current%.out -e>>%outfile% if errorlevel 1 echo A problem with stored procedure %current%. It may not exist in the database. echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% :daily rem Track the growth of your database by putting this info in rem a spread sheet set current=sp_spaceused echo %current% %4 call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "%current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% rem Verify the page frame integrity of the log in this database set current=checktable(syslogs) echo dbcc %current% %4 call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "dbcc %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% set current=textall rem use this only if there is text or image data types in the database rem for faster execution, omit the report detail by running rem dbcc textall(%4,FAST) echo dbcc %current%(%4) call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "dbcc %current%(%4)" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% set current=sp_help_revdatabase rem This will provide an up to the minute create database command rem should you ever need it rem sp_help_revdatabase %4 echo %current% %4 call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "%current% %4" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% :master_db set current=checkdb rem If your really desperate for maintenance time, omit dbcc checkdb and rely rem on dbcc newalloc nightly. Run both as often as possible. rem For faster execution, omit non-clustered index checking by using the following rem dbcc checkdb(%4,no_index) echo dbcc %current%(%4) call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "dbcc %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% set current=checkcatalog echo dbcc %current%(%4) call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "dbcc %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% set current=newalloc rem For faster execution, ommit non-clustered index checking by using the following rem dbcc newalloc (%4, no_index) echo dbcc %current%(%4) call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "dbcc %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% set current=sp_dboption echo %current% %4 call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "%current% %4" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% set current=sp_helpdb echo %current% %4 call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "%current% %4" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% rem Get the data from master that is needed to recreate devices should the master rem database become unuseable. if NOT %4==master goto :user_db set current=sysusages echo select * from %current% call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "select * from %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% select complete>>%outfile% time /T>>%outfile% set current=sysdatabases echo select * from %current% call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "select * from %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% select complete>>%outfile% time /T>>%outfile% set current=sysdevices echo select * from %current% call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "select * from %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% select complete>>%outfile% time /T>>%outfile% set current=sp_configure echo %current% call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "%current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% set current=memusage echo dbcc %current% call %olddir%\archive>nul isql -U %1 -P %2 -S %3 -d %4 -Q "dbcc %current%" -o %current%.out>>%outfile% echo:>>%outfile% echo %current% complete>>%outfile% time /T>>%outfile% :user_db rem parse all .out files in the current directory for potential errors findstr "error:" %outpath%*.out>ck%4.out findstr "Msg" %outpath%*.out>>ck%4.out findstr "dbprocess" %outpath%*.out>>ck%4.out echo Review %outpath%ck%4.out echo Investigate all entries in the specified file in the %4 subdirectory echo: echo Maintenance of database %4 complete! echo: echo: echo Review %outpath%ck%4.out>>%outfile% echo Investigate all entries in the specified file in the %4 directory>>%outfile% echo:>>%outfile% echo Maintenance of database %4 complete!>>%outfile% goto :end :badcall echo: echo usage: maint [User] [Password] [Server] [Database] echo: echo where echo [User] is sa echo [Password] is the sa password echo [Server] is the SQL Server to be maintained echo [Database] is the databse to be maintained echo: :end cd %olddir% del archive.bat