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

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

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'

Determining if a Folder Exists Using xp_cmdshell

The code below creates a stored proc that will report if a passed-in Windows folder exists or not. While it avoids using the undocumented xp_fileexist system proc, it uses xp_cmdshell, which in many places is the first thing that gets disabled because of the obvious security ramifications. Still, it's a nice hack if you're working in the file system a lot.

IF OBJECT_ID('dbo.usp_FolderExist') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.usp_FolderExist
END
 
GO
 
CREATE PROCEDURE dbo.usp_FolderExist (@FullPathName NVARCHAR(255),
                                      @Exists       TINYINT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON
   
    -- Remove any trailing slash.
    DECLARE @TrailingSlash NVARCHAR(255)
    SET @TrailingSlash = REVERSE(@FullPathName)
    
    IF '\' = LEFT(@TrailingSlash, 1) 
        SET @TrailingSlash = SUBSTRING(@TrailingSlash, 2, LEN(@FullPathName) + 1)
  
    SET @FullPathName = REVERSE(@TrailingSlash)
   
    -- Build and exec the command.
    DECLARE @Cmd NVARCHAR(4000)
    SET @Cmd = 'dir ' + @FullPathName
    SET @Exists = 0
    CREATE TABLE #dir (output NVARCHAR(4000))
 
    INSERT INTO #dir
        EXEC master..xp_cmdshell @Cmd
 
    IF EXISTS (SELECT * FROM #dir WHERE output LIKE '%' + @FullPathName + '%')
        SET @Exists = 1
      
    -- Done.
    DROP TABLE #dir
END
 

/* Test Code
 
set nocount on
declare @bExists int   set @bExists = 0
exec usp_FolderExist 'C:\Temp',  @bExists OUTPUT
print @bExists
exec usp_FolderExist 'C:\Temp\', @bExists OUTPUT
print @bExists
 
*/