Server Maintenance - Windows VBS Scripts

Click here to access some vbs server maintenance scripts to help you

1. Defragment your hard drive daily.
2. Run clean manager once a week. 
3. Create CSV files of WinNT accounts
4. etc.,etc.,etc.

Automation is the only way to go! This blog is still in progress but feel free to browse the scripts. If your a programmer you probably won't care about the article as much as the code! I have more VBS scripts embeded in SQL Server store procedures setup to dynamically create the vbs scripts and run them.

Procedures - usp_fs_wmi_defrag_drives_winxp

CREATE PROCEDURE [dbo].[usp_fs_wmi_defrag_drives_winxp]
  @DriveLetter   char(1)=NULL
, @ServerName   varchar(80)=NULL
, @FullPathLogFileName varchar(500)=NULL
, @Execute0Display1Both2 tinyint=0
AS
BEGIN
---------------------------------------------------------------------------
-- Define variables
---------------------------------------------------------------------------
DECLARE
  @cmd      varchar(8000)
, @SingleQuote    CHAR(1)
, @DoubleQuote    CHAR(1)
, @CarriageReturn   CHAR(1)
, @FileSystemTempDirectory varchar(500)
, @retval     integer
, @DatabaseName    varchar(80)
---------------------------------------------------------------------------
-- Initialize variables
---------------------------------------------------------------------------
SET @SingleQuote = CHAR(39)
SET @DoubleQuote = CHAR(34)
SET @CarriageReturn = CHAR(13)
SET @cmd = ''
SET @FileSystemTempDirectory = [dbo].[udf_AppDefault]('FileSystemTempDirectory', 'System',@@SERVERNAME, 'DBATools')
SET @ServerName  = ISNULL(@ServerName , @@SERVERNAME)
SET @DatabaseName = db_name()
SET @FullPathLogFileName = ISNULL(@FullPathLogFileName,
  [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\')
 + [dbo].[udf_getTempName]('usp_fs_wmi_defrag_drives_winxp.log', GetDate(), NULL, 1)
 )
---------------------------------------------------------------------------
-- Setup command string
---------------------------------------------------------------------------
SET @cmd = @cmd  + '
Function isDefragRunning()
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colProcesses = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = ''Dfrgntfs.exe''")
    isDefragRunning = colProcesses.Count
    Set colProcesses = nothing
    Set objWMIService = nothing
End Function

SUB DefragDrives(DriveLetter)
''-------------------------------------------------------------------------------------
'' Declare constants
''-------------------------------------------------------------------------------------
Const ForWriting = 2
Const ForAppending = 8
Dim fso, d, dc, FullPathLogFileName, sTime
FullPathLogFileName = "' + @FullPathLogFileName + '"
''-------------------------------------------------------------------------------------
''
''-------------------------------------------------------------------------------------
On Error Goto 0
''-------------------------------------------------------------------------------------
'' Get objects
''-------------------------------------------------------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
set objLogFile = objFSO.OpenTextFile(FullPathLogFileName, ForWriting, True)
set wshell = createobject("wscript.shell")
if err.number > 0 then msgbox err.description
''-------------------------------------------------------------------------------------
'' Check for defrag already running
''-------------------------------------------------------------------------------------
if isDefragRunning() <> 0 then
 objLogFile.WriteLine "Unable to run defrag.  It is already running."
    objLogFile.WriteLine ""
 objLogFile.close
 wscript.quit
end if
''-------------------------------------------------------------------------------------
'' Defrag drives
''-------------------------------------------------------------------------------------
objLogFile.WriteLine "=====================Begging Defrag================================="
''-------------------------------------------------------------------------------------
'' Init variable
''-------------------------------------------------------------------------------------
strComputer = "."
Set dc = objFSO.Drives
For Each d in dc
   If d.DriveType = 2 Then
      objLogFile.WriteLine "defrag " & d & " -f "
   sTime = "Start Time:" & Now()
      objLogFile.WriteLine sTime
      Return = wShell.Run("defrag " & d & " -f " , 1, TRUE)
   sTime = "End Time:" & Now()
   objLogFile.WriteLine sTime
   End If
Next
objLogFile.close
''-------------------------------------------------------------------------------------
'' Destroy Objects and exit
''-------------------------------------------------------------------------------------
set wShell    = nothing
set objLogFile  = nothing
Set objFSO   = nothing
Set dc    = nothing
END SUB
'
SET @cmd = @cmd  + 'Call DefragDrives(' + ISNULL(CHAR(34) + @DriveLetter + CHAR(34), 'NULL') + ') '
---------------------------------------------------------------------------
-- Execute command
---------------------------------------------------------------------------
--PRINT @cmd
SET @FileSystemTempDirectory =
  [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\')
 + 'usp_fs_wmi_defrag_drives_winxp.vbs'
-- + [dbo].[udf_getTempName]('usp_fs_wmi_defrag_drives.vbs', GetDate(), NULL, 1)
EXEC @retval  = [dbo].[usp_fs_CreateTextFile]
  @FullPathFileNameDestination  = @FileSystemTempDirectory
, @strTextToWriteToFile        = @cmd
, @boolOverwriteFile           = 0
IF @retval = 0
BEGIN
 SET @FileSystemTempDirectory = 'cmd.exe /c ' + @FileSystemTempDirectory
 PRINT @FileSystemTempDirectory
 EXEC @retval     = [dbo].[usp_srvr_xp_cmdshell]
    @cmd      = @FileSystemTempDirectory
  , @ReturnData    = 1
  , @Execute0Display1Both2 = 2
END
RETURN @retval
END

Procedures - usp_fs_wmi_defrag_drives_win2003

CREATE PROCEDURE [dbo].[usp_fs_wmi_defrag_drives_win2003]
  @DriveLetter   char(1)=NULL
, @MachineName   varchar(80)=NULL
, @FullPathLogFileName varchar(500)=NULL
, @Execute0Display1Both2 tinyint=0
AS
BEGIN
---------------------------------------------------------------------------
-- Define variables
---------------------------------------------------------------------------
DECLARE
  @cmd      varchar(8000)
, @SingleQuote    CHAR(1)
, @DoubleQuote    CHAR(1)
, @CarriageReturn   CHAR(1)
, @FileSystemTempDirectory varchar(500)
, @retval     integer
, @DatabaseName    varchar(80)
---------------------------------------------------------------------------
-- Initialize variables
---------------------------------------------------------------------------
SET @SingleQuote = CHAR(39)
SET @DoubleQuote = CHAR(34)
SET @CarriageReturn = CHAR(13)
SET @FileSystemTempDirectory = [dbo].[udf_AppDefault]('FileSystemTempDirectory', 'System',@@SERVERNAME, 'DBATools')
SET @MachineName   = ISNULL(@MachineName , '.')
SET @DatabaseName   = db_name()
SET @FullPathLogFileName = ISNULL(@FullPathLogFileName, [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\') + 'usp_fs_wmi_defrag_drives_win2003.log')
---------------------------------------------------------------------------
-- Setup command string
---------------------------------------------------------------------------
SET @cmd = 'Dim sTime, strComputer 
Const ForWriting = 2
Const ForAppending = 8
Const FullPathLogFileName = '
SET @cmd = @cmd  + @DoubleQuote
SET @cmd = @cmd  + @FullPathLogFileName
SET @cmd = @cmd  + @DoubleQuote + ' ' + @CarriageReturn
SET @cmd = @cmd  + '
strComputer = "' + @MachineName + '"
Function isDefragRunning()
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colProcesses = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = ''Dfrgntfs.exe''")
    isDefragRunning = colProcesses.Count
    Set colProcesses = nothing
    Set objWMIService = nothing
End Function
Set objFSO = CreateObject("Scripting.FileSystemObject")
set objLogFile = objFSO.OpenTextFile(FullPathLogFileName,  ForAppending, True)
if err.number > 0 then msgbox err.description
if isDefragRunning() <> 0 then
 objLogFile.WriteLine "' + @MachineName + ':Unable to run defrag.  It is already running."
    objLogFile.WriteLine ""
 objLogFile.close
 wscript.quit
end if
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colVolumes = objWMIService.ExecQuery ("Select * from Win32_Volume WHERE DriveType=3 '
---------------------------------------------------------------------------
-- Set drive letter
---------------------------------------------------------------------------
IF @DriveLetter IS NOT NULL
BEGIN
 SET @cmd = @cmd  + 'AND Name = '
 SET @cmd = @cmd  + @SingleQuote 
 SET @cmd = @cmd  + @DriveLetter
 SET @cmd = @cmd  + ':\\' 
 SET @cmd = @cmd  + @SingleQuote 
END
---------------------------------------------------------------------------
-- Continue setting up command string
---------------------------------------------------------------------------
SET @cmd = @cmd  + '") '
SET @cmd = @cmd  + '
objLogFile.WriteLine "=====================' + @MachineName + ':Begging Defrag================================="
For Each objVolume in colVolumes
 objLogFile.WriteLine "Please wait while drive " & objVolume.Name & " is defragged."
 sTime = Now()
    objLogFile.WriteLine "Start Time:" & sTime
     errResult = objVolume.Defrag(Force=True)
    If errResult = 0 Then
        objLogFile.WriteLine "Drive " & objVolume.Name & " successfully defragged."
    Else
        objLogFile.WriteLine "Drive " & objVolume.Name & " could not be defragged."
        objLogFile.WriteLine "Error number " & errResult & " occurred."
        Err.Clear
    End If
 sTime = Now()
    objLogFile.WriteLine "End Time:" & sTime
Next
objLogFile.close
Set colProcesses = nothing
Set objWMIService = nothing
'
---------------------------------------------------------------------------
-- Execute command
---------------------------------------------------------------------------
--PRINT @cmd
SET @FileSystemTempDirectory =
  [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\')
 + 'usp_fs_wmi_defrag_drives_win2003.vbs'
-- + [dbo].[udf_getTempName]('usp_fs_wmi_defrag_drives.vbs', GetDate(), NULL, 1)
EXEC @retval  = [dbo].[usp_fs_CreateTextFile]
  @FullPathFileNameDestination  = @FileSystemTempDirectory
, @strTextToWriteToFile        = @cmd
, @boolOverwriteFile           = 0
IF @retval = 0
BEGIN
 SET @FileSystemTempDirectory = 'cmd.exe /C cscript.exe ' + @FileSystemTempDirectory
 PRINT @FileSystemTempDirectory
 EXEC @retval     = [dbo].[usp_srvr_xp_cmdshell]
    @cmd      = @FileSystemTempDirectory
  , @ReturnData    = 1
  , @Execute0Display1Both2 = 2
END
RETURN @retval
---------------------------------------------------------------------------
-- This procedure builds a file to defrag drives
-- A is currently needed
---------------------------------------------------------------------------
--EXEC usp_fs_wmi_defrag_drives @DriveLetter= 'C'
--EXEC usp_fs_wmi_defrag_drives @Execute0Display1Both2 = 2
--EXEC [dbo].[usp_fs_wmi_defrag_drives_win2003] @MachineName='DAMSDEV01'
--EXEC [dbo].[usp_fs_wmi_defrag_drives_win2003] @MachineName='DAMSDEV02'
--EXEC [dbo].[usp_fs_wmi_defrag_drives_win2003] @MachineName='DAMSDEV03'
END

Server, database and application maintenance.

Code assimilated into my code base libraries via the Internet is more often than not I recoded a bit  adding input parameters and modifying the return data. My admin routines pass a server name and database name so they can be run on any target server database.  I have listed a few of the jobs I schedule or have available to run on my servers below. In some cases I use SQL Server to dynamically create and execute scripts such as VBScript, WMI, DOS, xp_cmdshell, SQLCMD, etc. 

Most of them call procedures in my personal DBA Tool's database.  I am currently in the process of splitting it up into Azure\cloud code and code that can only be executed on client machines such as File System code. 

Design Issues

Bad table or database design is not invisible.  There are just a few  simple concepts to understand to learn the most important aspects of database and table design quickly.

maint_notification_di_missing_clustered_index - Physically sorted on this index
maint_notification_di_missing_primary_keys - primary and foreign keys.
maint_notification_di_missing_real_data_unique_record_constraints
maint_notification_di_missing_referential_integrity_constraints

Maintenance - Server

maint_fs_defrag
maint_fs_cleanup_disk
maint_db_index_defrag_offline
maint_db_index_defrag_online
maint_db_integrity_checks
maint_db_statistics_update
maint_srvrall_index_maintenance
maint_notification_index_fragmentation

Maintenance - Database
main_db_repair_emergency
maint_db_fix_orphaned_logins
maint_db_growth_monitor
maint_ssrs_rpt_send_Perfmon
maint_backups_delete_old_zip_new
maint_db_autoshrink_off
maint_db_autoshrink_on
maint_db_backup_OLAP
maint_db_metadata_load
maint_db_recovery_models
maint_db_security_db_executor_update
maint_db_shrink_all_databases
maint_db_xp_cmdshell_set_pwds
maint_logs_RSExecutionLog_Update (Reporting Services Monitoring)
maint_notification_blocking
maint_notification_connections
maint_notification_db_growth_configuration
maint_notification_DBSizeAlert
maint_notification_disk_usage
maint_notification_diskspace
maint_notification_job_backups_disabled
maint_notification_job_errors_developer
maint_notification_job_show_running
maint_notification_jobs_disabled
maint_notification_jobs_scheduled
maint_notification_LogDirSize
maint_notification_missing_backups
maint_notification_server_down
maint_notification_server_issue_configuration
maint_notification_server_issue_counters
maint_notification_surface_area_configururation_issue
maint_rpt_jobs_scheduled
maint_rpt_srvr_user_accounts_DisabledOrExpireCheck
maint_dboptions - business closed
maint_dboptions - business open
maint_srvr_job_owner_assign
maint_srvr_option_monitor

Code Maintenancemaint_db_compile_modules                   Makes sure they all compile.
maint_db_compile_views                       Makes sure they all compile.

Maintenance - Data
rpt_ad_anomaly_detection                     Report application data issues. i.e. inactive date without active date.
maint_db_column_selectivity_log           Collect data population and selectivity statistics for all columns.

Application Support
maint_auditlogs_PopulateFromLocal
maint_db_move_subscription_data_files
maint_db_scriptall

Database Tuning Data
maint_trace_collect
maint_trace_database_tuning_wizard_run
maint_trace_flags_disable
maint_trace_flags_enable
maint_trace_import
maint_trace_jobs_disable
maint_trace_jobs_enable
maint_trace_killall
maint_trace_notification_cpu_usage_sp_who2
maint_trace_notification_sessions_active
maint_trace_notification_trace_show_cpu_intensive
maint_trace_purge_trace_job_history
maint_trace_rpt_running
maint_trace_rpt_table_counts
maint_trace_send_reports
maint_trace_start_DBName
maint_trace_start_specified
maint_trace_stop_DBName
maint_trace_stop_specified
maint_trace_table_limit_size
maint_db_index_hypothetical_delete