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

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

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'

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

Run DBCC CHECKDB Against All Databases on Server

This script runs DBCC CHECKDB against every database on the server. It starts off with the simplest and fastest commands, and works up the the most stringent test. Note that TABLOCK keyword is commented out - you can use it at your own discretion. The commands that actually repair the database can't be run accidentally, and the version that can allow data loss - aptly named REPAIR_ALLOW_DATA_LOSS - is left as an exercise for the reader. (In other words, I'm not giving you that particular big, sharp knife!)

-- Run DBCC CHECKDB commands against all databases, including system. 
-- Read-only checks are run automatically. For safety's sake, to run 
-- the "repair" checks, you have to highlight that section and run it. 
-- SQL Server 2005+ 

set nocount on
declare @Sql nvarchar(4000)


--------------------------------------- 
--           READ-ONLY CHECKS           
--------------------------------------- 

-- Do quickest check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* ?? TABLOCK, */ PHYSICAL_ONLY, ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now check things beside what PHYSICAL_ONLY does: the integrity of the 
-- physical structure of the page and record headers, and the consistency 
-- between the pages' object ID and index ID and the allocation structures. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now also check the indexes. This is the most thorough check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'') WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

go


--------------------------------------- 
--           READ-WRITE CHECKS          
--                                      
-- "Use the REPAIR options only as a    
-- last resort. To repair errors, we    
-- recommend restoring from a backup."  
--------------------------------------- 

raiserror('    *** THESE ARE READ-WRITE CHECKS - BE SURE YOU KNOW WHAT YOU ARE DOING!', 16, 1) with nowait

set nocount on
declare @Sql nvarchar(4000)

-- Performs both minor, quick repairs, such as repairing extra keys in 
-- nonclustered indexes, and time-consuming repairs such as rebuilding 
-- indexes. These repairs can be performed without risk of data loss. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', REPAIR_REBUILD) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- If you want to use REPAIR_ALLOW_DATA_LOSS, you'll have to write your own command! 

go

CMS: List All Jobs that Do Not Notify on Failure

Using CMS, we can get a list of all the (scheduled and enabled) Jobs on all the servers that have no email (nor page, nor netsend) notification set up for Failure. This can be useful to find Jobs that are silently failing, and even Jobs that are no longer in use.

-- Find scheduled and enabled Jobs that aren't notifying on failure. 
set nocount on

-- notify_level_email
-- 0 - Never
-- 1 - On success
-- 2 - On failure
-- 3 - Always
  select j.name   as 'Job Name'
    from msdb..sysjobs           j
    join msdb..sysjobschedules   js
      on j.job_id = js.job_id
   where j.enabled = 1
     and j.notify_level_email   not in (2, 3)
     and j.notify_level_page    not in (2, 3)
     and j.notify_level_netsend not in (2, 3)
     and j.name not like 'CDW\_%' escape '\'
     and j.name not like 'dtexecRemote\_temp\_job\_%' escape '\'
     and j.name <> 'syspolicy_purge_history'
     and j.name <> 'MySpecialJob'
     and @@servername not in ('MYSERVER1', 'MYSERVER2')
order by j.name

CMS: List Jobs not Writing to the Windows Event Log

Using CMS, we can get a list of all the SQL Agent Jobs that aren't writing to the Windows Event Log upon failure, and set them to do so.

-- Check for Jobs that aren't writing to the Event Log. 
declare @JobName sysname

-- 0 - Never 
-- 1 - On success 
-- 2 - On failure 
-- 3 - Always 
declare @NotifyLevelEventLog int
set @NotifyLevelEventLog = 2

declare cur cursor fast_forward for
    select name
      from msdb..sysjobs
     where enabled = 1
       and notify_level_eventlog = 0
       and name <> 'syspolicy_purge_history'
       and name <> 'MySpecialJob'
       and @@servername not in ('MYSERVER1', 'MYSERVER2')
  order by name

open cur
fetch next from cur into @JobName

while @@fetch_status = 0
begin
    print 'Setting notify_level_eventlog on server ' + @@servername + ', Job "' + @JobName + 
          '" to ' + cast(@NotifyLevelEventLog as nvarchar(50))
    exec msdb..sp_update_job @job_name = @JobName, @notify_level_eventlog = @NotifyLevelEventLog
    fetch next from cur into @JobName
end

close cur
deallocate cur

Display ETA and Percent Completed for BACKUP or RESTORE Commands

This code will show you the estimated time of completion and percent completed for currently running BACKUP or RESTORE commands.

-- Shows both backups and restores.
select session_id                                      as 'spid'
     , command                                         as 'Command'
     , start_time                                      as 'StartTime'
     , (total_elapsed_time / 1000) / 60                as 'ElapsedMins'
     , cast(percent_complete as int)                   as '% Done'
     , convert(nvarchar,
               dateadd(ms, estimated_completion_time,
               current_timestamp), 120)                as 'ETA'
  from sys.dm_exec_requests
 where command like '%BACKUP DATABASE%'
    or command like '%RESTORE DATABASE%'

CMS: List all Jobs and Their Owners on All Servers

Using CMS, we can get a list of all the Jobs on all servers that are not owned the way we would like. The code below is just an example of how you can exclude by server name, job name, and owner name. SQL Server 2005+ only. Note that this example excludes the 'sa' user, as usually it's considered an allowable owner.

-- Exclude SQL Server 2000 servers, and some specific servers.
set nocount on

