.
/* -- SQL Server 2008 and R2 Diagnostic Information Queries -- Glenn Berry -- June 2012 -- Last Modified: June 19, 2012 -- http://sqlserverperformance.wordpress.com/ -- http://sqlskills.com/blogs/glenn/ -- Twitter: GlennAlanBerry DESCRIPTION: List location of all files for all databases Look for tempDB and Tlog sharing the same drives as a source of I/O contention issues Compatibility list: MSSQL2005 MSSQL2008 MSSQL2008R2 MSSQL2012 DOES NOT WORK MSSQL2000 Update Log: */ SELECT DB_NAME([database_id])AS [Database Name] , [file_id] , name , physical_name , type_desc , state_desc , CONVERT( bigint, size/128.0) AS [Total Size in MB] FROM sys.master_files WITH (NOLOCK) WHERE [database_id] > 4 AND [database_id] <> 32767 OR [database_id] = 2 ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
.