Auditing SQL Agent job creation and deletion

SQL Agent jobs are added / removed using stored procedure interface sp_add_job and sp_delete_job. Customers have asked us how we could track user who has created a job / deleted a specific job. You could accomplish this using SQL Server Auditing feature. You can turn on Auditing to track and log stored procedure execution requests…

0

Starting Jobs in Powershell using Job Name

http://blogs.msdn.com/b/sqlagent/archive/2010/12/29/starting-jobs-in-powershell-using-job-name.aspx  This posting is provided “AS IS” with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm

0

Scheduled Policy Evaluation on multiple servers using MSX/ TSX in SQL Agent

One of our customer was looking for ways to schedule a policy evaluation on multiple servers using their existing MSX/TSX infrastructure.  I thought it would be better to blog an article that could help others looking for similar information http://blogs.msdn.com/b/sqlagent/archive/2010/11/19/scheduled-policy-evaluation-on-multiple-servers-using-msx-tsx-in-sql-agent.aspx  This posting is provided “AS IS” with no warranties, and confers no rights. Use of included…

0

Create a database backup job using SQL Server Management Studio

SQL Server Management Studio  can be used to create a database backup job to backup an user database. Link        This posting is provided “AS IS” with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm

1

Create SQL Agent job using SMO

# Powershell script to create a new Agent job and list the job using JobCollection # Inspects error collection and prints all inner exceptionfunction CheckForErrors{        $errorsReported = $False         if($Error.Count -ne 0)        {                write-host “******************************”                write-host “Errors:”, $Error.Count                 write-host “******************************”                foreach($err in $Error)                {                        $errorsReported  = $True                        if( $err.Exception.InnerException -ne $null)                        {                                write-host $err.Exception.InnerException.ToString()                        }                        else                        {                                write-host $err.Exception.ToString()                        }                        write-host “———————————————-”                }                throw        }} # Create a new job and get job back from…

1

Troubleshooting Arithmetic overflow errors in queries using Extended Events in SQL Server 2008

Problem: Management view sys.dm_exec_requests has a column total_elapsed_time to track the total time elapsed in milliseconds since the request arrived. Following T-SQL query converted this value from milli-seconds to micro-seconds. — Simplified version of a complex T-SQL batchSELECT  session_id, command,r.total_elapsed_time * 1000 AS total_elapsed_time_in_ms — Convert ms to us. FROM sys.dm_exec_requests AS r Sometimes the above query throws Arithmetic…

1

Configure Database Mirroring using SQL 2008 Power Shell

############################################################################################ Scenario: Configure Database mirroring##  How to use this powershell script:#    – Launch SQL Server PowerShell ( Start -> Run -> sqlps.exe)#    – Copy the following powershell script and save to file (Ex c:\DBMirrroringSetup.ps1)#    – in powershell window, type in script file path ( Ex: c:\DBMirrroringSetup.ps1) to run the script########################################################################################### # Backup Databasefunction BackupDatabase{        Param([string]$servername, [string]$dbName,…

4

SQL 2008 powershell script to print Resource Governor properties

############################################################################################ SQL 2008 powershell script to print Resource Governor state, properties, Resource pools and corresponding workload groups##  How to use this powershell script:#    – Launch SQL Server Management  Studio ( Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio)#    – Connect to SQL Server instance, Right click on the server…

0

SQL 2008 Powershell Script for creating a Policy and saving to file

############################################################ SQL 2008 Powershell Script for Policy Based Management # Scenario: Create a  server policy to check if XPCmdShell is disabled and save it to file# # To run this Powershell script# 1) Install SQL Server 2008# 2) Run SQL PowerShell ( Start -> Run -> sqlps.exe)# 3) Copy the following script and paste it…

1