Sql server agent stops then stops

SQL Server Administration
1

I am trying to copy a db from the live server to my local box. When I try to, it says sql server agent is not running and that could cause it to not work. I have tried several times to start the agent, but the message says it started then stopped. How can I get it running? windows 10/ms sql 2016 developer edition

2

Look in the SQL Server Agent log file to get more specific messages about why it can't start.

3

Thank you.

2016-08-04 10:57:15 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2016-08-04 10:57:15 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)

How do I fix this?

4

The account that the SQL Agent is running under does not have permissions to those objects. Verity that the SQL login that corresponds to the SQL Agent account has all the necessary permissions it needs in SQL Server (e.g., you could give it full "db_owner" in msdb (or just give it "sysadmin", although that is not technically recommended)).

5

I have gotten past that error. The SQL Agent job starts, then I get an error on the last step.

TITLE: Copy Database Wizard

The job failed. Check the event log on the destination server for details.

I the log I see this:

2016-08-05 08:58:23.57 spid52 [INFO] HkHostDbCtxt::Initialize(): Database ID: [6] 'ReportServer$SQLEXPRESSTempDB'. XTP Engine version is 0.0.
2016-08-05 08:58:23.57 spid52 Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2016-08-05 08:58:23.58 spid52 [INFO] HkHostDbCtxt::Initialize(): Database ID: [6] 'ReportServer$SQLEXPRESSTempDB'. XTP Engine version is 0.0.
2016-08-05 08:58:23.59 spid52 [INFO] HkHostDbCtxt::Initialize(): Database ID: [6] 'ReportServer$SQLEXPRESSTempDB'. XTP Engine version is 0.0.
2016-08-05 08:59:30.85 spid42s [INFO] HkHostDbCtxt::Initialize(): Database ID: [6] 'ReportServer$SQLEXPRESSTempDB'. XTP Engine version is 0.0.
2016-08-05 08:59:30.85 spid42s Starting up database 'ReportServer$SQLEXPRESSTempDB'.
2016-08-05 08:59:30.86 spid42s [INFO] HkHostDbCtxt::Initialize(): Database ID: [6] 'ReportServer$SQLEXPRESSTempDB'. XTP Engine version is 0.0.
2016-08-05 08:59:30.87 spid42s [INFO] HkHostDbCtxt::Initialize(): Database ID: [6] 'ReportServer$SQLEXPRESSTempDB'. XTP Engine version is 0.0.
2016-08-05 09:03:06.19 spid57 Setting database option SINGLE_USER to ON for database 'project_orange'.

This is the last entry.

6

If you got an error in the job, you need to look at msdb.dbo.sysjobhistory for that specific job, rather than the SQL Server Agent log.

For example:
SELECT jh.*
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
WHERE j.name = 'put_your_job_name_here'
ORDER BY jh.run_date DESC, jh.run_time DESC

7

Executed as user: DESKTOP-LP70EJ2\SYSTEM. Started: 4:11:10 PM Finished: 4:12:06 PM Elapsed: 56.391 seconds. The package execution failed. The step failed.

8

If you need to manually restore an SQL Database in Management Studio you can follow the instructions below for the version of SQL Server running on your server.

SQL 2012:

  1. Log onto SQL Server Management Studio.
  2. Navigate to the database you wish to restore.
  3. Right Click > Tasks > Restore > Database.
  4. Once on the General tab set the source to Device > Select Backup Device > Add > Navigate to the .bak file you're restoring to.
  5. Ensure the Destination Database is the correct one.
  6. Select the Options tab.
  7. Check "Overwrite the existing database (WITH REPLACE)" and "Close existing connections to destination database".
  8. Uncheck "Take tail-log backup before restore".
  9. Click OK.
9

I would restore a FULL Backup to copy a DB from one server to another. Only downside with that (which might also exist with using the Copy Database Wizard??) is that the Restore will create a database of the same size as the original - so if the original has lots of spare space, or a massive LOG file, then you need enough space on the target machine to accommodate all of that "Bloat"! Once created the Target database can be shrunk to a suitable size.

Beware that Restore (including using SSMS) defaults to trying to restore a database to the same location as the Backup was made - so same drive, path, filenames. The physical media Drive-Name may not exist on the target machine!! and the path may be entirely inappropriate, so usually necessary to override those with more appropriate values.