1. Connect to server for which you want to know the disk fragementation details.
2. PREVILEGES REQUIRED: LOCAL ADMINISTRATOR AND EXECUTE ON XP_CMDSHELL
1. Connect to server for which you want to know the disk fragementation details.
2. PREVILEGES REQUIRED: LOCAL ADMINISTRATOR AND EXECUTE ON XP_CMDSHELL
/*
OBECTIVE: DISK FRAGMENTATION ANALYSIS OF A SERVER
PREVILEGES REQUIRED: LOCAL ADMINISTRATOR AND EXECUTE ON XP_CMDSHELL
*/
if object_id('tempdb..#tmpdrives') is not null
begin
    drop table #tmpdrives
end
create table #tmpdrives(autoid int identity(1,1), drive char(1), freespace int)
insert into #tmpdrives
    EXEC master.dbo.xp_fixeddrives
declare @count int
select @count=count(*) from #tmpDrives
declare @loopcount int
set @loopcount = 1
declare @drive char(2)
if object_id('tempdb..#tblanalysis') is not null
begin
    drop table #tblanalysis
end
create table #tblanalysis (analysis nvarchar(255))
declare @cmd1 nvarchar(255), @cmd2 nvarchar(255)
if object_id('tempdb..#tblfragementationmaster') is not null
begin
    drop table #tblfragementationmaster
end
create table #tblfragementationmaster
(
    servername    nvarchar    (255),
    drive    char (2),
    analysis    nvarchar(255)
)
while @loopcount <= @count 
    begin
        select @drive = drive + ':' from #tmpdrives where autoid = @loopcount 
        set @cmd1 = 'defrag '+ @drive +' -a | FIND /I "fragmentation"'
        Insert Into #tblanalysis (analysis)
            EXEC master..xp_cmdshell @cmd1
        set @cmd2 = 'defrag '+ @drive +' -a | FIND /I "You"'
        Insert Into #tblanalysis(analysis)
            EXEC master..xp_cmdshell @cmd2
        insert into #tblfragementationmaster
        select @@servername, @drive, ltrim(rtrim(analysis)) from #tblanalysis where analysis is not null
        delete from #tblanalysis
        set @loopcount = @loopcount + 1
    end
select * from #tblfragementationmaster