Database name not found in the Edit job step in SQL Server Enterprise Manager

Hi,

I need help. I transferred my database from one server to another by
using attach detach process.I created all the jobs which existed in the

old server in the new server. Everything looked fine but a couple of
jobs is a giving me some problems. When I try to edit the job step I
could see that the database dropdown list doesn't contain the database
name(which I transferred from the old server)

Does anyone have any idea about it.

Thanks
Arun(arunkumar_m2001@.yahoo.com) writes:
> I need help. I transferred my database from one server to another by
> using attach detach process.I created all the jobs which existed in the
> old server in the new server. Everything looked fine but a couple of
> jobs is a giving me some problems. When I try to edit the job step I
> could see that the database dropdown list doesn't contain the database
> name(which I transferred from the old server)
>
> Does anyone have any idea about it.

Have you tried simply closing down Enterprise Manager and starting it
again? I did a quick test, and it appears that the job-step dialogue
caches the database list, so it may not have seen the new database yet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Database Mirroring Monitor Job Error

We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.

Both SQL Servers are running SP2 with the latest patches.

Can you please let me know what we have to do to resolve this issue?

Thank you!

Bosko

Is this been like this since the invocation of Database mirroring on these instances?

If not see what has been changed since the last successful occurence.

|||The problems started upon implementation of the database mirroring|||

I'm also facing the same issue.

Did anyone find a solution or workaround?

|||

I posted this in a couple different forums/newsgroups and never got an answer. We are still having this problem.

Bosko

Database Mirroring Monitor Job Error

We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.

Both SQL Servers are running SP2 with the latest patches.

Can you please let me know what we have to do to resolve this issue?

Thank you!

Bosko

Is this been like this since the invocation of Database mirroring on these instances?

If not see what has been changed since the last successful occurence.

|||The problems started upon implementation of the database mirroringsql

Database Mirroring Monitor Job Error

We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step
failed.

Both SQL Servers are running SP2 with the latest patches.

Can anyone help with the resolutionto this this issue?

Thank you!
Bosko(bosko19@.gmail.com) writes:

Quote:

Originally Posted by

We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:
>
Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step
failed.
>
Both SQL Servers are running SP2 with the latest patches.
>
Can anyone help with the resolutionto this this issue?

Eh, that looks bad. I haven't worked much with mirroring, or more precisely
not at all. But a syntax errors is not what I would expect. Could you
list the exact name of the servers and databases involved? My suspicion
is that there is a problem with special characters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Apr 13, 6:45 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(bosk...@.gmail.com) writes:

Quote:

Originally Posted by

We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:

>

Quote:

Originally Posted by

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step
failed.

>

Quote:

Originally Posted by

Both SQL Servers are running SP2 with the latest patches.

>

Quote:

Originally Posted by

Can anyone help with the resolutionto this this issue?

>
Eh, that looks bad. I haven't worked much with mirroring, or more precisely
not at all. But a syntax errors is not what I would expect. Could you
list the exact name of the servers and databases involved? My suspicion
is that there is a problem with special characters.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

LOL...I didn't think it looked good either :)

The database server has a dash in it (i.e. CompanyName-SQL1 and SQL2
respecitvely). All databases are just alpha characters except for one
that begins with a number sequence (i.e. 1000DatabaseName).

Thank you in advance!

Bosko|||(bosko19@.gmail.com) writes:

Quote:

Originally Posted by

The database server has a dash in it (i.e. CompanyName-SQL1 and SQL2
respecitvely). All databases are just alpha characters except for one
that begins with a number sequence (i.e. 1000DatabaseName).

What service-pack level are you at? (For all three machines.)

If you are on SP2, you should definitely submit a bug on
http://connect.microsoft.com/SqlServer/. And if it's not an acceptable
workaround to rename the servers, I think you should open a case with
Microsoft, and demand that you get a hotfix.

If you are not on SP2, I recommend you to download it to see if it addresses
the problem. Note there is a rollup to SP2 which addresses two serious
bugs with maintenance plans. Make sure you get the rollup, if you use
maintenance plans.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

What service-pack level are you at? (For all three machines.)

Sorry, I see now in your first post that you are on SP2.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Apr 16, 5:30 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Erland Sommarskog (esq...@.sommarskog.se) writes:

