How to determine last "monthversary" date of an account?

I work on a project where advertisers on a classifieds Web site have the
possibility to place a certain number of ads per month.
The difficulty comes from the fact that a month period starts at each
"monthversary", meaning that if somebody created his account on June 15, I
would need to take into account the ads placed since the 15th of this
month...
I am simply trying to see how I can extract, in a very compact way, the last
"monthversary" date knowing the date the account was created and the current
date.
Right now I am using a method that works: I extract the day from the
creation date, prepend the current month and append the year, then:
1- if the "monthversary" is after today's date, go back one month
2- take care of the 30-day months issue and of of February as well (even
leap year)
3- take care of the year changes
And then I have a string that represents the last "monthversary" date, but I
am sure there is a simple, one-liner way to do this.
Thanks a lot!White,
Can you post the DDL and sample data? Sounds like the MAX function may work
here.
HTH
Jerry
"White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote in message
news:435922b5$0$3767$39cecf19@.news.twtelecom.net...
>I work on a project where advertisers on a classifieds Web site have the
>possibility to place a certain number of ads per month.
> The difficulty comes from the fact that a month period starts at each
> "monthversary", meaning that if somebody created his account on June 15, I
> would need to take into account the ads placed since the 15th of this
> month...
> I am simply trying to see how I can extract, in a very compact way, the
> last "monthversary" date knowing the date the account was created and the
> current date.
> Right now I am using a method that works: I extract the day from the
> creation date, prepend the current month and append the year, then:
> 1- if the "monthversary" is after today's date, go back one month
> 2- take care of the 30-day months issue and of of February as well (even
> leap year)
> 3- take care of the year changes
> And then I have a string that represents the last "monthversary" date, but
> I am sure there is a simple, one-liner way to do this.
> Thanks a lot!
>|||lookup datedd and datepart in BOL
select dateadd(m,-1,getdate())
http://sqlservercode.blogspot.com/
"White Echo" wrote:

> I work on a project where advertisers on a classifieds Web site have the
> possibility to place a certain number of ads per month.
> The difficulty comes from the fact that a month period starts at each
> "monthversary", meaning that if somebody created his account on June 15, I
> would need to take into account the ads placed since the 15th of this
> month...
> I am simply trying to see how I can extract, in a very compact way, the la
st
> "monthversary" date knowing the date the account was created and the curre
nt
> date.
> Right now I am using a method that works: I extract the day from the
> creation date, prepend the current month and append the year, then:
> 1- if the "monthversary" is after today's date, go back one month
> 2- take care of the 30-day months issue and of of February as well (even
> leap year)
> 3- take care of the year changes
> And then I have a string that represents the last "monthversary" date, but
I
> am sure there is a simple, one-liner way to do this.
> Thanks a lot!
>
>|||Sorry, different computer if you see what I mean.
But the function is something that I could also use at this computer.
Simply put:
If I opened my account on June 15 2005, what function would help me to
determine what would be the day of the beginning of the current account
period, knowing that an accounting period starts at each "monthversary"?
(By the way I was surprised to see that the expression "monthversary" is out
there).
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23zVNfTm1FHA.2076@.TK2MSFTNGP14.phx.gbl...
> White,
> Can you post the DDL and sample data? Sounds like the MAX function may
> work here.
> HTH
> Jerry
> "White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote in message
> news:435922b5$0$3767$39cecf19@.news.twtelecom.net...
>|||Nicolas,
Sometimes data works better than narratives. Could you post me some sample
data i.e, here is the date the account was opened, here is the monthversary,
and here is the date I want with x being the current date? Sample data -->
desired results?
Thanks
Jerry
"Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote
in message news:435930ec$0$3759$39cecf19@.news.twtelecom.net...
> Sorry, different computer if you see what I mean.
> But the function is something that I could also use at this computer.
> Simply put:
> If I opened my account on June 15 2005, what function would help me to
> determine what would be the day of the beginning of the current account
> period, knowing that an accounting period starts at each "monthversary"?
> (By the way I was surprised to see that the expression "monthversary" is
> out there).
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23zVNfTm1FHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||This only removes on month from Today's date... It does not help.
> lookup datedd and datepart in BOL
> select dateadd(m,-1,getdate())
> http://sqlservercode.blogspot.com/
> "White Echo" wrote:
>|||I thought I did this, I am sorry.
For instance:
Account created on June 15, 2005.
Today's date: October 21, 2005.
The result here would be: October 15, 2005
Account created on January 31, 2004.
Today's date: October 21, 2005.
The result here would be: September 30, 2005
If the account is created on June 15, 2005...
The first "month" started on July 15, 2004.
The second "month" on August 15, 2004
Etc...
But what I need to calculate is when the current "month" started for this
account.
The start day of a month is not the first day of a month, but rather what I
call the "monthversary": the same day number as that of the creation date.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elZhW9m1FHA.904@.tk2msftngp13.phx.gbl...
> Nicolas,
> Sometimes data works better than narratives. Could you post me some
> sample data i.e, here is the date the account was opened, here is the
> monthversary, and here is the date I want with x being the current date?
> Sample data --> desired results?
> Thanks
> Jerry
> "Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam>
> wrote in message news:435930ec$0$3759$39cecf19@.news.twtelecom.net...
>|||On Fri, 21 Oct 2005 10:17:33 -0700, White Echo wrote:
(snip)
>Right now I am using a method that works: I extract the day from the
>creation date, prepend the current month and append the year, then:
>1- if the "monthversary" is after today's date, go back one month
>2- take care of the 30-day months issue and of of February as well (even
>leap year)
>3- take care of the year changes
>And then I have a string that represents the last "monthversary" date, but
I
>am sure there is a simple, one-liner way to do this.
Hi White Echo,
Not sure if it's simpler, but you could write it in one line (though I
prefer slightly more formatting - read this emssage with a fixed font
for best effect), and it has the definite advantage that it can be used
in a query to process all rows at once (though my example uses only a
variable):
DECLARE @.StartDate datetime
SET @.StartDate = '20050615'
SELECT DATEADD(month,
DATEDIFF(month,
@.StartDate,
CURRENT_TIMESTAMP)
- CASE WHEN DAY(@.StartDate) > DAY(CURRENT_TIMESTAMP)
THEN 1
ELSE 0
END,
@.StartDate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much, my friend!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7mmil1t3gtgk6ktgcisgteocp120egtvlh@.
4ax.com...
> On Fri, 21 Oct 2005 10:17:33 -0700, White Echo wrote:
> (snip)
> Hi White Echo,
> Not sure if it's simpler, but you could write it in one line (though I
> prefer slightly more formatting - read this emssage with a fixed font
> for best effect), and it has the definite advantage that it can be used
> in a query to process all rows at once (though my example uses only a
> variable):
> DECLARE @.StartDate datetime
> SET @.StartDate = '20050615'
> SELECT DATEADD(month,
> DATEDIFF(month,
> @.StartDate,
> CURRENT_TIMESTAMP)
> - CASE WHEN DAY(@.StartDate) > DAY(CURRENT_TIMESTAMP)
> THEN 1
> ELSE 0
> END,
> @.StartDate)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to determine if a user has a permission programmatically?

Hi,
I need to determine in advance if a user logged on to SQL Server 2005 can
execute certain privileged stored procedures and statements (such as
sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
determine programmatically if a user has specific permissions (ALTER ANY
LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
ALTER ANY ROLE).
Is there a way to perform this check programmatically via T-SQL? If this can
be done programmatically, is there a way to determine if a permission is
implied implicitly if it is not set up explicitly (e.g. via a group the user
belongs to, such as BUILTIN\Administrators)? Is there a better approach to
make the check?
Thanks,
AlekTry sp_helprotect , described here
http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Alek" <alekDOTdavisATintelDOTcom> wrote in message
news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to determine in advance if a user logged on to SQL Server 2005 can
> execute certain privileged stored procedures and statements (such as
> sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
> determine programmatically if a user has specific permissions (ALTER ANY
> LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
> ALTER ANY ROLE).
> Is there a way to perform this check programmatically via T-SQL? If this
> can be done programmatically, is there a way to determine if a permission
> is implied implicitly if it is not set up explicitly (e.g. via a group the
> user belongs to, such as BUILTIN\Administrators)? Is there a better
> approach to make the check?
> Thanks,
> Alek
>|||In SQL Server 2005, you can use the has_perms_by_name
function to check the current users effective permissions on
securable.
http://msdn2.microsoft.com/en-us/library/ms189802.aspx
-Sue
On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
<alekDOTdavisATintelDOTcom> wrote:

>Hi,
>I need to determine in advance if a user logged on to SQL Server 2005 can
>execute certain privileged stored procedures and statements (such as
>sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
>determine programmatically if a user has specific permissions (ALTER ANY
>LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
>ALTER ANY ROLE).
>Is there a way to perform this check programmatically via T-SQL? If this ca
n
>be done programmatically, is there a way to determine if a permission is
>implied implicitly if it is not set up explicitly (e.g. via a group the use
r
>belongs to, such as BUILTIN\Administrators)? Is there a better approach to
>make the check?
>Thanks,
>Alek
>|||Thank you Remus. This is very helpful.
Alek
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:%233xgzrYHHHA.1248@.TK2MSFTNGP03.phx.gbl...
> Try sp_helprotect , described here
> http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Alek" <alekDOTdavisATintelDOTcom> wrote in message
> news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
>|||Thanks a lot Sue. This looks exactly like what I'm looking for. :-)
Alek
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ng3sn2pdu6apq505ftk8mhnpp73l2i7mk7@.
4ax.com...
> In SQL Server 2005, you can use the has_perms_by_name
> function to check the current users effective permissions on
> securable.
> http://msdn2.microsoft.com/en-us/library/ms189802.aspx
> -Sue
> On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
> <alekDOTdavisATintelDOTcom> wrote:
>
>sql

How to detect if column data changed and know prev. and new value

I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

BillHi

Check out CREATE TRIGGGER in Books Online or at
http://msdn.microsoft.com/library/d...asp?frame=true

In particular the COLUMNS_UPDATED example of the IF UPDATE clause.

John

"Bill Tepe" <billtepe@.mssonline.net> wrote in message
news:7364847c.0309060600.7023b89a@.posting.google.c om...
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.
> Is there a "simple" way to do this? I know I could query the table
> before the update and compare to what the new value is and react
> accordingly.
> Just wondering if there is something nifty in Sql Server that I am
> missing that could help me with this.
> Thanks in advance for your help.
> Bill|||[posted and mailed, please reply in news]

Bill Tepe (billtepe@.mssonline.net) writes:
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.

In a trigger you can retrieve the new value in the "inserted" table
and the old value in the "deleted" tables. These tables are virtual
and are accessible only in the trigger.

Beware that a trigger in SQL Server fires once per statement, not once
per row as in some other products. Thus, the tables can old many rows.

You should also be aware of access to these tables when they contain
many rows can be slow. Therefore it is often good idea to start a trigger
with:

select * INTO #tblname_inserted FROM inserted
select * INTO #tblname_deleted FROM deleted

Since you are into auditing... If you are doing this on any large
scalce, you should probably consider third-party solutions rather
than reinventing the wheel. www.redmatrix.com has a product SQLAudit,
which I have no experience of myself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you need to do this at more of an enterprise level, you might look
into Lumigent's Entegra (haven't used it but buying it next year :))

http://lumigent.com/products/entegra/entegra.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. Thexp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix