File purge maintenance plan

Question

File purge maintenance plan

igloo21

Mr or Mrs. 500

Points: 534
More actions
September 10, 2009 at 7:22 am

#215022

.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

This will get you started. You can run this code to get the list of files and their dates into a table. You can then loop through the table, test the dates, build DEL command strings and then execute them using xp_cmdshell.

declare @directoryvarchar(200)

declare @cmdvarchar(800)

declare @dir table (rowid int identity(1,1), rowtext varchar(1000))

set @directory = '\\someServer\someDir\'

set @cmd = 'dir '+@directory+'*.*'

insert into @dir

exec xp_cmdshell @cmd

select * from @dir

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 2

igloo21

Mr or Mrs. 500

Points: 534

September 11, 2009 at 12:08 pm

#1052487

.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I said it was a start. I presumed you could parse the data once you had it in a table. I believe you can use the SUBSTRING() to pull just the file name and date out of those rows.

The question of what date to use is a different issue, and frankly your explanation is confusing. No matter what date the last day of a month is, the next day is the first. Or do you always do "month end" backups on the 30th? What do you do about February, which has 28 days except for leap years when it has 29?

Fill out this table for me please:

Month Backup Date Date to Use In Procedure

------ ------------ -------------------------

01

02

03

04

05

06

07

08

09

10

11

12

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 4

igloo21

Mr or Mrs. 500

Points: 534

September 11, 2009 at 1:49 pm

#1052539

.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

I've seen a lot of good discussions in here about business days calculations. Do a quick search on "Business Days" in the upper right hand corner and give me a call back if you don't find something that helps you with that issue.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

Sorry... duplicated my post. Please see below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

Please post 4 different file names that you wish to process. I also need you to post the CREATE TABLE statement for your calendar table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

It's also interesting to note that the accounting/calendar table doesn't include October 31st anywhere... at least not as you have posted it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 9

igloo21

Mr or Mrs. 500

Points: 534

September 12, 2009 at 5:57 am

#1052680

.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

igloo,

To put this in plain English, the date you want is the last businessday of the previous month.

Is that correct or not?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 11

igloo21

Mr or Mrs. 500

Points: 534

September 12, 2009 at 8:30 am

#1052709

.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 12

Did looking at any of the forums or articles on business days answer that question for you?

Do you currently have a calendar table?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

igloo21 Mr or Mrs. 500 Points: 534 More actions · Answer 13

Yes, I do have a calender table, that's actually where I got the dates that I posted from. I did some searches for business days but came up empty handed. 🙁

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

That doesn't tell us much....

When I search on "business days" I find pages of entries. There may not be one that says precisely "Find Last Business Day Of The Month", but the concepts spelled out in there should educate you about how to do it. With a calendar table it's easy:

select max(date)

from calendar

where daytype = 'Business'

and date < getdate()

What does your calendar table look like?

Does it have a row for every date, or just for weekends and holidays?

If a row for every date, does it have a column that indicates whether or not that date is a weekend, holiday, or business day?

Remember, we can't see your screen and we can't read your mind. Slow down and get specific. We have no information to work with other than what you take the effort to type out. That's why I had to ask you if the date in question was the last business day of the month. Your original post said nothing even close to that.

I need Month end backups taken on the second day after month end. Not the 2nd of the month. Example: Month End for October is on the 30th (not 31st). So I need the November 1st backup (not November 2nd).

Now I'm going to go enjoy my weekend. Hope someone else can take you the rest of the way. Have a good one.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills