I sometimes find job owners are from an Active Directory group. Here’s a report on how to find these. (The example code also shows one way to delete the jobs.)
USE msdb go SELECT 'EXEC sp_delete_job @job_name = ''' + sj.name + ''';' AS RunSQL, sj.name AS 'job', SUSER_SNAME(owner_sid) AS 'owner', spr.NAME AS 'proxy' --,sj.* FROM msdb.dbo.sysjobs sj LEFT JOIN msdb.dbo.sysjobsteps sjt ON sj.job_id = sjt.job_id LEFT JOIN msdb.dbo.sysproxies spr ON spr.proxy_id = sjt.proxy_id ORDER BY sj.name
The following messages appear in your database mail log file every 10 minutes.
“DatabaseMail process is started”
“DatabaseMail process is shutting down”
Database Mail is set to shut down every 10 minutes by default and restart when emails are sent. On a busy server you might want to change this as follows.
Go to SSMS object browser, right click on the Database Mail icon, choose “view of change system parameters”, set new value in “Database Mail Executable Minimum Lifetime (seconds)”. Here’s how to calculate a value for 12 hours.
-- 60 seconds (as 1 minute) * 60 minutes (as 1 hour) * 12 hours SELECT 60*60*12 AS ServiceUpTime ServiceUpTime ------------- 43200
Here’s how to write blocking messages out to the SQL Errorlog. It won’t look pretty but has everything you should need.
/* Set up a trace to monitor blocking on a server. */ -- Trace locks into the Errolog file DBCC TRACEON(1222, -1); -- Check the status DBCC TRACESTATUS (1222, -1) -- and turn it off DBCC TRACEOFF (1222,-1)
Here is a link which tells how to set up the SQL Profiler to monitor deadlocks using a Deadlock Graph. Choose “Save Deadlock XML Separately” in the new tab and “Each deadlock XML graph in a distinct file” as options and give it a path and filename. This way you can capture and read each lock easily, later.
Find any current locking … blocking for a particular database. I haven’t tried this.
/* http://stackoverflow.com/questions/12422986/sql-query-to-get-the-deadlocks-in-sql-server-2008 */ SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction AS IsUserTransaction, AT.name AS TransactionName, CN.auth_scheme AS AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = DB_ID() ORDER BY L.request_session_id
Transform a single column of data to a single row of data one column per each original row. I needed to quote-comma delimit the columns too.
SELECT name
FROM sys.sysdatabases
WHERE name LIKE '%master%'
ORDER BY name
SELECT TOP 1
STUFF((
SELECT ', "' + name + '"'
FROM sys.sysdatabases
WHERE name LIKE '%master%'
ORDER BY name
FOR
XML PATH('')
), 1, 1, '')
FROM sys.sysdatabases
/*
name
--------------------------------------------
master
master_admin
master_settings
--------------------------------------------
"master", "master_admin", "master_settings"
*/
Create a test to see if the command line had “-verbose” on it. Then run something.
if ($PSBoundParameters.verbose) {
Get-Item -Path "$OutputFile"
}
Here’s another way (found at StackOverflow.)
function DoStuff {
[CmdletBinding()]
param()
process {
if ($PSBoundParameters['Verbose']) {
# do verbose stuff
}
New-Item Test -Type Directory -Verbose:($PSBoundParameters['Verbose'] -eq $true)
}
}
I always forget to wrap the text in () which allows using the replace or formatting method to substitute a value. So here it is:
Write-Host -ForegroundColor Blue ("Found {0} servers" -f $a.Length)
This was a great idea taken from Three Really Handy Tricks With SQL Server Table Variables post.
1.Create a table variable @LOG which stores log text for each step and maybe some other key data about the step.
2.In each step, INSERT a row into @LOG.
3.When rollback occurs, INSERT another row in @LOG giving the reason for the rollback.
4.ROLLBACK
5.SELECT * FROM @LOGAll the code calling this batch has to do is interrogate the returned record set. If its last record is the one recording the ROLLBACK, then it knows something went wrong and can alert the user somehow, giving the full log of what happened. You cannot do this with a temporary table because temporary tables are transactional so the rollback will erase the log.
Here are some topics I want to keep referring to:
I like to run this against a server group in SSMS to check multiple servers at once.
SELECT job.Name,
job.job_ID,
job.Originating_Server,
activity.run_requested_Date,
DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) AS ElapsedMinutes,
activity.last_executed_step_date,
activity.last_executed_step_id
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity
ON job.job_id = activity.job_id
JOIN msdb.dbo.syssessions sess
ON sess.session_id = activity.session_id
JOIN (
SELECT MAX(agent_start_date) AS max_agent_start_date
FROM msdb.dbo.syssessions
) sess_max
ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_Requested_date IS NOT NULL
AND stop_execution_date IS NULL
GO
Here is what I use to prevent one job from trying to restart another job.
EXEC msdb.dbo.sp_help_job @execution_status = 1, @job_aspect = 'JOB', @job_name = 'DBA - Daily Database Maintenance' IF (@@ROWCOUNT = 0) PRINT 'Start job' ELSE PRINT 'Job already running ... Nothing to do'
This uses the old syntax.
SELECT DISTINCT
DB_NAME(dbid) AS DatabaseName,
fileid,
CASE WHEN fileid = 1 THEN 'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
ELSE ''
END DetachScript,
CASE WHEN fileid = 1
THEN 'exec sp_attach_db @dbname = N''' + DB_NAME(dbid) + '''' + ',@filename' + CONVERT(VARCHAR(10), fileid) + '=N''' + filename + ''''
ELSE ',@filename' + CONVERT(VARCHAR(10), fileid) + '=N''' + filename + ''''
END AS AttachScript
FROM master.dbo.sysaltfiles
WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'
AND DB_NAME(dbid) IN ('Sandbox')
ORDER BY DB_NAME(dbid),
fileid
GO