Hot answers tagged

20

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 = '...


14

The problem looks more complex than it is. Since you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012. The only thing that actually matters is: Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL. Description: Login failed for user '{domain user that runs SSIS ...


13

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 msdb.dbo....


12

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 http://sqlconcept.com/2011/06/25/how-to-query-...


12

I posted a query a while back for getting a list of currently running jobs here. SELECT ja.job_id, j.name AS job_name, ja.start_execution_date, ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id, Js.step_name FROM msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id ...


10

You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once. USE msdb; GO CREATE TABLE dbo.ReportServerJob_FailQueue ( job_id UNIQUEIDENTIFIER, run_date INT, run_time INT, -- horrible ...


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.


10

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant ...


9

That job has to do with policy-based management, not SQL Server Agent jobs in general. In Object Explorer right-click SQL Server Agent and select Properties, then move to the History tab. What does it say here: You can adjust those settings so that it keeps more rows overall, more rows per job, and keeps history longer. I am guessing either you have a lot ...


8

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 ...


8

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 ...


8

The @name parameter is a parameter of msdb.dbo.sp_add_category and not EXEC. See the sp_add_category documentation: [ @name = ] 'name' The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default. [Uncategorized (Local)] just indicates a default category when you didn't specify a ...


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

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, ...


7

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 ...


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 don't think you can work with that single job to get that exact schedule. You might want to consider having another job that disables/enables the job for the period of time that you don't want it to run. Another solution would be to add another conditional logic to your existing job and do a check on the current day of the week and what time it is, and ...


7

The job owner determines the execution context of the job's steps. For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job ...


7

Quite frankly, I would just not use the limited operators facility through the UI and instead: Create a step that only gets reached when the job succeeds, which e-mails person 1 Create another step that only gets reached when any step fails, which e-mails person 2 (You may need to modify multiple steps that could potentially fail, such that when they do ...


7

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 ...


7

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 ...


7

The way SQL Server Agent works is exactly how you want already - if you schedule the job for every 10 minutes, and at 11:00 it runs for 12 minutes, it won't run again until 11:20 (the 11:10 instance won't start, because an instance of the job was already running).


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

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

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

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet: Set-Location c: This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.


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

Because Microsoft hasn't learned to create unique object names. If you look at the definition for sp_delete_job, you will find this around line 115, a slightly different version of #temp_jobs_to_delete: CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED, job_is_cached INT NOT NULL) ...


6

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored. The least privilege you need to perform database maintenance is sysadmin. Lower privileged users could perform those activities too (I'm thinking of db_owners), but the least privilege needed to perform any activity on a SQL Server instance is sysadmin, ...



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