Archive for the ‘SQL Server Agent Jobs’ Category

How to Search SQL Server Agent Jobs for a Specific Keyword?

Similar to being dropped off in the middle of a forest that you had never visited, you had been assigned to troubleshoot SQL Server Agent job failures on a new SQL Server environment that you had never worked on. You had asked for documentations and the server contacts. You have gotten laugther replies – the answer is no and the contacts are no longer with the employer. That shouldn’t be a surprise of the century for you. The requester acknowledges that while there are a high number of job failures throughout the network, the top priority is to find the job that execute the stored procedure that runs the “Flash Report” and it’s conveniently named “usp_RunDailyFlashReport” as the requester had learned from the former SQL Server DBAs. How can you find all the SQL Server Agent jobs that may run this stored proc in a New York minute or less? Let’s try the following script:

-- search the jobs for a specific text 
SELECT SERVERPROPERTY('SERVERNAME') as [InstanceName],
	j.job_id,
	j.name,
	js.step_id,
	js.command,
	j.enabled 
FROM	msdb.dbo.sysjobs j
JOIN	msdb.dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
WHERE	js.command LIKE '%KEYWORD%' -- replace keyword with the word or stored proc that you are searching for
GO

How to create individual job step output files as per the execution times and purge them on a scheduled time?

Pop Quiz again HotShot! There’s a critical SQL Server Agent job that was created N years ago by some very seniored SQL Server DBAs with collaborations with what some had claimed the smartest SQL Server Developers under the sky. The job has more than 23 steps and the last step is to notify the VIPs (Very Import People) in your company on the results. The job is scheduled to run daily and it fails on step 5 and step 13 intermittently for the past 6 months. Everyone was too busy to attempt to fix this job failure that’s very critical to company. You are the newly hired SQL Server DBA. On your first day on the new job, you have been delegated to work with the Development team resolve this job failure. You will need to enable the job output and make sure they do not get overwritten so that the Development team get to review the job output log files for troubleshooting. What do you do? What do you do?! Remember: there’s no crying for DBAs! Let’s attempt our usual troubleshooting methodology: 1.Fact finding. 2. Investigation and research. 3. Exploring and testing your options in a testing environment. 4. Rollout to production when ready. 5. Get a Grande Frappuccino from the nearest Starbucks.
1. Facts:
1) Steps 5 and 13 have been failing intermittently for the past 6 months.
2) Step 5 has not job output.
3) Step 13 has job output and on each execution the last job out will overwrite the previous one as per the default option. In case the latest execution is successful, it will overwrite the previous job failure output. Therefore, you will not be able to see the previous failure or any other failure before it.

2. After some research and investigation, you find that:
1) You will need to enable tokens in order to dynamically generate the output file name. See “Use Tokens in Job Steps” for more details: http://msdn.microsoft.com/en-us/library/ms175575.aspx
2) After you had enabled the token, you will need to add in the “Output file:” textbox the directory for the job output, the job step name, and the date and time variables to dynamically generate the output file. For example: C:\OutputFiles\Example_Step1_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)).log
3) To purge the old output file, you can use a Powershell script to delete the *.log files in the job output file rather than using the xp_delete_file system proc. I know I had blogged about it a few months back, but it has a mind of its own and it’s not worth to waste our time on it any further.

3. Test the solution based on our research and investigation in a testing environment.
1) Enable the token:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

2) Create a test job. For example: “Test Job Output.”
3) Add a job step to run some basic select statements. For example:

SELECT SERVERPROPERTY('SERVERNAME') [Instance_Name], GETDATE() [Time_Now]

4) Click on the “Advanced” page and add in the “Output file:” text box:

C:\OutputFiles\Example_Step1_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)).log

Where:
C:\OutputFiles\ is the existing directory for the output files.
Test_Step1 is the job step name.
_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)) is the date and time of the job execution.
5) After the test job is created in step 4), run it 3 times or more.
6) Browse to the job output folder, you will then see the job outputs for each of the executions you had performed in step 5).
JobOutputFiles
7) To purge the output files older than 7 days or set your retention days, create a purge job and embed the following Powershell script. Set the retention day as the value for $Days and in the job step type, you will need to select “PowerShell”:

#----- define parameters -----#
#----- get current date ----#
$Now = Get-Date 
#----- define amount of days ----#
$Days = "7"
#----- define folder where files are located ----#
$TargetFolder = "C:\OutputFiles\"
#----- define extension ----#
$Extension = "*.log"
#----- define LastWriteTime parameter based on $Days ---#
$LastWrite = $Now.AddDays(-$Days)

#----- get files based on lastwrite filter and specified folder ---#
$Files = Get-Childitem $TargetFolder -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"}

foreach ($File in $Files) 
    {
    if ($File -ne $NULL)
        {
        Remove-Item $File.FullName | out-null
        }
    }

PowerShell_Script_To_Purge_JobOutput_Files

4. Rollout to production when ready.
You may need to submit a Change Request ticket, get the approval, or do some demonstrations to show and proof that it works before you can rollout to the production. If you encounter bureaucratic roadblocks, you are on your own. Watch some “Meet the Press” or “Face the Nation” on Sunday mornings to get some exposures on how to handle them diplomatically. ESPN SportCenter can’t help you on this subject matter.

5. Get a Grande Frappuccino from the nearest Starbucks and enjoy!

How to query for the most recent job failures for all the SQL Server Agent jobs?

POP QUIZ again HotShot: You need to query the most recent job failures for all the SQL Server Agent jobs so that you can send them over to the development team for further review and analysis. Sure, you can go to each of the jobs in the “Job Activity Monitor” and then sort the jobs by failure and then right-click each one to view their job steps and failures and then copy and paste each one of them into WORD or EXCEL. What if you have more than 100 instances and each instance contain 10 or more job failures and each of the jobs has 10 or more job steps? This will certainly take you close to or more than a week of manual processing. As much as you enjoy your work and capturing the job failures manually, you do need to leave the office and bring milk home before 8:00 PM. What do you do? What do you do? Don’t panic. You can use the script in this blog to save time and the trouble of manual processing.

Critical notes before you hit the execution button:
1. The script utilizes the system tables: msdb..sysjobhistory and msdb..sysjobs.
2. By default, the run_date column is formatted in YYYYMMDD, the run_time column is formatted in HHMMSS without the colons (:), and the run_duration is also formatted in HHMMSS without the colons (:). The script has parsed them and formatted them in YYYY-MM-DD HH:MM:SS.SSS for the run_data and run_time and HH:MM:SS for the run_duation.
3. Enjoy and send me your customized version when you get a chance.
Here’s the script:

-- Script: Query the most recent job failures for all jobs that had failed the latest execution 
-- Developer: JP Chen
-- Date Created: November 05, 2013
SELECT SERVERPROPERTY('SERVERNAME') AS InstanceName, job_name, step_id, step_name, message, run_datetime, run_duration
FROM
(
    SELECT job_name, step_id, step_name, message, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    FROM
    (
        SELECT DISTINCT
            j.name AS job_name, 
            h.step_id,
            h.step_name,
            h.message, 
            (CONVERT(DATETIME, RTRIM(h.run_date)) +  
                (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4) AS run_datetime,
            RIGHT('000000' + CONVERT(VARCHAR(6), h.run_duration), 6) AS run_duration 
        FROM msdb..sysjobhistory h
        inner join msdb..sysjobs j
        ON h.job_id = j.job_id
        join (SELECT j.name, h.step_id, MAX(h.run_date) AS run_date, MAX(h.run_time) AS run_time
				FROM msdb..sysjobhistory AS h JOIN msdb..sysjobs AS j
				ON h.job_id = j.job_id
				GROUP BY j.name, h.step_id) f 
		ON f.name = j.name and f.step_id = h.step_id and f.run_date = h.run_date and f.run_time = h.run_time
        WHERE h.run_status = 0 -- failed jobs        
    ) t 
) t
ORDER BY job_name ASC

REFERENCES:
SQL Server Script to Display Job History
http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx

Sysjobhistory
http://technet.microsoft.com/en-us/library/ms174997(v=sql.105).aspx

How to grant view only permission to users to see SQL Server Agent jobs and read only access in all user databases?

As per industry best practices, your SQL Servers are divided into separate environments: Development, Staging, and Production. Sure, there can be more layers such as Quality Assurance (QA), User Acceptance and Testing (UAT), Pre-Production (Pre-Prod), and etc. As per the best security practice, only the Production SQL Server DBA team will have full access (sys admin access) to the production servers. Through a change request process, the Development team has submitted a request to view the SQL Server Agents jobs and have read access to all tables in all user databases. The request has been approved by management. Now, it’s your turn to grant the least privileges required for the Development team to view and SQL Server Agent jobs and all user databases objects. How can you do so without over granting access privileges? You can do so by granting the login(s) to the SQLAgentReaderRole database role in the msdb and the db_datareader database role in all databases.

There are 3 SQL Server Agent fixed database roles in SQL Server. They are:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

The SQLAgentUserRole allows users to create jobs and manage the jobs that they create. When the users are granted to SQLAgentReaderRole, they will be able to see the jobs, job histories, even job that the users do not own. However, the users belonging to the SQLAgentReaderRole only cannot execute the jobs. The SQLAgentOperatorRole allows users all the rights to the SQLAgentReaderRole plus permissions to execute the jobs that they do not own. So, if you wish to grant the user(s) permission to view the jobs only in the production server, you will grant the user(s) to the SQLAgentReaderRole in the msdb.

The db_datareader database role in each database enables the members in the role to run SELECT statement on any tables or view in the database.  Just select or view only, nothing more.

You may also consider granting the development team the VIEW SERVER STATE permission in the master database so that they can see server level configuration information and also the VIEW DEFINITION on the user databases so that they can see the TSQL code and the metadata for the objects in each user databases.

We will walk-through the following steps:

  1. Create a test login. In this case: BillyBob.
  2. Log in as BillyBob and try to view the jobs and job histories. It will fail as BillyBob has not been added to the SQLAgentReaderRole. BillyBob can’t even see the SQL Server Agent.
  3. Log in as a sys admin, add BillyBob to the SQLAgentReaderRole.
  4. Log in as BillyBob and try to view the jobs and job histories. It will succeed this time as BillyBob has been added to the SQLAgentReaderRole. BillyBob can only view the jobs and job histories but can’t delete or run any jobs.
  5. Log in as a sys admin, create the user BillyBob in all databases in the instance and then add BillyBob to the db_datareader database role in all user databases. BillyBob will then be able to select data on the user databases.
  6. Log in as a sys admin, grant BillyBob the VIEW SERVER STATE permission in the master database so that BillyBob can see server level configuration information.
  7. Log in as a sys admin, grant BillyBob the VIEW DEFINITION privilege on all user databases for BillyBob to see the code and metadata for all objects in each user databases.

Implementation Steps:

  1. Create a test login. In this case: BillyBob.
    -- create the test login
    CREATE LOGIN BillyBob WITH PASSWORD = 'L0n3Ran8er'
    
  2. Log in as BillyBob and try to view the jobs and job histories. It will fail as BillyBob has not been added to the SQLAgentReaderRole. BillyBob can’t even see the SQL Server Agent.
  3. Log in as a sys admin, add BillyBob to the SQLAgentReaderRole.
    -- add BillyBob to the msdb database
    USE msdb
    GO
    CREATE USER BillyBob FOR LOGIN BillyBob
    
    -- add BillyBob to the SQLAgentReaderRole
    use msdb
    EXECUTE sp_addrolemember
    @rolename = 'SQLAgentReaderRole',
    @membername = 'BillyBob'
    
  4. Log in as BillyBob and try to view the jobs and job histories. It will succeed this time as BillyBob has been added to the SQLAgentReaderRole. BillyBob can only view the jobs and job histories but can’t delete or run any jobs.
  5. Log in as a sys admin, create the user BillyBob in all databases in the instance and then add BillyBob to the db_datareader database role in all user databases. BillyBob will then be able to select data on the user databases.
    -- create the user BillyBob in all databases in the instance
    EXECUTE master.sys.sp_MSforeachdb 
    'USE [?];
    CREATE USER BillyBob FOR LOGIN BillyBob
    '
    
    -- add the user BillyBob to the db_datareader database role in all user databases
     EXECUTE master.sys.sp_MSforeachdb 
    'IF ''?'' NOT IN (''tempDB'',''model'')
    BEGIN 
    USE [?]
    EXEC sp_addrolemember ''db_datareader'', ''BillyBob''
    END
    '
    
  6. Log in as a sys admin, grant BillyBob the VIEW SERVER STATE permission in the master database so that BillyBob can see server level configuration information.
    -- grant BillyBob the VIEW SERVER STATE permission in the master database
    USE master
    GO
    GRANT VIEW SERVER STATE to BillyBob
    
  7. Log in as a sys admin, grant BillyBob the VIEW DEFINITION privilege on all user databases for BillyBob to see the code and metadata for all objects in each user databases.
    -- grant BillyBob the VIEW DEFINITION to all user database
     EXECUTE master.sys.sp_MSforeachdb 
    'IF ''?'' NOT IN (''master'',''tempDB'',''model'',''msdb'')
    BEGIN 
    USE [?]
    GRANT VIEW DEFINITION TO BillyBob
    END
    '
    

BillyBob will now have the required privileges to view the SQL Server Agent jobs, select data from all tables and views from all user databases, and view the code and metadata in all user databases.

REFERENCES:

SQL Server Agent Fixed Database Roles
http://technet.microsoft.com/en-us/library/ms188283.aspx

VIEW DEFINITION Permission
http://technet.microsoft.com/en-us/library/ms175808(v=sql.105).aspx

CREATE LOGIN
http://technet.microsoft.com/en-us/library/ms189751.aspx

CREATE USER
http://technet.microsoft.com/en-us/library/ms173463.aspx

sp_addrolemember
http://technet.microsoft.com/en-us/library/ms187750.aspx

db_datareader
http://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

 

How to change the job owner for the jobs created by the SQL Server maintenance plans?

Last week, the SQL Server DBA for the Finance and Accounting department has left the company for a new opportunity. You were assigned to take on the extra responsibilities of supporting the SQL Servers for the Finance and Accounting department, until the replacement is hired. The Windows Administration team has received the request to disable the previous SQL Server DBA’s domain account today and had successfully done so in the past hour. You started receiving email alerts that the transaction log backup jobs are failing. You reviewed the jobs that had failed and all of them had returned the following error:

The job failed. Unable to determine if the owner (<Domain\Account Name>) of job <job name> has server access (reason: Could not obtain information about Windows NT group/user ‘<Domain\Account Name>’. [SQLSTATE 42000] (Error #####)).

The <Domain\Account Name> is the domain account that belongs to the previous SQL Server DBA. What can you do to resolve this problem quickly?

You tried to change the job owner to the ‘sa’ account and also tried changing it to the SQL Server Agent account. No avail. The job owner reverts back to the original job owner. You have plans to replace all the maintenance plan jobs that were created using the GUI to your customized stored procedure to backup, perform index maintenance, and update statistics. But, water that’s far away can’t help put out the fire that’s burning your pants and socks off right now. What can you do?

You know that all maintenance plan jobs are created as packages. DTS packages in SQL Server 2000. SSIS packages in SQL Server 2005, 2008, and 2008 R2. You can view all the DTS or SSIS packages in the system tables for the packages. To change the job owner, you must change the package owner and the job owner will be updated to the package owner. Their corresponding system table names are as follows:

  • msdb.dbo.sysssispackages for SQL Server 2008 and R2
  • msdb.dbo.sysdtspackages90 for SQL Server 2005
  • msdb.dbo.sysdtspackages for SQL Server 2000

To view the package(s) owner(s):

-- Querying the package names
-- Make a not for the ones you will need to update
SELECT name as PackageName
, suser_sname(ownersid)
FROM
[msdb].[dbo].[sysdtspackages90] -- SQL Server 2005: [sysdtspackages90], SQL Server 2008 and R2: [sysssispackages], SQL Server 2000: [sysdbmaintplans]
WHERE name in ('MaintenancePlanName', 'MaintenancePlanName') -- Replace with your maintenance plan names here

To update the package(s) owner(s):

-- Update the owner to the SQL Server Agent account
UPDATE [msdb].[dbo].[sysdtspackages90] -- SQL Server 2005: [sysdtspackages90], SQL Server 2008 and R2: [sysssispackages], SQL Server 2000: [sysdbmaintplans]
SET ownersid = SUSER_SID('DOMAIN\SQLServerAgentAccount') -- Replace with your SQL Server Agent account
WHERE ownersid = SUSER_SID('DOMAIN\Account Name') -- Replace with the account name reported from the job failure
	and name in ('MaintenancePlanName', 'MaintenancePlanName') -- Replace with your maintenance plan names here

The last and very important step is that you must open the maintenance plan and make a minor cosmetic change such as resizing one of the tasks and then save the changes for the job owner to take effect. Yes. No kidding. That’s why you should replace these GUI maintenance plans to customized stored procedures when you can.

REFERENCES:

sysssispackages
http://msdn.microsoft.com/en-us/library/ms181582(v=sql.105).aspx

sysdtspackages90
http://msdn.microsoft.com/en-us/library/ms181582(v=sql.90).aspx

SUSER_SNAME()
http://msdn.microsoft.com/en-us/library/ms174427(v=sql.105).aspx

SUSER_SID()
http://msdn.microsoft.com/en-us/library/ms179889(v=sql.105).aspx