Database Size

Question

Database Size

Kris-155042

Hall of Fame

Points: 3460
More actions
January 21, 2018 at 9:12 pm

#402855

Hi,
I was wondering if someone could help. I wanted a list of all the database sizes for logs and data files. It seems to be accurate for every database except the TempDB. Can anyone help?
SELECT [Database Name] = DB_NAME(database_id),
   [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
       WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
       ELSE Type_Desc END,
   [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM sys.master_files
GROUP BY  GROUPING SETS
     (
       (DB_NAME(database_id), Type_Desc),
       (DB_NAME(database_id))
     )
ORDER BY  DB_NAME(database_id), Type_Desc DESC
GO
Thanks,
Kris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Sys.master files is the size at last start of SQL Server iirc, so you probably have a very low default size for TemoDB and it has to grow afterwards (which is not ideal)

You can use sys.database_files, run in the context of TempDB for its current size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thom A SSC Guru Points: 99126 More actions · Answer 2

GilaMonster - Monday, January 22, 2018 1:08 AM
Sys.master files is the size at last start of SQL Server iirc, so you probably have a very low default size for TemoDB and it has to grow afterwards (which is not ideal)
You can use sys.database_files, run in the context of TempDB for its current size.

I'm not at all saying you're wrong Gail, but interestingly, Microsoft Docs defines the value of size in sys.master_files (Transact-SQL) as:

Current file size, in 8-KB pages. For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

https://connect.microsoft.com/SQLServer/feedback/details/377223/sys-master-files-does-not-show-accurate-size-information

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thom A SSC Guru Points: 99126 More actions · Answer 4

GilaMonster - Monday, January 22, 2018 6:07 AM
https://connect.microsoft.com/SQLServer/feedback/details/377223/sys-master-files-does-not-show-accurate-size-information

Heh, and this is why I doubted the documentation more than yourself. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk