How to determine if a record is not sync''d

Hello.

I'm not sure if this is possible, but I was hoping to get an answer
here. I'm creating a Windows Mobile 5.0 Smartphone program which
uses SqlCeResultSets as its data objects.

I was wondering if there is
anyway to easily tell if there are records which have been inserted
into the local DB but have not been sync'd up to the publisher yet.

I use merge replication to sync the device with the host and I know
that the merge replicator obviously can tell the difference. Is there
any special field or flag that I can check to see if the record is
only local, on the subscriber side?

Thanks!

SMP

from msmerge_genhistory you can find out the genstatus of each generation. For each generation that has not been delivered you can then use msmerge_contents to lookup the generation changes and that will give you the rowguid of the record that has not been delivered and the tablenick.

Take a look at this page:

http://www.replicationanswers.com/MergeInternals1.asp

Martin

How to determine if a record is not sync''d

Hello.

I'm not sure if this is possible, but I was hoping to get an answer
here. I'm creating a Windows Mobile 5.0 Smartphone program which
uses SqlCeResultSets as its data objects.

I was wondering if there is
anyway to easily tell if there are records which have been inserted
into the local DB but have not been sync'd up to the publisher yet.

I use merge replication to sync the device with the host and I know
that the merge replicator obviously can tell the difference. Is there
any special field or flag that I can check to see if the record is
only local, on the subscriber side?

Thanks!

SMP

from msmerge_genhistory you can find out the genstatus of each generation. For each generation that has not been delivered you can then use msmerge_contents to lookup the generation changes and that will give you the rowguid of the record that has not been delivered and the tablenick.

Take a look at this page:

http://www.replicationanswers.com/MergeInternals1.asp

Martin

How to Deploy the reports automatically in SSRS?

Hi,

I am creating a application to show reports for daily based information.

I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.

So i need to deploy the reports automatically.

can any one gave a idea to do this.

thanks

Hi,

You can do this via the RS command.

What you need to do is write a script (.rss) that would serve as an input file to the rs command.

this script should loop through a folder and upload all extension with .rdl

Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.

once this has been done go to cmd and do the following

1. Map to your folder ie cd: D:\RS

2. type in the following command

rs -i PublishReports.rss -s http://localhost/reportserver

PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.

Hope this helps.

|||

Hi,

Thanks for your solution.

It's work fine.

How to Deploy the reports automatically in SSRS?

Hi,

I am creating a application to show reports for daily based information.

I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.

So i need to deploy the reports automatically.

can any one gave a idea to do this.

thanks

Hi,

You can do this via the RS command.

What you need to do is write a script (.rss) that would serve as an input file to the rs command.

this script should loop through a folder and upload all extension with .rdl

Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.

once this has been done go to cmd and do the following

1. Map to your folder ie cd: D:\RS

2. type in the following command

rs -i PublishReports.rss -s http://localhost/reportserver

PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.

Hope this helps.

|||

Hi,

Thanks for your solution.

It's work fine.

How to deploy SQL Server Everywhere with my windows form application?

Hello!

I've been creating a vb.net 2005 windows form application using Everywhere and it has been going pretty well. However, I haven't had much luck finding any help on how to deploy my Everywhere database file(s) and Everywhere itself. To install redistribute Everywhere, can I just add the the 8 .dlls found in the Everywhere directory into my VS setup project? Also, what database file(s) do I need to deploy in my windows setup?

Thanks!

http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx

Darren

|||

Thanks for the link Darren. This looks like it's using ClickOnce but I was hoping to deploy Everything using the Windows installer. Any thoughts on how that might happen?

Thanks!

|||

it's the same process of putting the right DLLs that belong to SQL Ev into your program's app directory.

-ds

How to Deploy SQL server Database to another PC while creating SetUP package in .Net VB

How to Deploy SQL server Database to another PC, How to create a
package that craetes Database as well as ODBC driver for accessing data
at enduser PC, using .Net VBhi,
<hitendra15@.gmail.com> ha scritto nel messaggio
news:1102241845.011857.112670@.c13g2000cwb.googlegr oups.com
> How to Deploy SQL server Database to another PC, How to create a
> package that craetes Database as well as ODBC driver for accessing
> data at enduser PC, using .Net VB

John already answered you about drivers...
as regard database installation, personally I do not like backup/restore
practice, nor detach/attach...
and Ialways go for executing the DDL scripts to recreate the dbs and related
objects as long as performing BCP in and/or INSERT INTO scripts in order to
populate pre-loaded tables...
the best "universal" approach I've seen so far explained in a public article
is
http://msdn.microsoft.com/sql/archi...er/default.aspx
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply|||To add on to Andrea's response, we keep all of our DDL scripts under source
control and run these scripts during installation with a .Net custom action.

Depending on your requirements, you can include the scripts in a .Net
project as either content files or as embedded resources. You can then
include that project in your Setup and Deployment project. Your custom
action can read and execute the scripts from either the resource assembly or
from the file system.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<hitendra15@.gmail.com> wrote in message
news:1102241845.011857.112670@.c13g2000cwb.googlegr oups.com...
> How to Deploy SQL server Database to another PC, How to create a
> package that craetes Database as well as ODBC driver for accessing data
> at enduser PC, using .Net VB

How to deploy report in C# with ADO XML datasources

HI,

i m creating CR in C# and ADO.net XML file as data source. my application is calling CR in exe file for viewing report. although i hav work out the setup and deployment project, yet its still need to install Crystalreport in MS.Visual studio 2003.

any others way to solve this problem?

plz don hesitate to reply me k.

emiliecan you please clarify the question? You don't know what you need to package to get crystal working on the deployment machine?

How to delete records on dependent tables? Thank You.

Hello,
I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:
Surveys - [SurveyId](PK) and [SurveyName]
Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
[SurveyAnswer]
Each survay can include various questions and each question can include
several answers.
This is way I am using the Foreign Keys in both Questions and Answers
tables. To relate the tables.
I created a procedure which deletes a Survey given its SurveyId. This is
part is done.
I also need to delete all the questions dependent on that survey and all
the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving
the SurveyId?
Thank You Very Much,
Miguel
Here is the code of the procedure that I created which in this moment
only deletes the survey from the Surveys table:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
-- Procedure Parameters
@.SurveyId As uniqueidentifier
AS
BEGIN
-- Check if SurveyId is null
IF( @.SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
SurveyId ) )
RETURN -1
END
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
ENDYou just need to add cascade delete to your foreign key constraints and the
database will do this automatically.
This assumes that you always want to delete the related records.
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am creating my first procedures in SQL using SQL 2005.
> I have 3 tables, with the following columns:
> Surveys - [SurveyId](PK) and [SurveyName]
> Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
> Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
> [SurveyAnswer]
> Each survay can include various questions and each question can include
> several answers.
> This is way I am using the Foreign Keys in both Questions and Answers
> tables. To relate the tables.
> I created a procedure which deletes a Survey given its SurveyId. This is
> part is done.
> I also need to delete all the questions dependent on that survey and all
> the answers dependent on those questions.
> How can I delete survey, its questions and their answers when receiving
> the SurveyId?
> Thank You Very Much,
> Miguel
> Here is the code of the procedure that I created which in this moment
> only deletes the survey from the Surveys table:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
> -- Procedure Parameters
> @.SurveyId As uniqueidentifier
> AS
> BEGIN
> -- Check if SurveyId is null
> IF( @.SurveyId IS NULL )
> RETURN -1
> ELSE
> BEGIN
> -- Return '-1' if a survey with SurveyId given value is not found
> IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
> SurveyId ) )
> RETURN -1
> END
> -- Delete the survey with SurveyId given value
> DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
> -- Return '0' when successful
> RETURN 0
> END
>|||Hi,
Could you, please, explain how to add cascade delete to my foreign key
constraints.
I am starting with SQL and I have no idea how to do that.
Thanks,
Miguel
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
> You just need to add cascade delete to your foreign key constraints and th
e
> database will do this automatically.
> This assumes that you always want to delete the related records.
> "Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
> news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...|||It is best to look it up in Books OnLine, or check with your DBA.
Here is an example of the syntax, however.
ALTER TABLE [owner].[tablename] ADD CONSTRAINT
[constraintname] Foreign KEY
(
[Columnname]
) REFERENCES [owner].[OtherTablename] (
[Columnname]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ujxYNCOVGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could you, please, explain how to add cascade delete to my foreign key
> constraints.
> I am starting with SQL and I have no idea how to do that.
> Thanks,
> Miguel
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
>
the
[SurveyQuestion]
include
is
all
receiving
>

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent