Enable SSIS package logging with dated output filename

Had to outline this for an associate today, so I thought I'd share it here as well.  Just a quick method for adding logging to your SSIS packages, and including the date in the output filename.

Within VS, open your package and right click the Control Flow back panel, choose Logging:

This brings up 'Configure SSIS Logs: PackageName' dialog box, where ‘PackageName’ is the name of the package you are managing.  Check the box next to your package name in Containers.  Note at the bottom of the dialog it tells us we need to enable the logging in the tree view in order to configure logging. 

Now hit the ‘Details’ tab to choose your log details.  

My suggestions for detail selection:

     ALWAYS:                                OFTEN HELPFUL:

OnError                                 OnInformation

OnTaskFailed                         Diagnostic

OnWarning

After choosing the log details, go back to Providers and Logs tab and choose ‘SSIS log provider for Text files’ provider type.  Check the Name box, choose ‘Configuration’ and then <New connection…>.  

This opens a File Connection Manager Editor, where you choose 'Create file' and browse to your output folder directory, using a descriptive name for your package.  I always use packagename.txt.

Hit ‘Ok’ here, and again at the bottom of the 'Configure SSIS Logs: packagename' dialog box. Now you will see a packagename.txt entry down below in your Connection Managers.  That is your new log file connection manager.

Now, a ‘packagename.txt’ log file is created with every execution of your SSIS package ...which will be overwritten every time. To avoid that, we need to use a variable and an expression to dynamically include the date in the logfile name.  

For the variable, right click the Control Flow back panel and choose 'Variables'.  Scope will be filled in with your package name, but you must enter Name, Type and value, which should correspond to the path you want the log file written to, like this:

For the expression, right click your new log file connection manager and choose properties.  

Choose 'Expressions' in the returned properties and hit the box to open the 'Property Expressions Editor' dialog box.  Under Property, choose 'Connection String' and hit the box to open the 'Expression Builder' dialog box. Here you will paste either statement below into the Expression box and choose 'Evaluate Expression' to see the final format on your log filename.  If it's good, choose Ok and then you must Save your package.

packagename_log_YYYYMMDD.txt -

@[User::VarLogFolderPath]+ @[System::PackageName]+"_log_"+ (DT_WSTR,4)DATEPART("yyyy",GetDate()) +

RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +

RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2)+ ".txt"

packagename_log_YYYYMMDD_hhmm.txt -

