Thursday, January 28, 2016

Don't Use BETWEEN with DATETIME

I'm not going to try to convince you in English not to use BETWEEN with dates. Run the code below to see why it's not a good idea. (Yes, I know there are new date types - this post isn't for you.)

-- Never use BETWEEN with dates.  Just use >= and < . 

-- Drop temp table. 
if object_id('tempdb..#MyDates') is not null  drop table #MyDates

-- Create a table of edge-case dates to demonstrate the issue. 
create table #MyDates ( MyDate datetime not null)
insert #MyDates (MyDate) values ('2015-10-31 23:59:59.997')     -- October 
insert #MyDates (MyDate) values ('2015-11-01 00:00:00.000')     -- November 
insert #MyDates (MyDate) values ('2015-11-01 00:00:00.003')     -- November 
insert #MyDates (MyDate) values ('2015-11-29 23:59:59.997')     -- November 
insert #MyDates (MyDate) values ('2015-11-30 00:00:00.000')     -- November 
insert #MyDates (MyDate) values ('2015-11-30 00:00:00.003')     -- November 
insert #MyDates (MyDate) values ('2015-11-30 23:59:59.997')     -- November 
insert #MyDates (MyDate) values ('2015-12-01 00:00:00.000')     -- December 

-- (1)  Now let's get all the November dates using BETWEEN. 
-- We want to get 6 rows back, but this returns 4!  Notice '2015-11-30 00:00:00.003' and 
-- '2015-11-30 23:59:59.997' are missing from the results. 
select MyDate as '(1)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30' order by MyDate   -- This is WRONG - do not use! 

-- (2)  Okay, that didn't work, so let's try extending out an extra day. 
-- We want to get 6 rows back, but this returns 7!  Notice '2015-12-01 00:00:00.000' is 
-- included in the results, but we only want November. 
select MyDate as '(2)' from #MyDates where MyDate between '2015-11-01' and '2015-12-01' order by MyDate   -- This is WRONG - do not use! 

-- (3)  Hmmm, let't try going up to the last second of the last day of November. 
-- We want to get 6 rows back, but this returns 5!  Notice '2015-11-30 23:59:59.997' 
-- is missing from the results, because it occurs *inside* that last second of November. 
select MyDate as '(3)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30 23:59:59' order by MyDate   -- This is WRONG - do not use! 

-- (4)  So how *does* one use BETWEEN with dates correctly?  This is the only way. 
-- (Why 997?  Google for "sql server datetime 997" for more info.) 
select MyDate as '(4)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30 23:59:59.997' order by MyDate   -- This is CORRECT! 

-- (5)  But who wants to type all that out?  Instead of using BETWEEN, do it this way. 
select MyDate as '(5)' from #MyDates where MyDate >= '2015-11-01' and MyDate < '2015-12-01' order by MyDate   -- This is CORRECT! 

-- Drop temp table. 
if object_id('tempdb..#MyDates') is not null  drop table #MyDates

Monday, January 18, 2016

OPENQUERY - Linked Server error "Deferred prepare could not be completed"

If you're using OPENQUERY and get this error:

OLE DB provider "SQLNCLI10" for linked server "MYREMOTESERVER" returned message "Deferred prepare could not be completed.".
Msg 916, Level 14, State 1, Line 1
The server principal "linkedServer" is not able to access the database "MyDatabase" under the current security context.

Check to be sure the SID of the login is the same as the SID of the database's user:

-- When copying a database from another server, you will need to run this 
-- if the instance already has a login with the same name (because the SIDs 
-- will be different because they came from different instances). 

-- SELECT DATABASE !!!  
use MyDatabase

-- Use this to get instance login sid.  
select SUSER_SID('linkedServer')

-- Display a list of users in current database and their User SID. 
exec sp_change_users_login @Action='report'

-- Set database user SID to corresponding instance login SID.  
alter user linkedServer with login = linkedServer

Wednesday, October 7, 2015

Finding All Databases Where User is not in Role

This script will find all the databases (matching a certain pattern) on the server where a given user is not a member of a given role. Because sometimes you need to know that.

-- Be sure to change the User and Role variables. 
declare @Sql      nvarchar(4000)    set @Sql = ''
declare @UserName sysname           set @UserName = 'Larry'
declare @RoleName sysname           set @RoleName = 'db_owner'

set @Sql = 'USE [?]

            IF ''?'' LIKE ''MYDATABASE%''
            BEGIN
                IF NOT EXISTS (SELECT * 
                                 FROM sys.database_role_members
                                WHERE USER_NAME(role_principal_id)   = ''' + @RoleName + '''
                                  AND USER_NAME(member_principal_id) = ''' + @UserName + ''')
                BEGIN
                    RAISERROR(''Database %s - user %s not in role %s'', 10, 1, ''?'',
                              ''' + @UserName + ''', ''' + @RoleName + ''') WITH NOWAIT
                END
            END'

exec sp_msforeachdb @Sql

Monday, August 17, 2015

Load all TRC Trace Files in a Folder into a Single Trace Table

This script will load all the trace (*.TRC) files in a given folder into a single trace table. You have to specify the database to create the trace table in, the folder the trace files are in, and the filename prefix (if any). Be careful: trace files can take up a lot of space in a hurry when imported into a table.

-- Load all TRC files in a given folder into a single trace table. 
set nocount on

-- Set these values. 
use MyMaintenanceDatabase
declare @TrcFolderName nvarchar(260)      set @TrcFolderName = 'C:\SQL_Trace\'
declare @TrcFilePrefix nvarchar(260)      set @TrcFilePrefix = 'MySpecificFilenamePrefixIfAny-'

-- Load the TRC filenames into temp table. 
declare @DosCmd nvarchar(4000)
set @DosCmd = 'dir /b ' + @TrcFolderName + @TrcFilePrefix + '*.trc'

if object_id('tempdb..#TrcFiles') is not null
    drop table #TrcFiles

create table #TrcFiles (TrcFileName nvarchar(260))

insert #TrcFiles
    exec master..xp_cmdshell @DosCmd 

delete #TrcFiles
 where TrcFileName is null

-- Iterate the trace files and read them into the trace table. 
declare @TrcFileName  nvarchar(260)
declare @TrcPathName  nvarchar(260)
declare @RowsAffected int

if object_id('dbo.trace_table') is not null
    drop table dbo.trace_table

declare cur cursor for
    select TrcFileName from #TrcFiles 
     
open cur
fetch next from cur into @TrcFileName

while @@fetch_status = 0
begin
    set @TrcPathName = @TrcFolderName + @TrcFileName
    raiserror('%s', 10, 1, @TrcPathName) with nowait

    if object_id('dbo.trace_table') is null
        select * into dbo.trace_table from ::fn_trace_gettable (@TrcPathName, /* number_files */ 1)
    else
        insert dbo.trace_table
            select * from  ::fn_trace_gettable (@TrcPathName, /* number_files */ 1)

    set @RowsAffected = @@rowcount
    raiserror('   %9d rows inserted', 10, 1, @RowsAffected) with nowait
    
    fetch next from cur into @TrcFileName
end

close cur
deallocate cur

-- Quirk in trace: update NULL DatabaseName's from their ID's. 
update dbo.trace_table
   set DatabaseName = db_name(DatabaseID)
 where isnull(DatabaseName, '')  = ''

set @RowsAffected = @@rowcount
raiserror('   %9d NULL DatabaseNames updated from their IDs', 10, 1, @RowsAffected) with nowait

-- Example cleanup: delete all rows that don't show their database. 
delete dbo.trace_table
 where DatabaseName is null

set @RowsAffected = @@rowcount
raiserror('   %9d NULL DatabaseNames deleted', 10, 1, @RowsAffected) with nowait

-- Example results: find unique logins per database per day. 
  select dateadd(dd, 0, datediff(dd, 0, StartTime))   as 'LoginDate'
       , LoginName
       , DatabaseName
    from dbo.trace_table
group by dateadd(dd, 0, datediff(dd, 0, StartTime))
       , LoginName
       , DatabaseName
order by LoginDate desc
       , LoginName
       , DatabaseName

Monday, April 20, 2015

Find Orphaned MDF, NDF and LDF Files on All Drives

Sometimes you run across a server that for one reason or another is full of MDF, NDF, and LDF files that look to be months or years old. Of course, you can't go by the Date Modified timestamp of these files in Windows Explorer, but still, when you see a 300 GB file called "BOBS_TEST_DB.mdf" from three years ago, doesn't your finger inch closer to the 'Delete' key? If only there was a way to tell if any databases were really using files like this...

Well now there is! This script will scan all the fixed drives, looking for MDF, NDF, and LDF files, and then verify they're not being used by any database.Once you find them, I wouldn't just delete them from the disk. The smart thing to do is move them all to another folder, wait a month for someone to complain - or some process to break - and only then delete them.

------------------------------------------------------------------------------- 
-- Generates a list of MDF, NDF, and LDF files that do not have a matching      
-- entry in the instance - that is, finds the orphaned ones.  You can exclude   
-- specific drives, and specific folders.                                       
------------------------------------------------------------------------------- 

set nocount on

-- Get the set of fixed drives. Some drives you'll want to exclude. 
if object_id('tempdb..#tmpDrives') is not null
    drop table #tmpDrives
 
create table #tmpDrives
(
    Drive        char(1) not null,
    MBFreeUnused int     not null
)

insert #tmpDrives  exec xp_fixeddrives

delete from #tmpDrives
 where Drive in ('C')

-- Iterate through all the fixed drives, looking for database files. 
-- Some files we'll want to delete. 
if object_id('tempdb..#tmpOsFiles') is not null
    drop table #tmpOsFiles
 
create table #tmpOsFiles
(
    OsFile varchar(260) null
)

declare @Drive char(1)
declare @Sql nvarchar(4000)

declare cur cursor for
    select Drive from #tmpDrives

open cur
fetch next from cur into @Drive

while @@fetch_status = 0
begin
    raiserror(@Drive, 10, 1) with nowait

    set @Sql = 'dir ' + @Drive + ':\*.mdf /a-d /a-h /a-s /a-r /b /s'
    insert #tmpOsFiles  exec xp_cmdshell @Sql

    set @Sql = 'dir ' + @Drive + ':\*.ndf /a-d /a-h /a-s /a-r /b /s'
    insert #tmpOsFiles  exec xp_cmdshell @Sql

    set @Sql = 'dir ' + @Drive + ':\*.ldf /a-d /a-h /a-s /a-r /b /s'
    insert #tmpOsFiles  exec xp_cmdshell @Sql

    fetch next from cur into @Drive
end

close cur
deallocate cur

delete from #tmpOsFiles
 where OsFile is null
    or OsFile = 'File Not Found'
    or OsFile like '%:\$RECYCLE_BIN\%' escape '~' 
    or OsFile like '%:\Program Files\Microsoft SQL Server%' escape '~' 
    or OsFile like '%:\SW_DVD9_SQL_Svr_Enterprise_Edtn_2008_R2_English_MLF_X16-29540%' escape '~'

-- For each file, get the date modified and the size.  The dir command gives 
-- use a line like this:10/08/2013  02:37 PM            228253 TLXPVSQL01_TSS_ERD.png
alter table #tmpOsFiles
  add DtModified datetime null default(''),
      SizeMB     int      null default('')

declare @Dir    nvarchar(260)
declare @OsFile nvarchar(260)

if object_id('tempdb..#tmpOsFileDetails') is not null
    drop table #tmpOsFileDetails
 
create table #tmpOsFileDetails
(
    OsFileDetails nvarchar(4000) null
)

declare cur cursor for
    select OsFile from #tmpOsFiles

open cur

fetch next from cur into @OsFile

while @@fetch_status = 0
begin
    set @Sql = 'dir "' + @OsFile + '" /-c'
    insert #tmpOsFileDetails  exec xp_cmdshell @Sql

    delete from #tmpOsFileDetails
     where OsFileDetails is null
        or OsFileDetails like '%Volume in drive % is %'
        or OsFileDetails like '%Volume Serial Number is %'
        or OsFileDetails like '%1 File(s) %'
        or OsFileDetails like '%0 Dir(s) %'

    select @Dir = rtrim(ltrim(replace(OsFileDetails, 'Directory of', ''))) + '\'
      from #tmpOsFileDetails
     where OsFileDetails like '%Directory of %'

    delete from #tmpOsFileDetails
     where OsFileDetails like '%Directory of %'
    
    update #tmpOsFiles
       set DtModified = substring(ofd.OsFileDetails,  1, 20),
           SizeMB     = cast(substring(ofd.OsFileDetails, 21, 19) as bigint) / 1024 / 1024
      from #tmpOsFileDetails   ofd
      join #tmpOsFiles         os
        on os.OsFile = @Dir + substring(ofd.OsFileDetails, 40, 4000)

    delete from #tmpOsFileDetails

    fetch next from cur into @OsFile
end

close cur
deallocate cur

-- Now list the OS files that don't have an entry in the instance. 
    select os.*
         , datediff(Day, os.DtModified, getdate()) as DaysOld
      from master.sys.master_files   mf
right join #tmpOsFiles               os
        on mf.physical_name = os.OsFile
     where mf.physical_name is null
  order by os.SizeMB desc

Wednesday, April 15, 2015

Display Fragmentation on All Indexes in Current Database

This code displays the fragmentation on all indexes in the current database. (It excludes empty tables.)

-- SQL Server 2005+.  Show index fragmentation on all tables on current database. 
set nocount on

declare @DbId smallint
set @DbId = db_id()

  select quotename(db_name())                    as DatabaseName
       , quotename(object_name(ips.object_id))   as TableName
       , quotename(i.name)                       as IndexName
       , ips.page_count                          as PageCount
       , ips.page_count * 8 / 1024               as IndexSizeMB
       , ips.fragment_count                      as FragCount
       , ips.avg_fragmentation_in_percent        as AvgFrag
       , ips.index_type_desc                     as IndexType
    from sys.dm_db_index_physical_stats(@DbId, NULL, NULL, NULL, NULL)   ips
    join sys.indexes                                                     i
      on ips.object_id = i.object_id
     and ips.index_id  = i.index_id
   where i.index_id    <> 0
     and ips.page_count > 0
order by FragCount desc

Display Stale Index Statistics

This query displays all the index statistics in the current database that are more than 'n' days old.

-- When were Statistics last updated on all indexes more than 7 days ago?  
  select o.name                                as 'Table Name'
       , i.name                                as 'Index Name'
       , stats_date(i.object_id, i.index_id)   as 'Statistics Date'
       , s.auto_created
       , s.no_recompute
       , s.user_created
    from sys.objects   o
    join sys.indexes   i
      on o.object_id = i.object_id
    join sys.stats     s
      on i.object_id = s.object_id
     and i.index_id    = s.stats_id
   where o.type = 'U'
     and datediff(Day, stats_date(i.object_id, i.index_id), getdate()) > 7
order by [Statistics Date] desc

Tuesday, April 14, 2015

CMS: Find All Databases in all Servers that don't have a Specified Table

This code is for CMS (Central Management Servers), and displays all the databases on all the servers that don't have a specified table.

-- For all user databases in all servers, which ones don't have a "Customer" table? 
set nocount on

declare @Schema sysname     set @Schema = 'dbo'
declare @Table  sysname     set @Table  = 'Customer'
declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

    if db_id() > 4
        if object_id(N''' + @Schema + '.' + @Table + ''', N''U'') is null
            print @@SERVERNAME + '': '' + ''?'' + '' - ' + @Schema + '.' + @Table + ' does not exist'''

exec sp_msforeachdb @Sql

Monday, April 13, 2015

Run DBCC UPDATEUSAGE on All Databases in an Instance

A few lines of code for those of us migrating SQL Server 2000 databases to a higher version of SQL Server and having CHECKDB fail because the counts are wrong. This simply runs DBCC UPDATEUSAGE on all the databases in the instance. I can't say it any better than BOL:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

-- This can take a while.... 
set nocount on
declare @Sql nvarchar(4000)
set @Sql = 'RAISERROR(''?'', 10, 1) WITH NOWAIT; DBCC UPDATEUSAGE ([?])'
exec sp_MSforeachdb @Sql

Interestingly, even for SQL Server 2014, BOL says this:

Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.

Thursday, February 26, 2015

Adding a User to Multiple Databases

Everyone seems to have their own version. Mine is as simple as I could make it. Make sure you create the LOGIN first, and set the @User variable in the code below.

Assumes you have already created a login named @User . 
set nocount on

declare @User sysname
set @User = 'trespasserswill'                  -- Change this!! 

declare @Sql nvarchar(4000)

set @Sql =
'
    use [?]

    if db_id() > 4
    begin
        print ''?''
        create user ' + quotename(@User) + ' for login ' + quotename(@User) + '
        exec sp_addrolemember ''db_datareader'', ''' + @user + '''
        exec sp_addrolemember ''db_datawriter'', ''' + @user + '''
    end
'

exec sp_MSForEachDB @Sql

Tuesday, February 17, 2015

Display All Databases Using FULL Recovery Model, Yet Having No Log Backups

If a database is using the FULL Recovery Model, it obviously needs to be doing log backups - otherwise the log will eventually fill the disk (your "eventually" may vary). If you don't need log backups, change the database to SIMPLE Recovery Model and sleep easier at night. But, you know how it is - one way or another, databases wind up in this mongrel state. Run this script on your oldest server - you'll probably be surprised.

This script also prints out the T-SQL command to set each database to SIMPLE Recovery Model, so you can just copy and paste.

-- Find databases in FULL Recovery Model with no log backups. 
   SELECT d.name                                            AS 'DatabaseName'
        , MAX(db.recovery_model_desc)                       AS 'Model'
        , MAX(ISNULL(b.backup_finish_date, '1900-01-01'))   AS 'LogBackupFinishDate'
        , 'ALTER DATABASE ' + QUOTENAME(d.name) 
        + '  SET RECOVERY SIMPLE WITH NO_WAIT'              AS 'SqlStatement'
     FROM sys.sysdatabases   d
     JOIN sys.databases      db
       ON d.dbid = db.database_id
LEFT JOIN msdb..backupset    b
       ON b.database_name = d.name
      AND b.type          = 'L'
    WHERE db.recovery_model_desc = 'FULL'
      AND db.state_desc          = 'ONLINE'
      AND db.database_id         > 4
      AND db.is_read_only        = 0
 GROUP BY d.name
   HAVING MAX(ISNULL(b.backup_finish_date, '1900-01-01')) < DATEADD(Day, -180, GETDATE())
 ORDER BY d.name

Script to verify if "Instant File Initialization" is Enabled

Instant file initialization is a wonderful feature of the Windows file system - not of SQL Server, please note -- that allows the server to allocate disk pages without having to set each bit to zero. You can imagine the time savings when SQL Server is, say, adding a GB to a data file. Note that because of the way they embedded information in the first few bytes of the actual pages of the log files themselves - sigh - log files can't use Instant File Initialization. But still, it's a must-have (unless the idea of having old data laying around in your pages gives your security people the willies - best check with them). You can find lots of info about this all over the web, but here's a little script that will verify that you've jumped through all the hoops correctly.

--  Once you have added the SQL Server security account to this security policy 
--  you need to then execute gpupdate /force from the command prompt and restart 
--  the SQL Service for this change to take effect.

USE master
SET NOCOUNT ON

-- If a database named DummyTestDB_VerifyIFI already exists drop it. 
IF DB_ID('DummyTestDB_VerifyIFI') IS NOT NULL
    DROP DATABASE DummyTestDB_VerifyIFI

-- Temp table to hold output from sp_readerrorlog. 
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL 
    DROP TABLE #SqlLogs

GO

CREATE TABLE #SqlLogs(LogDate datetime, ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))

-- Turn on trace flags 3004 and 3605. 
DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS

-- Create a dummy database to see the output in the SQL Server Errorlog. 
CREATE DATABASE DummyTestDB_VerifyIFI 
GO

-- Turn off trace flags 3004 and 3605. 
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

-- Remove the dummy database. 
DROP DATABASE DummyTestDB_VerifyIFI

-- Now go check the output in the current SQL Server errorlog File. 
-- This can take a while if you have a large errorlog file. 
INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)
    EXEC sp_readerrorlog 0, 1, 'Zeroing'

IF EXISTS (SELECT * FROM #SqlLogs
            WHERE TEXT LIKE 'Zeroing completed%'
              AND TEXT LIKE '%DummyTestDB_VerifyIFI.mdf%'
              AND LogDate > DATEADD(HOUR, -1, LogDate))
    PRINT 'We do NOT have instant file initialization.'
ELSE
    PRINT 'We have instant file initialization.'

Tuesday, February 10, 2015

Rename SQL Agent Operator and all its Notifications

This script will change an Operator's name and update all three notification option types the Operator has.


-- Will change from the old Operator to the new Operator for all 
-- SQL Agent Alerts and Jobs for all three notification options. 
use msdb
set xact_abort on

-- Set these variables to the old and new Operator of your choice. 
-- This is the only code you have to change. 
declare @OperatorNameOld sysname
declare @OperatorNameNew sysname
set @OperatorNameOld = 'My Old Operator Name'
set @OperatorNameNew = 'My New Operator Name'

-- Verify your old operator exists. 
if not exists (select * from sysoperators where name = @OperatorNameOld)
    raiserror('   *** @OperatorNameOld ''%s'' does not exist!', 20, 1, @OperatorNameOld) with nowait, log

print 'Old operator name is ''' + @OperatorNameOld + ''' '

-- Verify your new operator exists. 
if not exists (select * from sysoperators where name = @OperatorNameNew)
    raiserror('   *** @OperatorNameNew ''%s'' does not exist!', 20, 1, @OperatorNameNew) with nowait, log

print 'New operator name is ''' + @OperatorNameNew + ''' '

-- Iterate through the Alerts and change the operator. 
declare @AlertNameVar            sysname
declare @IncludeEventDescription tinyint

declare cur cursor for
    select sa.name                        as AlertName,
           so.name                        as OperatorNameOld,
           sa.include_event_description   as IncludeEventDescription
      from sysalerts          sa
      join sysnotifications   sn
        on sa.id = sn.alert_id
      join sysoperators       so
        on sn.operator_id = so.id
     where so.name = @OperatorNameOld

open cur
fetch next from cur into @AlertNameVar, @OperatorNameOld, @IncludeEventDescription

while @@fetch_status = 0
begin
    print 'Alert ''' + @AlertNameVar + ''' - adding new operator ''' + @OperatorNameNew + ''' '

    exec sp_add_notification
        @alert_name          = @AlertNameVar,
        @operator_name       = @OperatorNameNew,
        @notification_method = @IncludeEventDescription

    print 'Alert ''' + @AlertNameVar + ''' - deleting old operator ''' + @OperatorNameOld + ''' '

    exec sp_delete_notification
        @alert_name    = @AlertNameVar,
        @operator_name = @OperatorNameOld

    fetch next from cur into @AlertNameVar, @OperatorNameOld, @IncludeEventDescription
end

close cur
deallocate cur

-- Iterate through the Jobs and change the operator 
-- for each of the three kinds of notifications. 
declare @JobNameVar             sysname
declare @OperatorNameMailOld    sysname
declare @OperatorNamePageOld    sysname
declare @OperatorNameNetSendOld sysname

declare cur cursor for
    select sj.name          as JobName,
           soMail.name      as OperatorNameMailOld,
           soPage.name      as OperatorNamePageOld,
           soNetSend.name   as OperatorNameNetSendOld
      from sysjobs        sj
 left join sysoperators   soMail
        on sj.notify_email_operator_id = soMail.id
 left join sysoperators   soPage
        on sj.notify_page_operator_id = soPage.id
 left join sysoperators   soNetSend
        on sj.notify_netsend_operator_id = soNetSend.id
     where (soMail.name    = @OperatorNameOld
        or  soPage.name    = @OperatorNameOld
        or  soNetSend.name = @OperatorNameOld)

open cur
fetch next from cur into @JobNameVar, @OperatorNameMailOld, @OperatorNamePageOld, @OperatorNameNetSendOld

while @@fetch_status = 0
begin
    -- Mail Job notification. 
    if @OperatorNameMailOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing Mail operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                   = @JobNameVar, 
            @notify_email_operator_name = @OperatorNameNew
    end

    -- Page Job notification. 
    if @OperatorNamePageOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing Page operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                  = @JobNameVar, 
            @notify_page_operator_name = @OperatorNameNew
    end

    -- NetSend Job notification. 
    if @OperatorNameNetSendOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing NetSend operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                     = @JobNameVar, 
            @notify_netsend_operator_name = @OperatorNameNew
    end

    fetch next from cur into @JobNameVar, @OperatorNameMailOld, @OperatorNamePageOld, @OperatorNameNetSendOld
end

close cur
deallocate cur

-- Delete old operator. 
exec msdb.dbo.sp_delete_operator @name = @OperatorNameOld
print 'Dropped old operator name ''' + @OperatorNameOld + ''' '

go

How to Find Operators with a Specific Email Address

This is one of those that I can never remember, for some reason. This is very handy in CMS, where you can search all your servers at once.

-- Find Operator for email address. 
  select distinct o.name     as 'Operator Name'
       , o.email_address     as 'Email Address'
    from msdb..sysoperators   o
   where @@servername not like 'BAD_SERVERS%'
     and o.email_address = 'MyEmailAddress@mydomain.com'
order by o.name
       , o.email_address

Monday, January 19, 2015

Using "msdb.dbo.agent_datetime" to Simplify Working with sysjobhistory

Just learned about the undocumented msdb.dbo.agent_datetime() function. Where has this been all my life? It takes an 8 digit date and a 6-digit time and converts it to a DATETIME - quite a time-saver.

-- An example of where it's really handy. 
   select j.name
        , jh.run_date
        , jh.run_time
        , msdb.dbo.agent_datetime(jh.run_date, jh.run_time)   as 'RunDate'
        , jh.step_id
        , jh.step_name
        , jh.message
     from msdb.dbo.sysjobs         j
     join msdb.dbo.sysjobhistory   jh
       on j.job_id = jh.job_id
 order by msdb.dbo.agent_datetime(run_date, run_time) desc

Beats trying to work with those columns yourself... but remember it's undocumented.

Friday, December 5, 2014

Find all Databases with AutoShrink Turned On

As far as I can tell, the only thing that all SQL Server DBAs agree on is that AutoShrink should be turned off. You never even see anyone say, "It depends." This level of agreement is, I think, unique in all of SQL Server-land. I mean, you would think we'd all agree that clustering on a non-sequential GUID is a Very Bad Idea, but there's actually people who argue in favor of it. Go figure.

So anyway, the script below will find all the databases (SQL Server 2000 and up) that for some mysterious reason have it turned on. (Of course, we all know the reason is that some yo-yo doesn't understand that big, empty databases run faster than small, full databases, but that's another story...) If you run this script on CMS, it will work across all the servers in the group (that's why we cursor through the resultsets instead of just returning them - it makes the output in CMS much easier to read).

-- Find all databases with AutoShrink turned on. 
declare @DatabaseName sysname

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from sys.databases
         where is_auto_shrink_on = 1
           and state_desc = 'ONLINE'
           and database_id > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end
else
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from master..sysdatabases
         where status & 4194304 = 4194304
           and status & 512 = 0
           and dbid > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end

Find all Occurrences of Text in all Stored Procedures in all Databases

Sometimes you'd like to find all occurrences of a piece of text in every stored procedure, trigger, etc., possibly to replace it with a new value. The script below will do just that; it returns the database name, the object name, and the object type. Note that it works for both SQL Server 2000 and post-SQL Server 2000 versions.

One thing to note is that the @TextToFind uses the LIKE operator with its ESCAPE argument set to the dollar sign symbol. I added the ESCAPE argument just to make it easier to search for text such containing the underscore character, such as 'My_HardToFind_Thing". Of course, if you're searching for text with a dollar sign symbol in it, you'll want to change the ESCAPE argument to something else.

(The script below can also be run in CMS, which will allow you to also return results across server groups.)

-- Simply set the text you want to look for here. 
declare @TextToFind nvarchar(4000)
set @TextToFind = 'THIS IS MY TEXT TO FIND'

declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

     declare @DatabaseName sysname
     declare @ProcName     sysname
     declare @ProcType     nvarchar(60)

     if left(convert(nvarchar, serverproperty(''productversion'')), 1) <> ''8''
     begin
         declare cur cursor local fast_forward for
            select distinct ''?''          as ''Database Name''
                          , o.name         as ''Object Name''
                          , o.type_desc    as ''Object Type''
              from sys.sql_modules   m
              join sys.objects       o
                on m.object_id = o.object_id
             where m.definition like ''%' + @TextToFind + '%'' escape ''$''

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    else
    begin
         declare cur cursor local fast_forward for
            select distinct ''?''               as ''Database Name''
                          , object_name(c.id)   as ''Object Name''
                          , o.xtype             as ''Object Type''
              from syscomments   c
              join sysobjects    o
                on c.id = o.id
             where c.[text] like ''%' + @TextToFind + '%'' escape ''$''
               and objectproperty(c.id, ''isprocedure'') = 1

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    
    '

exec sp_msforeachdb @Sql

Wednesday, November 5, 2014

Get SQL Agent Job History Durations in Units of Seconds

For some reason, the run_duration column of the sysjobhistory system table is stored as an integer in HHMMSS format. I don't know who thought this was a good idea, but here's some code to list all your Jobs' history, with their duration in units of seconds.

-- Note that, hilariously, "run_duration" is an INT in HHMMSS format. 
use msdb
set nocount on

  select jh.server                                             as 'Server Name'
       , convert(datetime, convert(varchar(8), jh.run_date))   as 'Run Date'
       , sum(jh.run_duration / 10000 * 3600
       + jh.run_duration % 10000 / 100 * 60
       + jh.run_duration % 100)                                as 'Duration Seconds'
    from sysjobs         j
    join sysjobhistory   jh
      on j.job_id = jh.job_id
   where j.enabled = 1
     and jh.step_id = 1
     and jh.run_status = 1
group by jh.server
       , jh.run_date
order by jh.run_date
       , jh.server

Thursday, October 30, 2014

Two Simple Ways to Find Last SQL Server Restart

The first command below will tell you the last time the SQL Server service was started. The second will tell you the last time the server itself was restarted.

-- Format will be:  2013-01-19 14:13:53.243 
select sqlserver_start_time from sys.dm_os_sys_info

-- Look for the row like:  Statistics since 1/18/2013 12:27:01 PM 
exec xp_cmdshell 'net statistics server'

Friday, October 17, 2014

Script to Create Multiple, Same-Sized Tempdb Files

This script will take a tempdb database with one database file, and create a set of equally-sized files. You can set how much total space you want the files to use, and how many files to have (in total).

From BOL, as a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs. Make each data file the same size; this allows for optimal proportional-fill performance.

"A more pragmatic approach however, is to have a 1:1 mapping between files and logical CPUs up to eight, and then add files if you continue to see allocation contention or if you're looking to push the I/O subsystem harder. The performance benefit from adding files diminishes each time, and in our experience, eight is the sweet spot, especially if you're implementing this as a pro-active measure." (Configuration Best Practices for SQL Server Tempdb--Multiple Files)

These calculations assume that currently the tempdb database has only one datafile. Also, the log file for tempdb is assumed to be on a different drive.

USE master
SET NOCOUNT ON

-- Enter your values here.  
RAISERROR('Comment out this line once you have entered your values here!', 20, 1) WITH NOWAIT, LOG

DECLARE @TempdbTotalSizeGB INT               SET @TempdbTotalSizeGB = 266
DECLARE @NumberOfDataFiles INT               SET @NumberOfDataFiles =   8
DECLARE @TempdbFolder      NVARCHAR(260)     SET @TempdbFolder      = 'E:\MSSQL_TEMPDB\'

-- Check that there is only one tempdb datafile.  
DECLARE @DataFileNum INT

SELECT @DataFileNum = COUNT(*)
  FROM msdb.sys.master_files
 WHERE database_id = DB_ID('tempdb')
   AND type_desc = 'ROWS'

IF @DataFileNum <> 1
    RAISERROR('Tempdb does not have exactly one file - cannot use this script', 20, 1) WITH NOWAIT, LOG

-- Check that the path exists. 
DECLARE @t TABLE (FileExists BIT, FileIsDirectory BIT, ParentDirectoryExists BIT)
INSERT @t EXEC master..xp_fileexist @TempdbFolder

IF NOT EXISTS (SELECT * FROM @t WHERE FileIsDirectory = 1)
    RAISERROR('Folder "%s" does not exist.', 20, 1, @TempdbFolder) WITH NOWAIT, LOG

-- Calculate the size of each tempdb datafile. We're leaving 10% free for extra space. YMMV. 
DECLARE @DataFileSizeGB INT
SET @DataFileSizeGB = ((9 * @TempdbTotalSizeGB) / 10) / @NumberOfDatafiles

-- First, modify the existing tempdb datafile's size. 
DECLARE @CrLf NVARCHAR(2)
SET @CrLf = CHAR(13) + CHAR(10)

DECLARE @Sql NVARCHAR(4000)

SET @Sql = 'ALTER DATABASE tempdb '                                                                       + @CrLf
         + '   MODIFY FILE (NAME = ''tempdev'', '                                                         + @CrLf
         + '                SIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB, '                     + @CrLf
         + '                FILEGROWTH = 0KB, MAXSIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB)' + @CrLf

RAISERROR(@Sql, 10, 1) WITH NOWAIT
EXEC sp_executesql @Sql

-- Now create the additional datafiles for tempdb. 
SET @DataFileNum = 1

WHILE @DataFileNum < @NumberOfDatafiles
BEGIN
    SET @Sql = 'ALTER DATABASE tempdb '                                                                                 + @CrLf
             + '  ADD FILE (NAME = ''tempdev' + CAST(@DataFileNum AS NVARCHAR(50)) + ''', '                             + @CrLf
             + '            FILENAME = ''' + @TempdbFolder + 'tempdb' + CAST(@DataFileNum AS NVARCHAR(50)) + '.mdf'', ' + @CrLf
             + '            SIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB, '                                   + @CrLf
             + '            FILEGROWTH = 0KB, MAXSIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB) '              + @CrLf

    RAISERROR(@Sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @Sql
    SET @DataFileNum = @DataFileNum + 1
END

-- Finally, set the logfile's autogrowth for the tempdb database. The Microsoft  
-- default of 1% is not best practice. 
DECLARE @LogFileSizeGB INT
SET @LogFileSizeGB = 1

SET @Sql = 'ALTER DATABASE tempdb '                                                                            + @CrLf
         + '   MODIFY FILE (NAME = ''templog'', FILEGROWTH = ' + CAST(@LogFileSizeGB AS NVARCHAR(50)) + 'GB) ' + @CrLf

RAISERROR(@Sql, 10, 1) WITH NOWAIT
EXEC sp_executesql @Sql

GO