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

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

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

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.