Negative number in sysjobhistory.run_duration

I got a strange value for only a couple of rows in my sysjobhistory table for the run_duration column.

Value:  -954441829

The job kicked off at 1:54am on 11/1/15.  This is the day we changed from daylight to standard time.

So the job thinks it ended before it began, hence the negative duration.  Problem solved!  Had me perplexed for a few days until I checked the start date and time.

Error when attempting to delete a maintenance plan in SQL 2005 using SSMS 2014.

Here is an error I received when attempting to delete a maintenance plan in a SQL 2005 instance using SSMS 2014.  

Exception has been thrown by the target of an invocation. (mscorlib)

Additional information:

Index was out of range.  Must be non-negative and less than the size of the collection.

Parameter name: index (mscorlib)

I was able to successfully delete the object by using SSMS 2005.

udfFormatJobNextRunDate - Properly format a job's next run date/time

Scenario

You want to quickly determine the next jobs to be executed on a busy server.  Perhaps you need to bring the server down and you want to ensure you don't miss any important jobs.  Or you are managing resources for the next couple of hours and you need to know which jobs to disable.

1^st Solution

Your first approach is to query sysjobs and sysschedules.

-- Shows list of jobs and when they next run

SELECT  j.name JobName,

            j.Enabled,

            j.Description,

            s.next_run_date,

            s.next_run_time

FROM msdb.dbo.sysjobs j

LEFT JOIN msdb.dbo.sysjobschedules s

      ON s.job_id = j.job_id

ORDER BY next_run_date desc, next_run_time desc

Alas, while the above query gets you everything you need, the next TIME for when a job is scheduled to run is formatted poorly.  Therefore the ordering is incorrect.

Note the poorly formatted time below.  Ordering by next_run_time will not yield correct results.  I added an additional column showing what the correctly formatted date/time should be.

Solution for formatting next run date and time of jobs using a user-defined function

Use Admin

-- Use master

IF Object_ID('dbo.udfFormatJobNextRunDate') Is Not Null

      DROP FUNCTION dbo.udfFormatJobNextRunDate

go

CREATE FUNCTION dbo.udfFormatJobNextRunDate(

      @NextRunDateTEXT varchar(8),

      @NextRunTimeTEXT varchar(6)

      )

RETURNS Datetime

AS

/*    DATE        AUTHOR            REMARKS

      10/19/15    PPaiva            Initial creation

      DESCRIPTION

            Formats the text columns Next_Run_Date and Next_Run_Time

                  in sysJobSchedules to a datetime format.

            TIME formatting

                   Input:  4000

                  Output:  00:40:00

      USAGE

            SELECT  *, 

              dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) ProperlyFormattedDatetime

            FROM msdb.dbo.sysJobSchedules

            ORDER BY len(next_run_time)

           

            SELECT dbo.udfFormatJobNextRunDate('20150401', '300')

            SELECT dbo.udfFormatJobNextRunDate('20150401', '2300')

            SELECT dbo.udfFormatJobNextRunDate('20150401', '12300')

            SELECT dbo.udfFormatJobNextRunDate('20150401', '112300')

*/

BEGIN

      DECLARE @Out datetime,

                  @NextRunTimeWithColons varchar(8),

                  @LenTime int

     

                 

      -- Pad left with zeros

      SET @LenTime = Len(@NextRunTimeTEXT)

      SET @NextRunTimeTEXT = REPLICATE('0', 6 - @LenTime) + @NextRunTimeTEXT

      -- Add colons.  Change 005000 to 00:50:00

      SET @NextRunTimeWithColons = Left(@NextRunTimeTEXT, 2)

                  + ':'

                  +     Substring(@NextRunTimeTEXT, 3, 2)

                  + ':'

                  +     Right(@NextRunTimeTEXT, 2)

      -- Concatenate date and time

      SET @Out = CASE WHEN IsNull(@NextRunDateTEXT, 0) = 0 THEN Null

                              ELSE Convert(datetime, @NextRunDateTEXT + ' ' + @NextRunTimeWithColons

                                                )

                                    END

      RETURN @Out

END

2^nd Solution

Now simply add the new function to the initial query.

-- Shows list of jobs and when they next run (modified with solution)

-- Now jobs are correctly order by next run date/time

SELECT  j.name JobName,

            j.Enabled,

            j.Description,

            s.next_run_date,

            s.next_run_time,

            dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) CorrectlyFormattedDatetime

FROM msdb.dbo.sysjobs j

LEFT JOIN msdb.dbo.sysjobschedules s

      ON s.job_id = j.job_id

ORDER BY 5 desc, 6 desc

vwDBScripts - Change database properties with ease

Some of these ALTER DATABASE commands are used when you are assessing a new server, or even for doing ongoing maintenance on an old server.

ALTER DATABASE [Admin] SET RECOVERY FULL 

ALTER DATABASE [Admin] SET RECOVERY SIMPLE     

ALTER DATABASE [Admin] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE     

