Lowering priority: job

Showing posts with label job. Show all posts

Friday, March 30, 2012

Maintenance plan failing

i keep getting the following error in my Event Viewer regarding a maintnance
plan I have set up in MS SQL Server 2000:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan master, msdb, model''
(0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
2006-11-14 00:00:05 - Message: The job failed. The Job was invoked by
Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
Any ideas about what I might be doing wrong?
--
John SchusterJohn Schuster wrote:
> i keep getting the following error in my Event Viewer regarding a maintnance
> plan I have set up in MS SQL Server 2000:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan master, msdb, model''
> (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> 2006-11-14 00:00:05 - Message: The job failed. The Job was invoked by
> Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> Any ideas about what I might be doing wrong?
You can't do a transaction log backup on a database that is in Simple
recovery mode.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Any ideas about what I might be doing wrong?
Specify a report file for the maint plan. In there you will find the error messages that lead to the
job failure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:D4C6E493-E90D-45E6-80B2-3BC0170D8DEB@.microsoft.com...
>i keep getting the following error in my Event Viewer regarding a maintnance
> plan I have set up in MS SQL Server 2000:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan master, msdb, model''
> (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> 2006-11-14 00:00:05 - Message: The job failed. The Job was invoked by
> Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> Any ideas about what I might be doing wrong?
> --
> John Schuster|||I set up a report and i get the following which doesn't tell me much:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
11/14/2006 12:00:07 AM
Backup can not be performed on database 'master'. This sub task is ignored.
[1] Database model: Transaction Log Backup...
Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200611140000.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
11/14/2006 12:00:08 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
--
John Schuster
"Tibor Karaszi" wrote:
> > Any ideas about what I might be doing wrong?
> Specify a report file for the maint plan. In there you will find the error messages that lead to the
> job failure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:D4C6E493-E90D-45E6-80B2-3BC0170D8DEB@.microsoft.com...
> >i keep getting the following error in my Event Viewer regarding a maintnance
> > plan I have set up in MS SQL Server 2000:
> >
> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> > 'DB Maintenance Plan master, msdb, model''
> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> > 2006-11-14 00:00:05 - Message: The job failed. The Job was invoked by
> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > Any ideas about what I might be doing wrong?
> > --
> > John Schuster
>
>|||Here's the problem:
> Backup can not be performed on database 'msdb'. This sub task is ignored.
You are trying to do a log backup on a database which is in simple recovery mode. No can do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:AC9FF4A5-0CF5-4661-A9F1-577320DB70A3@.microsoft.com...
>I set up a report and i get the following which doesn't tell me much:
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 11/14/2006 12:00:07 AM
> Backup can not be performed on database 'master'. This sub task is ignored.
> [1] Database model: Transaction Log Backup...
> Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200611140000.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 11/14/2006 12:00:08 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> --
> John Schuster
>
> "Tibor Karaszi" wrote:
>> > Any ideas about what I might be doing wrong?
>> Specify a report file for the maint plan. In there you will find the error messages that lead to
>> the
>> job failure.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:D4C6E493-E90D-45E6-80B2-3BC0170D8DEB@.microsoft.com...
>> >i keep getting the following error in my Event Viewer regarding a maintnance
>> > plan I have set up in MS SQL Server 2000:
>> >
>> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
>> > 'DB Maintenance Plan master, msdb, model''
>> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
>> > 2006-11-14 00:00:05 - Message: The job failed. The Job was invoked by
>> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
>> >
>> > Any ideas about what I might be doing wrong?
>> > --
>> > John Schuster
>>|||Thank you for your help.
--
John Schuster
"Tibor Karaszi" wrote:
> Here's the problem:
> > Backup can not be performed on database 'msdb'. This sub task is ignored.
> You are trying to do a log backup on a database which is in simple recovery mode. No can do.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:AC9FF4A5-0CF5-4661-A9F1-577320DB70A3@.microsoft.com...
> >I set up a report and i get the following which doesn't tell me much:
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 11/14/2006 12:00:07 AM
> > Backup can not be performed on database 'master'. This sub task is ignored.
> >
> > [1] Database model: Transaction Log Backup...
> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200611140000.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > [2] Database model: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Backup can not be performed on database 'msdb'. This sub task is ignored.
> >
> > Deleting old text reports... 0 file(s) deleted.
> >
> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 11/14/2006 12:00:08 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> > --
> > John Schuster
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > Any ideas about what I might be doing wrong?
> >>
> >> Specify a report file for the maint plan. In there you will find the error messages that lead to
> >> the
> >> job failure.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:D4C6E493-E90D-45E6-80B2-3BC0170D8DEB@.microsoft.com...
> >> >i keep getting the following error in my Event Viewer regarding a maintnance
> >> > plan I have set up in MS SQL Server 2000:
> >> >
> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> >> > 'DB Maintenance Plan master, msdb, model''
> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> >> > 2006-11-14 00:00:05 - Message: The job failed. The Job was invoked by
> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >> >
> >> > Any ideas about what I might be doing wrong?
> >> > --
> >> > John Schuster
> >>
> >>
> >>
>

Wednesday, March 28, 2012

Maintenance Plan DB Integrity Job Failed

I created a SQL Server 2000 database maintenance plan with the wizard.
I selected integrity checks and complete backups for my database and
completed the requiremenents.
I received the following error listed below.
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42
000]
(Error 22029). The step failed.
Please help me resolve this error.Joe
Did you check an Error log for any info there?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Yes, I checked the SQL Server error log.
Nothing was in the error log that correponds to this error.
Thanks,
"Uri Dimant" wrote:

> Joe
> Did you check an Error log for any info there?
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
>
>|||Also, which job did this fail on?
For example, if you have a tran log backup job and you have mixed full
recovery model databases with simple model, you will get an error (even
though the backups completed, the error will be thrown). Its always a good
idea to separate out the system, full model user, bulk mode user and simple
model user database backups into separate plans, and configure each
appropriately.
Awaiting your response for more info as per Uri.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Joe K." wrote:

> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Specify a report file for the main plan and check that report file for error
messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>sql

Maintenance Plan DB Integrity Job Failed

I created a SQL Server 2000 database maintenance plan with the wizard.
I selected integrity checks and complete backups for my database and
completed the requiremenents.
I received the following error listed below.
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
Please help me resolve this error.
Joe
Did you check an Error log for any info there?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>
|||Yes, I checked the SQL Server error log.
Nothing was in the error log that correponds to this error.
Thanks,
"Uri Dimant" wrote:

> Joe
> Did you check an Error log for any info there?
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
>
>
|||Also, which job did this fail on?
For example, if you have a tran log backup job and you have mixed full
recovery model databases with simple model, you will get an error (even
though the backups completed, the error will be thrown). Its always a good
idea to separate out the system, full model user, bulk mode user and simple
model user database backups into separate plans, and configure each
appropriately.
Awaiting your response for more info as per Uri.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Joe K." wrote:

> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>
|||Specify a report file for the main plan and check that report file for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>

Maintenance Plan DB Integrity Job Failed

I created a SQL Server 2000 database maintenance plan with the wizard.
I selected integrity checks and complete backups for my database and
completed the requiremenents.
I received the following error listed below.
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
Please help me resolve this error.Joe
Did you check an Error log for any info there?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Yes, I checked the SQL Server error log.
Nothing was in the error log that correponds to this error.
Thanks,
"Uri Dimant" wrote:
> Joe
> Did you check an Error log for any info there?
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> >
> > I created a SQL Server 2000 database maintenance plan with the wizard.
> > I selected integrity checks and complete backups for my database and
> > completed the requiremenents.
> >
> > I received the following error listed below.
> >
> > Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> > 42000]
> > (Error 22029). The step failed.
> >
> > Please help me resolve this error.
> >
>
>|||Also, which job did this fail on?
For example, if you have a tran log backup job and you have mixed full
recovery model databases with simple model, you will get an error (even
though the backups completed, the error will be thrown). Its always a good
idea to separate out the system, full model user, bulk mode user and simple
model user database backups into separate plans, and configure each
appropriately.
Awaiting your response for more info as per Uri.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Joe K." wrote:
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Specify a report file for the main plan and check that report file for error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>

Monday, March 26, 2012

Maintenance Plan / BackupJob failed with error 17055

Hello all,
we work with SQL Server 2000 SP3 and we had a problem with our
Maintenance Plan. Even time we start the job, the job failed with error
17055. SQL Server Logfile writes :
BackupMedium::ReportIoError: write-Fehler auf Sicherungsmedium 'D:
\Programme\Microsoft SQL Server\MSSQL\BACKUP\distribution
\distribution_tlog_200708240700.TRN'. Betriebssystemfehler: 112(Es steht
nicht genug Speicherplatz auf dem Datenträger zur Verfügung.).
The SQL Server Entry before the error was :
Internal I/O request 0x5B39A9C8: Op: Write, pBuffer: 0x11570000, Size:
983040, Position: 16259410432, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0xC922EA00, OffsetHigh: 0x3, m_buf: 0x11570000, m_len: 983040,
m_actualBytes: 0, m_errcode: 112, BackupFile: D:\Programme\Microsoft SQL
Server\MSSQL\BACKUP\distribution\distribution_tlog_200708240700.TRN
In the eventlog of the system the error 17055 occurs.
18210 :
BackupMedium::ReportIoError: write-Fehler auf Sicherungsmedium 'D:
\Programme\Microsoft SQL Server\MSSQL\BACKUP\distribution
\distribution_tlog_200708240700.TRN'. Betriebssystemfehler: 112(Es steht
nicht genug Speicherplatz auf dem Datenträger zur Verfügung.).
The filesystem where the transaction log is written has over 15GB free
space. So what is the problem?
Many thanks for Help.
PeterP.Nebermann,
If the file system on your D: drive is FAT32 then a 4GB file is the biggest
that you can write.
http://www.thescripts.com/forum/thread82720.html
Could that be the problem?
RLF
"P.Nebermann" <p.nebermann-'at'-hautau.de> wrote in message
news:Xns999650AAFA98pnebermannathautaude@.207.46.248.16...
> Hello all,
> we work with SQL Server 2000 SP3 and we had a problem with our
> Maintenance Plan. Even time we start the job, the job failed with error
> 17055. SQL Server Logfile writes :
> BackupMedium::ReportIoError: write-Fehler auf Sicherungsmedium 'D:
> \Programme\Microsoft SQL Server\MSSQL\BACKUP\distribution
> \distribution_tlog_200708240700.TRN'. Betriebssystemfehler: 112(Es steht
> nicht genug Speicherplatz auf dem Datenträger zur Verfügung.).
> The SQL Server Entry before the error was :
> Internal I/O request 0x5B39A9C8: Op: Write, pBuffer: 0x11570000, Size:
> 983040, Position: 16259410432, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0xC922EA00, OffsetHigh: 0x3, m_buf: 0x11570000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 112, BackupFile: D:\Programme\Microsoft SQL
> Server\MSSQL\BACKUP\distribution\distribution_tlog_200708240700.TRN
> In the eventlog of the system the error 17055 occurs.
> 18210 :
> BackupMedium::ReportIoError: write-Fehler auf Sicherungsmedium 'D:
> \Programme\Microsoft SQL Server\MSSQL\BACKUP\distribution
> \distribution_tlog_200708240700.TRN'. Betriebssystemfehler: 112(Es steht
> nicht genug Speicherplatz auf dem Datenträger zur Verfügung.).
> The filesystem where the transaction log is written has over 15GB free
> space. So what is the problem?
> Many thanks for Help.
> Peter

