Category Archives: Space-Issues

Tools to find culprits for space issues.

Growth of Database

The following script provides an educated guess regarding database growth

It does this by examining the size of backups. It queries MSDB for backup sizes, calculated the difference, divided by the number of days in between, and finally averages the difference.

/*
DESCRIPTION    Estimate Database growth based on backups.

Assuming backup size grows at the same rate as the Database size.  
First calculate difference in size between backups,
and then divides it by the number of days the backup took place
Finaly, averages the result.
CONFIGURATION    
    Set @LastXday to limit the number of days to search for
    Set @dbname for the database
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = 'AdventureWorks'
set @LastXdays = '150'

;WITH BK_Growth (BKsize,BKDate,rownumber) as(  
	SELECT
		 backup_size
		,backup_start_date
		,row_number() OVER 
				(ORDER BY database_name,
				[type], backup_start_date DESC)
				AS rownumber
	FROM msdb.dbo.backupset
	WHERE
		database_name like @dbname
		and DATEDIFF(dd,([backup_finish_date]),GETDATE()) < @LastXdays
		and type = 'D'
)
select 
@dbname 'dbname'
,CAST( CAST(avg(100*((
(prev.BKSize - cur.BKSize)/cur.BKSize)/(DATEDIFF(dd,cur.BKDate,Prev.BKDate))
)) as NUMERIC(5,3))
as VARCHAR(5))+' %' 'Average Daily Growth'

,     (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB
,     (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
 
from BK_Growth cur
inner join BK_Growth prev on CUR.rownumber = (PREV.rownumber+1)

cross apply sys.databases db
    LEFT JOIN (
    SELECT database_id, SUM(size) RowSize 
    FROM sys.master_files 
    WHERE type = 0 and database_id = DB_ID(@DBName)
    GROUP BY database_id, type
        ) mfrows ON mfrows.database_id = db.database_id
    
    LEFT JOIN (
    SELECT database_id, SUM(size) LogSize 
    FROM sys.master_files 
    WHERE type = 1  and database_id = DB_ID(@DBName)
    GROUP BY database_id, type
        ) mflog ON mflog.database_id = db.database_id

WHERE mfrows.RowSize is not null
GROUP BY mfrows.RowSize, mflog.LogSize        

 

Shrink a Data File

Normally, you would never want to shrink data files. Not even in case of emergency when running out of space. If you were to shrink the data file, the database would simply grow again and you end up with the same problem. If the database grew, it is because the database needed the space. If it is partially empty, it is because some data was removed, however, it is very likely that it would be needed again so it is a bad idea to shrink it again. If there was an error that caused the database to grow, or there was a change in process, or something extremely rare happened, you might need to shrink a database file. Since shrinking a database will hit performance and possible introduce some locks on the tables, if you must do it, it is better to do it little by little, using a script similar to this below. Remember, after the shrink is done, your indexes will be fragmented, so it is recommended to rebuild all your indexes (and hence the DB will grow a little)

In order to use this script, you should first find if there is space available on the data files, and find the logical name of the data file to shrink.

To do that, run this script first http://www.cookingsql.com/2016/01/data-file-size-and-free-space-one-database/ and use its output to run the script below.

 

/*
DESCRIPTION   Shrink a single DATA file, in increments.
It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment
until it reaches the target free space.


CONFIGURATION    TOP.
Run this script in the database with the file to be shrunk.
1.  Make sure to set the USE statement for the correct database
2. Set @DBFileName  Enter the Logical File name (not the physical name)
3. Set @TargetFreeMB to the desired file free space in MB after shrink.
4. Set @ShrinkIncrementMB to the increment to shrink file by in MB

 

Compatibility list:
MSSQL2005
MSSQL2008/SQL2008R2
MSSQL2012
MSSQL2014 
 
*/
SET NOCOUNT ON


declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

/*------ CONFIGURATION ---------*/

--USE DATABASE 

-- Set Name of Database file to shrink
set @DBFileName = 'AdventureWorks2012_Data2'

-- Set Desired file free space in MB after shrink
-- Remember, it is not the target file size but how much empty space will be left after the shrink operation
-- @SizeMB = @TargetFreeMB + @usedSpace
set @TargetFreeMB = 1300

-- Set Increment to shrink file by in MB
-- Make sure the @TargetFreeMB + @ShrinkIncrement is bigger than @EmptyDB
set @ShrinkIncrementMB = 300

/*------ END CONFIGURATION ---------*/

-- Declare variables
declare @sql varchar(8000)
declare @SizeMB int
declare @EmptyMB int
declare @UsedMB int

-- Check Input Values

IF NOT EXISTS (select name from sysfiles where name = @DBFileName)
begin
print 'The Data File does not exist on the current database. You either did not set
the USE statement or did not specify the Logical File name correctly
Terminating Batch'
GOTO EOL
end

select
@SizeMB = CAST(size/128.0 AS int)
,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0
,@EmptyMB = @SizeMB - @UsedMB
 
FROM sys.database_files
where name = @DBFileName



IF @EmptyMB < @TargetFreeMB
BEGIN
print 'Cannot proceed with operation because the target free space: ' +cast(@TargetFreeMB as nvarchar(10)) +'MB (user entry)
is bigger than the current free space: '+cast(@EmptyMB as nvarchar(10))+'MB
Terminating Batch'
GOTO EOL
END

IF @EmptyMB < @TargetFreeMB+@ShrinkIncrementMB
BEGIN
print 'Cannot proceed with operation because the Shrink Increment: ' +cast(@ShrinkIncrementMB as nvarchar(10)) +'MB (user entry)
Plus the Target Free '+cast(@TargetFreeMB as nvarchar(10))+'MB is bigger than the empty Space '+cast(@EmptyMB as nvarchar(10))+'MB
Terminating Batch'
GOTO EOL
END

 
declare @loopExit int
set @loopExit =  0


-- Loop until file at desired size
while  (@SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB) and (@loopExit < 10)
	begin

		set @sql =
		'dbcc shrinkfile ( '+@DBFileName+', '+convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) WITH NO_INFOMSGS'

		print 'Start ' + @sql + ' at '+convert(varchar(30),getdate(),121)
		exec ( @sql )

		print 'Done at '+convert(varchar(30),getdate(),121)

		-- Sets again Original Values
		select
		@SizeMB = CAST(size/128.0 AS int)
		,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0
		,@EmptyMB = @SizeMB - @UsedMB
		,@loopExit = @loopExit+1
		FROM sys.database_files
		where name = @DBFileName

	end
------------------------------------

IF (@loopExit = 10)
BEGIN
PRINT 'The SHRINK FILE operation completed 10 loops
This could mean that the file is not shrinking
or you need to increase the "shrink increment value"
Proceed with CAUTION
'
GOTO EOL
END

-- Show Size, Space Used, Unused Space, and Name of all database files


SELECT   
b.name
, b.type_desc
, CAST(b.size/128.0 AS int) 'SizeMB'  
, CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, 'SpaceUsed' ) AS int)/128.0 AS int) 'SpaceUsedMB'  
, ceiling(b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0) 'FreeSpaceMB'
, CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + '%'  'FreeSpace'

,  CASE
	WHEN b.is_percent_growth = 0 THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + ' MB, '  
    ELSE 'By ' + CAST(b.growth AS VARCHAR) + ' percent, '
 END
 + CASE  
    WHEN b.max_size = -1 THEN 'unrestricted growth'
	ELSE 'restricted growth to ' + LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + ' MB'  
    END AS Autogrow
, cast(b.max_size/128.0 as int) 'MaxSize'
, physical_name
 
FROM sys.database_files b



EOL:


 

Data File Size and Free Space – One Database

This script lists file size and how full the data files are for a single Database.

 

/*
DESCRIPTION    List space on a single database and location of its files

CONFIGURATION    Top. Modify the "use" statement
 
Compatibility list:
MSSQL2005
MSSQL2008
 
 
*/


USE Adventure_Works_2012

