About Me

My Photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Monday, November 9, 2015

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.

Monday, August 17, 2015

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.


Tuesday, July 28, 2015

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.

1st 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

2nd 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



Friday, June 12, 2015

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 =

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


Friday, May 15, 2015

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.





Friday, April 10, 2015

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