Issue: Long Running Database Restore and a Fix

I have a new QA server with a pretty slow restore operation. I found PREEMPTIVE_OS_WRITEFILEGATHER is very high by using Glenn Berry’s query
and I recalled from previous readings about this issue.
We need to assign SQL Server service account to perform volumn maintenance tasks.
The folllowing is how to and some references to explain why.

How to grant service account to “PERFORM VOLUME MAINTENANCE TASKS”

From Window machines search secpol.msc.

Use secpol.msc (search from window search box or search Gpedit.msc to drill down to Local Policies …)

From UI:
WIn 2012 Administrative Tools ->Local Security Policy>>Local Policies -> User Right Assignment -> Perform Volume Maintenance Tasks
Right click on it and click Add User or Group to add the SQL Server Service account running on your instance.

Restart SQL Server service to make the change into effect.

http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/
http://www.brentozar.com/archive/2014/02/preemptive_os_writefilegather-and-instant-file-initialization-ifi/
http://sqlserverperformance.wordpress.com/2014/04/18/sql-server-diagnostic-information-queries-for-april-2014/

From above link:
(Glenn Berry’s check wait query)

   

;WITH Waits
AS (SELECT wait_type, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS [wait_time_s],
	CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(12,2)) AS [pct],
	ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
	FROM sys.dm_os_wait_stats WITH (NOLOCK)
	WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE',N'SLEEP_TASK',
			                N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'LOGMGR_QUEUE',
			                N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT',
			                N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT',
			                N'DISPATCHER_QUEUE_SEMAPHORE' ,N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'XE_DISPATCHER_WAIT',
			                N'XE_DISPATCHER_JOIN', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'ONDEMAND_TASK_QUEUE',
			                N'BROKER_EVENTHANDLER', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'DIRTY_PAGE_POLL',
			                N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'SP_SERVER_DIAGNOSTICS_SLEEP',
							N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
							N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE',
							N'PWAIT_ALL_COMPONENTS_INITIALIZED')),
Running_Waits 
AS (SELECT W1.wait_type, wait_time_s, pct,
	SUM(pct) OVER(ORDER BY pct DESC ROWS UNBOUNDED PRECEDING) AS [running_pct]
	FROM Waits AS W1)
SELECT wait_type, wait_time_s, pct, running_pct
FROM Running_Waits
WHERE running_pct - pct <= 99
ORDER BY running_pct
OPTION (RECOMPILE);



 
Advertisements

Send Alert Email when SQL Server Agent Job Failed

1. Management>>Database Mail>>Configure your database mail and create profile ;
2. In Job Properties >> Notification >> When the Job Completes >>Email to one Profile –WHen Job Failed;
3. From Properties of SQL Server Agent, Choose Alert System >> Enable mail profile –Mail System and Mail Profile;
4. Restart SQL Server Agent Service to make the changes work!


Use msdb.dbo.sp_send_dbmail to send email to Multiple Recepients

When we need to have multiple recipients for @receipient variable, we cannot directly put them into one line directly.
Instead we can use variables to string them email addresses together and separate them with semicolon.
We can use another two parameters to hold more email addresses:@copy_recipients and @blind_copy_recipients.
We can use the variables for these two parameters to have more than one recipients.

   

 


declare @Email1 varchar(30) ='email1@gmail.com'
declare @Email2 varchar(30)='email2@hotmail.com'
declare @Email3 varchar(30)='email3r@mycompany.com

declare @Tos varchar(max);
select @Tos = @Email1 + ';' + @Email2 + ';' + @Email3;
 

EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'myProfile'
  , @recipients =@Tos
  ,@copy_recipients='dev@mycompany.com'
  ,@blind_copy_recipients='boss1@mycompany.com'
  , @subject ='This is subject line'
  , @body = 'Email body goes here!'   
;
   


 

Use SFTP (WinSCP) within SSIS

Download WinSCP from: http://winscp.net/eng/download.php

You can add a Script Component in your SSIS package and use the sample code below:

public void Main()
{
// TODO: Add your code here
try
{

const string logname = “E:\\myFolder\\ftp_log.xml”;
//// Run hidden WinSCP process
Process winscp = new Process();
winscp.StartInfo.FileName = “C:\\Program Files (x86)\\WinSCP\\winscp.com”;

winscp.StartInfo.Arguments = “/log=\”” + logname + “\””;
winscp.StartInfo.UseShellExecute = false;
winscp.StartInfo.RedirectStandardInput = true;
winscp.StartInfo.RedirectStandardOutput = true;
winscp.StartInfo.CreateNoWindow = true;
winscp.Start();

//// Feed in the scripting commands
winscp.StandardInput.WriteLine(“option batch abort”);
winscp.StandardInput.WriteLine(“option confirm off”);
winscp.StandardInput.WriteLine(“open myUser@sftp.mysftpsite.com”);
winscp.StandardInput.WriteLine(“put E:\\myFolder\\data\\myFiles.txt”);

winscp.StandardInput.WriteLine(“mv myFiles.txt myNewFile_” + System.DateTime.Now.Month.ToString() + “_” + System.DateTime.Now.Day.ToString() + “_” + System.DateTime.Now.Year.ToString() + “.csv”);
winscp.StandardInput.Close();

// Collect all output (not used in this example)
string output = winscp.StandardOutput.ReadToEnd();

// Wait until WinSCP finishes
winscp.WaitForExit();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception xxx) {
Dts.TaskResult = (int)ScriptResults.Failure;
}

}
}

