Sunday, February 6, 2011

Database Data & Log File Location

Whenever I take on a new project, I always have to go in and do a little digging, to find all of the data and log files (among other things).  I rarely use the default C:\Program Files\ location for my database files, but you know... there are many different ways to get it done.

Use this to return the default data and log file directories for an instance, regardless of  existing databases.  

 DECLARE @SmoDefaultFile NVARCHAR(1000)
 EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE',  
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', 
 @SmoDefaultFile OUTPUT

 DECLARE @SmoDefaultLog NVARCHAR(1000)
 EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', 
 @SmoDefaultLog OUTPUT

    ISNULL(@SmoDefaultFile,N'') [Default MDF Location],
    ISNULL(@SmoDefaultLog,N'') [Default LDF Location]

Use this to return details specific to the the databases on the server, using sp_msforeachdb with a select against sys.database_files:

  IF OBJECT_ID('DatabaseFiles') IS NULL
    SELECT TOP 0 * INTO DatabaseFiles
    FROM sys.database_files

    ALTER TABLE DatabaseFiles

  EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, 
  GETDATE() FROM [?].sys.database_files'

  SELECT * FROM dbo.DatabaseFiles

And don't forget this one, if you want to return your database file sizes back in MB, rather than KB:   Database File Size (MB)

No comments:

Post a Comment