Errors of System DB Backup T-logJobs for Maint Plan

I have been bothered with the error signs of the DB Backup
jobs for my DB Maint Plan. These job errors were shown in
SQL Server Agent node of the Enterprise Manager.
After checking the DB Maint Plan history, I found the
errors were due to the Backup T-log job errors on system
databases, e.g. master, msdb. Later on I found out that
we can set master to Full recovery model, but still no T-
log backup choice available, while tempdb can not even be
set to Full model.
If I still want to select 'All Databases' in my Maint
Plan, how can I avoid these Maint Plan job errors in the
SQL Server Agent node? How do I decide the DB recovery
model (Full or Simple) for the system databases in my
database servers when configuring my Maint Plan?
Furthermore, I have heard that there are no T-log backup
device for the system databases, only for user databases.
What exactly does this mean?
Thanks in Advance.
JeffreyI normally create one maint plan for my sysdbs this way you can avoid that
and some other errors.
You cannot restore the master database like you restore the users db, the
master db has to be rebuild. Because of the size of your system db i
normally backed them up twice a day, and overwrite every two weeks and i
have it set to simple.
Take a look at BOL (books online) to understand more about backup models.
Yovan Fernandez
"Jeffrey" <jwang@.dot.state.tx.us> wrote in message
news:0bd901c377d7$bcf03a40$a001280a@.phx.gbl...
> I have been bothered with the error signs of the DB Backup
> jobs for my DB Maint Plan. These job errors were shown in
> SQL Server Agent node of the Enterprise Manager.
> After checking the DB Maint Plan history, I found the
> errors were due to the Backup T-log job errors on system
> databases, e.g. master, msdb. Later on I found out that
> we can set master to Full recovery model, but still no T-
> log backup choice available, while tempdb can not even be
> set to Full model.
> If I still want to select 'All Databases' in my Maint
> Plan, how can I avoid these Maint Plan job errors in the
> SQL Server Agent node? How do I decide the DB recovery
> model (Full or Simple) for the system databases in my
> database servers when configuring my Maint Plan?
> Furthermore, I have heard that there are no T-log backup
> device for the system databases, only for user databases.
> What exactly does this mean?
> Thanks in Advance.
> Jeffrey
>

ErrorFile option

The documentation point out that "distribution agent utility" can accept the
option "-ErrorFile". But the distributor always refuse it. Why?
Thanks
you only get this file generated if there is an error. You can find these
files by default in %windir%\system32
Microsoft has not provided a mechanism to clean these files up, so you will
have to delete them manually.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Fausto" <Fausto@.discussions.microsoft.com> wrote in message
news:9AF87ED3-2F63-467E-9629-DD931DF25A88@.microsoft.com...
> The documentation point out that "distribution agent utility" can accept
the
> option "-ErrorFile". But the distributor always refuse it. Why?
> Thanks
>

Error18025:xp_test_mapi_profile failed with mail error0x80040111

Hi,

I configured the Sql server agent to start with local system and made a profile in intenet mail as SQLMail (Did not used Outlook or Excahnge Server used ar inbox on desktop (Windows messaging system) and internet mail, sendmail which we are having in house), When I tried to Configure SQL Mail for sql server and test , it is ginving the following error.

Error18025:xp_test_mail_profile failed with mail error 0x80040111

:mad:Hello,

i have the same Problemes, how you described it.

Did you start the mail Session?

Have a look on this:
USE master
EXEC xp_startmail

Please enter this brackets in Sql Query and start them.

My Problems already exists. I have problems in sending an e-mail if a error occurs by excuting jobs.

Bye
Harald Bothner|||Ok fellows...

There goes the answers to both of you.

1- Error18025:xp_test_mapi_profile failed with mail error0x80040111

The SQL Server relies in the MAPI libraries to send email messages. Unfortunately, it works at its best allied with Exchange, wich you seem to do not have. But it also works with a pop3 box, leaving the inconvenient need of requiring an Outlook open to get and send messages, and you would have to manage it manually.

