SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 List the file sizes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlpal2007
Posting Yak Master

184 Posts

Posted - 10/10/2012 :  11:36:19  Show Profile  Reply with Quote
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

184 Posts

Posted - 10/10/2012 :  11:53:02  Show Profile  Reply with Quote
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.

Go to Top of Page

sqlpal2007
Posting Yak Master

184 Posts

Posted - 10/10/2012 :  12:14:29  Show Profile  Reply with Quote
I resolved it myself.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000