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 ifif 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 = obj
Shell.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