List the file sizes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

List the file sizes

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2012-10-10 : 11:36:19

Hello All,

The below query gives me a list with the data and log file sizes. This gives me duplicate rows for each database.

How can I fix it?


 SELECT name AS [DB Name]
 ,[physical_name] AS [File Name]
 ,[ROWS] AS [Data File]
 ,[LOG] AS [Log File]
 ,GETDATE() AS [Run Date]
 FROM (SELECT db.name
 ,dbf.[physical_name]
 ,dbf.[Size]
 ,type_desc
 FROM sys.[databases] AS db
 LEFT OUTER JOIN sys.[master_files] AS dbf ON [db].[database_id] = [dbf].[database_id]
 ) ps
 PIVOT (SUM([Size]) FOR type_desc IN ([ROWS], [LOG])) AS pvt

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2012-10-10 : 11:53:02

I got the answer. I modified the query to add distinct.

Now, another problem is how do I convery the size of the files in MB?

I can use convert(decimal(18,2), (dbf.[Size] * 8)/1024.0) AS [File Size]. But PIVOT function does not like it.

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2012-10-10 : 12:14:29

I resolved it myself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources