Job to truncate database records more than 15 days old

I have a SQL Server 2K db with some 10 tables and I want to setup a nightly job to truncate all db records which are more than 15 days old. Can anyone provide me with steps involved? Any help will be highly appreciated.You do have a datetime field with the record createion date on the tables, right?

And you do not have any relational integrity that may get in your way?|||Thats correct.|||Then create a stored procedure perform,ing the delete and let it run nightly. SQL Server Assistant has to be running for this to happen. Pretty staraightforward.sql

Job ran for -95444:-89:-43 hours ?

I have a nightly job that runs at 2.00 AM...On saturday night first it ran
for -95444:-89:-43 and again ran at 2.00 AM
I know this occured beacuse we went back one hr on saturday night at 2.00
AM...My question is why the job should run again resulting in a failed
status? SQL Server should not have run this the second time. I hope in SQL
2005 this does not happen.
Thanks,
RangaHi
This would depend on when/how the next run date is calculated. This may be
how the scheduler is designed to work. If you moved the job off the hour you
should not see this occurring.
John
"Ranga" wrote:

> I have a nightly job that runs at 2.00 AM...On saturday night first it ra
n
> for -95444:-89:-43 and again ran at 2.00 AM
> I know this occured beacuse we went back one hr on saturday night at 2.00
> AM...My question is why the job should run again resulting in a failed
> status? SQL Server should not have run this the second time. I hope in SQ
L
> 2005 this does not happen.
> Thanks,
> Ranga

Job ran for -95444:-89:-43 hours ?

I have a nightly job that runs at 2.00 AM...On saturday night first it ran
for -95444:-89:-43 and again ran at 2.00 AM
I know this occured beacuse we went back one hr on saturday night at 2.00
AM...My question is why the job should run again resulting in a failed
status? SQL Server should not have run this the second time. I hope in SQL
2005 this does not happen.
Thanks,
RangaHi
This would depend on when/how the next run date is calculated. This may be
how the scheduler is designed to work. If you moved the job off the hour you
should not see this occurring.
John
"Ranga" wrote:
> I have a nightly job that runs at 2.00 AM...On saturday night first it ran
> for -95444:-89:-43 and again ran at 2.00 AM
> I know this occured beacuse we went back one hr on saturday night at 2.00
> AM...My question is why the job should run again resulting in a failed
> status? SQL Server should not have run this the second time. I hope in SQL
> 2005 this does not happen.
> Thanks,
> Ranga

Job Execution Error - Please Help

I have started having a problem with my SQL Server 2005 SSIS system and it has become a nightly event. What is happening is that sometime in the evening before 7:30 the server gets into a state in which any SSIS job that attempts to run, fails. The error message that SSIS reports is:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

I can bounce the SQL Server service and then packages run normally.

I will be greatful to anyone who can help with this error.

Best Regards,

Mark Redman.

Just searching I see this KB about server configuration.

"Err Msg: Not Enough Server Storage Is Available to Process This Command"

http://support.microsoft.com/kb/225782/en-us

Hope that helps

|||This Error has been solved. It turned out that all of the jobs were using the SQL Server destination with a named instance. Even though the destination was the local server, it appears that unless you use the <Local> designation, the server believes that you are going to a remote machine. Once I changed all of the destinations to an OLEDB connection, the errors disappeared.

Job Execution Error - Please Help

I have started having a problem with my SQL Server 2005 SSIS system and it has become a nightly event. What is happening is that sometime in the evening before 7:30 the server gets into a state in which any SSIS job that attempts to run, fails. The error message that SSIS reports is:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".

I can bounce the SQL Server service and then packages run normally.

I will be greatful to anyone who can help with this error.

Best Regards,

Mark Redman.

Just searching I see this KB about server configuration.

"Err Msg: Not Enough Server Storage Is Available to Process This Command"

http://support.microsoft.com/kb/225782/en-us

Hope that helps

|||This Error has been solved. It turned out that all of the jobs were using the SQL Server destination with a named instance. Even though the destination was the local server, it appears that unless you use the <Local> designation, the server believes that you are going to a remote machine. Once I changed all of the destinations to an OLEDB connection, the errors disappeared.

Job Executes Only First Step

I have three stored procedures that need to run nightly in SQL 7. The three
procedures are not related; but to keep the procedures from running at the
same time, I placed them as three steps of a single job. The first two steps
are set to "Goto next step" on success; the last step is set to "Quit with
success."

The job runs every night. However, only the first step/procedure is
executed. Also, the first step has a green flag next to its ID in the Steps
tab of the job properties

I suppose I could just call all three SPs from a single stored procedure
that is run nightly. But I thought that putting them as three steps in a
single job would cause all three to run. What am I doing wrong?

Thanks!

NeilAre you sure only the first step is executing? If you right click on the
job and choose show history, then check the checkbox to show the steps do
you see all three or only one?

--
Andrew J. Kelly SQL MVP

"Neil" <nospam@.nospam.net> wrote in message
news:KL5If.1367$UN.1000@.newsread2.news.pas.earthli nk.net...
>I have three stored procedures that need to run nightly in SQL 7. The three
>procedures are not related; but to keep the procedures from running at the
>same time, I placed them as three steps of a single job. The first two
>steps are set to "Goto next step" on success; the last step is set to "Quit
>with success."
> The job runs every night. However, only the first step/procedure is
> executed. Also, the first step has a green flag next to its ID in the
> Steps tab of the job properties
> I suppose I could just call all three SPs from a single stored procedure
> that is run nightly. But I thought that putting them as three steps in a
> single job would cause all three to run. What am I doing wrong?
> Thanks!
> Neil|||Yes, you are right. All three steps are executing. There was a problem with
the data which was giving the impression that the job wasn't executing.
Thanks!

Neil

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OsJ7bwNMGHA.740@.TK2MSFTNGP12.phx.gbl...
> Are you sure only the first step is executing? If you right click on the
> job and choose show history, then check the checkbox to show the steps do
> you see all three or only one?
> --
> Andrew J. Kelly SQL MVP
>
> "Neil" <nospam@.nospam.net> wrote in message
> news:KL5If.1367$UN.1000@.newsread2.news.pas.earthli nk.net...
>>I have three stored procedures that need to run nightly in SQL 7. The
>>three procedures are not related; but to keep the procedures from running
>>at the same time, I placed them as three steps of a single job. The first
>>two steps are set to "Goto next step" on success; the last step is set to
>>"Quit with success."
>>
>> The job runs every night. However, only the first step/procedure is
>> executed. Also, the first step has a green flag next to its ID in the
>> Steps tab of the job properties
>>
>> I suppose I could just call all three SPs from a single stored procedure
>> that is run nightly. But I thought that putting them as three steps in a
>> single job would cause all three to run. What am I doing wrong?
>>
>> Thanks!
>>
>> Neil
>>

Job Executes Only First Step

I have three stored procedures that need to run nightly in SQL 7. The three
procedures are not related; but to keep the procedures from running at the
same time, I placed them as three steps of a single job. The first two steps
are set to "Goto next step" on success; the last step is set to "Quit with
success."
The job runs every night. However, only the first step/procedure is
executed. Also, the first step has a green flag next to its ID in the Steps
tab of the job properties
I suppose I could just call all three SPs from a single stored procedure
that is run nightly. But I thought that putting them as three steps in a
single job would cause all three to run. What am I doing wrong?
Thanks!
NeilAre you sure only the first step is executing? If you right click on the
job and choose show history, then check the checkbox to show the steps do
you see all three or only one?
Andrew J. Kelly SQL MVP
"Neil" <nospam@.nospam.net> wrote in message
news:KL5If.1367$UN.1000@.newsread2.news.pas.earthlink.net...
>I have three stored procedures that need to run nightly in SQL 7. The three
>procedures are not related; but to keep the procedures from running at the
>same time, I placed them as three steps of a single job. The first two
>steps are set to "Goto next step" on success; the last step is set to "Quit
>with success."
> The job runs every night. However, only the first step/procedure is
> executed. Also, the first step has a green flag next to its ID in the
> Steps tab of the job properties
> I suppose I could just call all three SPs from a single stored procedure
> that is run nightly. But I thought that putting them as three steps in a
> single job would cause all three to run. What am I doing wrong?
> Thanks!
> Neil
>|||I believe that I saw this same question posted not too long ago.
The SP has to return an integer return status in order for DTS to know if it
completed successfully.
In other words, put
RETURN 0
at the bottom of your procedure.
--
"Neil" wrote:

> I have three stored procedures that need to run nightly in SQL 7. The thre
e
> procedures are not related; but to keep the procedures from running at the
> same time, I placed them as three steps of a single job. The first two ste
ps
> are set to "Goto next step" on success; the last step is set to "Quit with
> success."
> The job runs every night. However, only the first step/procedure is
> executed. Also, the first step has a green flag next to its ID in the Step
s
> tab of the job properties
> I suppose I could just call all three SPs from a single stored procedure
> that is run nightly. But I thought that putting them as three steps in a
> single job would cause all three to run. What am I doing wrong?
> Thanks!
> Neil
>
>|||Yes, you are right. All three steps are executing. There was a problem with
the data which was giving the impression that the job wasn't executing.
Thanks!
Neil
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OsJ7bwNMGHA.740@.TK2MSFTNGP12.phx.gbl...
> Are you sure only the first step is executing? If you right click on the
> job and choose show history, then check the checkbox to show the steps do
> you see all three or only one?
> --
> Andrew J. Kelly SQL MVP
>
> "Neil" <nospam@.nospam.net> wrote in message
> news:KL5If.1367$UN.1000@.newsread2.news.pas.earthlink.net...
>