Large OS Files

USE [msdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[prc_dba_Large_OS_Files]

@FilePath varchar(1000)

/*

EXEC prc_dba_Large_OS_Files @FilePath = 'D:\MSSQL'

EXEC prc_dba_Large_OS_Files @FilePath = 'D:'

CREATE TABLE dba_Large_OS_Files (

FileName varchar(1000) NULL,

Size varchar(1000) NULL, -- in bytes

ImportDate datetime

)

SELECT FileName, (cast(size as float)/1024)/1024 as 'Size(MB)' FROM dba_Large_OS_Files ORDER BY [Size(MB)] desc

SELECT FileName, (cast(size as float)/1024)/1024 as 'Size(MB)' FROM dba_Large_OS_Files

WHERE FileName like '%mssql%' ORDER BY [Size(MB)] desc

/* Files other than mdf and ldf files */

SELECT FileName, (cast(size as float)/1024)/1024 as 'Size(MB)' FROM dba_Large_OS_Files

WHERE FileName NOT LIKE '%.[ml]df%' ORDER BY [Size(MB)] desc

/* Only mdf and ldf files */

SELECT FileName, (cast(size as float)/1024)/1024 as 'Size(MB)' FROM dba_Large_OS_Files

WHERE FileName LIKE '%.[ml]df%' ORDER BY [Size(MB)] desc

-- Deleting files older than x days:

DECLARE @sql varchar(1000)

SET @sql = 'forfiles /p D:\MSSQL\SharepointBackups\ /m *.bak /c "cmd /c del @file " /d -2'

PRINT @sql

EXEC master..xp_cmdshell @sql

-- Free Disk Space

exec master.dbo.xp_fixeddrives

*/

AS

SET NOCOUNT ON

TRUNCATE TABLE dba_Large_OS_Files

DECLARE @sql varchar(1000)

CREATE TABLE #Temp (Col1 varchar(5000) NULL)

CREATE TABLE #Temp2 (Col1 varchar(5000) NULL)

CREATE TABLE #Temp3 (Col1 varchar(5000) NULL)

CREATE TABLE #Temp4 (Col1 varchar(5000) NULL, size varchar(100) null, filename varchar(1000))

CREATE TABLE #Dir (Col1 varchar(5000) NULL)

SET QUOTED_IDENTIFIER OFF

SET @sql = 'EXEC master..xp_cmdshell ' + '''' + 'Dir ' + @FilePath + '\ /s /n /od /t' + ''''

INSERT INTO #Temp

EXEC(@sql)

--INSERT INTO #Temp

--EXEC master..xp_cmdshell 'dir D:\MSSQL\ /s /n /od /t'

INSERT INTO #Dir

select * from #temp where col1 like '%Directory%'

delete from #temp where col1 is null

delete from #temp where col1 like '%Volume%'

delete from #temp where col1 like '%bytes%'

delete from #temp where col1 like '%<DIR>%'

delete from #temp where col1 like '%<DIR>%..%'

delete from #temp where col1 like '%<DIR>%.%'

UPDATE #Dir

SET Col1 = ltrim(rtrim(REPLACE(Col1, 'Directory of', '')))

SELECT * FROM #Dir

declare @Dir varchar(1000)

DECLARE DirectoryCur cursor for select col1 from #Dir

open DirectoryCur

fetch next from DirectoryCur into @Dir

WHILE @@fetch_status = 0 and @@error = 0

BEGIN

SET QUOTED_IDENTIFIER OFF

--SET @sql = 'EXEC master..xp_cmdshell ' + '''' + 'Dir ' + @dir + '\ /A-D /-C' + ''''

SET @sql = 'EXEC master..xp_cmdshell ' + '"' + 'Dir ' + @dir + '\ /A-D /-C' + '"'

PRINT @sql

INSERT INTO #Temp2

EXEC(@sql)

IF @@Error <> 0

BEGIN

GOTO Label1

END

delete from #temp2 where col1 is null

delete from #temp2 where col1 like '%Volume%'

delete from #temp2 where col1 like '%bytes%'

delete from #temp2 where col1 like '%<DIR>%'

delete from #temp2 where col1 like '%<DIR>%..%'

delete from #temp2 where col1 like '%<DIR>%.%'

delete from #temp2 where col1 like '%File Not Found%'

delete from #temp2 where col1 like '%Directory of%'

delete from #temp2 where col1 like '%is not recognized%'

delete from #temp2 where col1 like '%operable program or batch file%'

delete from #temp2 where col1 like '%The system cannot find the file specified%'

delete from #temp2 where col1 like '%The system cannot find the path specified%'

INSERT INTO #temp3

SELECT ltrim(rtrim(right(col1, len(col1)-20))) FROM #temp2

INSERT INTO #Temp4

SELECT col1, left(col1, charindex(' ', col1)) as size,

@Dir + '\' + right(col1, len(col1) - charindex(' ', col1)) as filename

FROM #temp3



Label1:

TRUNCATE TABLE #Temp2

TRUNCATE TABLE #Temp3

INSERT INTO dba_Large_OS_Files

SELECT FileName, Size, getdate() FROM #Temp4

TRUNCATE TABLE #Temp4

FETCH next FROM DirectoryCur INTO @Dir

END

CLOSE DirectoryCur

DEALLOCATE DirectoryCur

--SELECT FileName, cast(Size as bigint) as Size FROM #Temp4 ORDER BY size desc

DELETE FROM dba_Large_OS_Files WHERE size like '%,%'

DELETE FROM dba_Large_OS_Files WHERE size LIKE '0'

DELETE FROM dba_Large_OS_Files WHERE (cast(size as float)/1024)/1024 = 0