Tag Info

Hot answers tagged

15

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group: if (select ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id where ag.name = ...


10

Short Answer It looks like the data in msdb.dbo.sysjobschedules is updated by a background thread in SQL Agent, identified as SQLAgent - Schedule Saver, every 20 minutes (or less frequently, if xp_sqlagent_notify has not been called and no jobs have run in the meantime). For more accurate information, look at next_scheduled_run_date in ...


10

Use the better version of the undocumented sp_MSForEachDB to iterate through the databases in a SQL Server Agent job. Or simply use a WHILE loop yourself on sys.database entries if you have a naming pattern.


7

Disclaimer: I work for SQL Sentry. Our SQL Sentry Event Manager product has a facility built exactly for this: to chain jobs and arrange them in various workflow orders. I started using SQL Sentry years ago, before I ever joined the company, to do exactly this. What I wanted was a way to start a restore job on our test server immediately after the backup ...


7

If you are not granted the needed permissions, you will not be able to see the Agent, no matter whether you run Enterprise/Standard/Datacenter, etc... The roles needed are sysadmin, or the individual roles here - http://msdn.microsoft.com/en-us/library/ms188283.aspx


7

To find the first and last days of the month: DECLARE @MonthStart DATETIME DECLARE @Date DATETIME DECLARE @next DATETIME -- create the required date. because today is the 21st i subtracted month. -- since you're running the job on the first day of the new month, -- you may want to change MONTH to DAY. however, if the job fails that change -- that may ...


7

You don't want to use BETWEEN to satisfy this query for the reasons I outline in this blog post: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx Instead you want an open-ended date range. Begins on the first of the month in question, and is less than the next month. To determine the first of the ...


7

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use. Your code would ...


6

For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach ...


6

Did some googling around and here are some software that may interest you: SQL Job Vis (this seems the most popular, according to some forum and blog posts) http://www.sqlsoft.co.uk/sqljobvis.php SQL Job Manager http://www.idera.com/Free-Tools/SQL-job-manager/ Query Currently Running SQL Server Agent Jobs ...


6

The way I've done this in the past is added a final step to the job, then I can use Database Mail to customize everything about the message (subject, body, recipients, from, etc) based on the server, the status/outcome of the job, and even the duration. It is cumbersome if you have a lot of jobs, but I did this for a variety of other reasons as well (for ...


6

Sounds like you've got some sort of corruption problem on the disk which the SQL Agent logs get written to. I'd check the disk for corruption, as well as the C drive in case it's talking about the application log which the SQL Agent also writes to.


6

At my previous job I did exactly this, mostly because the jobs were all run from our central, primary cluster, which was the most visible server. I could see all of our scheduled tasks in one place instead of having to go check command line stuff on a bunch of servers. While this is largely subjective (and it's going to be hard to derive a "correct" answer ...


6

There is already an existing answer posted by Aaron Bertrand for SQL Server Express 2012. First install SQL Server 2014 Express with Advanced Services as you did. Then read Aaron's instructions at: http://stackoverflow.com/questions/10407337/express-with-advanced-services-cant-create-full-text-index The short version is that the user interface does not ...


6

In the job step advanced properties, send output to a flat file by inputing the "Output file" location and filename. That should include all of your output for later viewing. Or, if you want all of the output directly in the history of the job, you can select the check box "Include step output in history". It'll use history entry rollover to include all ...


6

Is there any way to extract the full text of these messages? You can achieve it in 2 ways - Go in job step and select the Advanced tab: a. Output to a file (<== My preferred method) b. "Log to table" and "Include step output in history" (<== You need to trim msdb..sysjobhistory in long-run as the messages are stored as nvarchar(max) instead of ...


5

You will have to attach the MSDB DB under a different name like 'jobsdb' and pull the jobs out of jobsdb. The jobs will be stored under jobsdb.dbo.sysjobs and the job steps will be under jobsdb.dbo.sysjobsteps. They can be joined by the job_id column. The schedules can be found under jobsdb.dbo.sysjobschedules joined with jobsdb.dbo.sysschedules on ...


5

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance: exec msdb.dbo.sp_update_operator @name = 'OldOperatorName', @new_name = 'NewOperatorName'; go As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, ...


5

If you have Management Studio Express installed (any version prior to SQL Server 2012 SP1), it simply doesn't contain any facility for SQL Server Agent (no matter which edition you're trying to manage). You can fix this by getting the very latest version from here: http://www.microsoft.com/en-us/download/details.aspx?id=43351 You want the file ...


5

You can't, as found under the 'Management Tools' section here: http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.100%29.aspx If this is just for your development environment, you can pick up a copy of Developer Edition for about $50 US. When you release, you will need a copy of Standard Edition or higher to be able to use the job agent.


5

from BOL : To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.


5

Or you can have one job that runs every 5 minutes with 2 steps: Step #1 -- checks the CPU Usage (PowerShell), just google and there are plenty of scripts out there; If <20% exit with success go to Step 2, Else Exit with Error and finish the job (No Step #2) Step#2 -- is the actual UPDATE. Or as Ken suggested.


5

I am not sure how dirty you want to get on this one but the only method is going to be opening up SQL Server Data Tools and building (or modifying) an SSIS package. One would be to import the maintenance plan into SQL Server 2012 Data Tools and modify the SSIS package. The other would be to create it from scratch. You have the same options as the maintenance ...


5

Per BOL: Configuring Windows Service Accounts and Permissions SQL Server Agent Login and Privileges The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.


5

Further to Shawn's answer, the Books Online topic titled Select an Account for the SQL Server Agent Service also says: The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles: The account must be a member of the sysadmin fixed server role. To use multiserver job processing, the account must be a ...


5

You are using jobs incorrectly, the steps from job B should simply be in job A. Use stored procedures to group and reuse common code not jobs. However it is possible to do this, in which case this is a duplicate of Good way to call multiple SQL Server Agent jobs sequentially from one main job?


4

Expand SQL Agent Expand Proxies Find your proxy, right-click on it and choose Properties Go to the Principals tab Add the NT AUTHORITY\SYSTEM account This will give that account permission to use the proxy.


4

There are logs in a few places. For the Agent logs please see the screenshot: In Object Explorer expand SQL Server Agent \ Error Logs.


4

This is likely a permissions issue. Verify that your replication agent accounts have the necessary permissions listed in the section Permissions That Are Required by Agents in Replication Agent Security Model.


4

During the install process for SQL 2008 R2 it gives you the option to change the start-up type. I can see a slight desire to not have it start automatically depending on what you might be using it for. I have some setups that the user has some Agent jobs that clean up data or immediately start processing data. If I am troubleshooting, or in general the ...



Only top voted, non community-wiki answers of a minimum length are eligible