Tag Info

Hot answers tagged

12

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


7

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


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


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

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


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

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


6

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


6

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


5

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.


5

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


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

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


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

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

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


4

You need access to SQL Server Agent in order to do what you need to do. This is done through msdb permissions: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole. You can create a job to adhere to your schedule, and then the job will execute whatever task it is that you need done. As for the performance impact on the server, SQL Server Agent ...


4

Could be nothing important according to KB 811484 However, I'd schedule a chkdsk on that drive on next boot, and makre sure all my backups are in order.


4

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


4

Yes, you will need to create the jobs on any other replicas that you would want those specific jobs to run if they were the primary replica. You will need to create your own logic for if/when each SQL Server Agent job will run. For instance, do you want to run a job only if the current instance is the primary replica of a particular Availability Group? ...


4

Personally I think the biggest caveat would be the difficulty in keeping the list of jobs organized. As far as I'm aware you can't create folders to organize the jobs, so a large number would be cumbersome. I'm not 100% sure of this, though, since none of my servers has more than a dozen or so jobs. Server 2008 and later's Task Scheduler allows for much ...


4

I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null $SqlServerNames = "Server1", "Server2" $SqlJobName = "YourJob" foreach ($SqlServerName in $SqlServerNames) { $SqlServer = New-Object ...


4

I don't think there's a built-in way to do this, but here are a few alternatives: Use OPENDATASOURCE like this: EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=master;Application Name="test app"') .master.sys.sp_configure; Note that using OPENDATASOURCE requires you to turn on the Ad hoc Distributed ...


4

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


4

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


4

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


3

SQL Server Agent is only available with certain versions of SQL Server. For instance, it is not available at all with SQL Server Express. If you have SQL Server Standard or Enterprise it will be available in SQL Server Management Studio (SSMS) at the bottom of the listing of server items.



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