maintenance plan

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

maintanence plan fails - Adding job step

I am trying to create a new maint plan on a dev server and it fails to create
it. It is failing when attempting to add the job steps to the agent. I have
performed this on two other dev server without any issues. I verified all
the settings, configurations, protocols, proxies, and logins are configured
the same on all servers.
The other servers taht this is working on do not have any special proxy
settings and I tried to create a new connection and used the "local
connection" settings. None will work.
Here is the tech details from the error it is generating. Any help will be
appreciated.
===================================
Create maintenance plan failed.
===================================
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
Program Location:
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on localConnObj, String proxyName)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
subplanName, String proxyAccount, Package package, ServerConnection
localConnObj)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String subplanName, String proxyAccount)
at
Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection createQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
===================================
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
For help, click:
http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
Server Name: TST-TFS2005\TSTTFS2005,1450
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 28
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
Stan
This error generally means that SSIS is not installed on the server. This is
required for the new style maintenance plans in SQL2005. Can you confirm
whether Integration Services is installed on the server you are seeing the
error on.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Stan" <Stan@.discussions.microsoft.com> wrote in message
news:696C7781-51A5-4B4E-9595-9AA307ECA806@.microsoft.com...
>I am trying to create a new maint plan on a dev server and it fails to
>create
> it. It is failing when attempting to add the job steps to the agent. I
> have
> performed this on two other dev server without any issues. I verified all
> the settings, configurations, protocols, proxies, and logins are
> configured
> the same on all servers.
> The other servers taht this is working on do not have any special proxy
> settings and I tried to create a new connection and used the "local
> connection" settings. None will work.
> Here is the tech details from the error it is generating. Any help will
> be
> appreciated.
>
> ===================================
> Create maintenance plan failed.
> ===================================
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on
> localConnObj, String proxyName)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
> subplanName, String proxyAccount, Package package, ServerConnection
> localConnObj)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String
> subplanName, String proxyAccount)
> at
> Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
> ===================================
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection
> sqlCommands, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection
> queries)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection
> queries, Boolean includeDbContext)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection
> createQuery, ScriptingOptions so)
> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
> ===================================
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
> --
> Server Name: TST-TFS2005\TSTTFS2005,1450
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 28
>
> --
> Program Location:
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
> methodName, Boolean async)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
>
> --
> Stan

Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

Mailing dba when Sql Server Agent Fails

Hello

I would like to have a script which mails the dba mail box when the sql server agent stops running. I am using the SMTP server for mailing.

I am using the query :
EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

to check the status.

I use the below for mailing :

EXEC master.dbo.xp_smtp_sendmail

@.FROM = N'testsql2000@.is.depaul.edu',

@.TO = N'dvaddi@.depaul.edu',

@.server = N'smtp.depaul.edu',

@.subject = N'Status of sqlserver!',

@.type = N'text/html',

@.message = @.message

How do I change it or write a script get a mail when the sql server agent is not running.

Thanks

You would need a script which checks the Agent, becasue there is no shutdown procedure which can be executed if the service becomes unavailable. The Script could either be executed from another Agent or from any scheduled component like AT / Winat of Windows.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I know that , if there are 2 servers first server can monitor the 2nd server agent and vice-versa.

But I am not sure how to work on it or what script to run.

So it would be of great help if anyone can let me know.

Thanks

Mailing DBA when sql server Agent Fails

Hello

I would like to know a script which mails the DBA mail box when the sql server agent fails ( I am working on sql server 2000) using SMTP. I have got SMTP server configured for all my other job failure notifications.

Please let me know any scripts that can be run on the command prompt or any ideas of how I can do it.



ThanksWhen the Agent service fails, or just when a job fails?|||When the Agent Service Fails.

Thanks|||Since the agent executes the scripts, how do you expect it to execute a script to notify you that it is no longer executing scripts?

Two alternatives:
1) Set up an agent on another server to check the functioning of the first one.
2) Set a script to run when the service is restarted (note that this will NOT notify if the server crashes and does not automatically restart).|||I use a configurable WMI script to check the status of selected services on selected servers. I check for any that are set to auto start and are not in a running state (or status, can't remember which).

Function CheckServices ( sComputer, sFQDN )

Dim oConnMail
Dim oCommMail

Dim oWMIService
Dim oItems
Dim oItem

Dim sDisplayName
Dim sSubject
Dim sMessage

Set oWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security )}!\\" & sFQDN & "\root\cimv2")
Set oItems = oWMIService.ExecQuery("SELECT * FROM Win32_Service",,48)

' Define objects for mail message
Set oConnMail = CreateObject("ADODB.Connection")
Set oCommMail = CreateObject("ADODB.Command")

' Open connection to mail database
oConnMail.ConnectionString = Replace(sConnectionString, sDatabase, "master")
oConnMail.Open

' Open connection to catalog database
oConn.ConnectionString = sConnectionString
oConn.Open

If Err.number = 0 Then
For Each oItem in oItems
If IsNull(oItem.DisplayName) Then
sDisplayName = oItem.Name
Else
sDisplayName = oItem.DisplayName
End If

' This part of the routine verifies that
' services that are set to Auto start are still running;
' if not running, then an alert is e-mailed
If oItem.StartMode = "Auto" and oItem.State <> "Running" Then

oCommMail.CommandText = "spSendMail"
oCommMail.CommandType = 4
oCommMail.ActiveConnection = oConnMail

sSubject = "Service " & oItem.Name & " stopped running on " & oItem.SystemName
sMessage = "Service " & sDisplayName & " stopped running on " & oItem.SystemName & VbCrLf & _
"Service Specific Exit Code: " & oItem.ServiceSpecificExitCode & vbCrLf & _
"Exit Code: " & oItem.ExitCode & vbCrLf & _
"Status: " & oItem.Status & vbCrLf & _
"State: " & oItem.State
' sMessage = "Test"
oCommMail.Parameters.Refresh
oCommMail.Parameters("@.Subject") = sSubject
oCommMail.Parameters("@.Message") = sMessage
oCommMail.Parameters("@.Recipient") = sRecipient

oCommMail.Execute

End If
Next

' Close mail database objects
Set oCommMail = Nothing
oConnMail.Close
Set oConnMail = Nothing
Else
Call AddLogEntry(sFQDN, "Error", "ServicesCheck", Err.number & " - " & Err.Source & " - " & Err.Description)
Err.Clear
End If

oConn.Close

End Function|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks|||Hello

Thanks for your script.

How do I go on to execute the script. And do I need to download any WMI .

Thanks

I have a management server which is a dumping ground for a lot of different monitoring tools (some COTS, some custom). This script is scheduled from this server using Windows Scheduled Tasks. I pass in selected parameters to the script from the command line.

WMI is native to Windows 2000/Server 2003; you shouldn't need to load anything special there. You may need to adjust your permissions on the target server to be able to read from the WMI repository (I think you need to be at least a Power User, but I could be wrong.

Note that the script provided is a function. It would run inside of a main script that would:

1. Pull in a list of servers to poll
2. Pull in any system parameters (such as the e-mail address(es) you want the alert to go to
3. Call the function (passing in the name of the computer)

Regards,

hmscott

Mail Profile disabled in SQL Server Agent Properties

It's a Windows 2000 Server box with SQL 2000 on it. MSSQLSERVER &
SQLSERVERAGENT services are logged in as valid domain user. I set up a
mail profile for that user, and can send email via Outlook 2003.
However, when I go to the SQL Agent Properties in Enterprise Manager,
Mail Profile is disabled.

I've restarted the services & even tried rebooting the server. This
works on my other servers, but they're Windows 2003. Not sure if
that's the problem though.

Anybody have any clues?<brianhensley@.yahoo.com> wrote in message
news:1114783987.666029.195920@.z14g2000cwz.googlegr oups.com...
> It's a Windows 2000 Server box with SQL 2000 on it. MSSQLSERVER &
> SQLSERVERAGENT services are logged in as valid domain user. I set up a
> mail profile for that user, and can send email via Outlook 2003.
> However, when I go to the SQL Agent Properties in Enterprise Manager,
> Mail Profile is disabled.
> I've restarted the services & even tried rebooting the server. This
> works on my other servers, but they're Windows 2003. Not sure if
> that's the problem though.
> Anybody have any clues?

See Q10 and Q19 (and also the article mentioned in the Summary section):

http://support.microsoft.com/defaul...kb;en-us;315886

Simon

mail profile

Hello,
I setup a default mail profile. However, on the same
server, in the SQL Server Agent property window, the mail
profile does not show any profile at all. If I manually
type in the default profile, it generates error message
stating that either there is no default mail client. Thank
you for your help.
JeffWhen you create the mail profile you need to be logged in as the same =account that SQL Agent is configured to use. Also start outlook using =the profile to ensure that any configuration prompts are sorted before =Agent tries to use the profile.
Mike John
"Jeff" <zli@.pattonboggs.com> wrote in message =news:4b4501c37642$a62a3950$a501280a@.phx.gbl...
> Hello, > > I setup a default mail profile. However, on the same > server, in the SQL Server Agent property window, the mail > profile does not show any profile at all. If I manually > type in the default profile, it generates error message > stating that either there is no default mail client. Thank > you for your help.
> > Jeff|||Mike,
I used the same login account as both SQLServer and
SQLServerAgent accounts, and it is also same as mailbox
account. And there is only one mail profile for my MS
outlook which is default. I did same configuration to one
of my test server and it works ok.
>--Original Message--
>When you create the mail profile you need to be logged in
as the same account that SQL Agent is configured to use.
Also start outlook using the profile to ensure that any
configuration prompts are sorted before Agent tries to use
the profile.
>Mike John
>"Jeff" <zli@.pattonboggs.com> wrote in message
news:4b4501c37642$a62a3950$a501280a@.phx.gbl...
>> Hello,
>> I setup a default mail profile. However, on the same
>> server, in the SQL Server Agent property window, the
mail
>> profile does not show any profile at all. If I manually
>> type in the default profile, it generates error message
>> stating that either there is no default mail client.
Thank
>> you for your help.
>> Jeff
>.
>|||Are SQL Server and Agent using the *same* account? I've seen this (no drop-down box) when you have
different accounts for the services.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jeff" <zli@.pattonboggs.com> wrote in message news:4c2b01c3764d$2e6b01f0$a501280a@.phx.gbl...
> Mike,
> I used the same login account as both SQLServer and
> SQLServerAgent accounts, and it is also same as mailbox
> account. And there is only one mail profile for my MS
> outlook which is default. I did same configuration to one
> of my test server and it works ok.
> >--Original Message--
> >When you create the mail profile you need to be logged in
> as the same account that SQL Agent is configured to use.
> Also start outlook using the profile to ensure that any
> configuration prompts are sorted before Agent tries to use
> the profile.
> >
> >Mike John
> >
> >"Jeff" <zli@.pattonboggs.com> wrote in message
> news:4b4501c37642$a62a3950$a501280a@.phx.gbl...
> >> Hello,
> >>
> >> I setup a default mail profile. However, on the same
> >> server, in the SQL Server Agent property window, the
> mail
> >> profile does not show any profile at all. If I manually
> >> type in the default profile, it generates error message
> >> stating that either there is no default mail client.
> Thank
> >> you for your help.
> >>
> >> Jeff
> >
> >.
> >|||Also seen this but memory failing me on the reason - does it not also =happen if you have omitted to fire up outlook using the profile?
Mike John
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:eYBRj2pdDHA.1532@.TK2MSFTNGP10.phx.gbl...
> Are SQL Server and Agent using the *same* account? I've seen this (no =drop-down box) when you have
> different accounts for the services.
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
> > > "Jeff" <zli@.pattonboggs.com> wrote in message =news:4c2b01c3764d$2e6b01f0$a501280a@.phx.gbl...
> > Mike,
> >
> > I used the same login account as both SQLServer and
> > SQLServerAgent accounts, and it is also same as mailbox
> > account. And there is only one mail profile for my MS
> > outlook which is default. I did same configuration to one
> > of my test server and it works ok.
> >
> > >--Original Message--
> > >When you create the mail profile you need to be logged in
> > as the same account that SQL Agent is configured to use.
> > Also start outlook using the profile to ensure that any
> > configuration prompts are sorted before Agent tries to use
> > the profile.
> > >
> > >Mike John
> > >
> > >"Jeff" <zli@.pattonboggs.com> wrote in message
> > news:4b4501c37642$a62a3950$a501280a@.phx.gbl...
> > >> Hello,
> > >>
> > >> I setup a default mail profile. However, on the same
> > >> server, in the SQL Server Agent property window, the
> > mail
> > >> profile does not show any profile at all. If I manually
> > >> type in the default profile, it generates error message
> > >> stating that either there is no default mail client.
> > Thank
> > >> you for your help.
> > >>
> > >> Jeff
> > >
> > >.
> > >
> >=20