Monthly Archives: August 2013

How to find Failed or Hung SQL Server Agent Jobs in the last 24 Hours

Run the below query to find failed/hung jobs in your SQL Server Instance:

 

 

SELECT sjh.server as 'Server Name' ,sj.name as 'Failed Job Name', sjh.message as 'Error Description', sjh.run_date, sjh.run_time as 'Job Run Time/Job Hung Time'
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid
FROM msdb..sysjobhistory
WHERE run_status NOT IN (1,4)
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid
WHERE    DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 24
UNION ALL
select job.originating_server as 'Server Name', job.Name as 'Failed Job Name', 'Warning: Job Hung for More than 1 Day.' as 'Error Description',
CONVERT(char(10), activity.run_requested_Date,112) as run_date,
datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on (job.job_id = activity.job_id)
where run_Requested_date is not null
and stop_execution_date is null
and datediff(hour, activity.run_requested_Date, getdate()) >= 24

 

Output:

SQL-Job-Hung-Failure

NOTE: Ideally, you would want to know if a job is hung as soon as possible.

Hence, you can replace the underlined code above with the one below to reduce the time-interval for Hung Jobs from 1 day to 1 Hour:

datediff(minute, activity.run_requested_Date, getdate()) >= 60

Use a Power Shell script/Windows Scheduled Task to have this scheduled & run on multiple SQL Server Instances in a jiffy!

Advertisements

Leave a comment

Filed under sql server 2008, sql server 2012

Difference between a Primary Key and a Unique Constraint – DBA Perspective

I was preparing for an interview (hope my manager isn’t reading this yet) & found this wonderful link SQL Server DBA Interview Questions which had a great question on the difference between a Primary Key and a Unique Constraint.

That question brought in a thought about how this difference between a Primary Key and a Unique Constraint matters to a DBA? (Developer’s headache – not mine was my first opinion, however that quickly changed when I started exploring it.)

For DBAs like me who might NOT remember all the differences, this simple post by Shailendra Chauhan captures the essence of the Difference between Primary Key and Unique Key

Here is the DBA perspective that DBAs would hopefully benefit from {or pay the price if ignored 😉 }

Primary Key creates a Clustered Index internally, where Unique Constraint creates a “Unique, Non-Clustered” Index.

This means you can actually have multiple Unique Constraints in a single table. Simple yet useful to maintain Data Consistency.

For all those Science fans who live by “Seeing is believing”, here’s the screen preview:

Unique_1

Notice that I have created a Unique Constraint “IX_ID” on a Column “ID”. SQL Server has created a “Unique, Non-Clustered” Index with the same name.

Key Takeaway here would be to consider the above difference in how SQL server perceives your Primary key/Unique Constraint. Based on this solid understanding, you can make your database design better!

Cheerio! Hope this article was useful 🙂

PS: Please feel free to add onto your inputs/experience in the below comments section.

Leave a comment

Filed under Keys and Constraint, sql server 2005, sql server 2008, sql server 2012, Uncategorized

What’s in a NULL ?

Let’s take a Sneak Peek inside SQL Server 2012 (this hasn’t changed in versions 2008 & 2005 as well) & find out How SQL Server stores a NULL Value!

 

Step 1: Create a Table

CREATE TABLE TestforNULLs

(Id int null)

Step 2: Insert the below values

insert into TestforNULLs
values
(NULL),
(”),
(1)

Step 3: Verify to see what you have inserted

select * from TestforNULLs

NULL_1

Hmmm… We’ve already found out that SQL treats ‘’ {empty value inserted as ‘0’ (zero)}

Step 4: Next enable a SQL Trace for the current session

DBCC TRACEON(3604)

GO

Output:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Step 5: Verify as to what traces have been enabled

DBCC TRACESTATUS

Go

Output:

NULL_2

Step 6: Next, find out where your Data pages are located:

DBCC IND(testbed, TestforNULLs, -1)

GO

NULL_3

Step 7: Copy the First Page ID, here it is ‘328’ & paste it in the below command:

This command will help you see what’s in a SQL Data page.

DBCC PAGE (testbed, 1, 328, 1)

GO

NULL_4

Step 8: Scroll down to the “DATA” section below:

NULL_5

  • Notice that the NULL Bitmap Value has been enabled for the NULL Value with Binary 1 indicating that the first & the only column contains a NULL value.
  • For the Second and Third records which are NOT NULLs, the NULL Bitmap Value is 0.
  • This is how SQL Server identifies a NULL Value.

Hope this was useful piece of Information for you & answers the questions such as –

“How does SQL Server store NULL values?” 

“What’s in a NULL, bro?”

“hmmm… NULL huh?”

 

Key Learning:

  1.  SQL Server marks NULL Values via its NULL BITMAP
  2. SQL Server stores Integer value 0 (zero) when you pass an empty value
  3. Empty value is NOT equal to NULL Value

 

Related Reading:

Deep Dive on to Paul Randall’s article on Misconceptions around null bitmap size

 

 

 

3 Comments

Filed under SQL NULL, sql server 2005, sql server 2008, sql server 2012

Useful URLs for SQL DBAs – Foundation

1)      SQL Server Architecture

http://blog.sqlauthority.com/2012/08/30/sql-server-beginning-of-sql-server-architecture-terminology-guest-post/

2)      SQL Server Transaction Log Architecture and Management

http://msdn.microsoft.com/en-us/library/jj835093.aspx

3)      About transaction log and its truncation in SQL Server

http://www.codeproject.com/Articles/380879/About-transaction-log-and-its-truncation-in-SQL-Se

4)      Truncating the Transaction Log

http://msdn.microsoft.com/en-us/library/aa174538(v=sql.80).aspx

5)      Checkpoints and the Active Portion of the Log

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

6)      How to Identify Blocking in SQL Server

http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/

http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

7)   Does a full backup truncate the log?

http://www.sqlservercentral.com/Forums/Topic567010-357-1.aspx#bm1311005

8)   Full Backups & Transaction Logs Backup – There’s Something about SQL!

http://blogs.technet.com/b/beatrice/archive/2008/07/24/full-backups-transaction-logs-backup.aspx

9) TempDB Related

http://msdn.microsoft.com/en-us/library/ms190768.aspx

http://msdn.microsoft.com/en-us/library/ms176029.aspx

http://blogs.msdn.com/b/deepakbi/archive/2010/04/14/monitoring-tempdb-transactions-and-space-usage.aspx

http://msdn.microsoft.com/en-us/library/ms175527.aspx

http://msdn.microsoft.com/en-us/library/ms345368.aspx

http://technet.microsoft.com/library/Cc966545

10) SQL Server Best Practices

http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

11) SQL Server Replication

http://www.replicationanswers.com/default.asp 

Please share your favorite URLs that will be useful for other DBAs.

Category: SQL Server Basics

Leave a comment

Filed under sql server 2008, sql server 2012

Welcome!

Brand new space to pour your SQL Learning out to the whole world!

Leave a comment

Filed under Uncategorized