Delete files using Ole Automation Procedures in SQL Server by serverku

To delete the files using Ole Automation Procedures in SQL Server we need to first enable 'Ole Automation Procedures' using sp_configure as following,

exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

After enabling it, we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name 'DeleteFile' in the tsql script. You can delete all the files or specific files as well.

DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'D:\TestFolder\*.txt'
EXEC @Result = sp_OADestroy @FSO_Token

Delete database backup history in SQL Server by serverku

As I have written the article to clean up the old database backup for the maintenance activities. The same thing i am doing here as a maintenance activity, but not for the clean up of database backups, It is for database backups history cleanup from MSDB database. Please read my earlier posts related database backup, you may like them.

1. Database Backup Statistics and History
2. Archive old database backup files using forfiles.exe and FOR /F
3. Archive old database backup files using TSQL Script

It is required to clean up the database backup history periodically if no longer needed. Because if the msdb database has more history then it will take the time to load it or sometime it will fail to load because of time out. The script to clean up the database backups, history and a demo are as follows, First we will look for the history of all the database backups done by this SQL instance.

USE msdb 
GO

SELECT bs.database_name, 
       bs.backup_start_date, 
       CASE bs.type 
         WHEN 'D' THEN 'Full Backup' 
         WHEN 'I' THEN 'Diff Backup' 
         WHEN 'L' THEN 'Log Backup' 
       END AS BackupType, 
       bmf.physical_device_name 
FROM   msdb..backupset bs 
       INNER JOIN msdb..backupmediafamily bmf 
               ON ( bs.media_set_id = bmf.media_set_id ) 
ORDER  BY bs.backup_start_date DESC 

We have two methods to delete the database backup history.

#1. Based on DateTime By passing the date in stored procedure , SP will delete all the database history older then specified date time. Let us run below query and get the output.

DECLARE @BackupOlderdate DATETIME
SET @BackupOlderdate = '2011-05-17 14:22:13.000'
EXEC sp_delete_backuphistory @BackupOlderdate
GO

#2. Based on Database
By passing the Database name in stored procedure, SP will delete all the database history for specified database only. Let us run below query and get the output.

DECLARE @DBname sysname
SET @DBname = 'trialmaster'
EXEC sp_delete_database_backuphistory @DBname
GO

Please comment here if you know any other methods to delete it.

Purge old data from dbo.sysmail_mailitems system table in msdb database - SQL Server by serverku

As a DBA, disk space is an important factor for daily monitoring and I encountered one issue of same for disk space. I found it based on disk space statistics report from all servers. I investigated disk space usage of all databases and found msdb system database went beyond 38 GB around. You can find the script at database size information. After database, it is needed to know where actually it was eating, which tables? I found one system table ‘dbo. sysmail_mailitems’ consumed high disk space and created space issue. Please read the post to get table size statistics for a particular database.

To make a disk space I removed some old data from dbo. sysmail_mailitems system table using a script. We can also delete data directly from this system table, but I found system stored procedure to purge old data from this table from here. Let me share with you,

1. sysmail_delete_mailitems_sp :  Permanently deletes e-mail messages from the Database Mail internal tables.
2. sysmail_delete_log_sp : Deletes events from the Database Mail log. Deletes all events in the log or those events meeting a date or type criteria.

And the script using these system procedures is following,

USE msdb; 
GO

DECLARE @DeleteBeforeDate DATETIME 

-- Purge data older than 30 days
SELECT @DeleteBeforeDate = Dateadd(d, -30, Getdate()) 

EXEC sysmail_delete_mailitems_sp 
  @sent_before = @DeleteBeforeDate 

EXEC sysmail_delete_log_sp 
  @logged_before = @DeleteBeforeDate 

To purge data manually, we can make it atomize with scheduled job. I do not know about setting which removed data automatically from this system table. Do you know about the same? This issue I experienced first time and I want to know about your experience if you can share here. What will be your comment to this post?

Archive old database backup files using forfiles.exe and FOR /F by serverku

Before so many posts published I posted to the old database backup files archive using TSQL and using SSIS. Hope you visit that post and you liked them. I am repeating the same thing here, but it will be with two different methods, the first one with FOR /F command which we saw last time to copy database backups to another location too, second one with forfiles.exe. Let us move on the first method.

FOR /F :  As we discussed earlier post, it used same to traverse rows from generated file from SQLCMD command. Here we do not have values with delims (,) and we have only one values to grab and assign in variables.

SQLCMD -Udba -Pdba@1234 -S"PARESH\MSSQLSERVER2012" -dmsdb -Q"set nocount on ; 
SELECT DISTINCT bmf.Physical_device_name FROM  msdb.dbo.backupset (nolock) bs 
INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf on (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date < DATEADD(Day,-7,GETDATE()) " -o "C:\BackupFile.txt"

FOR /F "tokens=1,1 skip=2 delims=," %%G IN (C:\BackupFile.txt) DO del %%G

Please make sure SQLCMD and FOR /F should be in single line individualized. Running above code and captured snapshot as follows,

forfiles.exe : It is used to delete files in giving directories and subdirectories with a specified day or date. Syntax of it is following, Here /P is a directory, /m for search criteria, /S for subdirectories search, /d for days or date and /C command to fire. Please visit this site for more detail.

forfiles [/p <Path>] [/m <SearchMask>] [/s] [/c "<Command>"] [/d [{+|-}][{<Date>|<Days>}]]

Let us run it and capture snap again with the method,

Hope you have some other methods to archive old database backups. Request to share here!