ALTER DATABASE [Admin] SET SINGLE_USER WITH ROLLBACK IMMEDIATE   

ALTER DATABASE [Admin] SET MULTI_USER WITH ROLLBACK IMMEDIATE    

ALTER DATABASE [Admin] SET READ_ONLY     

ALTER DATABASE [Admin] SET READ_WRITE    

ALTER DATABASE [Admin] SET AUTO_SHRINK OFF WITH NO_WAIT    

ALTER DATABASE [Admin] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [Admin] SET OFFLINE WITH ROLLBACK IMMEDIATE 

ALTER DATABASE [Admin] SET ONLINE  

ALTER DATABASE [Admin] SET COMPATIBILITY_LEVEL = 8 

It is much more efficient to do this via T-SQL rather than by right-clicking the database and modifying the properties in the GUI.  Using a script will give you repeatability.  

With this view, you can quickly identify the database properties that need to be changed, such as turning on CHECKSUM or putting the recovery model to SIMPLE or FULL as required.  Then simply look for the appropriate column containing the script and copy/paste into a new window.  All the other columns available from sys.databases are included for reference, especially if you want to add other columns depending on your need.

SELECT Instance, Name, DBID, CreateDate, SqlChecksum

FROM vwDBScripts

WHERE PageVerify <> 'CHECKSUM'

I put this view into my Admin database which I have on all servers which I maintain.  Makes administration much more manageable. 

USE Admin

IF Object_ID('dbo.vwDBScripts') Is Not Null

      DROP VIEW dbo.vwDBScripts

go

CREATE VIEW dbo.vwDBScripts

AS

/*    DATE        AUTHOR            REMARKS

      6/4/15      PPaiva            Initial creation.

      6/14/15     PPaiva            Added column MirroringState.

     

      SELECT *

      FROM vwDBScripts

      ORDER By Name

      SELECT Instance, Name, PageVerify, SqlChecksum

      FROM vwDBScripts

      WHERE PageVerify <> 'CHECKSUM'

      SELECT Instance, Name, RecoveryModel, SqlRecoveryFull, SqlRecoverySimple

      FROM vwDBScripts

--    WHERE RecoveryModel = 'SIMPLE'

      WHERE RecoveryModel = 'FULL'

      SELECT Instance, Name, AutoShrink, SqlAutoShrinkOff

      FROM vwDBScripts

      WHERE AutoShrink = 1

      SELECT db_name(database_id) DB, *

      FROM sys.database_mirroring m     

*/

SELECT  ServerProperty('ServerName') Instance,

            name,

            d.database_id DBID,

            CASE WHEN name In ('master', 'model', 'msdb', 'tempdb', 'distribution')

                        THEN 1

                        ELSE 0

                              END IsSys,

            create_date CreateDate,

            compatibility_level CompLevel,

            user_access_desc UserAccess,

            is_read_only ReadOnly,

            is_auto_close_on AutoClose,

            is_auto_shrink_on AutoShrink,

            state_desc StateDesc,

            recovery_model_desc RecoveryModel,

            CASE WHEN m.mirroring_state = 0 THEN 'Suspended'

                   WHEN m.mirroring_state = 1 THEN 'Disconnected from partner'

                   WHEN m.mirroring_state = 2 THEN 'Synchronizing'

                   WHEN m.mirroring_state = 3 THEN 'Pending Failover'

                   WHEN m.mirroring_state = 4 THEN 'Synchronized'

                   WHEN m.mirroring_state = 5 THEN 'Partners not synchronized. Failover not possible.'

                   WHEN m.mirroring_state = 6 THEN 'Partners synchronized.  Failover possible.'

                   WHEN m.mirroring_state Is Null THEN 'NONE'

                   ELSE 'undefined'

                   END MirroringState,

            page_verify_option_desc PageVerify,

            collation_name,

            is_in_standby,

            'ALTER DATABASE [' + name + '] SET RECOVERY FULL' SqlRecoveryFull,

            'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE' SqlRecoverySimple,

            'ALTER DATABASE [' + name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE' SqlRestrictedUser,

            'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' SqlSingleUser,

            'ALTER DATABASE [' + name + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE' SqlMultiUser,

            'ALTER DATABASE [' + name + '] SET READ_ONLY' SqlReadOnly,

            'ALTER DATABASE [' + name + '] SET READ_WRITE' SqlReadWrite,

            'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF WITH NO_WAIT' SqlAutoShrinkOff,

            'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM' SqlChecksum,

            'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE' SqlOffline,

            'ALTER DATABASE [' + name + '] SET ONLINE' SqlOnline,

            'ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 8  -- 2008 = 100, 2012 = 110, 2014 = 120' SqlCompatibility,

            is_cleanly_shutdown,

            is_supplemental_logging_enabled,

            snapshot_isolation_state,

            snapshot_isolation_state_desc,

            is_read_committed_snapshot_on,

            recovery_model,

            page_verify_option,

            is_auto_create_stats_on,

            is_auto_update_stats_on,

            is_auto_update_stats_async_on,

            is_ansi_null_default_on,

            is_ansi_nulls_on,

            is_ansi_padding_on,

            is_ansi_warnings_on,

            is_arithabort_on,

            is_concat_null_yields_null_on,

            is_numeric_roundabort_on,

            is_quoted_identifier_on,

            is_recursive_triggers_on,

            is_cursor_close_on_commit_on,

            is_local_cursor_default,

            is_fulltext_enabled,

            is_trustworthy_on,

            is_db_chaining_on,

            is_parameterization_forced,

            is_master_key_encrypted_by_server,

            is_published,

            is_subscribed,

            is_merge_published,

            is_distributor,

            is_sync_with_backup,

            source_database_id,

            owner_sid,

            user_access,

            state,

            service_broker_guid,

            is_broker_enabled,

            log_reuse_wait,

            log_reuse_wait_desc,

            is_date_correlation_on

