Tag: SysInternals

Defrag SQL MDF and LDF on Server

I wrote this script to defrag all the found MDF and LDF files on a server that I’ve been testing on.  It uses Sysinternal’s Contig tool which is a single-file defrag tool.

@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 ***
for /f tokens=2* delims=|= %%A in ('set __NeedsDefrag') do (
echo Defrag: %%A = %%B
)
call :ask
)
)
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

Determine Version of Microsoft Project Plan File

Found a quick way to determine the version of Microsoft Project Plan files (.MPP). The information is available at Microsoft’s site directly: Click Here
Here’s the batch script (requires strings executable which can be downloaded here):

@ECHO OFF
REM  Version.bat
ECHO Filename: %1
ECHO.
ECHO -- CHECK FOR PROJECT VERSION --
strings %1 | findstr "[0-9],.,....,...." 2>NUL
ECHO Check the following list for the first one or two digits of the string above (xx,.,....,....)
ECHO List of xx (Product Name): 8 (98), 9 (2000), 10 (2002), 11 (2003), 12 (2007), 14 (2010)
ECHO.
ECHO -- CHECK FOR MPP FILE VERSION --
strings %1 | findstr ".MPP" 2>NUL
ECHO Check the following list for the digit(s) at the end of the string above (...MPPxx)
ECHO List of xx (Product Name): 8 (98), 9 (2000/2002/2003), 12 (2007), 14 (2010)
ECHO.
PAUSE

Here’s an overview of project file compatibility with versions.

WHICH PROJECT VERSION ARE YOU USING? WHICH FILE VERSION ARE YOU TRYING TO OPEN? NOTES
Project 2007 Project 2010 No converter is available. If you received a Project 2010 file that you want to open using Project 2007, ask the person who sent the file to first save it to the Project 2007 file format.
Alternately, you can install the free trial version of Project 2010, and then view the file, or save it to the Project 2007 file.
Note Once the trial version of Project 2010 has expired, it can continue to open and view project files, but it won’t be able to save them.
Project 2003 Project 2007 There are two possibilities here.

  • If you are using Project 2003 with SP3, project files from Project 2007 can be opened read-only. Download Microsoft Office Project 2003 Service Pack 3.
  • If you are using Project 2003 without SP3, there is no converter available. Upgrade to Project 2003 with SP3, or ask the person who sent the Project 2007 or later file to save the file first in the Project 2000-2003 file format.
Project 2003 Project 2010 No converter is available. Ask the person who sent the Project 2010 file to save the file first in the Project 2000-2003 file format.
Project 2000 or Project 2002 Project 2007 or Project 2010 No converter is available. Ask the person who sent the Project 2007 or later file to save the file first in the Project 2000-2003 file format.
Project 98 Project 2000 and later versions No converter is available. Consider upgrading to the latest version of Project, or install the free trial version of Project 2010 to try out Project’s enhanced functionality.
Alternately, ask the person who sent the Project 2000-2003 file to save the file first in the Project 98 file format.
Note Project 2007 and later versions of Project do not have the ability to save to the Project 98 file format.”

How do I tell which version am I currently using?

  • For Project versions 98 through Project 2007, click the Help menu, and then click About Microsoft Office Project.
  • For Project version 2010, click the File tab, then click Help.

Server 2008 R2 Performance Tuning

Here’s some of the things I do to a new Server 2008 R2 install (physical and/or virtual settings are mixed in; (VIRTUAL ONLY) specifies I only do configuration change on Virtual systems).

  • Disable Screen Saver, Personalize -> Screen Saver -> None
  • Sound, Do not start Audio Service -> Sound
  •  No Sounds
  •  Display Performance, System -> Advanced System Settings -> Performance Settings -> Adjust for Best Performance
  • Power Options -> High Performance
  • Power Options -> Changed when the computer sleeps -> Turn off Display = Never
  •  Pagefile, System -> Advanced -> Performance -> Set no pagefile (VIRTUAL ONLY)
  •  Disable System Screensaver Regedit -> HKEY_USERS\DEFAULT\Control Panel\Desktop -> Delete SCRNSAVE.exe
  •  Stop Audio Service in services.msc, set to Manual
  •  Remove ipv6 support
  •  Start Menu -> remove quick launch
  •  Drive Indexing, My Computer -> C: Properties -> Unselect Index this drive… -> Apply recursively -> ignore all permission errors
  •  Run as administrator ‘cmd.exe’ -> powercfg -h OFF to disable hibernation and delete hiberfil.sys from C:
  •  reboot
  •  delete pagefile.sys on c: (VIRTUAL ONLY)
  •  defrag C: (I use contig http://live.sysinternals.com/contig.exe and run contig -s c:\*.*)
  •  clear c:\users\administrator\appdata\local\temp folder
  •  enable remote desktop, right click my computer -> properties -> advanced properties -> remote tab
  •  activate windows
  • Run as administrator ‘cmd.exe’ and copy/paste the following: 
    powercfg -setactive scheme_min
Powercfg -setacvalueindex scheme_current sub_processor 45bcc044-d885-43e2-8605-ee0ec6e96b59 100
Powercfg -setactive scheme_current
Powercfg -setacvalueindex scheme_current sub_processor 893dee8e-2bef-41e0-89c6-b55d0929964c 100
Powercfg -setactive scheme_current
Powercfg -setacvalueindex scheme_current sub_processor bc5038f7-23e0-4960-96da-33abaf5935ec 100
Powercfg -setactive scheme_current
powercfg -setacvalueindex scheme_current 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c 54533251-82be-4824-96c1-47b60b740d00 893dee8e-2bef-41e0-89c6-b55d0929964c 100
Powercfg -setactive scheme_current

Defrag SQL MDF, LDF Files on Disk using Contig

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