Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL not deleting files Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 2:31 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 321, Visits: 571
Why is the following statement not deleting the backup files?

EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master',N'.bak',N'2012-09-12T16:25:36',1

It finishes without error, in a second, but there are still files with the .bak at that file path.. dating back to Jan 2011.. when we loaded SQl on to this box.

Post #1361622
Posted Wednesday, September 19, 2012 4:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
dwilliscp (9/19/2012)
Why is the following statement not deleting the backup files?

EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master',N'.bak',N'2012-09-12T16:25:36',1

It finishes without error, in a second, but there are still files with the .bak at that file path.. dating back to Jan 2011.. when we loaded SQl on to this box.



Probably because of the "." in the N'.bak' string. This is probably being done through a maintenance plan, and you don't include the "." when providing the extension for the file names.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361677
Posted Wednesday, September 19, 2012 4:26 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 4, 2014 5:29 AM
Points: 967, Visits: 396
Ah, the joys of using undocumented stored procedures...

Note that there's a "feature" in the SQL2005 RTM version of that xp too - it won't traverse subdirectories, as I found a few years ago... Pimping myself? Surely not...



Post #1361687
Posted Thursday, September 20, 2012 6:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 321, Visits: 571
Thanks Lynn, that was the problem... darn I did not even think anything when I read the T-SQL.. you were right that they created this as a maintenance plan. Interesting that our non-system databases are all done using stored proc's but the system ones use maintenance plans.
Post #1361921
Posted Thursday, September 20, 2012 6:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 321, Visits: 571
ThomasRushton (9/19/2012)
Ah, the joys of using undocumented stored procedures...

Note that there's a "feature" in the SQL2005 RTM version of that xp too - it won't traverse subdirectories, as I found a few years ago... Pimping myself? Surely not...


Well we are running 2005 but it is deleteing files at least one level down.. from the backup directory.
Post #1361924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse