Database Overwritten: plan

Showing posts with label plan. Show all posts

Wednesday, March 21, 2012

Database Migration

Hello.
I'm going to migrate an entrie SQL Server to a new box. The original plan
was to backup and restore all DBs on the new server, but due the long time of
the backup I'd like to try other way.
I'm thinking of simply offline->copy->attach .MDF to the new server, but as
the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if a
simple attach is risk-free (basically in terms of systables/sysprocedures) or
maybe exist a better way to do it.
What can you advice me?
Thanks
RodrigoBackups should give you much less downtime than you can achieve by
detaching and re-attaching:
1. Database backup on Server A
2. Restore to new Server B
3. Set A to single-user mode
4. Transaction log backup on A
5. Restore transaction log(s) on B
Make sure you read the following article. Particularly the references
to orphaned users:
http://support.microsoft.com/?id=314546
--
David Portas
SQL Server MVP
--|||Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> Hello.
> I'm going to migrate an entrie SQL Server to a new box. The original plan
> was to backup and restore all DBs on the new server, but due the long time
> of
> the backup I'd like to try other way.
> I'm thinking of simply offline->copy->attach .MDF to the new server, but
> as
> the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> a
> simple attach is risk-free (basically in terms of systables/sysprocedures)
> or
> maybe exist a better way to do it.
> What can you advice me?
> Thanks
> Rodrigo|||Thanks David & Narayana; both excellent links.
There they solve the doubt about moving DBs between SQL versions (answer: no
problem).
The orphan users were been considerated too.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
> news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> > Hello.
> >
> > I'm going to migrate an entrie SQL Server to a new box. The original plan
> > was to backup and restore all DBs on the new server, but due the long time
> > of
> > the backup I'd like to try other way.
> >
> > I'm thinking of simply offline->copy->attach .MDF to the new server, but
> > as
> > the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> > a
> > simple attach is risk-free (basically in terms of systables/sysprocedures)
> > or
> > maybe exist a better way to do it.
> >
> > What can you advice me?
> >
> > Thanks
> > Rodrigo
>
>sql

Monday, March 19, 2012

Database Maintnenance Plan

Good Afternoon
I am using database maintenance plans in order to perform
Backups, including optimisation, index re-builds etc.
The problem is that I have discovered that the Maintenance
Plans do not always perform the Backup, although the rest
of the plans perform normally: -
Two Maintenance Plans performing Full Backup, one at
midday and one in the evening, monday to friday, deleting
old files, re-building indexes, re-sizing databases and
optimising unused space.
Two Maintenance Plans performing Transaction Log Backups,
one from 08:00 to 11:00 and the other 13:00 to 18:, monday
to friday, deleting old files and re-building indexes.
There is plenty of space on the Server to allow for the
Backups. E.g. The drive for the Transaction Log Backups
has over 5Gb of space available, the collective size of
the Backups never total more than 350Mb.
I created these Maintenance Plans, and I am registered on
the SQL Server as a System Administrator.
Thank You In Advance
Tony C.
Make sure that you have specified a report file for the maint plan and check that file for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tony C" <anonymous@.discussions.microsoft.com> wrote in message news:20d201c4279b$33e423a0$a601280a@.phx.gbl...
> Good Afternoon
> I am using database maintenance plans in order to perform
> Backups, including optimisation, index re-builds etc.
> The problem is that I have discovered that the Maintenance
> Plans do not always perform the Backup, although the rest
> of the plans perform normally: -
> Two Maintenance Plans performing Full Backup, one at
> midday and one in the evening, monday to friday, deleting
> old files, re-building indexes, re-sizing databases and
> optimising unused space.
> Two Maintenance Plans performing Transaction Log Backups,
> one from 08:00 to 11:00 and the other 13:00 to 18:, monday
> to friday, deleting old files and re-building indexes.
> There is plenty of space on the Server to allow for the
> Backups. E.g. The drive for the Transaction Log Backups
> has over 5Gb of space available, the collective size of
> the Backups never total more than 350Mb.
> I created these Maintenance Plans, and I am registered on
> the SQL Server as a System Administrator.
> Thank You In Advance
>
> Tony C.
|||Thanks for this, the Maintenance Plans were producing the
Logs..
It would seem that one of the Applications we are using
can hold up other Transactions when it is left in a
certain state, I've therefore got a bit of debugging to
do!!
Tony C
>--Original Message--
>Make sure that you have specified a report file for the
maint plan and check that file for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Tony C" <anonymous@.discussions.microsoft.com> wrote in
message news:20d201c4279b$33e423a0$a601280a@.phx.gbl...[vbcol=seagreen]
perform[vbcol=seagreen]
Maintenance[vbcol=seagreen]
rest[vbcol=seagreen]
deleting[vbcol=seagreen]
Backups,[vbcol=seagreen]
monday[vbcol=seagreen]
on
>
>.
>

Database Maintenence Plan Error

When trying to setup a Database Maintenence Plan, it does not give me
an option for amount of days to keep before deleting, the dropdown box
is empty. also, I set the UNC path and when I go out and come back
into the plan, this is empty, but the backup runs with 17055 errors.
How can I find out how to correct this?
RickRick,
What build of SQL2005 are you running?
Chris
"Rick" <drummer10980@.gmail.com> wrote in message
news:1189777049.876020.129450@.y42g2000hsy.googlegroups.com...
> When trying to setup a Database Maintenence Plan, it does not give me
> an option for amount of days to keep before deleting, the dropdown box
> is empty. also, I set the UNC path and when I go out and come back
> into the plan, this is empty, but the backup runs with 17055 errors.
> How can I find out how to correct this?
> Rick
>|||Actually running MSDE
Rick
On Sep 14, 9:52 am, "Chris Wood" <anonym...@.microsoft.com> wrote:
> Rick,
> What build of SQL2005 are you running?
> Chris
> "Rick" <drummer10...@.gmail.com> wrote in message
> news:1189777049.876020.129450@.y42g2000hsy.googlegroups.com...
>
> > When trying to setup a Database Maintenence Plan, it does not give me
> > an option for amount of days to keep before deleting, the dropdown box
> > is empty. also, I set the UNC path and when I go out and come back
> > into the plan, this is empty, but the backup runs with 17055 errors.
> > How can I find out how to correct this?
> > Rick- Hide quoted text -
> - Show quoted text -

Database maintence plan

I have looked at the wizard for creating a database
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
TomWe have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom

Database maintence plan

I have looked at the wizard for creating a database
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
Tom
We have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom

Database maintence plan

I have looked at the wizard for creating a database
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
TomWe have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom

Database Maintenance question...

I set up a Database Maintenance plan for backing 3 databases, their
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenance
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!
Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
You could use SMTP emails instead see
http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:

> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or do a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the database
> and transaction log backups and the optimization job. Again, the maintenance
> plan is configured for 3 databases, all of which are set to "Full" for their
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!
|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
> You could use SMTP emails instead see
> http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:

Database Maintenance question...

I set up a Database Maintenance plan for backing 3 databases, their
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do
a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenanc
e
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:

> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or d
o a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the databa
se
> and transaction log backups and the optimization job. Again, the maintena
nce
> plan is configured for 3 databases, all of which are set to "Full" for the
ir
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of t
he
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
>|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
>|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
>

Database Maintenance problem

Hi again,
i was wondering if anyone has encountered this problem:
I make a database maintenance plan (through the wizard) because i want to
backup the
server files to another computer on my network
I have made a map to the other computer (as G:),
and i then go to the tab "Complete Backup" and then i select "Use this
directory" option
to select the directory for the baclup files in the other computer.
But, the listbox presented at me there, displays only the locals C:\ and D:\
drives,
not the other mapped one (G:).
I manually enter the full path name and of cource the job cannot execute.
Has anyone any solution to that ?
anthonybI use UNC paths and it works fine for me. Remember that YOU are not going
to be executing this plan it will be SQL Server Agent so G may have
absolutely no meaning to it whatsoever.
Allan
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anthony Boudouvas" <anthonyb@.mediatel.gr> wrote in message
news:eK88Tv0VDHA.2252@.TK2MSFTNGP10.phx.gbl...
> Hi again,
> i was wondering if anyone has encountered this problem:
> I make a database maintenance plan (through the wizard) because i want to
> backup the
> server files to another computer on my network
> I have made a map to the other computer (as G:),
> and i then go to the tab "Complete Backup" and then i select "Use this
> directory" option
> to select the directory for the baclup files in the other computer.
> But, the listbox presented at me there, displays only the locals C:\ and
D:\
> drives,
> not the other mapped one (G:).
> I manually enter the full path name and of cource the job cannot execute.
> Has anyone any solution to that ?
> anthonyb
>

Sunday, March 11, 2012

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> >
> >|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > SP4 on another system to make sure your apps work correctly first, of course.
> >
> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > point out what is happening.
> >
> > That being said, there is an extended stored procedure that Microsoft uses
> > to delete the files. If the Agent service doesn't have the proper rights to
> > the subdirectory you might find that to be the issue.
> >
> > "Gary S" wrote:
> >
> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > database on the server and have created a database maintenance plan that
> > > backups up all the database nightly. I have also configured the maintenance
> > > plan to delete back files older than 3 days old but this isn't currently
> > > happening.
> > >
> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > >
> > >|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
> > What is the extended store procedure that's used so that I can check it
> > hasn't been removed.
> >
> > "Buck Woody - Microsoft SQL Server Team" wrote:
> >
> > > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > > SP4 on another system to make sure your apps work correctly first, of course.
> > >
> > > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > > point out what is happening.
> > >
> > > That being said, there is an extended stored procedure that Microsoft uses
> > > to delete the files. If the Agent service doesn't have the proper rights to
> > > the subdirectory you might find that to be the issue.
> > >
> > > "Gary S" wrote:
> > >
> > > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > > database on the server and have created a database maintenance plan that
> > > > backups up all the database nightly. I have also configured the maintenance
> > > > plan to delete back files older than 3 days old but this isn't currently
> > > > happening.
> > > >
> > > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > > >
> > > >|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the
>> > setup of
>> > SP4 on another system to make sure your apps work correctly first, of
>> > course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server
>> > Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They
>> > might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft
>> > uses
>> > to delete the files. If the Agent service doesn't have the proper
>> > rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a
>> > > number of
>> > > database on the server and have created a database maintenance plan
>> > > that
>> > > backups up all the database nightly. I have also configured the
>> > > maintenance
>> > > plan to delete back files older than 3 days old but this isn't
>> > > currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone
>> > > else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this
>> > > issue.
>> > >
>> > >|||> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users.
Buck,
Above is for 2005. For 2000, the files are removed by sqlmaint.exe (which is called by xp_sqlmaint
if the plan is created by the wizard).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Buck Woody - Microsoft SQL Server Team" <BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com>
wrote in message news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
>> > SP4 on another system to make sure your apps work correctly first, of course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft uses
>> > to delete the files. If the Agent service doesn't have the proper rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
>> > > database on the server and have created a database maintenance plan that
>> > > backups up all the database nightly. I have also configured the maintenance
>> > > plan to delete back files older than 3 days old but this isn't currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>> > >
>> > >|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:
> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html

Database Maintenance Plans - Not removing Files Older than x days

Database Maintenance Plans

I've set up a Database Maintenance Plan that Checks Database Integrity,
Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up History.
If I set the plan to perform these tasks on either All Databases or All User
Databases, will new databases be detected? Or would I need to recreate the
plan any time a new database is added?
You'll need to modify your maintanence plan to include the new ones. They'll
not be added to your old plan automatically.
However, performing all these jobs (I don't know your schedule however
something makes me think it's everyday) may not be appropriate for every
environment.
It's also not a good idea to include all of these jobs only in one plan as
one of these jobs would fail, the others after that failed one would not
work.
Ekrem ?nsoy
"cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
> I've set up a Database Maintenance Plan that Checks Database Integrity,
> Reorganizes Index, Rebuilds Index, Update Statistics and Cleans Up
> History.
> If I set the plan to perform these tasks on either All Databases or All
> User
> Databases, will new databases be detected? Or would I need to recreate
> the
> plan any time a new database is added?
|||Actually Ekrem .. I found maintenance plans pick up new databases without any
issue without me having to recreate it in 2000 and 2005. But then again I
only use that for backing up ;-).
I have found if I change the maintenace plan name after created it onces,
the jobs don't updated properly. And if I add new tasks then they don't get
reflected properly; but for backups it's been good but it's the nightly full
backups only.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:

