Using xp_delete_file in maintenance plans

There is no official documentation on the usage of the Stored Procedure xp_delete_file. In my research I have found that it can be used outside of the Maintenance plans. I also ran into some issues with it not deleting the backup files that I wanted it to delete in sub-directories.

It looks like in certain versions of MSSQL 2005 that the option to remove files from sub-directories is just not there.

The only resolution is to upgrade or grab the complete accumulative service pack according to Microsoft.

On one of the servers that I wanted to implement this on is going away soon so I didn't want to apply any updates but I still needed to implement a Maintenance plan that would remove old backups. The solution was to create a "Maintenance Cleanup Task" for every back folder containing the backup files that I wanted to have deleted. Luckily it was only 9 folders that I had to deal with.

For using xp_delete_file out side of the Maintenance plan here is some information I have gathered.

Usage:
SQL Server Version 9.0.1399
EXECUTE master.dob.xp_delete_file 0,N'E:SQLbackupdir',N'bak',N'06-03-2011 08:00:00'

On different versions I noticed you could use the parameter at the end to tell it to go into sub-directories but only the first level.

EXECUTE master.dbo.xp_delete_file 0,N'E:SQLbackupdir',N'bak',N'06-03-2011 08:00:00',1

So here it is broken down.

,N'E:\SQLbackupdir' - path of the directory that has the backup files
,N'bak' - extention of the backup file
,N'06-03-2011 08:00:00' - it will remove everything older than this date
,1 - some version have this parameter its for sub-directories

MySQL Error 1050 table already exists error

In MySQL when running on a Windows server with "Make table names case insensitive" set to 2. You will get a table exists error when trying to create a table that has previously been created. I have found that this occurs if you use upper case letters in the name of the table. For example: Create Table JohnTempTable (ud1 int, ud2 varchar(10)); Drop Table JohnTempTable; Now when you try and create the same table you get a table exists error. The work around I found is to create your original table using all lower case. example: Create Table johntemptable (ud1 int, ud2 varchar(10)); Now you can drop and recreate the table without the table exists error.

Using NOLOCK in sql select statements

It may not be necessary to use NOLOCK after the from clause in your scripts. Microsoft has put out some info on the use of NOLOCK and I have come to the conclusion that using NOLOCK should be the exception to the rule and not the rule. Here is a link that explains.

http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

Enjoy the article its quite long.

Thanks