How to see history of jobs scheduled in SQL Server?
Information on sysjobhistory
Information on sysjobs
– shows job history with the most recent one to show the most recent one with it’s run status
select job_name, run_datetime, run_duration, run_status
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ‘:’ + SUBSTRING(run_duration, 3, 2) + ‘:’ +
SUBSTRING(run_duration, 5, 2) AS run_duration,
run_status
from
(
select
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT(’000000′ + CONVERT(varchar(6), run_duration), 6),
run_status = CASE run_status WHEN 1 Then ‘Success’
WHEN 0 Then ‘Failure’
WHEN 2 Then ‘Retry’
WHEN 3 Then ‘Cancelled’
WHEN 4 Then ‘In-Progress’
Else ‘Unknown: ‘ + Convert(VARCHAR(2), run_status)
End
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
where step_id = 0 — show first step
) t
) t
order by run_datetime desc, job_name
Output:
Test 2010-01-28 20:15:43.000 00:00:00 Failure
Test 2010-01-28 19:37:33.000 00:00:00 Success
Code used from: http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx
Run Command Shortcuts for Administrative Tools
http://msmvps.com/blogs/ad/archive/2006/10/27/Run-Command-Shortcuts-for-Adminstrative-Tools.aspx
How to monitor shared pool?
http://www.dba-oracle.com/t_x$ksmlru_x$ksmsp_shared_pool_monitoring.htm
Window tools
Here are some of the windows tools that can be used when working on window internals.
Autoruns (autoruns.exe) – http://technet.microsoft.com/en-us/sysinternals/bb963902.aspx
Access Chk – http://technet.microsoft.com/en-us/sysinternals/bb664922.aspx
Dependency walker – http://dependencywalker.com/
List DLLs – http://technet.microsoft.com/en-us/sysinternals/bb896656.aspx
Handle Viewer – http://technet.microsoft.com/en-us/sysinternals/bb896655.aspx
Logon Sessions – http://technet.microsoft.com/en-us/sysinternals/bb896769.aspx
Process Explorer – http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
Object viewer – http://technet.microsoft.com/en-us/sysinternals/bb896657.aspx
Service control – sc.exe (built-in tool)
Task Process List – tlist.exe (built-in tool)
Windows Tool package – http://www.microsoft.com/downloads/details.aspx?FamilyId=49AE8576-9BB9-4126-9761-BA8011FABF38&
How to see backup history through the back-end?
By querying msdb.dbo.backupset and msdb.obackupmediafamily one can see backup history like start and end time, type of backup, location of the backup.
select bs.database_name
, bs.type
, bs.backup_start_date
, bs.backup_finish_date
, bmf.physical_device_name
FROM msdb.dbo.backupset bs
, msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and backup_start_date > ’2010-01-22′
testDB D 2010-01-22 00:00:02.000 2010-01-22 05:09:32.000 c:\Backup\testdb_full.bak
testDB I 2010-01-23 02:26:21.000 2010-01-23 02:30:28.000 C:\Backup\testdbdiff20100123_022621.bak
Type – D = Database, I – Differential Database, L – Log
Documentation on backupset:
SQLServer 2008 – http://technet.microsoft.com/en-us/library/ms186299.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms186299%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260602%28SQL.80%29.aspx
Documentation on backupmediafamily:
SQLServer 2008 http://technet.microsoft.com/en-us/library/ms190284.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms190284%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260605%28SQL.80%29.aspx
How to create differential backup?
Using the following T-SQL one can create differential backup or make it part of scheduled job.
declare @datetimestamp varchar(20)
declare @filename nvarchar(100)
declare @name nvarchar(100)
SELECT @datetimestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '')
SELECT @filename = 'C:\testDBBackup\testDBbdiff' + @datetimestamp + '.bak'
SELECT @name = 'testDB Diff Backup ' + @datetimestamp
BACKUP DATABASE testDB
TO DISK = @filename
WITH DIFFERENTIAL,INIT, NAME = @name
How to find the trace file name?
To find the trace file one using oradebug one can find it using the example below:
SQL> oradebug mysetpid
Statement processed.
SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_9964.trc
If you try to run the above command without attaching to a session it will return ORA-00074
SQL> oradebug tracefile_name
ORA-00074: no process has been specified
How to use oradebug to connect to a session?
To use oradebug one first needs to connect to a session using the process id or ora pid.
One can do so by doing one of the following, the following example shows how to connect using OS process id or oracle PID or current session.
SELECT a.username, a.sid, a.serial#, b.spid, b.pid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username = #&39;SCOTT#&39;;
USERNAME SID SERIAL# SPID PID
—————————— ———- ———- ———— ———-
SCOTT 25 920 7106 14
SQL> !ps -ef | grep 7106
oracle 7106 6778 0 21:05 ? 00:00:00 oracleTEST (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Connect using operating system (OS) process id:
SQL> oradebug setospid 7106
Oracle pid: 14, Unix process pid: 7106, image: oracle@localhost.localdomain (TNS V1-V3)
Connecting using oracle pid:
SQL> oradebug setorapid 14
Unix process pid: 7106, image: oracle@localhost.localdomain (TNS V1-V3)
Connect to current session
SQL> oradebug setmypid
Statement processed.
How to set unlimit size of trace file?
Using oradebug unlimit one can set to unlimit the size of the trace file.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
How to monitor jobs that are running on SQLServer through SQLServer Agent?
By calling msdb..sp_get_composite_job_info or msdb.dbo.sp_help_job one can find the current jobs running on SQLServer.
Example:
exec msdb..sp_get_composite_job_info @execution_status=1
Or
exec msdb.dbo.sp_help_job @execution_status = 1
Sample output:
job_id originating_server name
———————————— —————————— ———————
7E043796-44F3-4ABF-A047-AA27691DF674 sqlserver01 job_name