Quote:

Originally Posted by

What service-pack level are you at? (For all three machines.)

>
Sorry, I see now in your first post that you are on SP2.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you for taking the time to look into the issue! I will submit
the case to Microsoft and hopefully get a resolution from them.

Bosko

Database Marked as Single User Pls Help

Hi All,
Sql Server 7

I have database called ecatalog

i have a scheduled job which shrinks the database every day once at 12 am

today the job got failed

In the view Job history its showing the below contents
-----------------
Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error
924) DBCC execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528). The step failed.

And in Application Log of event viewer its showing the below contents
------------------------
The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The
local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database
- ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed.
The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..

I ran the job manually it worked fine

but now when i go and see the database ecatalog in my Enterprise Manager
it is showing ecatalog(Single User)
What is the meaning of this, will this make any problem to my database

Please help me in this.

Waiting for Reply

AdilIt means only one connection can be allowed in this db. use sp_dboption 'ecatalog', 'single', 'false' to turn it off.|||Hi Thanks for reply,

I wanted to know that does this happens on its own or has some one has done it.

Thanks waiting for reply
Adil|||No it won't do this on its own. check all jobs to see if any sp_dboption command would have done that. Also check sql error logs and look for a series of Closing file, Starting up database and Opening file. that may represent an event to put the db in single user mode.

Note some operatins do require single user mode, such as using sp_rename to change a db name.|||I think that many of the maintenance jobs will put the database into SINGLE USER mode because it is required for some kinds of maintenance. Those jobs normally take the database back out of SINGLE USER automagically when they complete.

-PatP

Database Maintenance Plan for SQL Server 2005 Problem

I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If these
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:
> I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected.
> I have checked and found that SQL Server Agent is already started. Is there
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am
> not able to create both DB Backup (early in the morning) and Transaction log
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
Most commonly this is because you have included some databases that are in simple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that task.
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you don't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ... it
> seems that they are run at the same time. Is there any way to change the time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance
>>Plan.
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild
>> index, database backup ... it seems that they are run at the same time.
>> Is there any way to change the time they are running ?
>> Thanks
>> Peter
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a likely cause.
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors, I assume that the
statements were executed with success.
> If there is problem when the maintenance plan is executed, will the error message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged to the various log
destinations (report file, maint plan history tables etc). Whether an error is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed yesterday (It supposed to
> start immediately) but it works properly today. The daily production database backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system database backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error). If there is problem when the maintenance plan is executed, will the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Most commonly this is because you have included some databases that are in simple recovery mode.
>> When a maint plan is returned an error inside a task, it will terminate that task.
>>
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
>> big thing since you will still have to create at least two tasks:
>> One for the databases in simple recovery (master, for example), where you don't do log backup.
>> And another for the database where you do want to do log backups...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ...
>> it seems that they are run at the same time. Is there any way to change the time they are
>> running ?
>> Thanks
>> Peter
>>
>

Database Maintenance Plan for SQL Server 2005 Problem

I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If thes
e
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:

> I am new to SQL Server 2005 and just set up a number of DB Maintenance Pla
n.
> However, I find that the transaction log backup job doesn't run as expecte
d.
> I have checked and found that SQL Server Agent is already started. Is the
re
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m
> not able to create both DB Backup (early in the morning) and Transaction l
og
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that
I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checke
d and
> found that SQL Server Agent is already started. Is there any way to check where t
he problem is ?
Most commonly this is because you have included some databases that are in s
imple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that
task.

> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the w
izard ?
Yes. But in sp2, you will be able to have separate schedules for each task.
I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you do
n't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan
.
> However, I find that the transaction log backup job doesn't run as expecte
d. I have checked and
> found that SQL Server Agent is already started. Is there any way to check
where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans wi
th the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it
> seems that they are run at the same time. Is there any way to change the
time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I
change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a li
kely cause.

> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors
, I assume that the
statements were executed with success.

> If there is problem when the maintenance plan is executed, will the error
message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged
to the various log
destinations (report file, maint plan history tables etc). Whether an error
is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed y
esterday (It supposed to
> start immediately) but it works properly today. The daily production datab
ase backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system datab
ase backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error). If there is problem when the maintenance plan is executed, wil
l the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>

