Be Cautious About Invoke-SqlCmd Bug

With PowerShell becoming more popular in daily DBA work, I’d like to mention one huge ugly bug I recently encountered to, IMHO, an corner-stone cmdlet in SQLPS module, invoke-sqlcmd.

I mentioned this here http://stackoverflow.com/questions/33271446/invoke-sqlcmd-runs-script-twice (and also in MS forum)

But I’ll give a more simplified version here to verify this bug (for SQLPS module of SQL 2012/SQL 2014)

First, let’s create two test tables in TestDB

use testdb
--drop table dbo.s, dbo.t

create table dbo.s (id int identity primary key, b varchar(50));
create table dbo.t (id int primary key, b varchar(50));

Ok, in PowerShell, now let’s just run two t-sql statements, one insert into each table, with the 2nd statement firing an error (I purposely make it error out by not populating the primary key column)

import-module sqlps;
invoke-sqlcmd -Server localhost -Database TestDB -Query "insert into dbo.s (b) values ('Hello'); insert into dbo.t (b) values ('World')"

Now you will see the following error

error

Now, if we check what is in table dbo.s, by select * from dbo.s, we will see two records instead of the expected one record as shown below

result

To me, this bug alone will stop me from using this invoke-sqlcmd, yet without which, SQLPS seems crippled in many cases.

When I try to report this as a bug in MS Connect, I find someone has reported a similar issue before in 2013, yet, MS closed it as “Won’t Fix”, I guess this is another bad decision from some PM who really did not appreciate the importance of this bug.  The MS connect item is here

https://connect.microsoft.com/SQLServer/Feedback/Details/811560

I hope those DBAs who are keen on using SQLPS, please vote it up to let MS change their weird previous decision.

Once SQL 2016 is RTM’ed, I will test it again and see how it goes and report back here.

Posted in Uncategorized | Tagged , | 2 Comments

Database Administration via CrowdSourcing

According to Wikipedia:

 Crowdsourcing is the process of obtaining needed services, ideas, or content by soliciting contributions from a large group of people, and especially from an online community, rather than from traditional employees or suppliers.

Every DBA must have googled something to address his/her technical issue, and broadly speaking, this can be considered as using crowdsourcing for work purpose. But this is a very light way of crowdsourcing. To me, a real high-quality and serious crowdsourcing for database administration needs to be treated as a real project with planning, timeline, goals, budget and some senior DBA resources. The key to success is on that senior DBA who should be able to design a needed system and divide it into small logical units that are suitable for crowdsourcing.

Some advantages for crowdsourcing in database administration can be:

1. Scalability in database administration capacity: we can harvest the wisdom of the online community to increase our productivity and efficiency instead of relying on more man-hours to meet increased business requirements.

2. Cost control: DBA work is not cheap, yet finding the quality resource is still challenging most of the time. With proper crowdsourcing, we can minimize the cost and reduce the burn-outs of existing DBAs.

3. Work Quality Control: Through paid crowdsourcing channels (such as topcoder.com), we can usually award the task to the best rated bidder for the work. Through non-paid crowdsourcing (like stackoverflow.com), people will vote for the best answer, and this is like a quality control feature free to everyone.

4.  Potential big time saving: In database administration, there are quite some very impressive open-sourced tools, which, if used properly, may save us huge amount of time. To develop these free tools alone may easily cost an excellent senior DBA hundreds (if not thousands) of hours.

Currently, crowdsourcing does not attract much attentions from IT leaders. I think it is mostly because of a few hurdles listed below:

1. Lack of crowdsourcing management professionals: crowdsourcing itself is mainly rooted in online community, but we have vast number of channels for crowdsourcing opportunities, such as twitter, linkedin, stackoverflow, experts’ personal blogs, and various online forums under specific websites (in sql server domain, we have MS forums, and SSC forums etc), and also those professional conferences (such as PASS annual conference) can be a good channel as well. To manage crowdsourcing channels, we need people to know what to ask and how to ask in each different channel and can engage in constructive conversations with online community, and at the end, the senior DBA resource should be able to assemble everything together to make the whole solution work. This is no easy work.

2. Hard to trace responsibility: if  I start a crowdsourcing project and it fails, who should take the responsibility? The online community or myself? But on the other hand, if I am assigned a specific project, if the project fails, it is my sole responsibility, thus easy for management to manage.

Crowdsourcing administration does not necessarily mean easy or inexpensive especially for small project, but crowdsourcing can be a good way to amplify the capacities of existing resources, and maximize investment in technical project. Most important, it can reduce the dependency on hiring more qualified staff to start work.

Maybe in future, we will some 3rd party companies dedicated to such crowdsourcing project and then sell the service / solution or product to other companies.

Posted in Uncategorized | Leave a comment

Award from MSSQLTIPS.com

Today, I am excited and humbled to learn that I am awarded “Rookie of the Year – 2014” by MSSQLTips.com.

I have to say it is probably my best decision in 2014 to write for MSSQLTips.com as I am not only paid decently for writing about what I love to share, but also forced to do a lot of R&D in areas that I am not very familiar with, such as .Net classes, PowerShell, SMO etc. This R&D work has benefited me a lot.