SELECT  
b.name
, b.type_desc
, CAST(b.size/128.0 AS int) 'SizeMB'  
, CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, 'SpaceUsed' ) AS int)/128.0 AS int) 'SpaceUsedMB'  
, CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + '%'  'FreeSpace'

,  CASE
	WHEN b.is_percent_growth = 0 THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + ' MB, '  
    ELSE 'By ' + CAST(b.growth AS VARCHAR) + ' percent, '
 END
 + CASE  
    WHEN b.max_size = -1 THEN 'unrestricted growth'
	ELSE 'restricted growth to ' + LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + ' MB'  
    END AS Autogrow
, cast(b.max_size/128.0 as int) 'MaxSize'
, physical_name
 
FROM sys.database_files b




Database Size Totals

Very simple script that list the total size of a database and its transaction log.

If you need for SQL 2000 click here

 



/*
DESCRIPTION    List space used on databases
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2012
MSSQL2008
MSSQL2005

DOES NOT WORK
MSSQL2000

 
*/


SELECT
    SERVERPROPERTY('SERVERNAME') ServerName,
    SERVERPROPERTY('IsClustered') IsCluster,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    case 
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.3 then 'Windows 2012R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.2 then 'Windows 2012'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.1 then 'Windows 2008R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.0 then 'Windows 2008'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.2 then 'Windows 2003'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.1 then 'Windows XP'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.0 then 'Windows 2000'

else RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)
end,
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sys.databases db
    LEFT JOIN (
    SELECT database_id, SUM(size) RowSize 
    FROM sys.master_files 
    WHERE type = 0 
    GROUP BY database_id, type
				) mfrows ON mfrows.database_id = db.database_id
    
    LEFT JOIN (
    SELECT database_id, SUM(size) LogSize 
    FROM sys.master_files 
    WHERE type = 1 
    GROUP BY database_id, type
				) mflog ON mflog.database_id = db.database_id
WHERE
	db.database_id > 4

 

  For SQL 2000


/*
DESCRIPTION    List space used on databases
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2000

 
*/

SELECT
    SERVERPROPERTY('SERVERNAME') ServerName,
    SERVERPROPERTY('IsClustered') IsCluster,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    case 
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.3 then 'Windows 2012R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.2 then 'Windows 2012'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.1 then 'Windows 2008R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.0 then 'Windows 2008'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.2 then 'Windows 2003'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.1 then 'Windows XP'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.0 then 'Windows 2000'

else RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)
end,
    DB_NAME(db.dbid) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sysdatabases db
    LEFT JOIN (
    SELECT dbid, SUM(size) RowSize 
    FROM master..sysaltfiles
    WHERE groupID = 1 
    GROUP BY dbid, groupID
				) mfrows ON mfrows.dbid = db.dbid
    
    LEFT JOIN (
    SELECT dbid, SUM(size) LogSize 
    FROM master..sysaltfiles
    WHERE groupID <> 1
    GROUP BY dbid, groupID
				) mflog ON mflog.dbid = db.dbid
WHERE
	db.dbid > 4

Shrink All Transaction Logs

The following script loops through all the Tlogs and attempts to shrink them. Remember that a Tlog can be shrunk only if there is empty space at the end. In order to tell where the empty space is, run:


use [database]
DBCC LOGINFO

If the Status column is 0, then it's empty. If you do not have empty space at the end, it will not shrink, so you need to run Tlog backups. Sometimes you need to run Tlog backups several times for the portion that's empty to move to the end of the file. More on that here:

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

Now, for the script:

/*
Get a list of Databases and its Tlogs, and shrink them
Do the following.
1- Run Tlog Backups
2- Run this script

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

Does not work:
MSSQL2000

*/




DECLARE @numRec INT
DECLARE @counter INT
DECLARE @DBName SYSNAME
DECLARE @Tlog SYSNAME
DECLARE @CMD NVARCHAR(MAX)

DECLARE @table TABLE
(RowID INT IDENTITY(1, 1), dbname SYSNAME, tlogname SYSNAME)

