T-SQL Tuesday #171 - Describe the Most Recent Issue You Closed

This month T-SQL Tuesday is hosted by Brent Ozar (blog). For this month's T-SQL Tuesday, we are asked to describe the most recent ticket or issue that we closed.

Executing xp_cmdshell with Non SysAdmin Account

In SQL Server, xp_cmdshell is an extended stored procedure that is stored in the master database that would allow us to execute Windows command shell. By default, the ability to execute xp_cmdshell is disabled. We could enable the ability to run xp_cmdshell by executing the following query against the SQL Server instance:

PASS Summit Video Recordings

PASS (Professional Association for SQL Server) Summit is the largest conference for data professional working with Microsoft SQL Server and Business Intelligence stack. Typically, PASS would have the Summit once a year (in the fall) in Seattle, Washington. PASS Summit offers a wealth of knowledge. Each year, there are hundreds of sessions on various SQL Server and Business Intelligence topics presented by industry experts and also Microsoft employees. If you are a data professional, who are working with Microsoft SQL Server and Business Intelligence, PASS Summit is a great event to learn and network with fellow data professionals. If you were unable to attend the past PASS Summits (or if you attended the past PASS Summits and want to relive and review those sessions), you can go to SQL PASS site and watch the past PASS Summit Session Recordings for free. As of the writing of this blog post, you can view recordings for PASS Summit 2015 and older for free (you need to be a member of SQL PASS but the registration is free). Even though they might be a few years old, they are excellent learning resources.

By the way, if you can attend PASS Summit in person, I would recommend it. PASS Summit 2018 is going to be in Seattle, Washington, in November 2018. You can get more information from PASS Summit 2018 site.

Remove Log Shipping When Secondary Server Not Available

The other day, I came accross a scenario where I need to remove log shipping from a database on the primary server. However, we have already shutdown/decommissioned the secondary server. If we try to disable the log shipping from the database properties window, we would most like get the following:

Reflection on Steve Stedman's Database Corruption Challenge

Over the past few months, Steve Stedman (blog | twitter) has been conducting database corruption challenge. It is a challenge where Steve would post corrupt SQL Server databases and the participants would try to fix the corruption to the best of their ability. The participants would need to avoid any data loss when trying to fix the database corruption.

Change Data Capture (CDC) and Simple Recovery Mode

One of the misconceptions that some people have about change data capture (CDC) in SQL Server is that it needs the database to be set up with full recovery mode. If we look at the following TechNet article, it mentioned:

It should be noted that change data capture works with all recovery models. But when change data capture is enabled, operations that would normally be minimally logged in simple or bulk-logged recovery models are fully logged to enable change data capture to capture all changes.

SQL Script: Database Auto Grow and/or Auto Shrink Report

Below is a SQL script that can be used to list recent auto grow and/or auto shrink events that happened on your SQL Server. It queries the SQL Server default trace. By default, default trace is enabled (you can check this by running: sp_configure 'default trace enabled').

PowerShell Script: List SQL Server Analysis Service Role Permission By Database and Cube

A while ago, I wrote a couple of blog entries in regards to using PowerShell scripts in order to list roles within the database and their members. The script can be found here and here.

Let’s try to expand on those scripts further.

PowerShell Script: Adding Database into Always On Group

Every once in a while, I have  a new database that I need to put into an existing Always On group. So I have decided to create a PowerShell script that would do just that. The script can be found here.

Stale View

If you have non-schema bound view in SQL Server and the underlying table was updated, you potentially can have a stale view. A quick way to demonstrate this:

Let say that you have the following table:

CREATE TABLE dbo.Contact (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(10),
    LastName VARCHAR(10));
GO

INSERT INTO dbo.Contact VALUES ('John', 'Doe');
INSERT INTO dbo.Contact VALUES ('Jane', 'Doe');

Then, create a simple view against the newly created table:

CREATE VIEW dbo.vwContact
AS
    SELECT *
    FROM dbo.Contact
GO

If we run the following query:

SELECT Id, FirstName, LastName
FROM dbo.vwContact

We would get the expected result:

Now, what happen if we add a new column to the Contact table:

ALTER TABLE dbo.Contact
ADD Country VARCHAR(10) NULL;
GO

UPDATE dbo.Contact
SET Country = 'USA'
WHERE Id = 1;

If we run the following query against the vwContact view:

SELECT    Id, FirstName, LastName, Country
FROM    dbo.vwContact;

We would get the following error message:

Msg 207, Level 16, State 1, Line 28

Invalid column name 'Country'.

That’s odd, it looks like the view does not recognize the newly added Country column.

Ok, let say that your company got a new contact, his name is Mr. Rhino Hippopotamus. Obviously, you will need to have a bigger last name column. So you decide to change the LastName column from varchar(10) to varchar(20).

ALTER TABLE dbo.Contact
ALTER COLUMN LastName VARCHAR(20);
GO

INSERT INTO dbo.Contact VALUES ('Rhino', 'Hippopotamus', 'USA');
GO

If you try to query the view using the following query (let’s forget about the Country column for now):

SELECT Id, FirstName, LastName
FROM dbo.vwContact;

That seems to work just fine. You get the expected result:

But, what happen if you try to run the same query from another server, using a linked server:

SELECT Id, FirstName, LastName
FROM ServerA.TestDB.dbo.vwContact;

Instead of getting the expected result, we got the following error:

Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'SQLNCLI11' for linked server 'HSQL01' returned data that does not match expected data length for column '[ServerA].[TestDB].[dbo].[vwContact].LastName'. The (maximum) expected data length is 10, while the returned data length is 12.

If we check on the column property for the LastName column of vwContact view from the server that has the linked server to the original server, using the following query, we would notice that somehow, the LastName column is still listed as having 10 characters long:

USE [master];
GO

EXEC sp_columns_ex 'ServerA', 'vwContact', 'dbo', 'TestDB', 'LastName', NULL;

Solution

So how to address the issue that we are having with stale view. There are several ways:

We could drop and recreate the view. In the above example, we can run the following query:

IF (OBJECT_ID('dbo.vwContact') IS NOT NULL)
    DROP VIEW dbo.vwContact
GO

CREATE VIEW dbo.vwContact
AS
    SELECT *
    FROM dbo.Contact
GO

We could also run an alter view statement:

ALTER VIEW dbo.vwContact
AS
    SELECT *
    FROM dbo.Contact
GO

Alternatively, we could refresh the view using the following script:

EXEC sp_refreshview 'dbo.vwContact';

SQL Server Agent Job Last Run Time and Result

I've seen situations in which we have some SQL Server Agent jobs that are scheduled to run very rarely  (like once every year or once every half year). Either because of audit or troubleshooting, we need to find out those SQL Server Agent jobs last run time and their outcome. However, the SQL Server Agent history is being purged every few months (Notes: purging SQL Server Agent history periodically is not a bad thing, it is actually a good practice to keep the msdb database size manageable, esp. if your environment has lots of SQL Server Agent jobs, in which many of them have lots of job steps, and some of the jobs are scheduled to run frequently - every few minutes or even seconds).

Are we out of luck? Well not necessarily.

Demo

First create a SQL Agent job with the following script:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SQL Job Test', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select 1', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 2', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select 2', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


GO

Then we run the job using the following script:

USE [msdb];
GO

EXEC sp_start_job @job_name=N'SQL Job Test';
GO

Once that has been completed, there should be a job history for the SQL Server agent job:

Now purge the job history by running the following:

USE [msdb];
GO

EXEC dbo.sp_purge_jobhistory @job_name=N'SQL Job Test';
GO

After purging the job history, we will no longer have the entries for “SQL Job Test” job on dbo.sysjobhistory table in the msdb database.

If we try to query the sysjobhistory, using the following query, we would get 0 row:

USE [msdb];
GO

SELECT    *
FROM    dbo.sysjobhistory
WHERE    job_id IN (
        SELECT job_id
        FROM dbo.sysjobs
        WHERE [name] = N'SQL Job Test');

Or this query:

USE [msdb];
GO

EXEC sp_help_jobhistory @job_name = N'SQL Job Test';
GO

Gulp! No more history for SQL job “SQL Job Test”. But wait, if we look at the job properties, we can see when the job was last executed.

So the information must still be in the database. If we try to run the following query:

USE [msdb];
GO

EXEC sp_help_job @job_name = N'SQL Job Test';

We can see the job’s last run date, time and even outcome (For the definition of last_run_outcome column, you can check the following MSDN article). There’s more. When you run the sp_help_job stored procedure, you can also see each of the job step’s last run date, time and outcome.

If we investigate it further, the last run date, time and outcome are stored on sysjobservers and sysjobsteps within the msdb database. We can query them using the following:

USE [msdb];
GO

SELECT    j.job_id,
        j.Name,
        js.last_run_outcome,
        last_run_outcome_desc = CASE 
            WHEN js.last_run_outcome = 0
                THEN 'Failed'
            WHEN js.last_run_outcome = 1
                THEN 'Succeeded'
            WHEN js.last_run_outcome = 3
                THEN 'Canceled'
            ELSE 'Unknown'
            END,
        js.last_outcome_message,
        last_run_datetime = dbo.agent_datetime(
            CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
        js.last_run_duration
FROM    dbo.sysjobs j
        INNER JOIN dbo.sysjobservers js 
        ON j.job_id = js.job_id

Or if you want to see it for each of the job’s steps:

USE [msdb];
GO

SELECT    j.job_id,
        j.Name,
        js.last_run_outcome,
        last_run_outcome_desc = CASE 
            WHEN js.last_run_outcome = 0
                THEN 'Failed'
            WHEN js.last_run_outcome = 1
                THEN 'Succeeded'
            WHEN js.last_run_outcome = 3
                THEN 'Canceled'
            ELSE 'Unknown'
            END,
        js.last_outcome_message,
        last_run_datetime = dbo.agent_datetime(
            CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
        js.last_run_duration,
        jst.step_id,
        jst.step_name,
        step_last_run_outcome = jst.last_run_outcome,
        step_last_run_outcome_desc = CASE 
            WHEN jst.last_run_outcome = 0
                THEN 'Failed'
            WHEN jst.last_run_outcome = 1
                THEN 'Succeeded'
            WHEN jst.last_run_outcome = 2
                THEN 'Retries'
            WHEN jst.last_run_outcome = 3
                THEN 'Canceled'
            ELSE 'Unknown'
            END,
        step_last_run_datetime = dbo.agent_datetime(
            CASE WHEN jst.last_run_date = 0 THEN NULL ELSE jst.last_run_date END, 
            CASE WHEN jst.last_run_time = 0 THEN NULL ELSE jst.last_run_time END)
FROM    dbo.sysjobs j
        INNER JOIN dbo.sysjobservers js 
            ON j.job_id = js.job_id
        INNER JOIN dbo.sysjobsteps jst
            ON j.job_id = jst.job_id

WHERE    j.name = N'SQL Job Test';

Clean-up

To clean up the demo, you can run the following script:

USE [msdb];
GO

EXEC sp_delete_job @job_name = N'SQL Job Test';

GO

Utilizing smtp4dev with SQL Server Database Mail for Testing

SQL Server has database mail, a solution which would allow us to send e-mail from our database server. It allows us to send either e-mail in html or text format to users. It’s great because we can use it to send query results to users (or ourselves). We can also configure SQL Server Agent to use database mail to send alerts to operator.

Database mail utilizes SMTP server to send the e-mails. In some cases, we might not have a readily available SMTP server in our environment (like in my home lab environment) or we might be block from using the SMTP server from our development environment for security reason. In those situations, I’ve been using smtp4dev. It is a neat little utility that would allow you to have a dummy SMTP server on your local computer. It is lightweight and also no installation is needed. When we run the utility, it would sit on the system tray and listen to port 25 (default SMTP port). It will receive the e-mail that get send to its way, without sending the e-mail to its final destination (the e-mail recipients). It is pretty handy when we need to run some tests.

Setting up database mail to work with smtp4dev is pretty simple.

• Download smtp4dev from http://smtp4dev.codeplex.com.
• Extract the file and save it on your system (assuming that it is your development system and it has the SQL Server).
• Run smtp4dev. Notes: If you have Windows Firewall turned on, you might get a “Windows Security Alert” that ask you if you want to allow smtp4dev to communicate with either the private network, public network or both. Since normally I run smtp4dev on my development system with also has SQL Server that I want to have database mail turn on, I would just hit the cancel button.
• If database mail in SQL Server has not been enable, we can enable it by using the following SQL script:

exec sp_configure 'show advanced', 1;
go
reconfigure;
go
exec sp_configure 'Database Mail XPs', 1;
go
reconfigure;
go

• We then need to set up database mail profile, account and then associate the profile with the account in SQL Server. To do that, we can use the following script (based on the “Database Mail Simple Configuration Template”):

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
        @display_name NVARCHAR(128),
        @error_display NVARCHAR(500);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'SMTP4Dev';

-- Account information. Replace with the information for your account.

        SET @account_name = 'SMTP4Dev Account';
        SET @SMTP_servername = 'localhost';            --- Since the smtp4dev is on local system. 
        SET @email_address = 'john.doe@email.com';
        SET @display_name = 'John Doe';

-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
    SET @error_display = 'The specified Database Mail profile (' + @profile_name + ') already exists.';
    RAISERROR(@error_display, 16, 1);
    GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
    SET @error_display = 'The specified Database Mail account (' + @account_name + ') already exists.';
    RAISERROR(@error_display, 16, 1) ;
    GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to create the specified Database Mail account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to create the specified Database Mail profile (' + @profile_name + ').';
    RAISERROR(@error_display, 16, 1);
    ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to associate the speficied profile with the specified account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

• Now the moment of truth, we can test to see if it works by trying to send an e-mail using sp_send_dbmail

exec msdb..sp_send_dbmail @profile_name = 'SMTP4Dev',
    @recipients = 'jane.doe@email.com',
    @subject = 'Test E-mail',
    @body = 'This is a test email.';

If everything works as plan hopefully on your tray notification, you would see the following message:

And if you open smtp4dev, you should see the following:

To view the e-mail, you can click on the “View” or “Inspect” button.

Some caveats that I’ve noticed:

• I have Microsoft Office 2013 installed on my system, which includes Outlook 2013. For some reason, when I click the “View” button, I would get an error message “The attempted operation failed. An object could not be found.” and it just would not open the e-mail (if I try to open it using Outlook 2013). It is ok if I use Windows Live Mail 2012. I would normally use the “Inspect” button instead.
• If you exit out smtp4dev, the e-mails that you have on the Messages tab will get deleted.

Cleaning Up

If you want to remove the database mail from your SQL Server, first you want to remove the profile association with account, the profile and the account. You can do that by using the following script:

DECLARE @profile_name sysname,
        @account_name sysname,
        @error_display nvarchar(500);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'SMTP4Dev';

-- Account information. Replace with the information for your account.
        SET @account_name = 'SMTP4Dev Account';

IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
    SET @error_display = 'The specified Database Mail profile (' + @profile_name + ') does not exists.';
    RAISERROR(@error_display, 16, 1);
    GOTO done;
END

IF NOT EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name)
BEGIN
    SET @error_display = 'The specified Database Mail account (' + @account_name + ') does not exists.';
    RAISERROR(@error_display, 16, 1);
    GOTO done;
END

-- Start a transaction before deleting the profile account, profile and account.
BEGIN TRANSACTION ;

DECLARE @rv INT;

EXEC @rv=msdb.dbo.sysmail_delete_profileaccount_sp 
    @profile_name = @profile_name, 
    @account_name = @account_name;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to delete the profile with the specified account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

EXEC @rv=msdb.dbo.sysmail_delete_profile_sp
    @profile_name = @profile_name;

IF @rv<>0
BEGIN
    SET @error_display = 'Failed to delete the database mail profile (' + @profile_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

EXEC @rv=msdb.dbo.sysmail_delete_account_sp
    @account_name = @account_name;
IF @rv<>0
BEGIN
    SET @error_display = 'Failed to delete the database mail account (' + @account_name + ').';
    RAISERROR(@error_display, 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

Then after that, you can disabled the database mail by using the following script:

exec sp_configure 'Database Mail XPs', 0;
go
reconfigure;
go
exec sp_configure 'show advanced', 0
go
reconfigure;
go

Unique Filtered Index

Consider a scenario where you need to create a table in which you need to store contact information, and for one of the columns, we need to store passport number for each contact, if one is available. Assuming that passport number is unique for each contact, but not all have passport, how can we ensure the uniqueness for the passport number enter while allowing null values for those contacts who don't have passport number?

Unique constraint/index would come to mind. However, the problem of using unique constraint/index in this scenario is while unique constraint/index would ensure that the data enter is unique, a column with unique constraint/index can only allow one null value in that column.

A quick demo for this using unique index (but the result should be similar if we use unique constraint as well):

IF (OBJECT_ID('dbo.ContactInfo') IS NOT NULL)
BEGIN
    DROP TABLE dbo.ContactInfo
END

CREATE TABLE dbo.ContactInfo (
    ContactID INT PRIMARY KEY IDENTITY (1,1),
    ContactName VARCHAR(100) NOT NULL,
    PassportNumber VARCHAR(25) NULL);

CREATE UNIQUE INDEX  UI_PassportNumber
ON dbo.ContactInfo(PassportNumber)

Now let's try to test to make sure that we can't enter duplicate passport number:

INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('John Doe', 'B1234567890');
INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('Jane Doe', 'B1234567890');

We would get the following messages:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.ContactInfo' with unique index 'UI_PassportNumber'. The duplicate key value is (B1234567890).
The statement has been terminated.

Which is to be expected, it would insert the information for John Doe just fine, but then it would failed to insert the information for Jane Doe since Jane Doe’s passport number is the same as John Doe’s.

Now let’s test to make sure that we can enter multiple contact info with blank passport number.

INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('Mary Doe', NULL);
INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('Jack Doe', NULL);

We would get the following messages:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.ContactInfo' with unique index 'UI_PassportNumber'. The duplicate key value is (<NULL>).
The statement has been terminated.

It would insert the contact info for Marry Doe just fine, but it would fail when trying to insert the contact info for Jack Doe. This is because the unique constraint can allow only one null value.

Possible Solution

So how we can ensure that our passport column can accept null values while ensure uniqueness for the non-null value? With SQL Server 2008 and above, one possibility is by using the unique filtered index. A quick demo for this one:

IF (OBJECT_ID('dbo.ContactInfo') IS NOT NULL)
BEGIN
    DROP TABLE dbo.ContactInfo
END

CREATE TABLE dbo.ContactInfo (
    ContactID INT PRIMARY KEY IDENTITY (1,1),
    ContactName VARCHAR(100) NOT NULL,
    PassportNumber VARCHAR(25) NULL);

CREATE UNIQUE INDEX  UI_PassportNumber
ON dbo.ContactInfo(PassportNumber)
WHERE PassportNumber IS NOT NULL

Let’s try to see what happen if we try to enter contact info with duplicate passport number:

INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('John Doe', 'B1234567890');
INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('Jane Doe', 'B1234567890');

We would get the following messages:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.ContactInfo' with unique index 'UI_PassportNumber'. The duplicate key value is (B1234567890).
The statement has been terminated.

That works as expected. Only the record for John Doe that got inserted.

Now let’s try to see what happen if we try to enter multiple contact info with blank passport number:

INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('Mary Doe', NULL);
INSERT INTO dbo.ContactInfo (ContactName, PassportNumber) VALUES ('Jack Doe', NULL);

We would get the following messages:

(1 row(s) affected)

(1 row(s) affected)

Both records are being inserted just fine, which what we are expecting.

Conclusion

We can use unique filtered index as a way to enforce uniqueness on filtered data. In the example above, it is to enforce uniqueness on a not null value on the passport number column. There is one caveat. Unlike unique constraint/index where it can be referenced by a foreign key constraint, unique filtered index can’t be referenced by a foreign key constraint.

So if we continue on from the above examples and try to create a new table named PassportVisit with the following definition:

IF (OBJECT_ID('dbo.PassportVisit') IS NOT NULL)
BEGIN
    DROP TABLE dbo.PassportVisit
END

CREATE TABLE dbo.PassportVisit (
    PassportNumber VARCHAR(25) FOREIGN KEY REFERENCES dbo.ContactInfo(PassportNumber),
    DateofVisit DateTime,
    CountryofVisit VARCHAR(50))

So we are trying to reference the passport number column on the passport visit table to the passport number on the contact info table. This will be successful if we have a unique constraint/index. But it will fail if we have unique filtered index. It will fail with the following messages:

Msg 1776, Level 16, State 0, Line 6
There are no primary or candidate keys in the referenced table 'dbo.ContactInfo' that match the referencing column list in the foreign key 'FK__PassportV__Passp__73B0DA39'.
Msg 1750, Level 16, State 0, Line 6
Could not create constraint. See previous errors.

SQL Server Agent Job Last Run Date and Time from Sysjobsteps Table

A couple of weeks ago, I wrote a blog entry in regards to finding out the last successful run date and time of SQL Server Agent job by querying the sysjobhistory table in the msdb database. That blog entry can be found here. The sysjobhistory table stores the execution history of each SQL Server agent job and job steps.

There is another way in which you can get the last run date and time of the SQL Server agent jobs (well, kind of). You can do this by querying the sysjobsteps table in the msdb database. The sysjobsteps stores information of each SQL Server job step, including the last_run_outcome (whether the job step execution failed, succeeded, retry, canceled or unknown), the last_run_date and the last_run_time. Similar to the run_date and run_time columns in the sysjobhistory table, the last_run_date and the last_run_time columns in sysjobsteps have integer data type. Thus, to convert it to date and time, we can use the agent_datetime system function in the msdb database. Unlike, the sysjobhistory, there is no step 0 in the sysjobsteps table, which would give the overall job outcome.

An sample query that we can use to retrieve the last run date and time for each SQL Server job steps:

select j.job_id,
  j.name,
  js.step_id,
  js.step_name,
  last_run_outcome = case when js.last_run_outcome = 0 then 'Failed'
        when js.last_run_outcome = 1 then 'Succeeded'
        when js.last_run_outcome = 2 then 'Retry'
        when js.last_run_outcome = 3 then 'Canceled'
        else 'Unknown'
       end,
  last_run_datetime = msdb.dbo.agent_datetime(
       case when js.last_run_date = 0 then NULL else js.last_run_date end,
       case when js.last_run_time = 0 then NULL else js.last_run_time end)
from msdb.dbo.sysjobs j
  inner join msdb.dbo.sysjobsteps js
   on j.job_id = js.job_id;

The last_run_outcome can be a bit misleading. If the job step has not been run, the last_run_outcome is 0, which indicates that the job step has failed on its last run.

Purging the SQL Server agent history (see my blog post) would not remove the last_run_date and last_run_time value from the sysjobsteps table.

Purge SQL Server Job History

The msdb database stores SQL Server job information, including the job and job steps history. The job history information can grow really fast, especially if you have a lot of SQL Server jobs which run frequently. In turn, this can cause the msdb database to grow in size rather quickly. It is a good idea to purge old SQL Server job history on regular basis. You might want to check with your company policy or legal team on how much of job history you need to keep.

How do you go about purging the SQL Server Job History? There are several ways in which you can do this.

sp_purge_jobhistory

The sp_purge_jobhistory stored procedure in msdb database can be used to delete the SQL Server agent job history. It accepts 3 input parameters:

• (Optional) @job_id - the SQL Server job id of the job history that you want to delete

• (Optional) @job_name - the SQL Server job name of the job history that you want to delete

• (Optional) @oldest_date - delete SQL Server job history that is older than the @oldest_date

One thing to know is that when running the sp_purge_jobhistory, you can specify the job_id or job_name or neither of them. But you can not specify both the job_id and job_name. If you do, you will most likely get the following error message:

"Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25
Supply either @job_id or @job_name to identify the job."

Some sample usage:

To delete all job history:

exec msdb.dbo.sp_purge_jobhistory;

To delete all job history of SQL Server job named "Backup TestDB":

exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB';

Alternatively:

declare @job as uniqueidentifier;
set @job = (select job_id from dbo.sysjobs where [name] = 'Backup TestDB');
exec msdb.dbo.sp_purge_jobhistory @job_id = @job;

To delete job history that is older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @oldest_date = @jobdate;

To delete job hisotry for SQL Server job named "Backup TestDB", which are older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB', @oldest_date = @jobdate;

It is pretty straight forward. To do this on regular basis, you can create a SQL Server agent job to run the sp_purge_jobhistory with the appropriate input parameter(s) on a scheduled time.

SQL Server Agent Properties

If you go to the SQL Server Agent Properties window, you have the option to "remove agent history". Here, you need to specify the cut-off time.

If you try to script this out, you will see that it is generating the following script:

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2013-02-12T22:54:55'

So basically it is executing the same sp_purge_jobhistory stored procedure on the background. One thing to remember about purging the job history this way is that it is just one time only. It will not actually create any SQL Server Agent that would automatically delete job history that are older than 4 weeks.

syspolicy_purge_history SQL Server Job

By default, if you are using SQL Server 2008, 2008 R2 or 2012, you might see the syspolicy_purge_history SQL Server agent job. The job is scheduled to run daily at 2 AM. This SQL Server job would look into the Policy Management "HistoryRetentionInDays" property (You can get to this window by going to Management on the Object Explorer and then right click Policy Management and select Properties).

By default, the HistoryRetentionInDays is set to 0, which means that it will not delete any SQL Server job history. If you set it 30, then on daily basis at 2 AM, it will delete SQL Server job history that are older than 30 days.

Last Successful SQL Server Agent Job Run Date and Time

SQL Server stores SQL Server Agent job history in sysjobhistory table within the msdb database. We can query the table to get the date and time of last successful SQL Server agent job(s) run. The table stores each job step history, where step_id 0 for each job actually give the overall job outcome.

The sysjobhistory table also stores each step's run date and time. However, they are being stored in separate columns, run_date and run_time. Also instead of having date and/or time data type, they have integer data type. But don't worry, there is a system function in msdb database, called agent_datetime, which can be used to convert those run_date and run_time into a datetime data type.

So, to get the date and time of the last successful SQL Server Agent job(s) run, we can use the following script:

WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status)

SELECT    j.job_id,
          j.name,
          jh.run_status,
          jh.last_run_time
FROM      msdb.dbo.sysjobs j
          LEFT OUTER JOIN jobhistory jh
          ON j.job_id = jh.job_id
ORDER BY j.name ASC

The script should be pretty straight forward. Basically, first we build a CTE to get the last successful time of each job from the sysjobhistory table. We do this by filtering those SQL Server agent jobs that have run status equal to 1 (success) for job step with id 0. Once we do that, we then create a join of the sysjobs table (which contains information such as the job name), with the CTE.

Installing 32-bit SQL Server on 64-bit Windows (x64)

Looking at the hardware and software requirements for SQL Server 2008, 2008 R2 and 2012 from Microsoft site, we should be able to install 32-bit version of SQL Server onto 64-bit (x64) Windows operating system.

If you have installation media or ISO image that contains both 32-bit and 64-bit version of SQL Server 2008, 2008 R2 or 2012, here’s how you can install the 32-bit SQL Server on 64-bit Windows (x64). When you insert the installation media, or mount the ISO image, in some cases you might need to double click on the setup.exe, you will be presented with the SQL Server Installation Center. Click on the Options link on the left.

You should then be able to select to install the 32-bit (x86) of the SQL Server.

You can kick off the 32-bit installation process, by running the following command:

setup.exe /action="install" /x86=true

As a matter of fact, you can use the X86=true in the command line or answer file if you want to have unattended installation of SQL Server 2008, 2008 R2 or 2012.

Once it has been installed, when you check the SQL Server version using:

SELECT @@VERSION

For SQL Server 2012 installation, you should get something like the following:

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)Feb 10 2012 19:13:17Copyright (c) Microsoft CorporationEnterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)

Noticed that it is WOW64 (Windows 32-bit on Windows 64-bit).

Deadlock Graph XML from Extended Events in SQL Server 2008

We were dealing with some deadlock problems in our database. However, unfortunately we did not have trace flag 1204 and/or trace flag 1222 turned on. Fortunately there is a way to retroactively retrieved deadlock information starting with SQL Server 2008 using Extended Events.

Someone forwarded an article written by Jonathan Kehayias (blog | twitter), an expert in Extended Events, regarding this. The article can be found here. It is a great article. In the article, Jonathan showed us the way to retrieved the deadlock graph from Extended Events.

Jonathan also mentioned in the article that there is a bug in the output of deadlock graph from the Extended Events, in which it is not a valid XML. There is a problem with missing end tag for the <victim-list> node. He then provided a work-around script in which he utilizes the replace function on the deadlock information text from the Extended Events. Then after that we could cast the text as XML.

I went ahead and try that script. When I tried it, I got the following error message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 5, character 17, end tag does not match starting

Upon further review, it looks like the script works on deadlocks that have exactly 1 <victimProcess> node. However, if there is empty <victimProcess> node or multiple <victimProcess> nodes, the script would not work. In the case of multiple <victimProcess> nodes, for some reasons, the Extended Events only closed the last <victimProcess> node, but all the other ones are missing the end tag. Thus, I made a small tweak to Jonathan’s script to handle empty <victimProcess> node or multiple <victimProcess> nodes. Here’s the script:

SELECT    CAST(REPLACE(

            REPLACE(
                REPLACE(

                    REPLACE(

                        REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

                        '<victimProcess', '</victimProcess><victimProcess'),

                    '<victim-list>', '<deadlock><victim-list><victimProcess>'),

                '<process-list>', '</victim-list><process-list>'),

            '<victim-list/>', '<deadlock><victim-list>'),

        '<victimProcess>' + CHAR(10) + SPACE(2) + '</victimProcess>', '') AS XML) AS DeadLockGraph

FROM

(SELECT CAST(target_data as XML) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

WHERE name = 'system_health') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Querying SQL Server Error Log

SQL Server error log offers good way to obtain information when troubleshooting SQL Server related problem. A while back, I wrote a blog post about reading SQL Server error log using Microsoft Log Parser. That blog post can be found here. There are many ways in which you can query the SQL Server error log. One of them is using the sys.sp_readerrorlog stored procedure. This stored procedure can be located in the master database. It accepts 4 input parameters:

1. @p1 – integer: This parameter is to specify which error log to read. SQL Server error log would rollover. By default, SQL Server error log would keep a file for the current log and maximum 6 of archived logs (this setting can be changed easily), ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4, ERRORLOG5 and ERRORLOG6. ERRORLOG is where SQL Server stores the current error log, ERRORLOG.1 is where SQL Server stores the most recent archived, etc. If we put 0 or null on this parameter, we are querying the current error log (ERRORLOG). 1 would refer to ERRORLOG.1. The same concept would apply to SQL Server Agent error log.
2. @p2 – integer: This parameter is to specify if we want to query the SQL Server Error Log or the SQL Server Agent Error Log. If we enter 1 or null, we are querying the SQL Server Error Log. However, if we enter 2, then we are querying the SQL Server Agent Error Log.
3. @p3 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log.
4. @p4 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log. If we enter a word or phrase on @p3 parameter and enter another word or phrase on @p4 parameter, the stored procedure should return error log entries that contain both words/phrases (AND operator). If we leave @p3 blank but enter a word or phrase on @p4, the stored procedure would not filter the error log. It will ignore the @p4 parameter filter.

Some Usage Examples

The following would return all entries on the current SQL Server error log (ERRORLOG):

EXEC sp_readerrorlog

or:

EXEC sp_readerrorlog 0

or:

EXEC sp_readerrorlog NULL, NULL, NULL, NULL

The following would return all entries on the current SQL Server Agent error log (SQLAGENT.OUT):

EXEC sp_readerrorlog 0, 2

The following would return entry from SQL Server error log when the SQL Server was starting the msdb database (in this case it was part of the server start up):

EXEC sp_readerrorlog 0, 1, 'starting', 'msdb'

This would returns:

Wait, There’s more…

If we look at the sp_readerrorlog stored procedure code closely, it is actually calling the xp_readererrorlog extended stored procedure. The xp_readerrorlog actually accepts more input parameter than the 4 input parameters described above. The following blog article described the parameters that xp_readerrorlog would accept. Basically it would accept 3 additional parameters:

1. Log date from range – Date time: this parameter would help to filter the log entries from a specific time period.
2. Log date to range – Date time: this parameter would help to filter the log entries to a specific time period.
3. Ascending or Descending – Varchar: this parameter can be use to specify the sorting order of the log entries based on the log date. Enter ‘asc’ for ascending order, and ‘desc’ for descending order.

So, for example, if we want to get the list of current SQL Server error log entries between 6:27 PM and 6:28 PM today (7th October 2012), and list the log entries in the descending log date order, I can use the following query:

EXEC xp_readerrorlog 0, 1, NULL, NULL, '2012-10-07 18:27', '2012-10-07 18:28', 'desc'

Related Notes:

• Querying SQL Error Log with Microsoft LogParser
• Querying SQL Server Agent Error Log with Microsoft LogParser

SQL Saturday #142–Waltham, MA

SQL Saturday is a one-day, jam-packed, educational event organized by and for local SQL Server Professionals, with the help of corporate and personal sponsors. It is free of charge, although you will need to register to reserve your spot. SQL Saturday #142 is going to be held in Waltham, Massachusetts. Here’s the detail:

What: SQL Saturday #142
When: Saturday 19th May 2012 8:30 AM – 5:30 PM (Eastern Time)
Where: Microsoft Office, 201 Jones Rd., Waltham, MA 02451 (Map)

To register, please go to http://www.sqlsaturday.com/142/eventhome.aspx. Currently, there are about 30 sessions scheduled for SQL Saturday #142. For a complete schedule and description of the sessions, you can check it out at http://www.sqlsaturday.com/142/schedule.aspx.

Why You Should Attend?

If you are in the area, I would recommend you to attend this event. There are 3 main reasons on why you should attend this event:

1. Educational – SQL Saturday is a great place to learn different aspects of SQL Servers. If you look at the list of sessions for this SQL Saturday, you will see a wide variety of topics related to SQL Server. You can learn anything from SQL in the cloud (SQL Azure) to the newly released SQL Server 2012. There are also sessions on SQL Server Integration Services, SQL Server Reporting Services and SQL Server Analysis Services. Each session is scheduled to run for about 1 – 1.5 hours. Obviously, you will most likely not be able to master the session topic within that short time period. However, it can help to get your feet wet on the subject.
2. Networking – This event is a great way to meet fellow local SQL Server Professionals. We can always learn from each other experiences.
3. Meet with Vendor(s) – There are going to be a few vendors that are coming to this SQL Saturday. This is a chance to see some of their product demo, ask questions about their product(s) and possibly see if any of their product(s) can help your organization.

Not in the Area?

SQL Saturday might be coming to a city near you. You should check SQL Saturday site for a list of upcoming SQL Saturday events in various location.