Thursday, August 28, 2014

DATABASE Backup Recovery Model and Last Completion Date

select @@servername, * from


-- Query last backup





        MAX(b.backup_finish_date) AS backup_finish_date

FROM     master.sys.databases d

         LEFT OUTER JOIN msdb..backupset b

         ON       b.database_name =

         --AND      b.type          = 'L'

GROUP BY, b.type, d.recovery_model_desc

--ORDER BY backup_finish_date DESC

) a


Tuesday, August 26, 2014

Script to identify index sizing without cross join

SELECT                  AS IndexName,

    SUM(s.used_page_count) * 8   AS IndexSizeKB

FROM sys.dm_db_partition_stats  AS s

JOIN sys.indexes                AS i

ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

WHERE s.[object_id] = object_id('YOURTABLENAMEHERE')




Log file space available

-- (with Mark Nelson)

USE master



Create Table ##temp


    DatabaseName sysname,

    Name sysname,

    physical_name nvarchar(500),

    FileSizeMB decimal (18,2),

                SpaceUsedMB decimal (18,2),

    FreeSpaceMB decimal (18,2)


Exec sp_msforeachdb '

Use [?];

Insert Into ##temp (DatabaseName, Name, physical_name, FileSizeMB, SpaceUsedMB, FreeSpaceMB)

    Select DB_NAME() AS [DatabaseName], Name,  physical_name,

    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) FileSizeMB,


    Cast(Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) as SpaceUsedMB,


    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -

        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpaceMB


    From sys.database_files


Select * From ##temp WHERE right(physical_name,3) = 'ldf'

drop table ##temp

Shrink all the logs on a server

-- (in conjunction with Mark Nelson)



SET @cmd = 'USE [?] ;


declare @filename varchar(1000), @dbcc varchar(2000)

select @filename = name from sys.sysfiles where filename like ''%ldf''

print @filename

set @dbcc = ''dbcc shrinkfile (xx, 1000)''

set @dbcc = REPLACE(@dbcc, ''xx'' ,@filename)

exec (@dbcc)



Exec sp_msforeachdb @cmd

Code to remove a single plan from cache

-- Remove one plan from the cache

-- Get the plan handle for a cached plan

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%YourProcNameHere%';


-- Remove the specific plan from the cache using the plan handle

DBCC FREEPROCCACHE (0x06001C00FCA2651EF08667E42A00000001000000000000000000000000000000000000000000000000000000);


Code to identify unused and lightly used indexes

This code looks in all databases for a server...

(revised in conjunction with Mark Nelson)


CREATE TABLE tempdb..TempIndexDrop(

       [Database_Name] [nvarchar](128) NULL,

       [Object_Name] [nvarchar](128) NULL,

       [Index_Name] [sysname] NULL,

       [Drop_Statement] [nvarchar](283) NULL,

       [user_seeks] [bigint] NOT NULL,

       [user_scans] [bigint] NOT NULL,

       [user_lookups] [bigint] NOT NULL,

       [SpaceSavedMeg] decimal (12,2) not null



SET @cmd = 'USE [?] ;


INSERT INTO tempdb..TempIndexDrop select db_name(database_id) as ''Database_Name'', object_name(i.object_id) as ''Object_Name'', as ''Index_Name'', ''drop index ['' + + ''].['' + '''' + ''].['' + + '']'' as ''Drop_Statement'',

user_seeks, user_scans, user_lookups,

(select sum(s.used_page_count)*8./1024. from sys.dm_db_partition_stats s where i.object_id = s.object_id and i.index_id = s.index_id)

from sys.dm_db_index_usage_stats ius join sys.indexes i

on ius.index_id = i.index_id and ius.object_id = i.object_id join sys.objects obj

on ius.object_id = obj.object_id join            sys.schemas sch

on            obj.schema_id = sch.schema_id

where       database_id = db_id(''?'') and user_seeks + user_scans + user_lookups <100 and="" span="" style="mso-spacerun: yes;"> not like ''PK_%''   and     object_name(i.object_id) NOT LIKE (''sys%'') 

order by       object_name(i.object_id),'



--print @cmd

Exec sp_msforeachdb @cmd


SELECT SUM(SpaceSavedMeg) as 'Potential Storage Savings' FROM  tempdb..TempIndexDrop


SELECT * FROM tempdb..TempIndexDrop

ORDER BY Database_Name, Object_Name


DROP TABLE tempdb..TempIndexDrop

Revised script to identify missing indexes

Remember NOT to simply apply all of these indexes without investigating frequency &/or similar indexes:


set concat_null_yields_null off
      'create index IX_' +

            replace(replace(replace (equality_columns, '[', ''),']',''),', ','_') +

            ' on ' +

   + '.' + +

            ' (' +

            equality_columns +

            case when inequality_columns is null

                                    then ''

                                    else ',' +  inequality_columns end +

            ')' +

            case when included_columns is not null then

                  ' include (' +

                  isnull(included_columns,'') +

                  ') ' else '' end +

            ' -- ' + convert (varchar, avg_user_impact) + '% anticipated impact'

      from sys.dm_db_missing_index_details mid join

                  sys.dm_db_missing_index_groups mig

                        on mid.index_handle = mig.index_handle          join

                  sys.dm_db_missing_index_group_stats migs

                        on migs.group_handle = mig.index_group_handle join

                        sys.objects obj on obj.object_id = mid.object_id join

                        sys.schemas sch on obj.schema_id = sch.schema_id


       where db_name(database_id) = 'VPS'

                             and            avg_user_impact > 20

                             and equality_columns is not null


      order by, equality_columns

--       avg_user_impact desc