@[User::VarLogFolderPath]+ @[System::PackageName]+"_log_"+ (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"

Of course, there are many other formats for outputting the datestamp into the log filenames, but those are the two I use most often.  

Hope to have helped!

How to schedule Agent job to run every 2 weeks

SQL Server Agent job scheduling is generally pretty easy, but I still have received this question many times.  I've even Googled it myself this morning, and am very surprised about all the inquiries on how to do this, and all of the different suggested solutions.  No, you don't need Powershell.  No, you don't have to create two jobs with two different schedules.  You don't even need two different schedules for one job...  It really is quite simple.  

For example, say we need to run a job every two weeks on Saturday.  You will create a new schedule, or edit your existing one to be like the one I've shown here.  The trick is with the Frequency.  Choose 'Weekly' and set it to recur every 2 weeks.  And, of course, be sure to check the Saturday box.  

That's it!  Now your Agent job will run every two weeks on Saturday, at 7AM.

Who dropped that table?

Who dropped that table?  Who altered that view?  Who added that column.... and so forth.  You'd like to think you'll always know what's going on in your databases, but unless NOBODY touches it but you, that's just not possible.  I can tell you war stories that will floor you!  Some other time with a glass of red...  Until then, this is a quick query to tell you who dropped or altered any object within any database on your server.   

       -- declarations

       DECLARE

              @current VARCHAR(255),

              @start VARCHAR(255),

              @index INT;

       -- find your trace path

       SELECT @current = path FROM sys.traces WHERE is_default = 1;

       SET @current = REVERSE(@current)

       SELECT @index = PATINDEX('%\%', @current)

       SET @current = REVERSE(@current)

       SET @start = LEFT(@current, LEN(@current) - @index) + '\log.trc';

       -- query on the eventclasses for delete and alter

       SELECT CASE EventClass WHEN 164 THEN 'Object:Altered'

              WHEN 47 THEN 'Object:Deleted' END [Action],

              DatabaseName,

              ObjectName,

              HostName,

              ApplicationName,

              LoginName,

              StartTime

       FROM::fn_trace_gettable(@start, DEFAULT)

       WHERE EventClass IN (164,47)

       AND EventSubclass = 0

       AND DatabaseID <> 2

 Your end result will vary.. but this is my output from one of my instances:

Now you go talk to Joe Shmo or Ms. Lenovo and figure out why they're editing/dropping objects in your database.  😏

See these for details on fn_trace_gettable and the object events I used in my query:

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-gettable-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/objects-event-category?view=sql-server-2017

How long will the CheckDB run?

For the most part, how long it will run depends on how large the database is, and how many objects are being checked.  More objects = more time.  There are also certain arguments that you can use to make it run faster and slower, like TABLOCK and PHYSICAL_ONLY respectively, but that's for another discussion.  In this post I just want to show you how to determine how long a checkdb is going to run before it is complete.

I ran the integrity check for the first time on a 1.7TB database over the weekend, and it ran for 23:12:44.  Pretty much a full day.  I knew it would be timely, but I wasn't expecting 24hrs.  I ran this statement about 16 hours into it, to find the estimated completion time:

       SELECT 

          session_id,

          request_id,

          percent_complete,

          estimated_completion_time,

          DATEADD(ms,estimated_completion_time,GETDATE()) [EstimatedEndTime],

          estimated_completion_time/(1000*60) [EstimatedCompletionTimeInMinutes],

          start_time,

          status,

          command

       FROM

          sys.dm_exec_requests

       WHERE

          session_id = 65 --< this is the spid for your actively running checkdb

And this was my output:

     

Not as clear as I'd like, but it told me I was about 69% complete and gave me the estimated end time -- both in minutes, as well as the actual estimated time it will complete.  In this case, it estimated 7:46AM, but it actually completed at about 7:02AM.  Because it was so long running, this information was very helpful.  I was able to reach out and let a few people know when it would be complete, rather than just saying 'I don't know'.

NOTE:  This doesn't just apply to the CHECKDB.  As per this reference,  the sys.dm_exec_requests DMV returns details about any request actively running on the server.  Can be quite helpful!

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-2017

How to query for locking and blocking details?

I've got a new customer who is having a lot of blocking problems on his database, but he didn't have any real details about where the blocking was occurring.  This query is something that I use to return useful aggregate details on where the blocking has occurred since the last service restart.

USE YOURDBNAME;

SELECT

       db_name(database_id) [Database],

       object_name(object_id) [Table],

       row_lock_count + page_lock_count [#Locks],

       row_lock_wait_count + page_lock_wait_count [#Blocks],

       row_lock_wait_in_ms + page_lock_wait_in_ms [BlockWaitTime],

       index_id [IndexID]

FROM

       sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)

WHERE

       row_lock_wait_count + page_lock_wait_count > 0

       AND object_name(object_id) IS NOT NULL

ORDER BY

       BlockWaitTime DESC

Your output will be something like this:

You can see it gives you a quick visual on where the blocking is happening.  Pretty helpful, but don't take it verbatim.  The lock and block counts are accurate, but there are some known inaccuracies with the block wait time (see below link).  In my opinion, the query is still accurate enough to give you an idea of where your blocking is occurring, and let you know where to look further.  Hope you find it useful!

https://support.microsoft.com/en-us/help/3170042/dmv-sys-dm-db-index-operational-stats-returns-incorrect-range-scan-cou

How to find out who installed SQL Server?

Odd question, but it came up for me today.  Today a customer told me that I installed both SQL Server 2016 and 2017 on their instance, and they wanted me to remove v2017.  um... No.  Why would I do that?  So I took a look, and there were two full installations side by side -- but it definitely was not done by me.  I cleaned it up, and also performed the steps below, so that I could let them know this was not done by me.

First, go to this location to find your setup bootstrap logs:

At that location, you should search for this file:   sql_engine_core_inst_Cpu64.log

Open up that file and search for ' logonuser '... and there you go!  Of course, this is my installation log and not my customer's, but you get the point.  The LogonUser value corresponds to whomever was logged on while the installation is performed.

Happy SQL Servering!   ☺☺☺

When was the Windows server last booted?

Many times I must look into sys.databases to determine the last SQL Server service restart.  The fastest way to do that is just look at your tempdb create date:

SELECT * FROM sys.databases

--or

SELECT create_date FROM sys.databases WHERE [name] = 'tempdb'

But, today I need to know when the Windows server was rebooted.  Very easy, and I wanted to share it with you.  Open up a cmd prompt and type 'net statistics server'.  

Voila!  See the 'Statistics since 10/18/2018 1:03:54 PM'  -- that is your last server boot time.

Happy SQL Servering!