FROM sys.databases d

JOIN sys.database_mirroring m

      ON d.database_id = m.database_id

Workaround for error with installing SSDT-BI for SQL 2014

While installing SSDT –BI for SQL 2014 (aka SQL Server Data Tools - Business Intelligence for Visual Studio 2013), you might get this error if you have a pre-existing instance of SQL 2014 and you had opted to use the existing instance.

Same architecture installation

When you click on View detailed report, you will find this message:

BlockMixedArchitectureInstall

The CPU architecture of installing feature(s) is different than the instance specified.  To continue, add features to this instance with the same architecture.

Solution

Apparently this is a bug, because if you go back a couple of dialog windows and select Perform a new installation of SQL Server 2014, it will actually use the existing instance without installing a new one.

Interview Question - How to remove duplicates in a large table with no primary key

Use tempdb

/*    Interview Question.

      How to identify and remove duplicates.

      Imagine this is a very large (billion+ rows) table with

            foreign keys.  Therefore it is not acceptable to delete

            and then re-add a set of distinct rows.

      Assignment:  Remove duplicate rows.  For example, if there are

            4 rows of Daffy Duck, need to remove 3 rows.

*/

-- First let's create a sample Employee table containing dups.

-- This table is intentionally made without a primary key

-- for a greater challenge.

-- drop table Employee

CREATE TABLE dbo.Employee (

      FirstName varchar(20),

      LastName varchar(20)

      )

INSERT INTO dbo.Employee VALUES ('Mickey', 'Mouse')

INSERT INTO dbo.Employee VALUES ('Donald', 'Duck')

INSERT INTO dbo.Employee VALUES ('Daffy', 'Duck')

INSERT INTO dbo.Employee VALUES ('Daffy', 'Duck')

INSERT INTO dbo.Employee VALUES ('Minnie', 'Mouse')

INSERT INTO dbo.Employee VALUES ('Daffy', 'Duck')

INSERT INTO dbo.Employee VALUES ('Beaver', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('Daffy', 'Duck')

INSERT INTO dbo.Employee VALUES ('Huey', 'Duck')

INSERT INTO dbo.Employee VALUES ('Beaver', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('Beaver', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('Ward', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('Ward', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('Ward', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('Ward', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('June', 'Cleaver')

INSERT INTO dbo.Employee VALUES ('June', 'Cleaver')

-- Examine table with dups.  17 rows.

SELECT *

FROM Employee

ORDER BY 2, 1

-- After we have removed dups, should look like this, 8 rows.

SELECT DISTINCT *

FROM Employee

ORDER BY 2, 1

-- Identify dups and put into temp table

-- DROP TABLE #Dups

SELECT  identity (int, 1, 1) ID,

            FirstName, LastName, Count(*) Qty

INTO #Dups

FROM Employee

GROUP BY FirstName, LastName

HAVING Count(*) > 1

ORDER BY 2, 1

-- If the #Dups table contains more than a thousand rows, might be worthwile to make this index

CREATE UNIQUE INDEX idx_Dups ON #Dups(ID) INCLUDE (FirstName, LastName)

-- Examine the dups

SELECT *

FROM #Dups

-- Remove the dups

DECLARE @FirstName varchar(20),

            @LastName varchar(20),

            @ID int,

            @MaxID int,

            @NumToDelete int

SELECT  @MaxID = Max(ID),

            @ID = 0

FROM #Dups

WHILE @ID < @MaxID

      BEGIN

            -- Get next ID

            SELECT @ID = Min(ID)

            FROM #Dups

            WHERE ID > @ID

            -- Get other attributes for this ID

            SELECT  @FirstName = FirstName,

                        @LastName = LastName,

                        @NumToDelete = Qty - 1

            FROM #Dups

            WHERE ID = @ID

            -- Remove rows 

            DELETE TOP (@NumToDelete)

            FROM Employee

            WHERE FirstName = @FirstName

              AND LastName = @LastName

      END

-- Examine to verify dups have been removed

SELECT *

FROM Employee