-- create a table with the DBname and its Tlog file
-- skip offline and system DBs
INSERT INTO @table 
SELECT db_name(database_id), name FROM sys.master_files 
WHERE type_desc = 'LOG' and state_desc ='ONLINE' and database_id > 4

-- Set and initialize counters
SELECT @numRec=count(*), @counter=1 FROM @table

WHILE  @counter <= @NumRec
BEGIN 

SELECT @DBName = dbname, @Tlog = tlogname FROM @table WHERE RowID = @counter

SET @CMD='USE ' + quotename(@DBName, N'[') + 
                        N'
CHECKPOINT
DBCC SHRINKFILE (['+@Tlog+'], 0, TRUNCATEONLY)'

print @CMD
-- EXECUTE (@CMD)

set @counter=@counter+1
END


 

Ok.. well… what happen if you are forced to shrink the Tlog and you don't have space for backups? Now what?

There is a way where you can take a backup "into the ether"… tricking the database into dumping all pages and forcing to mark the Tlog as empty pages.

Doing this will not cause data loss, however it will invalidate the backup chain, and a full backup should be taken as soon as possible.

 

 


BACKUP LOG [AdventureWorks2012] TO  DISK = N'NUL' 

 

Location of Data files and Tlog

.


/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry


DESCRIPTION:  List location of all files for all databases

Look for tempDB and  Tlog sharing the same drives as a source of I/O contention issues


 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

 

 

SELECT

DB_NAME([database_id])AS [Database Name]

,   [file_id]

, name

, physical_name

, type_desc

, state_desc

,  CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

.

Last Autogrowth Event

.


/*
DESCRIPTION:    Queries the default trace to find out last AUTOGROWTH event

The default trace should be enabled by, well, default

Check if it is enabled:  select status, path from sys.traces where is_default = 1

How far back does it report?

The default trace creates up-to 5 files, 20 mb in size. So depending on how busy the server is, it could be 20 mins or days



Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:
 
*/

 
 
Use master
go
 
declare @curr_tracefilename varchar(500) ;  
declare @base_tracefilename varchar(500) ;  
declare @indx int ;
 
select @curr_tracefilename = path from sys.traces where is_default = 1 ;  
 
set @curr_tracefilename = reverse(@curr_tracefilename);
 
select @indx = patindex('%\%', @curr_tracefilename) ;
 
set @curr_tracefilename = reverse(@curr_tracefilename) ;
 
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;  
 
 
select  
 loginname
 , spid
 --,convert(int, EventClass) as EventClass,  
,DatabaseName
,[Filename]
,Duration
, StartTime
, EndTime
, (IntegerData*8.0/1024) as ChangeInSize
, e.name
, RIGHT(@curr_tracefilename,@indx) as curr_tracefilename
 
from ::fn_trace_gettable(@base_tracefilename, default ) a
INNER JOIN sys.trace_events e    ON eventclass = trace_event_id

where  
            e.name = 'Log File Auto Grow'
        OR  e.name = 'Log File Auto Shrink'
        OR  e.name = 'Data File Auto Grow'
        OR  e.name = 'Data File Auto Shrink'
 
order by StartTime desc ;  
GO

.

SQL Job to delete old files

This scripts builds up on the idea of using ForFiles. See

http://www.cookingsql.com/2014/12/batch-file-to-remove-old-files/

/*
DESCRIPTION:
Creates a JOB to delete files of a certain age on a folder, based on EXTENSION
 
CONFIGURATION
Plenty
SET @days = 30   for the age of the file, in days
SET @NAME = 'IBM_Cleanup'  for the name of the job
SET @ext = 'txt'  for the extension of the files to delete.

The DEFAULT FOLDER is the location for your ERRORLOG folder, but it can be customized

After running the script, you still need to schedule the job

Author: Miguel Quintana

LIMITATION:
It does not do subfolders

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
*/

 

 
declare @cmd nvarchar (1000)
DECLARE @sqlerrorlog TABLE (logdate DATETIME, processinfo SYSNAME, logtext VARCHAR(MAX))
DECLARE @path VARCHAR(300)
DECLARE @LOG_FOLDER varchar(300)
DECLARE @days varchar(4)
DECLARE @NAME varchar(100)