Database Maintenance Plan -> SQLMAINT.exe Error

Having a problem with a Database Maintenance Plan that runs a tran log
backup. The job has been running successfully until very recently. (The
failures seem to coincide with the installation of the Feb 2005 CTP.) When
the job runs, it fails almost immediately. No report is generated. No
Maintenance Plan History is recorded in the Plan History Window. The only
info that gets recorded is that the step failed in the Sql Agent Job history
for the job.
Here is output from running it directly from Query Analyzer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
/* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
not in the path and not in current directory)
-- but then that util fails - probably a dll overlaided by the sql2005 beta
install
output
-----------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
NULL
(5 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Not much info there, huh?
Any help would be appreciated.
Check to make sure the db is not in simple recovery mode.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----------
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>
|||Checked that already, all dbs are in full. It appears as if the job isnt
even getting off the ground. It returns failure in a few seconds (< 5
seconds). Previously, if we had included a DB in simple mode in the job, the
job would run, but exit with a return code of 1 and a report would be
created. It appears the job is failing before it tried to hit any of the
databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eTz5ke$IFHA.2356@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Check to make sure the db is not in simple recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--
>
|||I solved the problem. It appears that the uninstallation of Sql server 2005
interferes/unregisters the sqldmo.dll library. I reregistered the DLL and
all is well. Thanks!
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----------
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>
|||You should NOT be running SQL2005 on a production server. I won't even run
it in anything other than a virtual server myself.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I solved the problem. It appears that the uninstallation of Sql server
>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>and all is well. Thanks!
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--
>
|||This is a DEV server, not PROD.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:udfY5pFJFHA.3064@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You should NOT be running SQL2005 on a production server. I won't even
> run it in anything other than a virtual server myself.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
--
>

Database Maintenance Plan -> SQLMAINT.exe Error

Having a problem with a Database Maintenance Plan that runs a tran log
backup. The job has been running successfully until very recently. (The
failures seem to coincide with the installation of the Feb 2005 CTP.) When
the job runs, it fails almost immediately. No report is generated. No
Maintenance Plan History is recorded in the Plan History Window. The only
info that gets recorded is that the step failed in the Sql Agent Job history
for the job.
Here is output from running it directly from Query Analyzer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
/* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
not in the path and not in current directory)
-- but then that util fails - probably a dll overlaided by the sql2005 beta
install
output
----
----
----
--
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
NULL
(5 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Not much info there, huh?
Any help would be appreciated.Check to make sure the db is not in simple recovery mode.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----
----
----
--
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||Checked that already, all dbs are in full. It appears as if the job isnt
even getting off the ground. It returns failure in a few seconds (< 5
seconds). Previously, if we had included a DB in simple mode in the job, the
job would run, but exit with a return code of 1 and a report would be
created. It appears the job is failing before it tried to hit any of the
databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eTz5ke$IFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Check to make sure the db is not in simple recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--[vbcol=seagreen]
>|||I solved the problem. It appears that the uninstallation of Sql server 2005
interferes/unregisters the sqldmo.dll library. I reregistered the DLL and
all is well. Thanks!
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----
----
----
--
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||You should NOT be running SQL2005 on a production server. I won't even run
it in anything other than a virtual server myself.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I solved the problem. It appears that the uninstallation of Sql server
>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>and all is well. Thanks!
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--[vbcol=seagreen]
>|||This is a DEV server, not PROD.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:udfY5pFJFHA.3064@.TK2MSFTNGP12.phx.gbl...
> You should NOT be running SQL2005 on a production server. I won't even
> run it in anything other than a virtual server myself.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
--[vbcol=seagreen]
>

Database Maintenance Plan -> SQLMAINT.exe Error

Having a problem with a Database Maintenance Plan that runs a tran log
backup. The job has been running successfully until very recently. (The
failures seem to coincide with the installation of the Feb 2005 CTP.) When
the job runs, it fails almost immediately. No report is generated. No
Maintenance Plan History is recorded in the Plan History Window. The only
info that gets recorded is that the step failed in the Sql Agent Job history
for the job.
Here is output from running it directly from Query Analyzer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
/* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
not in the path and not in current directory)
-- but then that util fails - probably a dll overlaided by the sql2005 beta
install
output
-----------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
NULL
(5 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Not much info there, huh?
Any help would be appreciated.Check to make sure the db is not in simple recovery mode.
--
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> -----------
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||Checked that already, all dbs are in full. It appears as if the job isnt
even getting off the ground. It returns failure in a few seconds (< 5
seconds). Previously, if we had included a DB in simple mode in the job, the
job would run, but exit with a return code of 1 and a report would be
created. It appears the job is failing before it tried to hit any of the
databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eTz5ke$IFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Check to make sure the db is not in simple recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
>> Having a problem with a Database Maintenance Plan that runs a tran log
>> backup. The job has been running successfully until very recently. (The
>> failures seem to coincide with the installation of the Feb 2005 CTP.)
>> When the job runs, it fails almost immediately. No report is generated.
>> No Maintenance Plan History is recorded in the Plan History Window. The
>> only info that gets recorded is that the step failed in the Sql Agent Job
>> history for the job.
>> Here is output from running it directly from Query Analyzer.
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt
>> "C:\MSSQL\Log\Backup\Transaction Log Bkup6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog
>> "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
>> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
>> not in the path and not in current directory)
>> -- but then that util fails - probably a dll overlaided by the sql2005
>> beta install
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> NULL
>> (5 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>> Not much info there, huh?
>> Any help would be appreciated.
>|||I solved the problem. It appears that the uninstallation of Sql server 2005
interferes/unregisters the sqldmo.dll library. I reregistered the DLL and
all is well. Thanks!
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> -----------
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||You should NOT be running SQL2005 on a production server. I won't even run
it in anything other than a virtual server myself.
--
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I solved the problem. It appears that the uninstallation of Sql server
>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>and all is well. Thanks!
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
>> Having a problem with a Database Maintenance Plan that runs a tran log
>> backup. The job has been running successfully until very recently. (The
>> failures seem to coincide with the installation of the Feb 2005 CTP.)
>> When the job runs, it fails almost immediately. No report is generated.
>> No Maintenance Plan History is recorded in the Plan History Window. The
>> only info that gets recorded is that the step failed in the Sql Agent Job
>> history for the job.
>> Here is output from running it directly from Query Analyzer.
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt
>> "C:\MSSQL\Log\Backup\Transaction Log Bkup6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog
>> "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
>> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
>> not in the path and not in current directory)
>> -- but then that util fails - probably a dll overlaided by the sql2005
>> beta install
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> NULL
>> (5 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>> Not much info there, huh?
>> Any help would be appreciated.
>|||This is a DEV server, not PROD. :)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:udfY5pFJFHA.3064@.TK2MSFTNGP12.phx.gbl...
> You should NOT be running SQL2005 on a production server. I won't even
> run it in anything other than a virtual server myself.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
>>I solved the problem. It appears that the uninstallation of Sql server
>>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>>and all is well. Thanks!
>> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
>> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
>> Having a problem with a Database Maintenance Plan that runs a tran log
>> backup. The job has been running successfully until very recently. (The
>> failures seem to coincide with the installation of the Feb 2005 CTP.)
>> When the job runs, it fails almost immediately. No report is generated.
>> No Maintenance Plan History is recorded in the Plan History Window. The
>> only info that gets recorded is that the step failed in the Sql Agent
>> Job history for the job.
>> Here is output from running it directly from Query Analyzer.
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt
>> "C:\MSSQL\Log\Backup\Transaction Log Bkup6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog
>> "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
>> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it
>> is not in the path and not in current directory)
>> -- but then that util fails - probably a dll overlaided by the sql2005
>> beta install
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> NULL
>> (5 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>> Not much info there, huh?
>> Any help would be appreciated.
>>
>

Database Mainetance Plan(Backup)

I recieved the follwoing message from the backup job of my database manitean
nce plan:
What does this mean? Can I not make a backup if users are on the system? Wha
t do I change?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'P
G_PROD' as 'PG_PROD\SQLAGENT' (trusted)
Starting maintenance plan 'PayCentralFULLSunday' on 4/4/2004 12:30:00 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Database state cannot be changed w
hile other users are using the database 'PAYCENTRAL'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[1] Database PAYCENTRAL: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Repair statement not processed. Da
tabase needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'PayCentralFULLSunday' on 4/4/2004 12:30:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Thanks,
ThomasLLThomas
It's not your backup. In your plan under the database integrity check you ha
ve ticked attempt to repair any minor problems. SQL Server is attempting to
set the database to single user mode so it can do this. It is not required,
untick the box. Don't forge
t to check the output for errors and if you get any arrange to run dbcc chec
kdb with applicable repair option at a time that no users are on the system.
Hope this helps
John

Database Mail Problems / Job Notifications...

I've read an extensive bit about this problem in several forums and so far none of the information has helped me to determine if there is a bug in SQL 2005 x64 regardless of version or SP or if I just don't know how to do this correctly.

I can send a test email from db mail but when a job fails it does not send an email to an operator. I have monitored the IIS logs to see if it is trying to send and it is not. Database mail is enabled in Surface AC and in SQL Agent [one odd thing is that the 'test' button is only available on the SQL Agent Alerts page if I use SQL Mail]. No specific error happens (in the mail log, sql agent log, or application event log) when I try to send mail from a failed job. Mail accounts and credentials are valid. Please note that in the steps listed below, I have restarted the SQL Agent Service each time I make a change.

I've tried deleting and recreating the operators (they are enabled). I've tried using a mail server that uses anonymous authentication as well (I changed this back to one that uses basic authentication). The profiles were made default and public. (Though this shouldn't ever have to be done, as the account is a sysadmin) the SQLServer2005SQLAgentUser was explicitly added to the msdb DatabaseMail user role.

This query works, so I know that the job is not trying to execute the sp:

USE msdb ;

GO

EXEC dbo.sp_notify_operator

@.profile_name = N'staging monitor',

@.name = N'Charles Evans',

@.subject = N'Test Notification',

@.body = N'Successfully emailed profile.' ;

GO

This is happening in SQL 2005 x64 EE and x64 DE SP1 and SP2 respectively. Any thoughts on what else this could be? The same setup works fine in SQL 2005 SE x86 SP1.

Check the following article...

How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions

http://support.microsoft.com/kb/908360

|||So the only way is to use SQL Mail, instead of database mail?|||

SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions.

Please read the article... you can setup mail using SMTP where SQL mail uses MAPI ...

|||

In SP0 this is true - according to the article:

"In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems."

I've made this work in our production system now with SP-1 EE x64, there was a permissions issue. However, things are not working in our staging environment SP-2 DE x64 with the same setup and account settings/permissions.

I'll keep posted if I figure out why not in staging.

Database mail issue - any ideas?

We recently installed 2005 and have been playing with it. I seem to be
having a problem with Database Mail as it relates to job notifications,
and wanted to see if anyone knew what I was doing wrong or how to fix
this issue.
We've enabled Database Mail using the SAC tool. I've set up a mail
profile & account successfully and sent successful test messages. I
followed the instructions here (
http://msdn2.microsoft.com/en-us/library/ms186358.aspx ) on configuring
SQL Agent mail to use my Database Mail profile. But alas - no messages
ever arrive. In the SQL Agent log, I see this:
Message
[264] An attempt was made to send an email when no email session has
been established
Everything seemed rather straightforward....am I doing something wrong?
Did you run msdb.dbo.sysmail_start_sp? Try
msdb.dbo.sysmail_help_status_sp and see what the result is.
-KJ
|||The result was...
STARTED
|||You may need to define an operator.
|||I also defined an operator (myself). The operator email has the same
email address as the test message that was sent successfully, so I know
everything outside of SQL is working (SMTP relay, etc.).
|||OK - got it figured out. I just had to stop & restart the service,
which I did through SAC tool. Then I immediately received my next test
from the job.

Database mail issue - any ideas?

We recently installed 2005 and have been playing with it. I seem to be
having a problem with Database Mail as it relates to job notifications,
and wanted to see if anyone knew what I was doing wrong or how to fix
this issue.
We've enabled Database Mail using the SAC tool. I've set up a mail
profile & account successfully and sent successful test messages. I
followed the instructions here (
http://msdn2.microsoft.com/en-us/library/ms186358.aspx ) on configuring
SQL Agent mail to use my Database Mail profile. But alas - no messages
ever arrive. In the SQL Agent log, I see this:
Message
[264] An attempt was made to send an email when no email session has
been established
Everything seemed rather straightforward....am I doing something wrong?Did you run msdb.dbo.sysmail_start_sp? Try
msdb.dbo.sysmail_help_status_sp and see what the result is.
-KJ|||The result was...
STARTED|||You may need to define an operator.|||I also defined an operator (myself). The operator email has the same
email address as the test message that was sent successfully, so I know
everything outside of SQL is working (SMTP relay, etc.).|||OK - got it figured out. I just had to stop & restart the service,
which I did through SAC tool. Then I immediately received my next test
from the job.

Database mail issue - any ideas?

We recently installed 2005 and have been playing with it. I seem to be
having a problem with Database Mail as it relates to job notifications,
and wanted to see if anyone knew what I was doing wrong or how to fix
this issue.
We've enabled Database Mail using the SAC tool. I've set up a mail
profile & account successfully and sent successful test messages. I
followed the instructions here (
http://msdn2.microsoft.com/en-us/library/ms186358.aspx ) on configuring
SQL Agent mail to use my Database Mail profile. But alas - no messages
ever arrive. In the SQL Agent log, I see this:
Message
[264] An attempt was made to send an email when no email session has
been established
Everything seemed rather straightforward....am I doing something wrong?Did you run msdb.dbo.sysmail_start_sp? Try
msdb.dbo.sysmail_help_status_sp and see what the result is.
-KJ|||The result was...
STARTED|||You may need to define an operator.|||I also defined an operator (myself). The operator email has the same
email address as the test message that was sent successfully, so I know
everything outside of SQL is working (SMTP relay, etc.).|||OK - got it figured out. I just had to stop & restart the service,
which I did through SAC tool. Then I immediately received my next test
from the job.

database mail isn't working in the SQL Agent job notification

Hi,

Starting a few weeks ago I stopped receiving notifications when SQL Server Agent Jobs failed. The only error I see from the Error logs is the following: "[264] An attempt was made to send an email when no email session has been established"

I've followed all of the postings about stoping and restarting the Agent service, confirming that the database mail can send out test emails and making sure that database mail account name matches the actual SMTP account.

From my testing, I can successfully send out a test email by right-clicking on "Database Mail" - "Send Test E-Mail."

Here's my setup:
Active/Passive SQL Server 2005 Cluster.
32 bit
SS2005 SP1 installed

Database Mail worked for around 6 months and then stopped. Not sure if it's a permissions issue, patch issue or a configuration issue.

Any help resolving this would be great.

Sincerely,

Steve

Ditto for me. Standard SQL 2005 setup. Engine, AS, Integration, etc. with SP1. I've done all the troubleshooting steps detailed here because this was the closest related issue I found on the Web.
The stored proc sp_send_dbmail works. So I can craft a workaround.

I found the same issue posted under Technet here. Any ideas?

|||

Well this is a slap in the face.

My manager suggested I do a restart of the SQL Server Agent. I had tried that before but since he is the boss I did it again expecting the same results. Well as you might have guessed - its working. And not just on the development server but the production too. Can't figure it.

|||

Restarting has become my habit in debugging now thanks to this

"Restart" has been mentioned in couple threads though if you searched by your error message here (or Google)

It's really weird, SQL Mail stopped working randomly IMO, thank god this doesn't happen to SQL Server

|||

Steve,

I've seen this from time-to-time on our servers, as well. After a restart of SQL Server Agent, database mail will usually run fine for a month and then the error will come back again. It is frustrating especially when it happens on production boxes with replication, but at least it doesn't require a server reboot like SQL Mail always did when it locked up.

Database Mail fails to email Operator

Sqlserver 2005 SP2
Hi All,
Upon completion of a job, an email should be sent to an operator but instead
it return the following error:
Message
The job succeeded. The Job was invoked by User. The last step to run was
step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
I am able to send the test email without any issues so I am not sure why the
email notification does not go through.
Regards
JN
Try to verify the privileges of the SQL Server Agent Service Account;
More important: do you configured Database Mail and created a profile for it
(and added members to DatabaseMailUserRole of msdb)?
Look at Books on line the topics related to Database Mail
Gilberto Zampatti
"JN" wrote:

> Sqlserver 2005 SP2
> Hi All,
> Upon completion of a job, an email should be sent to an operator but instead
> it return the following error:
> Message
> The job succeeded. The Job was invoked by User. The last step to run was
> step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
> I am able to send the test email without any issues so I am not sure why the
> email notification does not go through.
> Regards
> JN
>
>

Database Mail fails to email Operator

Sqlserver 2005 SP2
Hi All,
Upon completion of a job, an email should be sent to an operator but instead
it return the following error:
Message
The job succeeded. The Job was invoked by User. The last step to run was
step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
I am able to send the test email without any issues so I am not sure why the
email notification does not go through.
Regards
JNTry to verify the privileges of the SQL Server Agent Service Account;
More important: do you configured Database Mail and created a profile for it
(and added members to DatabaseMailUserRole of msdb)?
Look at Books on line the topics related to Database Mail
Gilberto Zampatti
"JN" wrote:
> Sqlserver 2005 SP2
> Hi All,
> Upon completion of a job, an email should be sent to an operator but instead
> it return the following error:
> Message
> The job succeeded. The Job was invoked by User. The last step to run was
> step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
> I am able to send the test email without any issues so I am not sure why the
> email notification does not go through.
> Regards
> JN
>
>

Database Mail Error Object reference not set to an instance of an object

We have SQL Server 2005 setup to send out emails on job failure... This am no emails went out for failed jobs and there were several (identical) log entries as shown below

---

Date 2/27/2007 4:30:04 AM
Log Database Mail (Database Mail Log)

Log ID 48
Process ID 2180

Mail Item ID 29
Last Modified 2/27/2007 4:30:04 AM
Last Modified By sa

Message
Object reference not set to an instance of an object.

---

How can I diagnose this? I searched the forums and google groups and couldnt find this error message in the context of the database mail component of SQLServer. Thanks in advance

PS: This has been moved from the SSIS forum ... The moderator there can delete that thread

got these yesterday am and today am ... any insights?
|||deleted the mail profile and all associated accounts and re-created them and that fixed the issue

Database mail does not work from Sql Server Agent Job after upgrade to SP1

Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

I had the exact same problem with the same error on 2005. To fix it, I did the following:

1. Went into object-explorer, right-clicked on the SQL Server Agent, and selected properties. Then went to the Alert System page, and enabled the mail profile for the server agent. (you may already have this).

2. Then, went to "Database mail", right-clicked and selected "Configure Database mail." Selected "Manage profile security" and made sure my profile was set to public. THEN, and this is the kicker, clicked on the "Default Profile" field and set it to "yes".

|||

I am still having issues after working through all these steps. Any other suggestions.

In general I seem to be having a lot of issues with the 64 Bit on 2005.

|||

I'm having the exact same issue.

I have SQL Server Standard Edition 2005 (SP1) running and the Database Mail is not working correctly. I get a message in the SQL Agent log saying:

"SQLServer Error: 2812, Could not find stored procedure msdb.dbo.xp_sqlagent_notify"

Any solutions to this? What is this stored procedure? I can't find any documentation on it on Microsoft's site or anywhere for that matter.

|||

I just started working on the mail on my test server and I have the same problem.

I have followed the instruction above to no avail. I have set up a job, when it completes it is to email me. I have set up mail and have been able to send a test mail by right clicking the db mail and selecting send test mail. I have se my self up as an operator. I have enabled db mail. The only thing I find odd is when I go to SQL server agent and select properties then select alert system the test button is grayed out.

|||

Ok I found another test I could run to see if I have it configured correctly.

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'User@.Place.com',

@.subject=N'just another mail test.',

@.body=N'just another mail test.',

@.profile_name = 'EMail';

When I run this I get the response that the mail is queued. Shortly there after I get the mail. In my case there must be an issue between the jobs and the operator.

|||

Ok it looks like I might have solve it for my self. Follow this link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322173&SiteID=1