Writing an article is different from writing a blog. Writing a blog is easy in the sense that you know it will be “published” but writing an article in a highly-regarded website is totally another thing because first there is no guarantee that your article will be published and second, even if it is published, you will more likely get caught for any bugs or deficiency in your code or logic. To me, the concern or worry that my article is below accepted quality forces me to spend more time and efforts in crafting an article from idea selection, article composition to multiple environments tests (i.e. from sql server 2005 to sql server 2014) and continuous proof-reading /modification. The process is much longer and more painful than writing a blog.

I really encourage all SQL Server professionals to share their ideas via anyway they like, blog, forum, or articles. But personally, I’d recommend MSSQLTips.com if you have some practical tips that are not too long and can be easy to follow. It is very rewarding to know that your tip helps others in their daily work and on the other hand, it is awesome to treat yourself a good dinner outside with royalties received.

Posted in professional development | Tagged | Leave a comment

Bug in SQL Server 2014 SQLPS For Registered Servers/Groups

I recently had an opportunity to write an article for mssqltips.com on managing registered servers under “local server groups”, and Greg Robidoux was kind enough to do a review for me and he got back to me saying my script not working under SQL Server 2014 SSMS, though it works under SQL Server 2012. Indeed, I tested the script only in SQL Server 2008/R2 and SQL Server 2012 because I have not installed SQL Server 2014 (expecting SP1 to be released soon).

So here is the background information, we can create registered servers / groups via PowerShell (i.e. SQLPS environment). What I need to do is to start SQL Server 2012 SSMS (abbr. as SSMS 2012 hereafter), connect to my local SQL instance and right-click an object, and from the pop-up window, click “Start PowerShell” as shown below

image 

Fig-1

I then can enter into SQL Server 2012 SQLPS command window, where I can go into SQLServer:\SQLRegistration “folder” as shown below

image

Fig-2

Then I can use new-item cmdlet to create groups and registered servers.

For example, in the SQLPS 2012 window, if I run “new-item –path Group_A –type directory”

image

Fig-3

I will see in SSMS 2012, a group “Group_A” appear in the “Registered Servers” window:

image

Fig-4

However, if I do the same thing in SQLPS 2014, I will not see “Group_A” in SSMS 2014.

In my case, I prepared a script that will automatically create all the groups / registered servers based on my input, the generated groups / registered servers can be like the following:

image

Fig-5

I have tested my script in SQL Server 2008 / 2008 R2 / 2012 environments, no issue found, everything went well.

Since Greg told me it did not work in SSMS 2014, I started to install a SQL Server 2014 instance on my computer, on which I already have another two sql instances, one is SQL Server 2008 R2 and the other SQL 2012. After installation, I tested my script under SQL 2014 SQLPS window, and no doubt, it does not work, i.e. in SSMS 2014, I cannot see the registered servers / groups as shown in Fig-4. However, I noticed a weird thing that after I run my script in SQLPS 2014, I see all the registered servers / groups from SSMS 2012 though I do not see them in SSMS 2014, not in SSMS 2008R2 either (of course, I have cleaned everything in SSMS 2012 beforehand).  So this got me thinking there must be something wrong in SQL Server 2014.

