-
List Sql Server jobs and their owners and duration history
[sourcecode language=”sql”] use msdb go select name, suser_sname(sysjobs.owner_sid) as owner from msdb..sysjobs go select sj.name,sjh.run_status,case sjh.run_status when 1 then ‘Sucess’ when 2 then ‘Retry’ when 3 then ‘Cancelled’ when 4 then ‘In Progress’ when 0 then ‘Failed’ End as ‘Run Status’,sjh.run_date as ‘Run Date’, CASE len(run_time) WHEN 1 THEN cast(’00:00:0′ + cast(run_time as char) as […]
-
List last running status of enabled jobs in Sql Server
Script below. This one works better for Sql Server 2000. I will do a version for Sql Server 2005, when I get a chance. It didn’t work very well on Sql Server 2005 because Ad Hoc Distributed Queries is disabled by default. set nocount on IF OBJECT_ID(‘tempdb..#TmpJobs’) IS NOT NULL DROP TABLE #TmpJobs SELECT * […]
-
List all sa sysadmin in your Sql Server instances with Perl
This is mainly for my own reference. Save it as ListSysadmin.pl. Run ListSysadmin.pl SqlServerList.txt on command line. It lists all logins that has sa (sysadmin) rights on Sql Server instance, including individual domain members in a domain group that has sysadmin rights. SqlServerList.txt should be a text file, with each individual line contains a Sql […]
-
When was the last time a full backup was taken
One of the first things you want to do as a DBA is to check when was the last time a full backup was taken for all databases you manage. For Sql Server, here is a script you can run: set nocount on select cast(a.name as char(30)), max(b.backup_finish_date) from master..sysdatabases a left outer join msdb..backupset […]
-
Some notes on Sql Server and MySQL
I will take MySQL certification exams, therefore I am going through MySQL 5.0 Certification Study Guide. It is a pretty cool book in that it goes to the point right away without many wasted words. Too many technical books dance around the topic, fill the volume with screen shots, but do not deliver the goods […]