Defrag SQL MDF, LDF Files on Disk using Contig

This content 8 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

Wrote this batch script to search an entire system (all disks) and find MDF/LDFs and then prompt whether or not to defrag. You need to have ‘contig’ from SysInternals. Also, set MINFRAGMENTS variable to something suitable for you.

Again, I wrote this quickly and YMMV certainly.

Code

@echo off
: Rich Kreider
:
: You need contig.exe from sysinternals: http://live.sysinternals.com/contig.exe
SETLOCAL

set MINFRAGMENTS=5
set CONTIGBIN=c:tempcontig.exe

if not exist %CONTIGBIN% goto missingcontig
: Don't play with this...
set TOTALMDF=0
set TOTALLDF=0
set FRAGCOUNT=0

FOR %%P IN (C D E F G H I J K L M N O P Q R S T U V W X Y Z) DO (
IF EXIST %%P:nul (
call :checkdrive %%P
)
)

: set __NeedsDefrag

for /f %%x in ('set __NeedsDefrag 2^>NUL ^|find /v /c ~~~') do (
if %%x geq 1 (
echo *** FOUND FILES NEEDIN DEFRAGMENTATION ***
call :ask
) else (
echo *** NO FILES NEED DEFRAGGED (MINIMUM FRAGS REQ: %MINFRAGMENTS%^) ***
)
)
goto eof
:checkdrive
for %%y in (mdf ldf) do (
echo ===Searching %%P:*.%%y...
for /F delims=, %%x in ('dir /b /s %1:*.%%y 2^>NUL') do (
if exist %%~fsx (
if %%y==mdf set /A TOTALMDF+=1
if %%y==ldf set /A TOTALLDF+=1
call :analyze %%~fsx
)

)

)
echo ===%1 MDF: %TOTALMDF% LDF: %TOTALLDF%

goto :eof
:analyze
for /F tokens=2,5 %%i in ('%CONTIGBIN% -a %1 ^| find is in') do (
if %%j geq %MINFRAGMENTS% (
set __NeedsDefrag^|%1^=%%j frags/file
)
)



goto :eof

:ask
set answer=
set /p answer=Defragment all? (y/n): 
if %answer%== goto ask
if /i %answer:~0,1%==y goto defrag
if /i %answer:~0,1%==n goto eof

goto :eof

:defrag
for /f tokens=2* delims=|= %%A in ('set __NeedsDefrag') do (
echo Starting defrag on %%A...
%CONTIGBIN% -q %%A 2>NUL 1>NUL
for /F tokens=2 delims=: %%r in ('%CONTIGBIN% -a %%A ^| find Average') do (
echo Completed defrag on %%A (Was %%B now %%r^)
)
echo.
)

goto :eof

:missingcontig
cls
echo.
echo.
echo. Missing contig binary; you NEED this.
echo. I can start the download now, it should open
echo. in your default browser.
echo.
echo. After you download it, note the location
echo. and modify this file and set CONTIGBIN accordingly.
echo.
start http://live.sysinternals.com/contig.exe

goto eof

:eof
echo Press any key to quit...
pause >NUL

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.