> You'll need to modify your maintanence plan to include the new ones. They'll
> not be added to your old plan automatically.
> However, performing all these jobs (I don't know your schedule however
> something makes me think it's everyday) may not be appropriate for every
> environment.
> It's also not a good idea to include all of these jobs only in one plan as
> one of these jobs would fail, the others after that failed one would not
> work.
> --
> Ekrem ?nsoy
>
> "cc.shine" <cc.shine@.discussions.microsoft.com> wrote in message
> news:0011FCDF-395D-4F01-912C-90C189011EBD@.microsoft.com...
>
|||Actually, it would be the most correct to say new databases would be
included automatically if you do not choose particular databases from the
list and choose All User Databases =)
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:09E5DB39-C9E5-4D2D-BBF1-AB5303143EFC@.microsoft.com...[vbcol=seagreen]
> Actually Ekrem .. I found maintenance plans pick up new databases without
> any
> issue without me having to recreate it in 2000 and 2005. But then again I
> only use that for backing up ;-).
> I have found if I change the maintenace plan name after created it onces,
> the jobs don't updated properly. And if I add new tasks then they don't
> get
> reflected properly; but for backups it's been good but it's the nightly
> full
> backups only.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:

Database Maintenance Plans

Hello all, I seem to be having some trouble with maintenance plans which
are to backup all my user dbs.
The plan worked before, backing up all the user dbs daily. However,
recently, I have noticed the dbs are not getting backed up. The latest
backup was on Sept. 21, 2004. I have not made any major changes, so I
don't know why the plan stopped working all of a sudden.
If I go to SQL Server Agent > Jobs > Right click on my backup job > View
Job History > Turn on show step details
I see two steps repeated many times
Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule 1).
The last step to run was step 1 (Step 1).
Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error
22029). The step failed.
I have tried creating a new job and set the time to run within a few
minutes, but it does not run. I believe the reason for this is because
under 'Next Run Date' it says (Date and time are not available). I have
also tried simply right clicking the job and clicking Start Job. This
appears to do nothing.
I have looked up numerous MS Knowledge Base articles
(http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
various newsgroup postings, but none seem to really cure my problem.
One thing I have accomplished was backing up the dbs manually using
sqlmaint in a command prompt. I went to SQL Server Agent > Jobs > Opened
my job properties > Steps > Edit and found the TSQL script:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
"d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
-BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
I modified the line to:
sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
"d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
-BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
and ran it in a command prompt. The backups were created without any
problems. So why can't I create backups using Enterprise Manager >
Database Maintenance Plans?
I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
Any help is greatly appreciated.
Thanks,
HarryHave you verified that the SQL Server Agent is running?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harry Kim" <heri0n@.pastpower.net> wrote in message
news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
Hello all, I seem to be having some trouble with maintenance plans which
are to backup all my user dbs.
The plan worked before, backing up all the user dbs daily. However,
recently, I have noticed the dbs are not getting backed up. The latest
backup was on Sept. 21, 2004. I have not made any major changes, so I
don't know why the plan stopped working all of a sudden.
If I go to SQL Server Agent > Jobs > Right click on my backup job > View
Job History > Turn on show step details
I see two steps repeated many times
Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule 1).
The last step to run was step 1 (Step 1).
Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error
22029). The step failed.
I have tried creating a new job and set the time to run within a few
minutes, but it does not run. I believe the reason for this is because
under 'Next Run Date' it says (Date and time are not available). I have
also tried simply right clicking the job and clicking Start Job. This
appears to do nothing.
I have looked up numerous MS Knowledge Base articles
(http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
various newsgroup postings, but none seem to really cure my problem.
One thing I have accomplished was backing up the dbs manually using
sqlmaint in a command prompt. I went to SQL Server Agent > Jobs > Opened
my job properties > Steps > Edit and found the TSQL script:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
"d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
-BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
I modified the line to:
sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
"d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
-BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
and ran it in a command prompt. The backups were created without any
problems. So why can't I create backups using Enterprise Manager >
Database Maintenance Plans?
I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
Any help is greatly appreciated.
Thanks,
Harry|||Yes it is running
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Have you verified that the SQL Server Agent is running?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
> Hello all, I seem to be having some trouble with maintenance plans which
> are to backup all my user dbs.
> The plan worked before, backing up all the user dbs daily. However,
> recently, I have noticed the dbs are not getting backed up. The latest
> backup was on Sept. 21, 2004. I have not made any major changes, so I
> don't know why the plan stopped working all of a sudden.
> If I go to SQL Server Agent > Jobs > Right click on my backup job > View
> Job History > Turn on show step details
> I see two steps repeated many times
> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule 1).
> The last step to run was step 1 (Step 1).
> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error
> 22029). The step failed.
> I have tried creating a new job and set the time to run within a few
> minutes, but it does not run. I believe the reason for this is because
> under 'Next Run Date' it says (Date and time are not available). I have
> also tried simply right clicking the job and clicking Start Job. This
> appears to do nothing.
> I have looked up numerous MS Knowledge Base articles
> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
> various newsgroup postings, but none seem to really cure my problem.
> One thing I have accomplished was backing up the dbs manually using
> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs > Opened
> my job properties > Steps > Edit and found the TSQL script:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
> I modified the line to:
> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
> and ran it in a command prompt. The backups were created without any
> problems. So why can't I create backups using Enterprise Manager >
> Database Maintenance Plans?
> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
> Any help is greatly appreciated.
> Thanks,
> Harry
>|||Do you have any entries in the SQL Server or SQL Agent error logs - or the
Windows event logs?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harry Kim" <heri0n@.pastpower.net> wrote in message
news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
Yes it is running
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Have you verified that the SQL Server Agent is running?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
> Hello all, I seem to be having some trouble with maintenance plans which
> are to backup all my user dbs.
> The plan worked before, backing up all the user dbs daily. However,
> recently, I have noticed the dbs are not getting backed up. The latest
> backup was on Sept. 21, 2004. I have not made any major changes, so I
> don't know why the plan stopped working all of a sudden.
> If I go to SQL Server Agent > Jobs > Right click on my backup job > View
> Job History > Turn on show step details
> I see two steps repeated many times
> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule 1).
> The last step to run was step 1 (Step 1).
> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error
> 22029). The step failed.
> I have tried creating a new job and set the time to run within a few
> minutes, but it does not run. I believe the reason for this is because
> under 'Next Run Date' it says (Date and time are not available). I have
> also tried simply right clicking the job and clicking Start Job. This
> appears to do nothing.
> I have looked up numerous MS Knowledge Base articles
> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
> various newsgroup postings, but none seem to really cure my problem.
> One thing I have accomplished was backing up the dbs manually using
> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs > Opened
> my job properties > Steps > Edit and found the TSQL script:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
> I modified the line to:
> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
> and ran it in a command prompt. The backups were created without any
> problems. So why can't I create backups using Enterprise Manager >
> Database Maintenance Plans?
> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
> Any help is greatly appreciated.
> Thanks,
> Harry
>|||There doesn't seem to be any relevant information in the SQL Server log.
For the SQL Agent logs, isn't it what I originally posted? Or do I find
this somewhere else. Windows Event Log gives me:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked on:
2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
(The event ID is 208)
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Do you have any entries in the SQL Server or SQL Agent error logs - or the
> Windows event logs?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
> Yes it is running
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job > View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is because
>> under 'Next Run Date' it says (Date and time are not available). I have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs > Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>|||Right click on SQL Server Agent in EM. Click on Display Error Log. I the
drop-down, select All Types. See if anything pops out.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harry Kim" <heri0n@.pastpower.net> wrote in message
news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
There doesn't seem to be any relevant information in the SQL Server log.
For the SQL Agent logs, isn't it what I originally posted? Or do I find
this somewhere else. Windows Event Log gives me:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked on:
2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
(The event ID is 208)
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Do you have any entries in the SQL Server or SQL Agent error logs - or the
> Windows event logs?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
> Yes it is running
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job > View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
(Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is because
>> under 'Next Run Date' it says (Date and time are not available). I have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs > Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>|||Yes
There are 12 information messages and 1 warning. No errors.
Is there anything in particular you are looking for?
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Right click on SQL Server Agent in EM. Click on Display Error Log. I the
> drop-down, select All Types. See if anything pops out.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
> There doesn't seem to be any relevant information in the SQL Server log.
> For the SQL Agent logs, isn't it what I originally posted? Or do I find
> this somewhere else. Windows Event Log gives me:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked on:
> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
> (The event ID is 208)
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job > View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is because
>> under 'Next Run Date' it says (Date and time are not available). I have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs > Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>|||What did the warning say?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harry Kim" <heri0n@.pastpower.net> wrote in message
news:Pine.LNX.4.60.0410081409270.1410@.pastpower.net...
Yes
There are 12 information messages and 1 warning. No errors.
Is there anything in particular you are looking for?
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Right click on SQL Server Agent in EM. Click on Display Error Log. I the
> drop-down, select All Types. See if anything pops out.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
> There doesn't seem to be any relevant information in the SQL Server log.
> For the SQL Agent logs, isn't it what I originally posted? Or do I find
> this somewhere else. Windows Event Log gives me:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked on:
> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
> (The event ID is 208)
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or
the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job > View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule
1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is because
>> under 'Next Run Date' it says (Date and time are not available). I have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs >
Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>|||[396] An idle CPU condition has not been defined - OnIdle job schedules
will have no effect
On Fri, 8 Oct 2004, Tom Moreau wrote:
> What did the warning say?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081409270.1410@.pastpower.net...
> Yes
> There are 12 information messages and 1 warning. No errors.
> Is there anything in particular you are looking for?
>
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Right click on SQL Server Agent in EM. Click on Display Error Log. I the
>> drop-down, select All Types. See if anything pops out.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
>> There doesn't seem to be any relevant information in the SQL Server log.
>> For the SQL Agent logs, isn't it what I originally posted? Or do I find
>> this somewhere else. Windows Event Log gives me:
>> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
>> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked on:
>> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
>> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
>> (The event ID is 208)
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or
> the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job > View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule
> 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
>> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is because
>> under 'Next Run Date' it says (Date and time are not available). I have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs >
> Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>>
>|||Can you run other - non-DBMaint - jobs through SQL Agent? I'm trying to
narrow down the scope of the problem.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harry Kim" <heri0n@.pastpower.net> wrote in message
news:Pine.LNX.4.60.0410081433390.1425@.pastpower.net...
[396] An idle CPU condition has not been defined - OnIdle job schedules
will have no effect
On Fri, 8 Oct 2004, Tom Moreau wrote:
> What did the warning say?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081409270.1410@.pastpower.net...
> Yes
> There are 12 information messages and 1 warning. No errors.
> Is there anything in particular you are looking for?
>
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Right click on SQL Server Agent in EM. Click on Display Error Log. I
the
>> drop-down, select All Types. See if anything pops out.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
>> There doesn't seem to be any relevant information in the SQL Server log.
>> For the SQL Agent logs, isn't it what I originally posted? Or do I find
>> this somewhere else. Windows Event Log gives me:
>> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
>> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked on:
>> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
>> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
>> (The event ID is 208)
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or
> the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans
which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job >
View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule
> 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
>> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is because
>> under 'Next Run Date' it says (Date and time are not available). I
have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs >
> Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>>
>|||Yes, I believe so.
I have other successfully completed jobs in SQL Server Agent > Jobs
Such as:
Start_Full on Palmas.PagesSearch.[15.6]
Desc: Scheduled full-text full population for full-text Catalog
PagesSearch in database Palmas. This job was created by the full-text
scheduling dialog or full-text index wizard..
SmartRisk Daily Content Expiry
Desc: Execute package: SmartRisk Daily Content Expiry
Which have completed successfully...
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Can you run other - non-DBMaint - jobs through SQL Agent? I'm trying to
> narrow down the scope of the problem.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081433390.1425@.pastpower.net...
> [396] An idle CPU condition has not been defined - OnIdle job schedules
> will have no effect
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> What did the warning say?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081409270.1410@.pastpower.net...
>> Yes
>> There are 12 information messages and 1 warning. No errors.
>> Is there anything in particular you are looking for?
>>
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Right click on SQL Server Agent in EM. Click on Display Error Log. I
> the
>> drop-down, select All Types. See if anything pops out.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
>> There doesn't seem to be any relevant information in the SQL Server log.
>> For the SQL Agent logs, isn't it what I originally posted? Or do I find
>> this somewhere else. Windows Event Log gives me:
>> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
>> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked on:
>> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
>> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
>> (The event ID is 208)
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or
>> the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans
> which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job >
> View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule
>> 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
>> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is because
>> under 'Next Run Date' it says (Date and time are not available). I
> have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs >
>> Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>>
>>
>|||I get the feeling that somehow, the DLL's relating to DBmaint may be
corrupted. I know you've tried re-doing the job. How about a new, simple
DB maintenance plan - just Northwind, full backup? Does that work?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harry Kim" <heri0n@.pastpower.net> wrote in message
news:Pine.LNX.4.60.0410081503020.1425@.pastpower.net...
Yes, I believe so.
I have other successfully completed jobs in SQL Server Agent > Jobs
Such as:
Start_Full on Palmas.PagesSearch.[15.6]
Desc: Scheduled full-text full population for full-text Catalog
PagesSearch in database Palmas. This job was created by the full-text
scheduling dialog or full-text index wizard..
SmartRisk Daily Content Expiry
Desc: Execute package: SmartRisk Daily Content Expiry
Which have completed successfully...
On Fri, 8 Oct 2004, Tom Moreau wrote:
> Can you run other - non-DBMaint - jobs through SQL Agent? I'm trying to
> narrow down the scope of the problem.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081433390.1425@.pastpower.net...
> [396] An idle CPU condition has not been defined - OnIdle job schedules
> will have no effect
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> What did the warning say?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081409270.1410@.pastpower.net...
>> Yes
>> There are 12 information messages and 1 warning. No errors.
>> Is there anything in particular you are looking for?
>>
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Right click on SQL Server Agent in EM. Click on Display Error Log. I
> the
>> drop-down, select All Types. See if anything pops out.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
>> There doesn't seem to be any relevant information in the SQL Server log.
>> For the SQL Agent logs, isn't it what I originally posted? Or do I find
>> this somewhere else. Windows Event Log gives me:
>> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
>> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked
on:
>> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
>> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
>> (The event ID is 208)
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or
>> the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans
> which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The
latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job >
> View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule
>> 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
>> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is
because
>> under 'Next Run Date' it says (Date and time are not available). I
> have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs >
>> Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>>
>>
>|||No, I tried that too before but with a different database.
I also tried it again now with Northwind.
It does not seem to execute. Under next run date it says dat and time are
not available. I also tried right clicking on the job and pressing start
job. Nothing seemed to happen...
On Fri, 8 Oct 2004, Tom Moreau wrote:
> I get the feeling that somehow, the DLL's relating to DBmaint may be
> corrupted. I know you've tried re-doing the job. How about a new, simple
> DB maintenance plan - just Northwind, full backup? Does that work?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081503020.1425@.pastpower.net...
> Yes, I believe so.
> I have other successfully completed jobs in SQL Server Agent > Jobs
> Such as:
> Start_Full on Palmas.PagesSearch.[15.6]
> Desc: Scheduled full-text full population for full-text Catalog
> PagesSearch in database Palmas. This job was created by the full-text
> scheduling dialog or full-text index wizard..
> SmartRisk Daily Content Expiry
> Desc: Execute package: SmartRisk Daily Content Expiry
> Which have completed successfully...
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Can you run other - non-DBMaint - jobs through SQL Agent? I'm trying to
>> narrow down the scope of the problem.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081433390.1425@.pastpower.net...
>> [396] An idle CPU condition has not been defined - OnIdle job schedules
>> will have no effect
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> What did the warning say?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081409270.1410@.pastpower.net...
>> Yes
>> There are 12 information messages and 1 warning. No errors.
>> Is there anything in particular you are looking for?
>>
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Right click on SQL Server Agent in EM. Click on Display Error Log. I
>> the
>> drop-down, select All Types. See if anything pops out.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
>> There doesn't seem to be any relevant information in the SQL Server log.
>> For the SQL Agent logs, isn't it what I originally posted? Or do I find
>> this somewhere else. Windows Event Log gives me:
>> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
>> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked
> on:
>> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
>> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
>> (The event ID is 208)
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or
>> the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans
>> which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The
> latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job >
>> View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule
>> 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
>> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is
> because
>> under 'Next Run Date' it says (Date and time are not available). I
>> have
>> also tried simply right clicking the job and clicking Start Job. This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs >
>> Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>>
>>
>>
>|||I think you're gonna have to open a ticket with Product Support Services.
It sounds like something has been corrupted.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harry Kim" <heri0n@.pastpower.net> wrote in message
news:Pine.LNX.4.60.0410081547010.1425@.pastpower.net...
No, I tried that too before but with a different database.
I also tried it again now with Northwind.
It does not seem to execute. Under next run date it says dat and time are
not available. I also tried right clicking on the job and pressing start
job. Nothing seemed to happen...
On Fri, 8 Oct 2004, Tom Moreau wrote:
> I get the feeling that somehow, the DLL's relating to DBmaint may be
> corrupted. I know you've tried re-doing the job. How about a new, simple
> DB maintenance plan - just Northwind, full backup? Does that work?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Harry Kim" <heri0n@.pastpower.net> wrote in message
> news:Pine.LNX.4.60.0410081503020.1425@.pastpower.net...
> Yes, I believe so.
> I have other successfully completed jobs in SQL Server Agent > Jobs
> Such as:
> Start_Full on Palmas.PagesSearch.[15.6]
> Desc: Scheduled full-text full population for full-text Catalog
> PagesSearch in database Palmas. This job was created by the full-text
> scheduling dialog or full-text index wizard..
> SmartRisk Daily Content Expiry
> Desc: Execute package: SmartRisk Daily Content Expiry
> Which have completed successfully...
> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Can you run other - non-DBMaint - jobs through SQL Agent? I'm trying to
>> narrow down the scope of the problem.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081433390.1425@.pastpower.net...
>> [396] An idle CPU condition has not been defined - OnIdle job schedules
>> will have no effect
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> What did the warning say?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081409270.1410@.pastpower.net...
>> Yes
>> There are 12 information messages and 1 warning. No errors.
>> Is there anything in particular you are looking for?
>>
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Right click on SQL Server Agent in EM. Click on Display Error Log. I
>> the
>> drop-down, select All Types. See if anything pops out.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081307240.1373@.pastpower.net...
>> There doesn't seem to be any relevant information in the SQL Server
log.
>> For the SQL Agent logs, isn't it what I originally posted? Or do I
find
>> this somewhere else. Windows Event Log gives me:
>> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'user
>> dbs'' (0x9AC86DAEC2694A469BADFCFAD9063C53) - Status: Failed - Invoked
> on:
>> 2004-10-08 01:00:00 - Message: The job failed. The Job was invoked by
>> Schedule 30 (Schedule 1). The last step to run was step 1 (Step 1).
>> (The event ID is 208)
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Do you have any entries in the SQL Server or SQL Agent error logs - or
>> the
>> Windows event logs?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081204240.1323@.pastpower.net...
>> Yes it is running
>> On Fri, 8 Oct 2004, Tom Moreau wrote:
>> Have you verified that the SQL Server Agent is running?
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Harry Kim" <heri0n@.pastpower.net> wrote in message
>> news:Pine.LNX.4.60.0410081056070.1087@.pastpower.net...
>> Hello all, I seem to be having some trouble with maintenance plans
>> which
>> are to backup all my user dbs.
>> The plan worked before, backing up all the user dbs daily. However,
>> recently, I have noticed the dbs are not getting backed up. The
> latest
>> backup was on Sept. 21, 2004. I have not made any major changes, so
I
>> don't know why the plan stopped working all of a sudden.
>> If I go to SQL Server Agent > Jobs > Right click on my backup job >
>> View
>> Job History > Turn on show step details
>> I see two steps repeated many times
>> Step 0: The job failed. The Job was invoked by Schedule 30 (Schedule
>> 1).
>> The last step to run was step 1 (Step 1).
>> Step 1: Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000]
>> (Error
>> 22029). The step failed.
>> I have tried creating a new job and set the time to run within a few
>> minutes, but it does not run. I believe the reason for this is
> because
>> under 'Next Run Date' it says (Date and time are not available). I
>> have
>> also tried simply right clicking the job and clicking Start Job.
This
>> appears to do nothing.
>> I have looked up numerous MS Knowledge Base articles
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;288577) and
>> various newsgroup postings, but none seem to really cure my problem.
>> One thing I have accomplished was backing up the dbs manually using
>> sqlmaint in a command prompt. I went to SQL Server Agent > Jobs >
>> Opened
>> my job properties > Steps > Edit and found the TSQL script:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> I modified the line to:
>> sqlmaint -PlanID 1D626322-E403-4065-A37C-EE5F8B9BC2E0 -To "hkim" -Rpt
>> "d:\mssqldata\MSSQL\LOG\user dbs4.txt" -DelTxtRpt 4DAYS -WriteHistory
>> -BkUpMedia DISK -BkUpDB "E:\sqlback" -DelBkUps 3DAYS -BkExt "BAK"'
>> and ran it in a command prompt. The backups were created without any
>> problems. So why can't I create backups using Enterprise Manager >
>> Database Maintenance Plans?
>> I am running MS-SQL 2000 (8.00.760) on Windows 2000 Server.
>> Any help is greatly appreciated.
>> Thanks,
>> Harry
>>
>>
>>
>>
>>
>

Wednesday, March 21, 2012

Database Migration

Monday, March 19, 2012

Database Maintnenance Plan

Database Maintenence Plan Error

Database maintence plan

Database maintence plan

Database maintence plan

Database Maintenance question...

Database Maintenance question...

Database Maintenance problem

Sunday, March 11, 2012

Database Maintenance Plans - Not removing Files Older than x days

Database Maintenance Plans - Not removing Files Older than x days

Database Maintenance Plans - Not removing Files Older than x days

Database Maintenance Plans

Database Maintenance Plans

Database Maintenance Plans

Database Overwritten

Blog Archive

About Me