Try to use Exchange. If you cant, try using CDONTS. Take a look at the link bellow:
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839

2- mail problems by using sql server

It seems to me that you have not configured the SQL Mail Agent, wich is used by the SQL Server Agent to send notifications about alerts or job status etc.
Take a look at the properties of your SQL Server Agent and see if the email profile is set properly. If its not, just set it and then restart the SQL Server Agent and youre done.

Error: The replication agent has not logged a progress message in 10 minutes

Hello,

We are consistently getting the error message below on our subscribers that have blob images. Is there a way to increase a setting to avoid SQL to throw this error, or another suggestion? Thanks in advance.

John

Error messages:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

18 -BcpBatchSize 100000
18 -ChangesPerHistory 100
18 -DestThreads 2
18 -DownloadGenerationsPerBatch 5
18 -DownloadReadChangesPerBatch 100
18 -DownloadWriteChangesPerBatch 100
18 -FastRowCount 1
18 -HistoryVerboseLevel 3
18 -KeepAliveMessageInterval 300
18 -LoginTimeout 15
18 -MaxBcpThreads 2
18 -MaxDownloadChanges 0
18 -MaxUploadChanges 0
18 -MetadataRetentionCleanup 1
18 -NumDeadlockRetries 5
18 -PollingInterval 60
18 -QueryTimeout 400
18 -SrcThreads 2
18 -StartQueueTimeout 0
18 -UploadGenerationsPerBatch 3
18 -UploadReadChangesPerBatch 100
18 -UploadWriteChangesPerBatch 100
18 -Validate 0
18 -ValidateInterval 60

Due to the memory leak issue with respect to replicating blobbed images we have changed UploadGenerationsPerBatch to = 3.