DECLARE @ext varchar (4)


/*
This section sets the ErrorLog folder as destination to delete file.
If you would like to set your own path, comment out this section.
and use the  SET @LOG_FOLDER  section
 
 
*/
SET @days = 30
SET @NAME = 'IBM_Cleanup'
SET @ext = 'txt'


--/*
INSERT INTO @sqlerrorlog EXEC xp_readerrorlog 0, 1
SELECT @path=logtext FROM @sqlerrorlog WHERE logtext LIKE '%Logging SQL Server messages in file%'

SET @LOG_FOLDER= SUBSTRING(@path, CHARINDEX( '''', @path) + 1,LEN(@path)-CHARINDEX( '''', @path) - CHARINDEX( '\', REVERSE(@path)))
-- */
 
-- only use this line below if you do not want the errorlog as your default folder
-- SET @LOG_FOLDER = 'D:\folder_here'
 
 
/*  DO not change below this line */
 

set @cmd=N'cmd /q /c "For /F "tokens=1 delims=" %v In (''ForFiles /P "'+@LOG_FOLDER+'" /m *.'+@ext+' /d -'+@days+' 2^>^&1'') do if EXIST "'+@LOG_FOLDER+'"\%v echo del "'+@LOG_FOLDER+'"\%v& del "'+@LOG_FOLDER+'"\%v"' 

 
--print @cmd
 
USE [msdb]
 
 
/****** Object:  Job [IBM_CommandLog Cleanup]    Script Date: 04/02/2014 14:16:35 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 04/02/2014 14:16:35 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@NAME,  
        @enabled=1,  
        @notify_level_eventlog=2,  
        @notify_level_email=0,  
        @notify_level_netsend=0,  
        @notify_level_page=0,  
        @delete_level=0,  
        @category_name=N'Database Maintenance',  
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [output_text]    Script Date: 04/02/2014 14:16:35 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'output_text',  
        @step_id=1,  
        @cmdexec_success_code=0,  
        @on_success_action=1,  
        @on_success_step_id=0,  
        @on_fail_action=2,  
        @on_fail_step_id=0,  
        @retry_attempts=0,  
        @retry_interval=0,  
        @os_run_priority=0, @subsystem=N'CmdExec',  
        @command=@cmd,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
 
GO
 
 

Batch File to remove Old Files

This script uses ForFiles to remove old files based on age. However, you must have forfiles installed. Windows 2003 and above have it by default

 

The Syntax is: 

FORFILES -p "Path to the folder" -s -m *.txt -d   -c "cmd /c del @path"

 

Example:

REM  Delete all BAK files from folder C:\backup  that are 5 days or older

FORFILES -p "C:\backup" -s -m *.bak -d -5 -c "cmd /c del @path"

 

 

Want a job to do this automatically? See

SQL Job to delete old files

VBScript to remove files based on age

Sometimes it's necessary to delete files based on age, and we can use a VB Script to do that:

'On Error Resume Next ' < -- Comment out for troubleshooting.

Const strPath = "C:\SQL_Backups\SOG-NADC-SP-DB1" ' Update this parent folder.
Const iDaysOld = 2   

' PURPOSE:	This script will delete all files recursively starting from
'			strPath older than 2 days and empty subfolders.
'			Very convenient for cleaning temp and log files.
' WARNING:	Please understand the risks of using this script.
' WARNING:	DO NOT MODIFY BELOW UNLESS YOU KNOW WHAT YOU ARE DOING.

Dim objFolder, objSubFolder, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getFolder(strPath)

Call Search(objFolder)

Sub Search(objFolder)
	For Each objFile In objFolder.files
		If dateDiff("d",objFile.dateLastModified,Now) > iDaysOld Then objFile.Delete
	Next
	For Each objSubFolder In objFolder.SubFolders
		Search(objSubFolder)
	Next

End Sub

'  end of file