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