So my first task in the debugging process is to figure out where these registered servers/groups information is saved. After some googling, I did find out and verified the answer from this link (http://stackoverflow.com/questions/3064289/where-are-registered-servers-stored). (Big thanks to both who asked and answered the question, you are my heroes.)

In my computer, the RegSrvr.xml for SSMS 2014 is located at the following folder (notice \120\, that is SQL Server 2014 version number)

image

Fig-6

Similarly the RegSvr.xml for SSMS 2012 is located at: (notice \110\, that is sql server 2012 version number)

image

Fig-7

With this information and the weird observation (i.e. registered servers/groups are seen from SSMS 2012 instead of SSMS 2014), I guess SQLPS 2014 may probably mis-manipulate the RegSrvr.xml in the wrong folder. So I cleaned up the registered servers/groups from SSMS 2012, and then mark down the [Date modified] value of RegSrvr.xml in \110\ folder, let’s say the time value is 10:00pm,  and I also marked the [Date modified] value for the RegSrvr.xml in \120\ folder, let’s say the value is 10:02pm. Now at 10:20pm, I run my script from SQLPS 2014 window to register all the servers/groups, and then I check back the [Date modified] value of RegSrvr.xml in both \110\ and \120\ folders, no surprise, the value of the file in \110\ folder changed from 10:00pm to 10:20pm, while that in \120\ folder remains at 10:02pm.

So this indeed proves SQLPS 2014 has a bug when it deals with registered servers/groups for “Local Server Groups” in SSMS 2014.

For fun purpose, I copied RegSrvr.xml from \110\ folder to \120\ folder (overwriting the existing one in \120\ folder), and then I started SSMS 2014 again. Just as expected, I find all the registered servers/groups appear in SSMS 2014 “Registered Servers” window, wow !

Posted in Administration | Tagged | Leave a comment

Decode SQL Server Job Schedules

As once a data architect, I cannot help but admiring and appreciating the data model behind the sql server job schedules. The core design is actually just one table, msdb.dbo.sysschedules, but the design beauty/elegance of the table is that it can support many different types of schedule with a rich set of variations for each, for example,  a schedule can be: on the weekdays of 2nd week of every 3 months, run every 30 min between 1:00am to 3:00pm starting at 1:15am.

Recently, I have been working on a task:

With two parameters, @Start_DateTime and @End_DateTime, find the all/specific scheduled jobs that will run during this time window, and list the exact scheduled run time for each job.

I initially try to google out a solution, but I simply cannot find out one that works to my needs, the closest one I can google out is this “Keep Track of all your jobs schedules”, but it has some deficiencies that cannot generate correct result, for example, if the job schedule is daily running every hour from 8:00am to 8:00pm, and you want to find out the job schedules between 10:05am to 1:05pm, the result will give you all schedules from 8:00am to 8:00pm instead of just between 10:05am and 01:05pm, and sometimes, it simply did not give out any job schedules.

So I start to work on my own, I have to say it is a fun / rewarding journey, my 1st version is about 1,300 lines with complicated if/else/case when, and my 2nd version is about 800 lines and my 3rd (the current) version is about 400 lines and I have tested in various weird schedules (in sql server 2012 and a few sql server 2008 cases as well), like a schedule of the 2nd Tuesday/Friday of every 3 months, and also a job with multiple daily / weekly schedules etc, and I always get the correct results.

I will give a few explanations regarding the code, so it is better to understand.

1. The @Start_DateTime and @End_DateTime should be in future, i.e. they should > getdate().

2. I need to rely on next_run_date/next_run_time of msdb.dbo.sysjobschedules, which will be refreshed every 20 min as indicated on BOL. This means if you create a new job with a new schedule, the next_run_date / next_run_time columns will not be populated immediately and if so, my script may not return correct results.

3. The most difficult part is about the calculation of “the <nth> <weekday> of every <X> months”, the difficulty here is that weekday as in datepart(weekday, <@date>) is impacted by the @@DateFirst setting (language dependent). However, I was lucky to find a language-independent solution in Itzik Ben-Gan’s series articles (DateTime Calculations).

if object_id('dbo.uspCheckJobFutureSchedule', 'P') is not null

    drop proc dbo.uspCheckJobFutureSchedule;

go


create proc dbo.uspCheckJobFutureSchedule

  @Start_DT datetime

, @End_DT datetime

, @JobName varchar(128)=''

, @ScheduleName varchar(128)=''

as

begin --proc

    if object_id('tempdb.dbo.#tmp', 'U') is not null

        drop table #tmp;

    if object_id('tempdb.dbo.#job', 'U') is not null

        drop table dbo.#job;



    set nocount on;

    if object_id('tempdb..#tblCalendar', 'U') is not null

        drop table #tblCalendar;

    create table #tblCalendar (dt smalldatetime, [year] smallint, [month] tinyint, [day] tinyint, [weekday] tinyint, [week_of_month] tinyint)

    declare @tbl table (dt smalldatetime, [year] smallint, [month] tinyint, [day] tinyint, [weekday] tinyint, [week_of_month] tinyint);


    --declare @start_dt datetime, @end_dt datetime;

    declare @diff_days int, @calc_start_dt datetime, @i int, @inner_loop_dt datetime;

    declare @current_dt datetime, @current_week datetime;

    declare @dw int;

    declare @calc_end_dt datetime;

    declare @min_end_date datetime, @max_start_date datetime, @dt datetime;

    declare @next_run_date int, @next_run_time int, @next_run_dt datetime;


    --select @start_dt = '2014-02-21', @end_dt = '2014-04-28';

    -- first get all jobs that are scheduled, with job name and the schedule details 

    create table #tmp (

      schedule_id int

    , jobname sysname

    ,schedule_name    sysname

    ,freq_type    int

    ,freq_interval    int

    ,freq_subday_type    int

    ,freq_subday_interval    int

    ,freq_relative_interval    int

    ,freq_recurrence_factor    int

    ,active_start_date    char(8)

    ,active_end_date    char(8)

    ,active_start_time    char(8)

    ,active_end_time    char(8)

    ,active_start_date_int   int

    ,active_end_date_int int

    ,active_start_time_int int

    ,active_end_time_int   int

    ,next_run_date int

    ,next_run_time int

    )


    insert into #tmp (schedule_id, jobname, schedule_name, freq_type

    ,freq_interval

    ,freq_subday_type

    ,freq_subday_interval

    ,freq_relative_interval

    ,freq_recurrence_factor

    ,active_start_date

    ,active_end_date

    ,active_start_time

    ,active_end_time

    ,active_start_date_int

    ,active_end_date_int

    ,active_start_time_int

    ,active_end_time_int

    ,next_run_date

    ,next_run_time

    )

    select s.schedule_id, j.name, s.name, s.freq_type

    ,s.freq_interval

    ,s.freq_subday_type

    ,s.freq_subday_interval

    ,s.freq_relative_interval

    ,s.freq_recurrence_factor

    ,cast(s.active_start_date    as char(8))

    ,cast(s.active_end_date as char(8))

    ,substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),1,2)+':'+    substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(s.active_start_time as varchar(6)), 6),5,2)

    ,substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),1,2)+':'+    substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(s.active_end_time as varchar(6)), 6),5,2)

    , s.active_start_date

    , s.active_end_date

    , s.active_start_time

    , s.active_end_time

    , js.next_run_date

    , js.next_run_time

    from msdb.dbo.sysschedules s

    inner join msdb.dbo.sysjobschedules js

    on js.schedule_id = s.schedule_id

    inner join msdb.dbo.sysjobs j

    on j.job_id = js.job_id

    where s.enabled=1

    and j.enabled =1

    and js.next_run_date > 0

    and s.name = case @ScheduleName when '' then s.name else @ScheduleName end

    and j.name= case @JobName when '' then j.name else @JobName end


    -- delete all job schedules that are beyond the boundary of the @start_dt and @end_dt

    -- for example, if a job is scheduled to be run in Jan, 2020, and if @start_dt='2018-01-01' and @end_dt = '2018-02-01', 

    -- then we do not need to consider this job schedule (i.e. Jan 2020)


    delete from #tmp

    where convert(datetime, active_start_date +' '+ active_start_time ) > @end_dt

    or convert(datetime, active_end_date +' '+ active_end_time ) < @start_dt;



    -- create a temp table to hold those jobs that will be started between @start_dt and @end_dt window


    create table dbo.#job (

      ServerName sysname default @@servername

    , jobname sysname

    , ScheduleName sysname

    , run_datetime datetime

    );


    declare @schedule_id int, @schedule_name sysname, @active_start_dt datetime, @active_end_dt datetime;

    declare @freq_type int, @freq_interval  int

    , @freq_subday_type   int

    , @freq_subday_interval  int

    , @freq_relative_interval  int

    , @freq_recurrence_factor  int

    , @active_start_time_int int

    , @active_end_time_int int;



    declare curSch cursor for

    select distinct schedule_id, schedule_name, active_start_dt=convert(datetime, active_start_date +' '+ active_start_time )

    , active_end_dt=convert(datetime, active_end_date +' '+ active_end_time )

    , freq_type, freq_interval

    , freq_subday_type

    , freq_subday_interval

    , freq_relative_interval

    , freq_recurrence_factor

    , active_start_time_int

    , active_end_time_int

    , next_run_date

    , next_run_time

    from #tmp;


    open curSch;

    fetch next from curSch into @schedule_id, @schedule_name, @active_start_dt, @active_end_dt

    , @freq_type, @freq_interval

    , @freq_subday_type

    , @freq_subday_interval

    , @freq_relative_interval

    , @freq_recurrence_factor

    , @active_start_time_int

    , @active_end_time_int

    , @next_run_date

    , @next_run_time;



    while @@fetch_status = 0

    begin -- loop

        if @freq_type = 1 -- one time only

        begin -- one time only

            if @active_start_dt between @start_dt and @end_dt

                insert into #job (jobname, ScheduleName, run_datetime)

                select jobname, @schedule_name, convert(datetime, active_start_date +' '+ active_start_time)

                from #tmp

                where schedule_id = @schedule_id

        end -- one time only

        else

        begin -- else not one time only


            select @next_run_dt = convert(datetime, cast(@next_run_date as varchar(10)) +' ' + substring(right('00000' + cast(@next_run_time as varchar(6)), 6),1,2)+':'+    substring(right('00000' + cast(@next_run_time as varchar(6)), 6),3,2)+':'+substring(right('00000' + cast(@next_run_time as varchar(6)), 6),5,2))

            select @max_start_date = case when @next_run_dt >= @start_dt then @next_run_dt else @start_dt end,

            @min_end_date = case when @active_end_dt >= @end_dt then @end_dt else @active_end_dt end;


            select @calc_start_dt = dateadd(month, datediff(month, 0, @max_start_date), 0); -- switch to the month's first day for @max_start_date

            select @calc_end_dt = dateadd(month, datediff(month, 0, @min_end_date)+1, 0)-1; -- witch to the month's last day for @min_end_date


            -- generate a calendar table btw @calc_start_dt and @calc_end_dt

            set @inner_loop_dt = @calc_start_dt

            truncate table #tblCalendar;

            begin tran

            while @inner_loop_dt <= @calc_end_dt

            begin

                insert into #tblCalendar (dt, [year], [month], [day], [weekday], [week_of_month])

                select    @inner_loop_dt, year(@inner_loop_dt), month(@inner_loop_dt), day(@inner_loop_dt), datepart(dw, @inner_loop_dt + @@datefirst -1)

                , datepart(week, @inner_loop_dt) - datepart(week, dateadd(month, datediff(month, 0, @inner_loop_dt), 0)) + 1 ;


                set @inner_loop_dt = @inner_loop_dt + 1;

            end

            commit tran


            delete from @tbl;


        if @freq_type = 4 -- daily 

        begin -- daily

            insert into @tbl (dt)

            select dt from #tblCalendar

            where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

            and datediff(day, @active_start_dt, dt)%@freq_interval = 0;

        end -- daily


        if @freq_type = 8 -- weekly 

        begin -- weekly

            insert into @tbl (dt)

            select dt from #tblCalendar

            where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

            and datediff(week, @next_run_dt, dt)%@freq_recurrence_factor = 0

            and power(2, datepart(dw, dt+@@datefirst-1)%7) & @freq_interval >=1;

        end -- weekly


        if @freq_type = 16

        begin -- monthly, @freq_type = 16


            insert into @tbl (dt)

            select dt from #tblCalendar

            where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

            and datediff(month, @active_start_dt, dt)%@freq_recurrence_factor = 0

            and [day]=@freq_interval;


        end -- monthly, @freq_type = 16



        if @freq_type = 32

        begin -- monthly, @freq_type = 32


            -- find the days that the scheduled jobs will be run (later, we will loop through time to figure out the exact date/time)

            if @freq_relative_interval in (1, 2, 4, 8)

            begin

                if @freq_interval < 8

                begin

                    ; with c as (

                        select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by dt asc)

                        from #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        and [weekday] = case @freq_interval when 1 then 7 else @freq_interval -1 end

                    )

                    insert into @tbl (dt)

                    select dt from c

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and rn =  log(@freq_relative_interval)/log(2)+1

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

                end

                if @freq_interval = 8

                    insert into @tbl

                    select * from #tblCalendar

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and [day] = @freq_relative_interval

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;


                if @freq_interval=9 -- weekday

                    insert into @tbl

                    select * from #tblCalendar

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and week_of_month =  log(@freq_relative_interval)/log(2)+1

                    and [weekday] <6

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;


                if @freq_interval=10 -- weekend

                    insert into @tbl

                    select * from #tblCalendar

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and week_of_month =  log(@freq_relative_interval)/log(2)+1

                    and [weekday] in (6, 7)

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

            end

            else -- @freq_relative_interval = 16 -- last

            begin -- @freq_relative_interval = 16

                if @freq_interval < 8

                begin

                    ; with c as (

                        select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)

                        from #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select dt, [year], [month], [weekday], [day], [week_of_month] from c

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and week_of_month =  log(@freq_relative_interval)/log(2)+1

                    and [weekday] = case @freq_interval when 1 then 7 else @freq_interval -1 end

                    and rn=1

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

                end

                if @freq_interval = 8

                begin

                    ; with c as (

                        select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by [day] desc)

                        from #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select dt, [year], [month], [weekday], [day], [week_of_month] from c

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and rn =1

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

                end

                if @freq_interval=9 -- weekday

                begin

                    ; with c as (

                        select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)

                        from #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select dt, [year], [month], [weekday], [day], [week_of_month] from c

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and [weekday] <6 and rn=1

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

                end

                if @freq_interval=10 -- weekend

                begin

                    ; with c as (

                        select dt, [year], [month], [weekday], [day], [week_of_month], rn=row_number() over (partition by year, month order by week_of_month desc)

                        from #tblCalendar

                        where dt between @calc_start_dt and @calc_end_dt

                        )

                    insert into @tbl

                    select dt, [year], [month], [weekday], [day], [week_of_month] from c

                    where datediff(day, @max_start_date, dt) >=0  and datediff(day, dt, @min_end_date)>=0

                    and [weekday] in (6, 7) and rn=1

                    and datediff(month, @calc_start_dt, dt)%@freq_recurrence_factor = 0;

                end

            end    -- @freq_relative_interval = 16    


        end -- monthly, @freq_type = 32


        if exists (select * from @tbl) -- we do have days that match the schedules, now we need to loop through time to check whether the job will be run

        begin

            declare curT_daily cursor for

            select dt from @tbl;


            open curT_daily;

            fetch next from curT_daily into @dt;

            while @@fetch_status =0

            begin -- while loop

                    set @inner_loop_dt = dateadd(second, @active_start_time_int/10000*3600+@active_start_time_int%10000/100*60+@active_start_time_int%100, @dt);

                    while @inner_loop_dt <= dateadd(second, @active_end_time_int/10000*3600+@active_end_time_int%10000/100*60+@active_end_time_int%100, @dt)

                    begin


                        if @inner_loop_dt between @max_start_date and @min_end_date

                            insert into #job (jobname, ScheduleName, run_datetime)

                            select jobname, @Schedule_Name, @inner_loop_dt

                            from #tmp

                            where schedule_id = @schedule_id


                        if @freq_subday_type = 1

                        begin

                            set @inner_loop_dt = @inner_loop_dt + 1;

                            continue;

                        end

                        if @freq_subday_type = 2

                            set @inner_loop_dt = dateadd(second, @freq_subday_interval, @inner_loop_dt);

                        else if @freq_subday_type = 4

                                set @inner_loop_dt = dateadd(MINUTE, @freq_subday_interval, @inner_loop_dt);

                            else

                                set @inner_loop_dt = dateadd(HOUR, @freq_subday_interval, @inner_loop_dt);

                    end

                fetch next from curT_daily into @dt;

            end -- while loop


            close curT_daily;

            deallocate curT_daily;

        end


        end -- -- else not one time only


        fetch next from curSch into @schedule_id, @schedule_name, @active_start_dt, @active_end_dt

        , @freq_type, @freq_interval

        , @freq_subday_type

        , @freq_subday_interval

        , @freq_relative_interval

        , @freq_recurrence_factor

        , @active_start_time_int

        , @active_end_time_int

        , @next_run_date

        , @next_run_time;


    end -- loop

    close curSch;


    deallocate curSch;


    select ServerName, JobName, ScheduleName, Run_DateTime from #job

end -- proc

go

So how can we use this code in a more innovative way other than finding the future job scheduled times? Here are a few user cases:

1. You need to check whether there is any job not running at an expected datetime.

A job can miss its execution because the last running is past the current schedule time. For example, if a job is scheduled to run every 10 min and usually it takes only 5 min to finish the run, now if the job (run at 10:00am) suddenly runs 12 min, then the expected 10:10am run will not start at all. There are some other weird incidents that can cause the job not running, like failover happened and caused sql agent service not started during the time that the job is scheduled to run etc.

2. You need to make sure a job indeed runs.

I have seen in some environments, some jobs are configured with a completion notification, i.e. whether the job succeeds or fails, an email alert will be sent. But 99% of the time, the job succeeds, yet, as a DBA, I still need to bear the pain to receive and delete a job saying “The job xxxx completes”.  What I really want is if the job fails, send me an alert, otherwise, do not send me anything. But I need to make sure the job indeed runs. The job schedule can be something weird, like the every 3 months, the 3rd weekend days from 10:00am to 10:00pm every 2 hours.

The solution: Every  1st day of each month (or any day you like), I will run the script and dump the result to a table, let’s call it tblJobFutureSchedule, and then at the end of every day (or every hour/week to your own choice), I will run a check script to compare the result in  the msdb.dbo.sysjobhistory with tblJobFutureSchedule to make sure (1) there is no missing job execution and (2) the job indeed runs. If issue found, an alert will be sent, and we can do further investigation.

