DavidWimbush

declare @DBName                 sysname

        , @LogicalDataFile      sysname

        , @LogicalLogFile       sysname

        , @PhysicalDataFile     nvarchar(260)

        , @PhysicalLogFile      nvarchar(260)

set @DBName = '<database-name>'

-- Data file

select  @LogicalDataFile = name

        , @PhysicalDataFile = physical_name

from    sys.master_files

where   database_id = db_id(@DBName)

        and type_desc = 'ROWS'

-- Log file

select  @LogicalLogFile = name

        , @PhysicalLogFile = physical_name

from    sys.master_files

where   database_id = db_id(@DBName)

        and type_desc = 'LOG'

select  @LogicalDataFile as [@LogicalDataFile]

        , @LogicalLogFile as [@LogicalLogFile]

        , @PhysicalDataFile as [@PhysicalDataFile]

        , @PhysicalLogFile as [@PhysicalLogFile]

if exists (    select     1

        from     dbo.sysobjects

        where     id = object_id(N'dbo.dba_depends')

            and objectproperty(id, N'IsProcedure') = 1    )

    drop procedure dbo.dba_depends

go

create proc dbo.dba_depends

(

    @SearchTerm        varchar(255)

    , @IncludeSystemDBs    bit = 0

)

as

/*

-------------------------------------------------------------------------------

Version : 1.5

Date     : 07/11/2007

Comments :    syscomments.xtype

        -----------------

        C = CHECK constraint

        D = Default or DEFAULT constraint

        F = FOREIGN KEY constraint

        L = Log

        FN = Scalar function

        IF = Inlined table-function

        P = Stored procedure

        PK = PRIMARY KEY constraint (type is K)

        RF = Replication filter stored procedure

        S = System table

        TF = Table function

        TR = Trigger

        U = User table

        UQ = UNIQUE constraint (type is K)

        V = View

        X = Extended stored procedure

-------------------------------------------------------------------------------

*/

declare @DBName        varchar(128)

    , @SearchThisDB    bit

create table #t

(

    DBName        varchar(128)    null

    , ObjectName    sysname        null

    , ObjectXType    char(2)        null

)

declare db_cursor cursor

local forward_only

for

select    [name]

from    sys.databases

where    state = 0 --ONLINE

order by name

open db_cursor

    fetch    next

    from    db_cursor

    into     @DBName

    while @@fetch_status = 0

    begin

        set @SearchThisDB = 1

        if @IncludeSystemDBs = 0 and lower(@DBName) in ('master','tempdb','model','msdb','reportserver','reportservertempdb')

            set @SearchThisDB = 0

        if @SearchThisDB = 1

            exec master.dbo.dba_depends_inner @SearchTerm, @DBName

        fetch    next

        from    db_cursor

        into     @DBName

    end

close db_cursor

deallocate db_cursor

select    distinct

    DBName

    , case

        when ObjectXType = 'FN' then 'Function'

        when ObjectXType = 'P' then 'Stored Procedure'

        when ObjectXType = 'IF' then 'Inlined table-function'

        when ObjectXType = 'TF' then 'Table function'

        when ObjectXType = 'TR' then 'Trigger'

        when ObjectXType = 'V' then 'View'

        when ObjectXType = 'X' then 'Extended stored procedure'

        when ObjectXType = 'U' then 'Table'

        else ObjectXType

    end as ObjectType

    , ObjectName

    , ObjectXType

from    #t

order by 1,2,3

drop table #t

go

if exists (    select     1

        from     dbo.sysobjects

        where     id = object_id(N'dbo.dba_depends_inner')

            and objectproperty(id, N'IsProcedure') = 1    )

    drop procedure dbo.dba_depends_inner

go

create proc dbo.dba_depends_inner

(

    @SearchTerm     varchar(255)

    , @DBName    varchar(128)

)

as

