Are disk volumes fragmented on server? Does it require to be defragged

,

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

Rate

Share

Share

Rate