Maintenance Plan

I have deleted a maintenance plan it appears that the job for that maintence plan has been left behind. When I attempt to delete the job I get the below message. Any idea how to remove this orphaned job?

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job 'User DB Maintenance Plan'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

BUTTONS:

i have tested this and when i delete the maintenance plan via Mgmt Studio (2047/sp1) it also removes the corresponding job. However, I have produced this effect you are describing in the past. What build of Mgmt Studio are you using?|||

Michael,

Run this query against your server and see if your maintence plan is in the database:

Use MSDB
GO
Select * from sysmaintplan_subplans
GO

If you see it there, pick a column like the name of your maintenence plan and fill in the Where statement below and run it to see if you can delete it

Use MSDB
GO
Delete from sysmaintplan_subplans
Where name = 'myplan'
GO

If you get an error with an FK like you did before, there is another table that has a relationship with this table, you'll need to detemine the other table name and query it and try to delete the relationship of the plan from there and then go back to the sysmaintplan_subplans table.

I had to do something similar to this before and I was able to get it out by using this approach.

|||

I have the same problem. It was created by a bug in the maintenance plans wizard.

As I was about to complete the wizard's process to build a maintenance plan, I clicked the BACK button all the way back to the first wizard dialog. After that I could not complete the wizard process because something had been created. Therefore the wizard got confused leaving this problem, which I cannot delete the job which has no associated maintenance plan.

ktmd

|||

There was a foreign key constraint on the sysmaintplan_log. I deleted the item with the contraint in the sysmaintplan_log table and that did the trick.

delete from dbo.sysmaintplan_log where plan_id = 'my_plan_id '

Thank you.

|||

Hi Michael-

Your situation is exactly what has happened to me. I realize your posted the fix, but I am a SQL newbie and do not know where/how to run the 'delete from dbo.sysmaintplan.log where plan_id = "my_plan_id'. I am currently running SQL 2005 standard. Thanks in advance.

|||

Thank you Michael and Andy_T. I had the same problem, I'm a SQL n00b and this has been bugging me for weeks now. Your posts helped me delete my unused maintenance plan.

Thanks!!!

|||

Thanks for the postings all!!! A year later these postings are still amazingly helpful!!!

One tip, for those who couldn't find the plan name you can use the plan id to delete:

Delete from sysmaintplan_subplans

Where plan_id = 'plan id'

sql

Maintenance Plan

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job 'User DB Maintenance Plan'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

BUTTONS:

Michael,

Run this query against your server and see if your maintence plan is in the database:

Use MSDB
GO
Select * from sysmaintplan_subplans
GO

If you see it there, pick a column like the name of your maintenence plan and fill in the Where statement below and run it to see if you can delete it

Use MSDB
GO
Delete from sysmaintplan_subplans
Where name = 'myplan'
GO

I had to do something similar to this before and I was able to get it out by using this approach.

|||

I have the same problem. It was created by a bug in the maintenance plans wizard.

ktmd

|||

There was a foreign key constraint on the sysmaintplan_log. I deleted the item with the contraint in the sysmaintplan_log table and that did the trick.

delete from dbo.sysmaintplan_log where plan_id = 'my_plan_id '

Thank you.

|||

Hi Michael-

|||

Thank you Michael and Andy_T. I had the same problem, I'm a SQL n00b and this has been bugging me for weeks now. Your posts helped me delete my unused maintenance plan.

Thanks!!!

|||

Thanks for the postings all!!! A year later these postings are still amazingly helpful!!!

One tip, for those who couldn't find the plan name you can use the plan id to delete:

Delete from sysmaintplan_subplans

Where plan_id = 'plan id'

Maintenance Plan

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job 'User DB Maintenance Plan'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

BUTTONS:

Michael,

Run this query against your server and see if your maintence plan is in the database:

Use MSDB
GO
Select * from sysmaintplan_subplans
GO

If you see it there, pick a column like the name of your maintenence plan and fill in the Where statement below and run it to see if you can delete it

Use MSDB
GO
Delete from sysmaintplan_subplans
Where name = 'myplan'
GO

I had to do something similar to this before and I was able to get it out by using this approach.

|||

I have the same problem. It was created by a bug in the maintenance plans wizard.

ktmd

|||

There was a foreign key constraint on the sysmaintplan_log. I deleted the item with the contraint in the sysmaintplan_log table and that did the trick.

delete from dbo.sysmaintplan_log where plan_id = 'my_plan_id '

Thank you.

|||

Hi Michael-

|||

Thank you Michael and Andy_T. I had the same problem, I'm a SQL n00b and this has been bugging me for weeks now. Your posts helped me delete my unused maintenance plan.

Thanks!!!

|||

Thanks for the postings all!!! A year later these postings are still amazingly helpful!!!

One tip, for those who couldn't find the plan name you can use the plan id to delete:

Delete from sysmaintplan_subplans

Where plan_id = 'plan id'

Maintenance Plan

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job 'User DB Maintenance Plan'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

BUTTONS:

Michael,

Run this query against your server and see if your maintence plan is in the database:

Use MSDB
GO
Select * from sysmaintplan_subplans
GO

If you see it there, pick a column like the name of your maintenence plan and fill in the Where statement below and run it to see if you can delete it

Use MSDB
GO
Delete from sysmaintplan_subplans
Where name = 'myplan'
GO

I had to do something similar to this before and I was able to get it out by using this approach.

|||

I have the same problem. It was created by a bug in the maintenance plans wizard.

ktmd

|||

There was a foreign key constraint on the sysmaintplan_log. I deleted the item with the contraint in the sysmaintplan_log table and that did the trick.

delete from dbo.sysmaintplan_log where plan_id = 'my_plan_id '

Thank you.

|||

Hi Michael-

|||

Thank you Michael and Andy_T. I had the same problem, I'm a SQL n00b and this has been bugging me for weeks now. Your posts helped me delete my unused maintenance plan.

Thanks!!!

|||

Thanks for the postings all!!! A year later these postings are still amazingly helpful!!!

One tip, for those who couldn't find the plan name you can use the plan id to delete:

Delete from sysmaintplan_subplans

Where plan_id = 'plan id'

Maintenance Plan

Can you generate script for a maintenance plan?
I know how to script a job, I was wondering about a plan.
If not, whats the best way to record the configuration?
Thanks
LystraLook into the following tables in MSDB:

sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
sysdbmaintplans
sysjobhistory
sysjobs
sysjobs_view
sysjobschedules
sysjobservers
sysjobsteps|||Please forgive me, but what will looking into those tables get me?

Thanks

Lystra|||I think that when you script a maintenance plan, you get a GUID-like reference which is the unique identifier for the plan. There are some additional parameters which are included, but in general the script is not readable nor would it help in recreating a plan (because of the unique reference).

I generally try to build my plans in a similar fashion (checking off the same options). The only variation I may factor in is a different timing scheme (so that I minimize contention as the backups are being written to the network drive). Each server has a User database maintenance plan and a system database maintenance plan.

I then make notes somewhere so that in case I am hit by a Mack truck a new DBA could (hopefully) pick up and understand where I had left off.

This is okay in my environment where I have about 10-12 servers to manage. It gets a bit out of hand when you get into some of the hosted environs where there might be hundreds of servers to monitor.

Regards,

hmscott

Friday, March 23, 2012

maintenance plan

What is going on. I setup a maintenance plan for a
database backup. It works fine but the intergrity check
part in the job folder on sql server agent fails. The
backup is fine and the optimizations is fine.
I checked the maintenance plan section of the integrity
part and it shows that I checked the box for doing databse
integrity, include indexes and attempt to repair any minor
problems. The perform these tests before backing up the
database box was not checked.
Interseting when I checked to perform these tests before
backing up and click apply and ok, it clears off again by
itself once I open up the maintenance plan. What is going
on any help will be appreaciated.,
Thanks,
Aboki.Clear the check box for "attempt to repair any minor
problems" under the Integrity tab. Refer to the following
for more info:
BUG: Database Maintenance Plan on System Databases Fails on
Integrity Check if "Attempt to repair minor problems" is
Selected
http://support.microsoft.com/?id=290622
--Sue
On Thu, 7 Aug 2003 09:31:59 -0700, "Aboki"
<waco361@.hotmail.com> wrote:
>What is going on. I setup a maintenance plan for a
>database backup. It works fine but the intergrity check
>part in the job folder on sql server agent fails. The
>backup is fine and the optimizations is fine.
>I checked the maintenance plan section of the integrity
>part and it shows that I checked the box for doing databse
>integrity, include indexes and attempt to repair any minor
>problems. The perform these tests before backing up the
>database box was not checked.
>Interseting when I checked to perform these tests before
>backing up and click apply and ok, it clears off again by
>itself once I open up the maintenance plan. What is going
>on any help will be appreaciated.,
>Thanks,
>Aboki.

Maintenance Job not working properly

Hi,

I an new to the forum.

I am having some problems where my maintenance job is not backing up the user databases in SQL2000. Unfortunately, I found out the hard way as my payroll in GP did not run properly and I had to do a restore from the BAK file.

I am receiving the following error msg in the event log

Status: Failed - Invoked on: 2006-04-11 22:00:02 - Message: The job failed. The owner () of job DB Backup Job for DB Maintenance Plan 'Daily Backup of User Databases' does not have server access.

I cannot find where and how the user permission was chmaged on this job. I would like some assistance with this problem.

thanks

Raymond

Hi,

Go to Jobs list under SQL Server Agent in the Enterprise Manager.

Find the job for the maintenance plan you have created. Name of the job will include the name of the maintenance plan.

If you open the job properties screen and activate the Steps tab. You will see the "run as user" in the advanced tab of the edit job steps screen.

Check this user for necessary credentials.

Also control the owner of the job in the main job properties screen.

Eralper

Maintenance Job log incomplete

I have created a maintenance plan and specified a detailed job log. The log
only contains the entries down to the "Details" line and noting after.
This is on a SQL Server 2005 SP1 server.
Any ideas?This is happening to me too. This is all I get in the report text file and
yet the job has run for 4hrs and ends in a failed status.
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "DBSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:

Maintenance Job log incomplete

I have created a maintenance plan and specified a detailed job log. The log
only contains the entries down to the "Details" line and noting after.
This is on a SQL Server 2005 SP1 server.
Any ideas?
This is happening to me too. This is all I get in the report text file and
yet the job has run for 4hrs and ends in a failed status.
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "DBSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:

Maintenance Job failure

I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?
Yes, you can run sp_who to see who is in the database currently.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?
|||Yes, you can run sp_who to see who is in the database currently.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?
|||Run sp_who2 from within Query Analyzer, to see who are accessing the
database you are checking.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
|||Run sp_who2 from within Query Analyzer, to see who are accessing the
database you are checking.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
|||You should not run the automatic repair option as part of checking
consistency (this option has been removed in SQL Server 2005). Firstly it
requires changing the database state to single_user mode and secondly you
should always do root cause analysis of any inconsistencies.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
|||You should not run the automatic repair option as part of checking
consistency (this option has been removed in SQL Server 2005). Firstly it
requires changing the database state to single_user mode and secondly you
should always do root cause analysis of any inconsistencies.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
sql

Maintenance Job failure

Maintenance job fails

Hi there,

Used the wizard to create a maintenance plan for a db. But it fails most of
the time. I checked the log-files.

At 1.00 AM, it runs the "Optimizations" job for 6-9 seconds. It succeeds
always.

At 1.05 AM, for 1 second, it runs "integrity check", but most of the time it
fails, and says that it couldn't switch to single-user because other users
are using the database.

And when "integrity check" fails the "backup" job won't run at 1.10 AM. When
it DOES run it takes 2 seconds.

How do I find out who is using the database ? How do I put a sp_who (or
similar) in the "integrity check" job ?
I got this tip:
----
Assuming you have SQL2000, then you can use something like this to
kick out all the users:

ALTER DATABASE foo SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

If you want to know who is using the database, then this is one way:

select suser_sname(sid)
from master..sysprocesses
where dbid = db_id('foo')
---

But how do I actually do it ? How do I edit a maintenance plan ?

thx
/jimYou don't have to edit the maintenance plan; you can either add a step
to the job before the maintenance plan runs, or create a new job that
runs prior to you maintenance plan.

Stu|||in EM, Managment> SQL Server Agent>Jobs > Properties > Steps . You will
see the steps, which you can edit or insert. I would imagine you could
insert a step before the integrity to do the commands you posted.
HTH|||I'm not sure I completely follow your series of events, but I would try
using multiple steps in a single job. In the first step, set the
database to RESTRICTED_USER; in the second, run your integrity checks;
in the third, run the backup; in the fourth, set the database back to
MULTI_USER.

If you do set this up, one common error is to set the Database in the
job steps to the user database, instead of master. If you don't choose
master, then the job can block itself.

Simon|||Guys

We seem to be missing the main point here. The maint plan is trying to
put the database into single user mode because you have ticked the
attempt to repair minor problems option for the database integrity
checks.

As the vast majority of times this check runs, it will find no errors,
a lot of sites consider this unecessary to do every night. An option to
consider is to remove this option, which solves your imediate problem.
Check the output from the maint plan regularly and if you do get any
errors, schedule a run of DBCC CHECKDB with applicable options at a
time when you can arrange to not have users on the system.

As for editing your maintenance plan. In EM open the management folder
and highlight Database maintenance plans. Double click the plan in the
right paine and you can now change it.

Hope this helps

John|||My trick is to schedule the maintenance right after my system is
recycled during the night.
That way, I dont have to deal with this nagging issue.

Mario

Friday, March 30, 2012

Maintenance plan failing

Maintenance plan failing

Maintenance plan failing

Wednesday, March 28, 2012

Maintenance Plan DB Integrity Job Failed

Maintenance Plan DB Integrity Job Failed

Maintenance Plan DB Integrity Job Failed

Monday, March 26, 2012

Maintenance Plan / BackupJob failed with error 17055

Maintenance Plan

Maintenance Plan

Maintenance Plan

Maintenance Plan

Maintenance Plan

Friday, March 23, 2012

maintenance plan

Maintenance Job not working properly

Maintenance Job log incomplete

Maintenance Job log incomplete

Maintenance Job failure

Maintenance Job failure

Maintenance Job failure

Maintenance job fails

Lowering priority

Blog Archive

About Me