In Recovery Status

Sometimes a very large database (VLF) stalls and or takes forever and a day to perform a 2 minutes task. In these fun times we restart the server. It gets funner when the server is back online and your database is IN RECOVERY.

This generally happens prior to the server restart, you still have large transactions running (i.e. millions/billions or records being DELETE/UPDATE/INSERT).

To check on the status of your database simply run

EXEC sys.sp_readerrorlog 0, 1, ‘Your_Database_MDF_Name’

At this point, you can’t use sp_helpfile because your database is inaccessible therefore navigate to where the mdf file is located. Most of the time, the mdf file is the same name as your database.

When you run the command above, in your message tab you’ll see the percentage of completion and how many seconds to go.

 

WAITFOR it …

There are times when you want to delay a script such as sending yourself a reminders or to run a job/stored procedure/etc.

Wait by Time

BEGIN
             WAITFOR TIME ’01:10′;
PRINT ‘This message will print at 1:10 am’;
END;

Wait by Delay

BEGIN
             WAITFOR DELAY ’01:00′;
PRINT ‘This message will print in one hour’;
END;

SQL Back Door – DAC (Dedicated Administrator Connection)

There will be a time or more in your life where SQL Server becomes unresponsive and stops working. You can try to remote desktop into the server with no luck– or take the Rambo route and hard restart the server or have IT do it. This is where DAC (Dedicated Administrator Connection) comes into play. DAC allows one back door SA connection into the SQL Server. To use DAC, you first have to turn it on and here’s how you do it.

GUI Method

First open up SQL Management Studio

Step 1. Right click on your server

Step 2. Click on Facets

Once the Facets windows show up do the following

Step 1. Under the Facet drop-down-list, pick Surface Area Configuration

Step 2. Select RemoteDacEnabled and from the drop-down-list pick True

Step 3. Click OK

That’s all to it

If you prefer the T-SQL method

USE master

GO

sp_configure ‘remote admin connections’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

How to log onto the server using DAC

Step 1: Open up SQL Server Management Studio (SSMS)

Step 2: On the tool bar, click on Database Engine Query


Step 3: In the Server Name box, type in ADMIN: before your server name.

Step 4: Click Connect

Once connected, you can then troubleshoot your SQL Services. If you can’t log in, it means two thing.

1. You don’t belong in the SysAdmin group
2. A SysAdmin is already logged onto the server
   

As always, I hope this helps.

Microsoft Update Catalog

If you want to find out if you’re on the latest Service Pack for Microsoft SQL Server here’s a direct link to the Microsoft Update Catalog. The link will take you to MS SQL 2014. If you want other version just do a search for your version.

Add Email to SSIS for troubleshooting

When your SSIS package fails, wouldn’t it be nice to receive an email with as much details as possible. Here are steps to setup this hidden gem.

Step 1. Setup the Event Handlers

1. Click on the Event Handlers tab.
2. Under the Event handler, select OnError
3. Under the Executable drop-down-list, select the top most from the tree
4. Click on the link

Step 2. Add the Send Mail Task

1. From the SSIS Toolbox, drag the Send Mail Task into the Even Handlers canvas

Step 3. Setup the Email connection

1. From the Connection Manager, right click in the white section and click on New Connection

Select SMTP and click Add…

Fill in the Name and SMTP server and click OK.

Step 2. Open up the Send Mail Task by double clicking on it

1. Click on the Mail tab
2. SmtpConnection: Click on the drop-down-list and select the connection you previously created
3. Fill in the From, To fields with your emails. You can also use Expression for this but in this example, we’re going to hard code the emails

Click on the Expressions tab

You’ll want to do this step for the Message Source and Subject

Copy and Paste these to the respective Expression. When you’re done, click OK.

Message Source

“Machine Name: ” + (DT_STR, 2000,1252)  @[System::MachineName] + “\r\n” +

“Package Name: ” + (DT_STR, 2000,1252)  @[System::PackageName] + “\r\n” +

“User Name: ” +  (DT_STR, 2000,1252)  @[System::UserName] + “\r\n” +

“Source Name: ” + (DT_STR, 2000,1252)  @[System::SourceName] + “\r\n” +

“Source Description: ” +  (DT_STR, 2000,1252) @[System::SourceDescription] + “\r\n” +

“Error Code: “+ (DT_STR, 2000,1252) @[System::ErrorCode]   + “\r\n” +

“Error Description: ” + (DT_STR, 2000,1252)  @[System::ErrorDescription] + “\r\n”

Subject

“** Error Msg from OnError Event Handler *** ” +  ” SSIS Package Name:  [” +  @[System::PackageName] +  “] ” + “; Machine Name: ” + @[System::MachineName]

Proof is in the pudding!

Here’s a look at how it looks like when an error occurs. You’ll get an email telling you which Package and Task failed. Keep in mind that you no longer need to go to SQL Job History, SSIS Catalog,  Event Viewer to view the errors. The errors will come to you via email! Sweet Jesus!

As always, I hope this helps.

Fail-Safe

There are times when you’re on vacation, kids soccer games, family outing, church, etc where you’re inaccessible. Thanks to Murphy’s law something bad will happen to the database server and alerts will fire off and you won’t receive those notifications because you’re enjoying life for once. This is where you want to enable Fail-Safe– a.k.a. your backup buddy.

As TechNet puts it, “when things go wrong with SQL Server notifications, operators are not notified and problems might not be corrected in a timely manner. As a precaution, you might want to designate a fail-safe operator. A fail-safe operator is notified when a SQL Server Agent cannot access system tables in the msdb database (which is where operator definitions and notification lists are stored), when all pager notifications to designated operators have failed, or when the designated operators are off duty (as defined in the pager schedule). ”

The steps to enable this tucked away feature is as follows.

Open up Management Studio -> Right Click on SQL Server Agent -> Click on Properties

Click on “Alert System”

Tick the “Enable mail profile” as you’ll need this for the Fail-Safe to work.

I’m assuming you already have a Mail Profile setup so at this point, select your Mail System/Profile from the drop-down-list. Once this is done, the Fail-Safe operator will highlight.

Tick on “Enable fail-safe operator” and select your Operator from the drop-down-list. Then Tick on how you want that operator to receive the messages and then Click OK

As always, I hope this helps.

FREE SQL System Reports!

Are you a DBA that has no funding for fancy SQL System Health Reports like RedGate, SQLTools, Foglight, SentryOne, etc? Do you hate looking at dmv queries because there are so many of them to remember? Well did you know that SQL Management Studios comes with System Health Reports? Below are steps to get System Health Reports at no cost.

To view the System Reports

Right click on “your Server” -> Reports -> Standard Reports -> “select a report”

To view the Database Specific Reports

Right click on “your Database” -> Reports -> Standard Reports -> “select a report”

Each report can be expanded for detail information.

As always, hope this helps!

 

SQL Server Alert System: ‘Severity Level 020: Error In Current Process – High’

I get this very vague Alert in a weekly basis and it was driving me nuts. I reached out to the IT Network/OS for months to see if they could pin down the issue and were not able to find anything on their logs. I provided an Event Handler Email Task to the developers to put inside their SSIS Packages and when I would get Severity Level 020 Alerts, I would now see their Packages. The common errors are

• Named Pipes Provider: The specified network name is no longer available.
•  

  A fatal error occurred while reading the input stream from the network. The session will be terminated

• The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <named pipe>]
• Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.
• SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The “OLE DB Destination.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “OLE DB Destination.Inputs[OLE DB Destination Input]” specifies failure on error.
• Communication link failure

It did not matter when the Job ran (on a schedule, manual, Visual Studio Data Tools), it would still randomly fail. Most of these SSIS are straight forward where they pull 5 million records from Teradata to SQL– Just a straight SELECT * FROM Table (these tables are the final product of an ETL performed in Teradata). Then one day, I get a new error “Communication link failure” so I Googled it and here’s where it lead me to https://support.microsoft.com/en-us/help/942861/general-network-error-communication-link-failure-or-a-transport-level

I ran the command “Netsh int tcp show global” to see the following

In lamens term, Receive-Side Scaling is like the waiter directing traffic at a Food Buffet. The line can only move as fast as the waiter can find you a table. If you disable Receive-Side Scaling, it’s Free For All. You have eyes and the stranger next to you have eyes and you both can find an empty table without the help of the waiter therefore there’s no wait or less wait. Here’s a link to a more technical term https://docs.microsoft.com/en-us/windows-hardware/drivers/network/introduction-to-receive-side-scaling

After I disabled Received-Side Scaling (Network Adapter -> Properties -Configure.. -> Advanced -> and increased the SQL (Properties -> Advance) Remote Login Timeout from 20 to 35,  the SSIS Packages began to run without Severity Level 020.

I hope this helps someone that is experiencing the same issue.

Modify Column Data Type

Syntax:
USE [YourDatabaseName]
GO
ALTER TABLE YourTableName
ALTER COLUMN column_name datatype;

Senario:
I have a database call Afterowl with a table call Birds. In my Birds table, I want to change TypeOfBird data type from NVARCHAR(10) to VARCHAR(100).

Example:
USE Afterowl
GO
ALTER TABLE Birds
ALTER COLUMN TypeOfBird VARCHAR(100)

List Calendar Dates

I use this to bounce against my weekly ETL’s to make sure that every day of the week data is captured. This helps me quickly identify a Calendar date with no ETL date which means no data was pulled.

DECLARE @start_date DATETIME= ‘2017-01-01’;
DECLARE @end_date DATETIME= GETDATE();
WITH CalendarDates
AS (
SELECT CAST(@start_date AS DATE) AS Calendar_Day
UNION ALL
SELECT DATEADD(DAY, 1, Calendar_Day)
FROM CalendarDates
WHERE Calendar_Day < @end_date)

SELECT Calendar_Day,
DATENAME(dw, Calendar_Day) AS Day_of_Week,
DATENAME(week, Calendar_Day) AS Week_Number
FROM CalendarDates OPTION(MAXRECURSION 0);