Unfortunately, you are seeing something that I've had severe problems with at several customers. Support refuses to acknowledge this as a problem and also refuses to offer any kind of valid solution. My particular case was in having tens or hundreds of thousands of level 18 errors thrown into the logs for a normal operational state. The only solution from support was to completely disable error logging for absolutely everything on the instance. Yours is a slightly smaller issue, but still a major one. Unfortunately, there is no setting at all to allow someone to suppress error messages. My only solution was to run the SQL Server in an unsupported state by directly editing system stored procedures and commenting out the offending code within the replication procs. I've filed several bugs on the tremendous amount of logging that the replication agents do, but every one has either fallen off the face of the earth or been closed as "by design". (One of the customers that I shut the logging of 14151 errors off on saw their processor utilization on a 32 way machine drop from an average of about 75% to less than 25%. So, I'm not really sure how a design feature of SQL Server could be to chew up about 50% of the processor cycles just to log error messages.)

My first suggestion is to open a support case. Maybe after a couple hundred people open support cases on the fact that logging in 2005 is substantial and a DBA has zero control over suppressing things that they consider a normal operational state, we might start getting somewhere.

|||

Have you tried setting these 2 parameters:

-OutputVerboseLevel 0

-HistoryVerboseLevel 0

?

|||

Mahesh,

I have used the above mentioned setting for transactional replication. Replication runs fine for 2 days or so and after that without any error message I get 'subscriber re-initialization' required message. I have replication running every minute for 12 publication and you would imagine the amount of logging from distribution agent. Unfortunately, running replication continously is not an option because we are currently experiencing network-related issue that drop the connection every 10 minutes or so.

I would appreciate if you could offer me some thoughts on that.

|||please refer to:

http://msdn2.microsoft.com/en-us/library/ms146868.aspx

especially the section:

-- Change the heartbeat interval at the Distributor to 5 minutes.
USE master
exec sp_changedistributor_property
@.property = N'heartbeat_interval',
@.value = 5;
GO

Setting this to a higher value will have the agents only log at this higher value.

This setting was introduced in SQL 7 IIRC.

Error: The replication agent has not logged a progress message in 10 minutes

Hello,

We are consistently getting the error message below on our subscribers that have blob images. Is there a way to increase a setting to avoid SQL to throw this error, or another suggestion? Thanks in advance.

John

Error messages:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

18 -BcpBatchSize 100000
18 -ChangesPerHistory 100
18 -DestThreads 2
18 -DownloadGenerationsPerBatch 5
18 -DownloadReadChangesPerBatch 100
18 -DownloadWriteChangesPerBatch 100
18 -FastRowCount 1
18 -HistoryVerboseLevel 3
18 -KeepAliveMessageInterval 300
18 -LoginTimeout 15
18 -MaxBcpThreads 2
18 -MaxDownloadChanges 0
18 -MaxUploadChanges 0
18 -MetadataRetentionCleanup 1
18 -NumDeadlockRetries 5
18 -PollingInterval 60
18 -QueryTimeout 400
18 -SrcThreads 2
18 -StartQueueTimeout 0
18 -UploadGenerationsPerBatch 3
18 -UploadReadChangesPerBatch 100
18 -UploadWriteChangesPerBatch 100
18 -Validate 0
18 -ValidateInterval 60

Due to the memory leak issue with respect to replicating blobbed images we have changed UploadGenerationsPerBatch to = 3.

Unfortunately, you are seeing something that I've had severe problems with at several customers. Support refuses to acknowledge this as a problem and also refuses to offer any kind of valid solution. My particular case was in having tens or hundreds of thousands of level 18 errors thrown into the logs for a normal operational state. The only solution from support was to completely disable error logging for absolutely everything on the instance. Yours is a slightly smaller issue, but still a major one. Unfortunately, there is no setting at all to allow someone to suppress error messages. My only solution was to run the SQL Server in an unsupported state by directly editing system stored procedures and commenting out the offending code within the replication procs. I've filed several bugs on the tremendous amount of logging that the replication agents do, but every one has either fallen off the face of the earth or been closed as "by design". (One of the customers that I shut the logging of 14151 errors off on saw their processor utilization on a 32 way machine drop from an average of about 75% to less than 25%. So, I'm not really sure how a design feature of SQL Server could be to chew up about 50% of the processor cycles just to log error messages.)

My first suggestion is to open a support case. Maybe after a couple hundred people open support cases on the fact that logging in 2005 is substantial and a DBA has zero control over suppressing things that they consider a normal operational state, we might start getting somewhere.

|||

Have you tried setting these 2 parameters:

-OutputVerboseLevel 0

-HistoryVerboseLevel 0

?

|||

Mahesh,

I have used the above mentioned setting for transactional replication. Replication runs fine for 2 days or so and after that without any error message I get 'subscriber re-initialization' required message. I have replication running every minute for 12 publication and you would imagine the amount of logging from distribution agent. Unfortunately, running replication continously is not an option because we are currently experiencing network-related issue that drop the connection every 10 minutes or so.

I would appreciate if you could offer me some thoughts on that.

|||please refer to:

http://msdn2.microsoft.com/en-us/library/ms146868.aspx

especially the section:

-- Change the heartbeat interval at the Distributor to 5 minutes.
USE master
exec sp_changedistributor_property
@.property = N'heartbeat_interval',
@.value = 5;
GO

Setting this to a higher value will have the agents only log at this higher value.

This setting was introduced in SQL 7 IIRC.

Error: Server Agent cant find dts file

Im trying to schedule an integration sevice package with no anvile. I have tried both running it in Agent under Integration services and operation system (CmdExec) and still wont work. Under CmdExec, the command is /FILE "C:\Documents and Settings\ebuah\My Documents\Visual Studio 2005\Projects\Swamp_Solution\FIRSTTIME_DOWNLOAD\SSIS_ACT.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF. In the advance tab, I checked outfile to a log.txt file so as to read the log if there was any errors. The account running the job is the same account that sql server agent services is running which would only mean I really dont need any proxy account to run the job. When i run the job under CmdExec, i get the error

Message
Executed as user: CYPRESS\Administrator. The process could not be created for step 1 of job 0x529C255F20199B40A83858B1CCC875F2 (reason: The system cannot find the file specified). The step failed.

when I run the same package under integeration system, the error I get in the logg file is

<record>
<event>OnError</event>
<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>
<computer>SPIDERMAN</computer>
<operator>CYPRESS\Administrator</operator>
<source>ACT TABLE 250</source>
<sourceid>{99b92f4c-686d-47a6-b136-4d1ec698c837}</sourceid>
<executionid>{CD95BB1A-7FE4-468B-904D-C02B3FEBF1B0}</executionid>
<starttime>4/17/2006 1:20:47 PM</starttime>
<endtime>4/17/2006 1:20:47 PM</endtime>
<datacode>-1073450910</datacode>
<databytes>0x</databytes>
</record>

If you ask me, I dont know whats going on but I am very fustrated. Have been working on this for 4 days now. I really really need help.

The error is right there. "Data source name not found".

I would guess that either you haven't deployed the ODBC DSN on that machine or the account you are running it as doesn't have permission to see it.

-Jamie

|||I am running on a 64x machine on which i installed the 64x version of SSIS. The odbc driver in installed in the wow folder (32 bit). My packages are set up to execute in 32bit so they can see the odbc driver. Do you think that under server agent, its not able to see the 32bit odbc driver. (by the way, the account i running under is an admin account, has rights to everything)

Also, in the process of debugging, I tried a few things to eliminate causing senarios. I created a simple parkage that doesnt use the odbc drive. It does a staight pull from one sql server table to another using ole connection . When i run that under Sever Agent with Integration services, it runs smoothly. For the same simple package, when i run under CmdExec, I get the error: The process could not be created for step 1 of job 0xF791BD51E4709E48AD83FB56038ECF95 (reason: The system cannot find the file specified). The step failed. So, my question is what is wrong. It runs fine under integeration services but errors out under CmdExec.

From my first test, I made an assumption that only running under intergration service in Sever Agent will work since its passed the test. Based on that, I changed my simple package to include an odbc source connection (pulling from a remote DB/4 FC server with 32bit DB/4 odbc driver installed in wow on 64bit). When I run the package in Agent under integration services, I got an error: The package execution failed. The step failed. Luckly, a detailed error information by the package log indicated

<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>

which was my intial error. So Jamie, I think you are right that it has something to do with the DNS. Since the odbc drive is registered in the 32bit DNS, Server Agent is not able to see it.

If you can help me answer my trial error as to how a package could runs fine under intergeration services but breakes under CmdExec (does the command sting have to be different) and also if there is a way to get around this driver issue. Thanks.|||I tried a 32bit install of sql server on a 32bit machine and the job run just fine so the problem is that Sever Agent on 64bit machine can only see the 64x DNS and not 32 but in SSIS, the IDE give you the option to run in 64bit debugging mode or not, and when you choose not to run in 64 mode, it able to see the 32bit DNS. That was why it was able to run in SSIS just fine but not in Server Agent. In summary, make sure your driver has a 64bit version before developing packages to be run in Server Agent on a 64bit machine.|||I am having the same problem. It looks like when I run the SSIS package through the SQL Server Management Studio or through Visual Studio, the package honors the 64 bit runtime option. But, when I run it through the SQL Server Agent, it doesn't. I did some checking, and it looks like the package file doesn't even contain that flag. Is there some way to alter the command line in the job step to force the package to run in 32 bit mode?|||You could just use the CmdExec job step and specify the 32 bit dtexec program. This has other benefits, in that you have job step logging for CmdExec steps, unlike the SSIS subssytem. This can be very usefull in troubleshooting, e.g. Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)|||Thanks. That seems to be working.

Error: Server Agent cant find dts file

Im trying to schedule an integration sevice package with no anvile. I have tried both running it in Agent under Integration services and operation system (CmdExec) and still wont work. Under CmdExec, the command is /FILE "C:\Documents and Settings\ebuah\My Documents\Visual Studio 2005\Projects\Swamp_Solution\FIRSTTIME_DOWNLOAD\SSIS_ACT.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF. In the advance tab, I checked outfile to a log.txt file so as to read the log if there was any errors. The account running the job is the same account that sql server agent services is running which would only mean I really dont need any proxy account to run the job. When i run the job under CmdExec, i get the error

Message
Executed as user: CYPRESS\Administrator. The process could not be created for step 1 of job 0x529C255F20199B40A83858B1CCC875F2 (reason: The system cannot find the file specified). The step failed.

when I run the same package under integeration system, the error I get in the logg file is

<record>
<event>OnError</event>
<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>
<computer>SPIDERMAN</computer>
<operator>CYPRESS\Administrator</operator>
<source>ACT TABLE 250</source>
<sourceid>{99b92f4c-686d-47a6-b136-4d1ec698c837}</sourceid>
<executionid>{CD95BB1A-7FE4-468B-904D-C02B3FEBF1B0}</executionid>
<starttime>4/17/2006 1:20:47 PM</starttime>
<endtime>4/17/2006 1:20:47 PM</endtime>
<datacode>-1073450910</datacode>
<databytes>0x</databytes>
</record>

If you ask me, I dont know whats going on but I am very fustrated. Have been working on this for 4 days now. I really really need help.

The error is right there. "Data source name not found".

I would guess that either you haven't deployed the ODBC DSN on that machine or the account you are running it as doesn't have permission to see it.

-Jamie

|||I am running on a 64x machine on which i installed the 64x version of SSIS. The odbc driver in installed in the wow folder (32 bit). My packages are set up to execute in 32bit so they can see the odbc driver. Do you think that under server agent, its not able to see the 32bit odbc driver. (by the way, the account i running under is an admin account, has rights to everything)

Also, in the process of debugging, I tried a few things to eliminate causing senarios. I created a simple parkage that doesnt use the odbc drive. It does a staight pull from one sql server table to another using ole connection . When i run that under Sever Agent with Integration services, it runs smoothly. For the same simple package, when i run under CmdExec, I get the error: The process could not be created for step 1 of job 0xF791BD51E4709E48AD83FB56038ECF95 (reason: The system cannot find the file specified). The step failed. So, my question is what is wrong. It runs fine under integeration services but errors out under CmdExec.

From my first test, I made an assumption that only running under intergration service in Sever Agent will work since its passed the test. Based on that, I changed my simple package to include an odbc source connection (pulling from a remote DB/4 FC server with 32bit DB/4 odbc driver installed in wow on 64bit). When I run the package in Agent under integration services, I got an error: The package execution failed. The step failed. Luckly, a detailed error information by the package log indicated

<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>

which was my intial error. So Jamie, I think you are right that it has something to do with the DNS. Since the odbc drive is registered in the 32bit DNS, Server Agent is not able to see it.

If you can help me answer my trial error as to how a package could runs fine under intergeration services but breakes under CmdExec (does the command sting have to be different) and also if there is a way to get around this driver issue. Thanks.|||I tried a 32bit install of sql server on a 32bit machine and the job run just fine so the problem is that Sever Agent on 64bit machine can only see the 64x DNS and not 32 but in SSIS, the IDE give you the option to run in 64bit debugging mode or not, and when you choose not to run in 64 mode, it able to see the 32bit DNS. That was why it was able to run in SSIS just fine but not in Server Agent. In summary, make sure your driver has a 64bit version before developing packages to be run in Server Agent on a 64bit machine.|||I am having the same problem. It looks like when I run the SSIS package through the SQL Server Management Studio or through Visual Studio, the package honors the 64 bit runtime option. But, when I run it through the SQL Server Agent, it doesn't. I did some checking, and it looks like the package file doesn't even contain that flag. Is there some way to alter the command line in the job step to force the package to run in 32 bit mode?|||You could just use the CmdExec job step and specify the 32 bit dtexec program. This has other benefits, in that you have job step logging for CmdExec steps, unlike the SSIS subssytem. This can be very usefull in troubleshooting, e.g. Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)|||Thanks. That seems to be working.