From the WinSCP login client UI, you use myUser/mypass to connect your site and save your connection information as this:
myUser@sftp.mysftpsite.com
And your package should be ready to go. You can go WInSCP website to find other configuration as well.
The last setup is to make sure the package can run as an agent job. What I did was to connect to my ftp site and saved the credentials (user/pass) on my local machine and export the settings from WinSCP:>>Tools>>Export/backup Configuration to WinSCP.ini file and I put this file to WinSCP root folder and gave the service account user access permission to access this folder. You can modify path information within this WinSCP.ini file.
Don’t forget to give the permission to the data folder for the account who is running the agent service!

After create the account, from Edit>>Advanced>>Directories check Remote directory and Local directory (working folder for the file).


Package with XMLDatasource Migrated to SSIS 2014 with Error: “the data source you are using does not support mixed content in elements..”

When I migrate an SSIS package which includes an XMLDatasource to import data from SSIS 2008 to SSIS 2014,
I ran into the following error:
“the data source you are using does not support mixed content in elements..”
We have no control for the coming XML file so I decide to move the logic into database
and use an Execute SQL Task to pull data from the coming XML file.

Here is the query I used for the SQL Task:


--Target table
CREATE TABLE [dbo].[SomeTable](
	[cat-code] [nvarchar](255) NULL,
	[class-code] [nvarchar](255) NULL,
	[subclass-code] [nvarchar](255) NULL,
	[ad-number] [nvarchar](255) NULL,
	[start-date] [nvarchar](255) NULL,
	[end-date] [nvarchar](255) NULL
	
) 


--Solution query

declare @tmp table (XmlCol XML)

INSERT INTO @tmp(XmlCol) 
SELECT * FROM OPENROWSET( BULK 'E:\X\data\myXMLfeed.xml', SINGLE_BLOB) AS x ;
 


 insert into  [dbo].[Sometable]

SELECT 
   p.value('(./cat-code)[1]', 'NVARCHAR(255)') AS [cat-code],
   p.value('(./class-code)[1]', 'NVARCHAR(255)') AS [class-code],
   p.value('(./subclass-code)[1]', 'NVARCHAR(255)') AS [subclass-code],
   p.value('(./ad-number)[1]', 'NVARCHAR(255)') AS [ad-number],
   p.value('(./start-date)[1]', 'NVARCHAR(255)') AS [start-date],
   p.value('(./end-date)[1]', 'NVARCHAR(255)') AS [end-date]     
FROM @tmp 
   CROSS APPLY XmlCol.nodes('/web-export/run-date/pub-code/ad-type/.') t(p)

 

SSIS Package to Archive File to a destination wiht a name appended with datetime stamp

When we have a package to copy file to a new location, we may want to keep different copy for each time. We can append a datetime stamp to the file name.
Here are a few steps to follow:

1. Create two variables for the package one for destination folder (Dest_Dir) and one for destination file name (Dest_File);
2. Create a new file connection from Connection Manager and use create file property and give it a name, any name;
3. From the property window of the connection just created: go to +Expression…:
Choose ConnectionString from Property Dropdownbox and use the following as expression:

@[Dest_Dir] + “\\” + @[Dest_File] + “_”
+ (DT_WSTR,4)DatePart(“yyyy”, GetDate()) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“mm”, GetDate()), 2) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“dd”, GetDate()), 2) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“hh”, GetDate()), 2) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“mi”, GetDate()), 2) + “.txt”

You may need to make it in one row:

@[Dest_Dir] + “\\” + @[Dest_File] + “_” + (DT_WSTR,4)DatePart(“yyyy”, GetDate()) + RIGHT(“0” + (DT_WSTR,2)DatePart(“mm”, GetDate()), 2) + RIGHT(“0” + (DT_WSTR,2)DatePart(“dd”, GetDate()), 2) + RIGHT(“0” + (DT_WSTR,2)DatePart(“hh”, GetDate()), 2) + RIGHT(“0” + (DT_WSTR,2)DatePart(“mi”, GetDate()), 2) + “.txt”

4. From a System Task Editor, you can copy file from your source to the destination we created above to archive
the file from source and rename it with datetime appended to it so we will not overwrite it next time to copy file to this folder.


Retrieve Report Definition (rdl file) from Report Server (SSRS).

You can use a migration tool from Microsoft to retrieve all report definition in .rdl.
Here is the download link:
http://www.microsoft.com/en-us/download/details.aspx?id=29560