Auto-start Defragging a SQL Server

Last week I announced that this weekend would be (queue fanfaire): Maintenance Weekend! I don’t know when this database last had any kind of take-it-down work done on it, but every data and log drive was at least 85% fragmented on the file level.

A fragmented database file means more work for everyone. The database has to gather data from more than one place on the drive, which means that the hard drives’ heads need to move more, which means waiting on a physical device to make a physical move.

Also, the database is more likely to get an execution plan that shows things will take a while. When that happens, the plan will involve taking as many degrees of parallelism it can get its hands on, and then the processor context has to switch, split, and manage all the threads that want in on the action. More on that in a future blog.

Another day will see making the filegroup and database file sizes set more correctly than they are now. That’s because as database files get shrunk and autogrow, they fragment.

But for today, the goal is to shut down SQL Server and start defragging without having to be there. For that, I have developed the following. Quick note: sometimes I am just really happy to do some VB6-style programming. Its like a nicely worn pair of jeans.

Set objShell = CreateObject(“Wscript.Shell”)
‘——————————————————————————–
‘Created: 6/12/09 by Randy Sims
‘Purpose: 1) Check that backups have run successfully.
‘ 2) If not, send an email to me saying its a no-go
‘ 3) If so, send me an email saying everything’s ok.
‘ 4) Defrag the drives specified.
‘ 5) Keep a log of the defrag execution if it runs.
‘execute on the command line as:
‘
‘ c:/>DefragMe.bat [drive letter(s)] [a|f]
‘
‘Inside of DefragMe.bat:
‘ C:\Maintenance\DefragMaintenance.vbs %1 %2
‘
‘Check arguments used on command line. If none, use C.
‘——————————————————————————–
if wscript.arguments.count >0 then
  vDrive = wscript.arguments(0)
else
  vDrive = “C”
end if
if wscript.arguments.count =2 then
  vSwitch = wscript.arguments(1)
end if

if lcase(vSwitch) = “a” or lcase(vSwitch) = “f” then

 vSwitch = “-” & vSwitch
elseif vSwitch <> “” then
 vSwitch = “-a”
else
 vSwitch=”"
end if

–An array for the list of drives
dim arrDrive()

 if len(vDrive) > 1 then

  redim arrDrive(len(vDrive)-1)
 
  do while vDrive <> “”

    arrDrive(len(vDrive)-1) = left(vDrive,1)
    vDrive = right(vDrive,len(vDrive)-1)

  loop

else

  redim arrDrive(0)

  arrDrive(0) = vDrive

end if

intResults = CheckBackups()

if intResults = 0 then
‘——————————————————————————–
‘No Jobs Failed – Proceed
‘——————————————————————————–

‘Send the Email to say everything’s a GO
‘Database has a great udf for sending email
‘The .sql file has the SQL command for the email I want to send.

  objShell.Exec (“osql -E -i c:\Maintenance\PassEmail.sql”)

‘Shut down the SQL Server
  objShell.Exec (“NET STOP SQLSERVERAGENT”)
  objShell.Exec (“NET STOP MSSQLSERVER”)

‘Line 50
  dim index

  for index = 0 to ubound(arrDrive)

‘Running the Defrag and Taking Notes
    vDrive = arrDrive(index)
    Set objExec = objShell.Exec (“defrag ” & vDrive  & “: ” & vSwitch)

    vOut = objExec.StdOut.ReadAll

    Call WriteLog (vOut, vDrive)

  next

‘SQL Server back on
  objShell.Exec (“NET START MSSQLSERVER “)
  objShell.Exec (“NET START SQLSERVERAGENT”)

‘Tell me everything’s finished
  objShell.Exec (“osql -E -i c:\Maintenance\WootEmail.sql”)

else

‘——————————————————————————–
‘Job Failed – send the bad news
‘——————————————————————————–

  objShell.Exec (“osql -E -i c:\Maintenance\FailEmail.sql”)

end if
 
set objShell = nothing

 

 

‘———————————————————————————
‘SUBS and FUNCTIONS
‘———————————————————————————

 

Function CheckBackups ()
‘——————————————————————————–
‘FIRST check to see if any of my backup jobs have failed,
‘excluding the ones where I dont care – all databas jobs start with DB
‘——————————————————————————–

    Dim cnn, strConnection
    Set cnn = CreateObject(“ADODB.Connection”)
    strConnection = “Driver={SQL Server};Server=URSERVER;” & _
 ”Database=MSDB;Trusted_Connection=TRUE”
    cnn.Open strConnection

    Dim strSQLQuery

‘Tried and works on SQL Server 2000 and 2005

    strSQLQuery = “select count(*) cnt ” & _
“from msdb.dbo.sysjobhistory h ” & _
 ”join msdb.dbo.sysjobs j ” & _
 ”on h.job_id = j.job_id ” & _
 ”where name like ‘DB%backup’ ” & _
 ”and step_id = 0 ” & _
 ”and run_status <> 1 ” & _
 ”and right(run_date,2) = day(getdate())”
    Dim rst
    Set rst=CreateObject(“ADODB.Recordset”)
    Set rst = cnn.Execute(strSQLQuery)

    ‘——————————————————————————–
    ‘Get the Count of jobs that failed
    ‘——————————————————————————–
    CheckBackups = rst.Fields(0)

    rst.Close
    cnn.Close

    set rst = nothing
    set cnn = nothing

End Function

‘——————————————————————————–

Sub WriteLog (vLogIt, vDrive)

    ‘———————————————————————————
    ‘Create the Log – give it a name with a date.
    ‘———————————————————————————
    vPath = “c:\maintenance”

    vMonth = Month(Date())
    vDay = Day(Date())

    if len(vMonth)=1 then vMonth = “0″ & vMonth end if
    if len(vDay)=1 then vDay = “0″ & vDay end if

    vLog = “\” & Year(Date()) & vMonth & vDay & “_DefragLog_Drive” & UCASE(vDrive) & “.log”
    Set objFSO = CreateObject(“Scripting.FileSystemObject”) 
    Set objFolder = objFSO.GetFolder(vPath) 

    ‘——————————————————————————–
    ‘Create the File
    ‘——————————————————————————–

    set objLog = objFSO.CreateTextFile (vPath & vLog)
    set objLog = Nothing
    Const ForAppending = 8

    Set objLog = objFSO.OpenTextFile (vPath & vLog, ForAppending, True)

 

    ‘——————————————————————————–
    ‘Write the data into the file
    ‘——————————————————————————–
    objLog.Write vLogIt

    objLog.close
    set objLog = Nothing

END SUB