We help IT Professionals succeed at work.

SQL Server Maintenance Plans

1,628 Views
Last Modified: 2012-05-11
We have an SQL Server Maintenance plan that is set up to just run a full back up of all of our Databases and drop them in sub folders on one of the local disks. At the moment none of these run (and as far as I can see they never have!)

When I select the option to execute them outwith the scheduled times the Execute Maintenance Plan window opens and it sits and apparently does nothing. I have received no errors and there doesn't appear to be any log of something going wrong.

Help appreciated.
Comment
Watch Question

You have to enable sql agent to run scheduled task. make sure that sql agent is running.

As you are saying that if you try to execute it manually even then it didn't did any thing. it mean that sql data backup plan is not created properly.

see this link to create backup plan

http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

Author

Commented:
The SQL Agent is up running as a local user SQLBackup and this user is a member of the SQLServerSQLAgentUsers group - does this group not give the user the required privileges to undertake the SQL Agent tasks?

As for creating the plan, I used the wizard and it appears okay!
In Sql server agent when you see job history for this particular plan what it is showing?

Author

Commented:
The Activity Monitor claims never to have run the plans.
not in activity monitor.
If you open Sql Server Agent, Inside you will see Jobs Tab. In jobs tab you will find job that was created by Maintenance plan. If you right click on that job and clik on View history. What it is showing?

Author

Commented:
There is nothing there. However, in the Error Logs for Server Agent there is this:

05/19/2011 13:11:49,,Error,[382] Logon to server 'APPLE' failed (ConnLogJobHistory)
05/19/2011 13:11:49,,Error,[298] SQLServer Error: 5<c/> A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
05/19/2011 13:11:49,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/19/2011 13:11:49,,Error,[298] SQLServer Error: 5<c/> Named Pipes Provider: Could not open a connection to SQL Server [5]. [SQLSTATE 08001]
Can you create maintenance plan again? Because if you have created a maintenance plane for backing up database, then corresponding job should also get created in Jobs tab under SQL Server Agent.

Author

Commented:
Sorry, to clarify - the job is there but there is nothing in the log file summary.
now right clik on job and execute it. it will tell exact error message

Author

Commented:
It's now doing what it did before and sitting on Status "In Progress" The plan I'm running backs up a couple of very very small databases so it shouldn't take long at all.
wait for this to execute it may or may not execute. then you can see the log for job. if it is taking time to execute this then it means it is not able to make connection to database.  wait for some time to execute it

Author

Commented:
No error message has come up. It's just sitting saying in progress. Could the error message in the logs have anything to do with it? I don't know why it's trying to connect to APPLE is surely should be connecting to local no?
It should connect to local database only. I created database backup plan on my local system and it worked fine. i am still wonderring why it is not working on ur system.

Author

Commented:
I left the process running over night and got the following error:


===================================

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

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query, Object con)
   at Microsoft.SqlServer.Management.Smo.PostProcessJobActivity.GetColumnData(String name, Object data, DataProvider dp)
   at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess()
   at Microsoft.SqlServer.Management.Smo.DataProvider.GetTable()
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(StringCollection query, Object con, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName)
   at Microsoft.SqlServer.Management.Smo.Agent.Job.get_LastRunDate()
   at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.WaitForJobToFinishAction.DoAction(ProgressItemCollection actions, Int32 index)
   at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()

===================================

A severe error occurred on the current command.  The results, if any, should be discarded. (.Net SqlClient Data Provider)

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

------------------------------
Server Name: (local)
Error Number: 0
Severity: 11
State: 0


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
are u able to take database backup by right clicking on database?

Author

Commented:
Yes - this executes perfectly.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That lets me run it if I press execute under jobs but the scheduled task doesnt run and I get the Logon to Server Local error again in the Server Agent logs. Could this be because server agent runs as a domain user (eusasys1@domain.com) I have added domain\eusasys1 to the SQL server logins but it wont let me add eusasys1@domain.com as it's not a valid NT account name or something.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Victory! I got the SQL Agent to run as Domain\eusasys1 and this appears to allow the Job Scripts you suggested run!

Thanks buddy!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.