3. If there is a scheduled server patching work (implying that there are multiple reboots during the patching process), we can use the code to generate a list of jobs that will be scheduled to run during the patching window, so we can disable these jobs before the patching starts and later enable these jobs after the patching window.

Attachment:uspCheckJobFutureSchedule

Posted in Administration, SQL Development | Tagged , | Leave a comment

Fun in DateTime Calculations

Recently I was working on a task that needs to handle various datetime calculations, and it is really fun.

Usually there are two types of approaches, one is to convert datetime value to string value and then through string calculations like substring, concatenation and convertion to get the result, another is via pure datetime functions, like dateadd and datediff. I like the second approach, which is better in performance.

I learned a lot from Itzik Ben-Gan’s series articles here: (DateTime Calculations), especially the Language-Independent (i.e. @DateFirst non-relevant) weekday calculation in part 2 of the series.

   1: -- 1 find the first day of the prev month / current month / next month

   2: declare @dt datetime  = getdate();

   3: select prev_mth_1st_day = dateadd(month, datediff(month, 0, @dt)-1, 0)

   4: , curr_mth_1st_day=dateadd(month, datediff(month, 0, @dt), 0)

   5: , next_mth_1st_day=dateadd(month, datediff(month, 0, @dt)+1, 0)

   6:

   7: -- 2 find the last day of the prev month / current month / next month

   8: select prev_mth_last_day = dateadd(month, datediff(month, 0, @dt),  -1)

   9: , curr_mth_1ast_day=dateadd(month, datediff(month, 0, @dt)+1, -1)

  10: , next_mth_1ast_day=dateadd(month, datediff(month, 0, @dt)+2, -1)

  11:

  12: -- 3.1 find the first week day of the prev month / current month / next month

  13: select prev_mth_1st_wkday=case datepart(dw, dateadd(month, datediff(month, 0, @dt)-1,0)+@@datefirst-1)

  14:         when 6 then  dateadd(month, datediff(month, 0, @dt)-1,0)+2

  15:         when 7 then  dateadd(month, datediff(month, 0, @dt)-1,0)+1

  16:         else   dateadd(month, datediff(month, 0, @dt)-1,0) end

  17: , curr_mth_1st_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt),0)+@@datefirst-1)

  18:         when 6 then  dateadd(month, datediff(month, 0, @dt),0)+2

  19:         when 7 then  dateadd(month, datediff(month, 0, @dt),0)+1

  20:         else   dateadd(month, datediff(month, 0, @dt),0) end

  21: , next_mth_1st_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)+@@datefirst-1)

  22:         when 6 then  dateadd(month, datediff(month, 0, @dt)+1,0)+2

  23:         when 7 then  dateadd(month, datediff(month, 0, @dt)+1,0)+1

  24:         else   dateadd(month, datediff(month, 0, @dt)+1,0) end;

  25:

  26: -- 3.2 find the first weekend day of the prev month / current month / next month

  27: select prev_mth_1st_wkndday=case when datepart(dw, dateadd(month, datediff(month, 0, @dt)-1,0)+@@datefirst-1) < 6

  28:         then dateadd(month, datediff(month, 0, @dt)-1,0)+ 6- datepart(dw, dateadd(month, datediff(month, 0, @dt)-1,0)+@@datefirst-1)

  29:         else   dateadd(month, datediff(month, 0, @dt)-1,0) end

  30: , curr_mth_1st_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt),0)+@@datefirst-1) < 6

  31:         then dateadd(month, datediff(month, 0, @dt),0)+ 6- datepart(dw, dateadd(month, datediff(month, 0, @dt),0)+@@datefirst-1)

  32:         else   dateadd(month, datediff(month, 0, @dt),0) end

  33: , next_mth_1st_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)+@@datefirst-1) < 6

  34:         then dateadd(month, datediff(month, 0, @dt)+1,0)+ 6- datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)+@@datefirst-1)

  35:         else   dateadd(month, datediff(month, 0, @dt)+1,0) end;

  36:

  37: -- 4.1 find the last week day of the prev month / current month / next month       

  38: select prev_mth_last_wkday=case datepart(dw, dateadd(month, datediff(month, 0, @dt),0)-1+@@datefirst-1)

  39:         when 6 then  dateadd(month, datediff(month, 0, @dt),0)-1-1

  40:         when 7 then  dateadd(month, datediff(month, 0, @dt),0)-1-2

  41:         else   dateadd(month, datediff(month, 0, @dt),0)-1 end

  42: , curr_mth_last_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)-1+@@datefirst-1)

  43:         when 6 then  dateadd(month, datediff(month, 0, @dt)+1,0)-1-1

  44:         when 7 then  dateadd(month, datediff(month, 0, @dt)+1,0)-1-2

  45:         else   dateadd(month, datediff(month, 0, @dt)+1,0)-1 end

  46: , next_mth_last_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt)+2,0)-1+@@datefirst-1)

  47:         when 6 then  dateadd(month, datediff(month, 0, @dt)+2,0)-1-1

  48:         when 7 then  dateadd(month, datediff(month, 0, @dt)+2,0)-1-2

  49:         else   dateadd(month, datediff(month, 0, @dt)+2,0)-1 end;

  50:

  51: -- 4.2 find the last weekend day of the prev month / current month / next month       

  52: select prev_mth_last_wkndday= case when  datepart(dw, dateadd(month, datediff(month, 0, @dt),0)-1+@@datefirst-1) < 6

  53:         then dateadd(month, datediff(month, 0, @dt),0)-1-datepart(dw, dateadd(month, datediff(month, 0, @dt),0)-1+@@datefirst-1)

  54:         else   dateadd(month, datediff(month, 0, @dt),0)-1 end

  55: , curr_mth_last_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)-1+@@datefirst-1) < 6

  56:         then dateadd(month, datediff(month, 0, @dt)+1,0)-1-datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)-1+@@datefirst-1)

  57:         else   dateadd(month, datediff(month, 0, @dt)+1,0)-1 end

  58: , next_mth_last_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt)+2,0)-1+@@datefirst-1) < 6

  59:         then dateadd(month, datediff(month, 0, @dt)+2,0)-1-datepart(dw, dateadd(month, datediff(month, 0, @dt)+2,0)-1+@@datefirst-1)

  60:         else   dateadd(month, datediff(month, 0, @dt)+2,0)-1 end;

  61:

  62:  go

  63:

  64: -- 5 find the first nth weekday (Mon/Tue/Wed/Thu/Fri/Sat/Sun) of the month where @dt is in

  65: -- example, find the 5th Friday of the month of Aug, 2013 

  66: declare @nth int=5, @dt datetime='2013-08-12';

  67: declare @dw tinyint  =5 -- 1=Mon,2= Tue,3=Wed, 4=Thur, 5=Fri, 6=Sat, 7=Sun

  68:

  69: select [1st_nth_wkday]=case when  datepart(dw, dateadd(month, datediff(month,0,@dt),0)+@@datefirst-1) >= @dw

  70: then dateadd(day, (@nth-1)*7 + (7-(datepart(dw, dateadd(month, datediff(month,0,@dt),0)+@@datefirst-1)-@dw)), dateadd(month, datediff(month,0,@dt),0))

  71: else dateadd(day, (@nth-1)*7 + (0-(datepart(dw, dateadd(month, datediff(month,0,@dt),0)+@@datefirst-1)-@dw)), dateadd(month, datediff(month,0,@dt),0))

  72: end

  73: go

  74:

  75:

  76: -- 6 find the last nth weekday (Mon/Tue/Wed/Thu/Fri/Sat/Sun) of the month where @dt is in

  77:

  78: -- find the 2nd last Sunday of current month

  79: declare @nth int=2, @dt datetime=current_timestamp;

  80: declare @dw tinyint  =7 -- 1=Mon,2= Tue,3=Wed, 4=Thur, 5=Fri, 6=Sat, 7=Sun

  81:

  82: select [last_nth_wkday]=case when datepart(dw, dateadd(month, datediff(month,0,@dt)+1,0)-1+@@datefirst-1) >= @dw

  83: then dateadd(day, -(@nth-1)*7 - (datepart(dw, dateadd(month, datediff(month,0,@dt)+1,0)-1+@@datefirst-1)-@dw), dateadd(month, datediff(month,0,@dt)+1,0)-1)

  84: else dateadd(day, -(@nth)*7 - (datepart(dw, dateadd(month, datediff(month,0,@dt)+1,0)-1+@@datefirst-1)-@dw), dateadd(month, datediff(month,0,@dt)+1,0)-1)

  85: end

  86: go

  87:

  88:

I believe there can be other interesting datetime related calculations, like quarterly related datetime calculations, but should be similar to the queries mentioned above.

Posted in Uncategorized | 4 Comments

Reliably Drop A Database With CLR Stored Procedure

 

After I read Greg Low’s post and my initial try, I kept on thinking that I need to come up with a solution that in theory is robust. The only way I can think of is to use a CLR stored procedure which will do the following three steps:

1. alter database <blah> set offline with rollback immediate;

2. find all the files that compose the <blah> database, using

select physical_name from master.sys.master_files where database_id = db_id(‘blah’)

3. delete each file found in step 2.

So here is C# code to create the CLR stored procedure:

   1: using System;

   2: using System.Collections;

   3: using System.Data;

   4: using System.Data.SqlClient;

   5: using System.Data.SqlTypes;

   6: using Microsoft.SqlServer.Server;

   7: using System.IO;

   8:  

   9: public partial class StoredProcedures

  10: {

  11:     [Microsoft.SqlServer.Server.SqlProcedure]

  12:     public static int usp_DropDB (SqlString db_name)

  13:     {

  14:  

  15:         try

  16:         {

  17:             using (SqlConnection conn = new SqlConnection("context connection=true"))

  18:             {

  19:                 string sql = "select cnt=count(*) from master.sys.databases where name=@dbname";

  20:                 SqlCommand scmd = new SqlCommand();

  21:                 SqlParameter parmDBName = new SqlParameter("@dbname", SqlDbType.VarChar, 128);

  22:                 parmDBName.Value = db_name.Value;

  23:                 scmd.Parameters.Add(parmDBName);

  24:                 scmd.CommandText = sql;

  25:                 scmd.Connection = conn;

  26:                 conn.Open();

  27:                 Int32 i = (Int32)scmd.ExecuteScalar();

  28:                 conn.Close();

  29:                 scmd.Dispose();

  30:  

  31:                 if (i == 1)

  32:                 {

  33:                     

  34:                     sql = "if exists (select * from sys.databases where name=@dbname)";

  35:                     sql = sql + "\r\n alter database [" + db_name.Value + "] set offline with rollback immediate;";

  36:  

  37:                     SqlCommand cmd = new SqlCommand();

  38:                     SqlParameter pDBName = new SqlParameter("@dbname", SqlDbType.VarChar, 128);

  39:                     pDBName.Value = db_name.Value;

  40:  

  41:  

  42:                     cmd.Parameters.Add(pDBName);

  43:  

  44:                     cmd.CommandText = sql;

  45:                     cmd.Connection = conn;

  46:  

  47:                     conn.Open();

  48:                     cmd.ExecuteNonQuery();

  49:                     cmd.CommandText = "select physical_name from master.sys.master_files where database_id=db_id(@dbname)";

  50:                     SqlDataReader rd = cmd.ExecuteReader();

  51:                     ArrayList alFilePath = new ArrayList();

  52:                     while (rd.Read())

  53:                     {

  54:                         alFilePath.Add((string)rd.GetString(0));

  55:                     }

  56:                     conn.Close();

  57:  

  58:                     cmd.CommandText = "drop database [" + db_name.Value + "];";

  59:  

  60:                     conn.Open();

  61:                     cmd.ExecuteNonQuery();

  62:                     conn.Close();

  63:  

  64:                     for (i = 0; i <= alFilePath.Count - 1; i++)

  65:                     {

  66:                         File.Delete((string)alFilePath[i]);

  67:                     }

  68:                 }

  69:                 else

  70:                 {

  71:                     return 1; // no db found

  72:                 }

  73:             }

  74:             return 0; // succeed

  75:         }

  76:         catch

  77:         {

  78:             return (-1); // -1 meaning error occurred

  79:         }

  80:     }

  81: }

 

Using Visual Studio to compile this to DLL file, let’s assume the file is called/located at c:\temp\CLR_SP.dll, and then using the following sql statement to create the CLR procedure:

   1: use master

   2: create assembly clr_dropDB from 'C:\temp\CLR_SP.dll' 

   3: with permission_set = External_ACCESS;

   4: go

   5:  

   6: create proc dbo.usp_DropDB @db_name nvarchar(128)

   7: as external name clr_dropDB.StoredProcedures.usp_DropDB;

   8: go

   9:  

  10: -- example 

  11: use master;

  12: declare @id int;

  13: exec dbo.usp_dropDB @db_name=N'testdb';

  14: print @id;

 

My dev/test environment is Visual Studio 2012 Professional and SQL Server 2012 Developer Edition.

Posted in Uncategorized | Leave a comment