Problem solve Get help with specific problems with your technologies, process and projects.

Alternate way to find database size

Here's a quick SQL Server script that finds the size of the database and separates the log and data file sizes.

I like the concept of Madhusudan Naidu Gundapaneni's tip, Find the size of every database. This is a simple variation on his theme that slits out the log and data file sizes.

sp_msforeachdb 
  'SELECT ''?'' DatabaseName
  , case when status & 0x40 = 0x40 then ''data'' else ''log'' end FileType
  , cast(((sum(size))*0.0078125) as decimal(15,2)) [FileSize (MB)] 
    FROM "?"..SYSFILES GROUP BY status ORDER BY status desc'
  

Reader Feedback

Craig S. writes: I ran this script but I think the log and data sizes are reversed? At least that's the way it came up on my SQL Server.

The author writes: He's right. Here's the correct code:

sp_msforeachdb 
  'SELECT ''?'' DatabaseName
  , case when status & 0x40 = 0x40 then ''log'' else ''data'' end FileType
  , cast(((sum(size))*0.0078125) as decimal(15,2)) [FileSize (MB)] 
    FROM "?"..SYSFILES GROUP BY status ORDER BY status desc'

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, SQL Server, DB2, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

Dig Deeper on SQL Server Database Compliance

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close