/*

-------------------------------------------------------------------------------

Version : 1.3

Date     : 12/03/2007

-------------------------------------------------------------------------------

*/

set nocount on

declare @SQL nvarchar(500)

set @SQL = 'use [' + @DBName + '] ' +

'insert    #t

    (DBName

    , ObjectName

    , ObjectXType)

select     ''' + @DBName +

'''    , so.[name]

    , so.xtype

from     syscomments sc

    inner join sysobjects so on so.[id] = sc.[id]

where     so.xtype in (''FN'', ''P'', ''IF'', ''TF'', ''TR'', ''V'', ''X'')

    and sc.[text] like ''%' + @SearchTerm + '%''

order by sc.[id], sc.colid'

exec sp_executesql @sql

set @SQL = 'use [' + @DBName + '] ' +

'insert    #t

    (DBName

    , ObjectName

    , ObjectXType)

select     ''' + @DBName +

'''    , obj.name + ''.'' + col.name

    , ''U''

from    sysobjects obj

    inner join syscolumns col

    on obj.id = col.id

where    obj.xtype in (''U'')

    and col.name like ''%' + @SearchTerm + '%'''

exec sp_executesql @sql

set nocount off

go

--Delete earliest day of backup and restore history if it's older than 30 days.

--This way you can clear a long history a bit at a time.

use msdb

go

set nocount on

declare @oldest_backup_date    smalldatetime

    , @oldest_restore_date    smalldatetime

    , @oldest_date        smalldatetime

--Find the oldest backup date (can be null)

select    @oldest_backup_date = min(backup_finish_date)

from    backupset with (nolock)

where    backup_finish_date is not null

set @oldest_backup_date = isnull(@oldest_backup_date, '01-JUN-2079')

--Find the oldest restore date (can be null)

select    @oldest_restore_date = min(restore_date)

from    restorehistory with (nolock)

where    restore_date is not null

set @oldest_restore_date = isnull(@oldest_restore_date, '01-JUN-2079')

--Determine the earlier of the two dates

if @oldest_restore_date < @oldest_backup_date

    set @oldest_date = @oldest_restore_date

else

    set @oldest_date = @oldest_backup_date

print 'Oldest date is ' + convert(varchar(10), @oldest_date, 103)

--If it's more than 30 days ago delete it

if @oldest_date >= dateadd(day, -30, getdate())

begin

    print 'no action'

end

else

begin

    print 'deleting history'

    --sp_delete_backuphistory deletes everything earlier than @oldest_date.

    --Add 1 day otherwise it won't delete anything.

    set @oldest_date = dateadd(day, 1, @oldest_date)

    exec sp_delete_backuphistory @oldest_date

end

-- msdb performance indexes for SQL Server 2005

-- History:

-- 12/12/2005 Geoff N. Hiten Created (http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx).

-- 06/07/2009 David Wimbush   Commented out indexes that Microsoft have implemented.

--                            Added an index on restorehistory restore_date.

use msdb

go

--backupset

--implemented by Microsoft

--create index IX_backupset_backup_set_id on backupset(backup_set_id)

--go

--implemented by Microsoft

--create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)

--go

create index IX_backupset_media_set_id on backupset(media_set_id)

go

create index IX_backupset_backup_finish_date on backupset(backup_finish_date)

go

create index IX_backupset_backup_start_date on backupset(backup_start_date)

go

--backupmediaset

--implemented by Microsoft

--create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)

--go

--backupfile

--implemented by Microsoft

--create index IX_backupfile_backup_set_id on backupfile(backup_set_id)

--go

--backupmediafamily

--implemented by Microsoft

--create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)

--go

--restorehistory

--implemented by Microsoft

--create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)

--go

--implemented by Microsoft

--create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)

--go

--NEW:

create index IX_restorehistory_restore_date on restorehistory(restore_date)

go

--restorefile

create index IX_restorefile_restore_history_id on restorefile(restore_history_id)

go

--restorefilegroup

create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)

go