if '8' <> convert(nvarchar(1), serverproperty('ProductVersion'))
and @@servername not in ('MYDEVSERVER', 'MYTESTSERVER')

     select j.name   as JobName
          , l.name   as JobOwner
       from msdb..sysjobs   j
  left join sys.syslogins   l
         on j.owner_sid = l.sid
      where j.enabled = 1
        and j.name <>       'syspolicy_purge_history'
        and j.name not like 'CDW\_%'
        and j.name not like 'dtexecRemote%'
        and l.name <>       'sa'
        and l.name <>       'MYDOMAIN\MY_PREFERRED_OWNER'   -- Change this.
   order by JobName

CMS: Find, in All Servers and Databases, if given Table.Column's data is unique

Using CMS, we can determine if all the Customer.CustomerID values are unique, and therefore, for example, a candidate for being made a primary key.

-- Find, in All Servers and Databases, if given Table.Column's data is unique. 
set nocount on

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare @Schema sysname     set @Schema = 'dba'
    declare @Table  sysname     set @Table  = 'Customer'
    declare @Column sysname     set @Column = 'CustomerId'

    declare @Sql nvarchar(4000)

    set @Sql = 'use [?]

                if  exists (select *
                              from sys.tables
                             where name = ''' + @Table + '''
                               and schema_id(''' + @Schema + ''') = schema_id)
                begin
                    declare @NonUniqueCount int
                    
                    select @NonUniqueCount = count(*)
                      from ' + @Schema + '.' + @Table  + '
                  group by ' + @Column + '
                    having count(*) > 1

                    if @NonUniqueCount is not null
                    begin
                        print @@SERVERNAME +'': '' + ''?'' + '' - @NonUniqueCount: '' + cast(@NonUniqueCount as nvarchar(50))
                    end
                end'

    exec sp_msforeachdb @Sql
end

CMS: Find all Databases on all Servers with Table "A" without Column "B"

This CMS (Central Management Server) code will tell you, for all databases in all servers, which tables of a given name don't have a column of a given name.

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

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare @Schema sysname     set @Schema = 'dbo'
    declare @Table  sysname     set @Table  = 'Customer'
    declare @Column sysname     set @Column = 'ZipCode'

    declare @Sql nvarchar(4000)

    set @Sql = 'use [?]

                if exists (select *
                            from sys.tables
                           where name = ''' + @Table + '''
                             and schema_id(''' + @Schema + ''') = schema_id)
                begin
                    if not exists (select *
                                     from sys.columns
                                    where name = ''' + @Column + '''
                                      and object_name(object_id) = ''' + @Table + ''')
                    begin
                        print @@SERVERNAME + '': '' + ''?'' + '' - ' + @Table + ' table has no ' + @Column + ' column''
                    end
                end'

    exec sp_msforeachdb @Sql
end

Quick Query to Display Database by Dataspace, Table, and Index

This query just breaks down the current database by dataspace, table, and index, giving you some useful information such as size in MB, pathname of the dataspace file, etc.

-- Shows size of each object in each dataspace in the current database. 
  select db_name()                              as DatabaseName
       , ds.name                                as DataSpaceName
       , ds.type_desc                           as DataSpaceTypeDesc
       , o.name                                 as TableName
       , i.name                                 as IndexName
       , i.type_desc                            as IndexDesc
       , (sum(ps.used_page_count) * 8) / 1024   as IndexSizeMB
       , df.physical_name                       as PathName
    from sys.objects                 o
    join sys.indexes                 i 
      on o.object_id = i.object_id 
    join sys.data_spaces             ds 
      on ds.data_space_id = i.data_space_id
    join sys.database_files          df
      on ds.data_space_id = df.data_space_id
    join sys.dm_db_partition_stats   ps
      on i.object_id = ps.object_id
group by ds.name
       , o.name
       , i.name
       , ds.type_desc
       , i.type_desc
       , df.physical_name
order by ds.name
       , o.name
       , i.name

Use Central Management Servers to Alter a Column in All Servers

The "Central Management Servers" functionality of SQL Server Management Studio is an excellent way to apply the same code to every server. By "every server", I mean groups of servers that you define: you might have different servers in folders named "Production", "Development", "QA", "SQL Server 2000", etc. It just depends on your needs.

To get to the "Central Management Servers" pane in SSMS, simply select the "View, Registered Servers" menu item. After you've set up your servers, you can execute queries against every database in every server. The example below changes the width of a column in a table. Note that in this example, we look for only databases whose name begins with "DB100", we exclude read-only databases, and we specify the schema, table, and column name we want to modify. The code also checks that the length isn't already set to the value we want, to avoid unnecessary noise in the output. This code is compatible with SQL Server 2000 and later (hence the use of "syscolumns", etc.)

-- Comment. 
set nocount on
declare @Sql nvarchar(4000)

set @Sql = 'use [?]

            if exists (select *
                         from syscolumns   sc
                         join sysobjects   so
                           on sc.id = so.id
                        where ''?''                                   like ''DB100%''
                          and databaseproperty(''?'', ''IsReadOnly'')    = 0
                          and user_name(so.uid)                          = ''dbo''
                          and object_name(sc.id)                         = ''Customer''
                          and sc.name                                    = ''FirstName''
                          and sc.length                                 != 120)
            begin
                select ''?'', sc.length
                  from syscolumns   sc
                  join sysobjects   so
                    on sc.id = so.id
                 where ''?''                                   like ''DB100%''
                   and databaseproperty(''?'', ''IsReadOnly'')    = 0
                   and user_name(so.uid)                          = ''dbo''
                   and object_name(sc.id)                         = ''Customer''
                   and sc.name                                    = ''FirstName''
                   and sc.length                                 != 120

                ALTER TABLE dbo.Customer ALTER COLUMN FirstName VARCHAR(120) NULL
            end '

exec sp_msforeachdb @Sql
go

References

For more information on Central Management Servers:

     Create a Central Management Server and Server Group

     Registered Servers and Central Management Server Stores

     Execute SQL Server query on multiple servers at the same time