Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

SQL Server sql statements to find out actual database file size.


I wonder whether there're any sql statements or procedures to find out actual and maximum database size in SQL Server 2005 (Express). And one more thing that I would like to find out whether it's possible to run the query that would produce LAST 5 percent of the table content.
I know it's possible to use: SELECT TOP 5 PERCENT FROM ... But, since as far as I know there's no contrary statement to output BOTTOM percent of the table how can this be achieved.

Thank you!
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
should ALWAYS be used WITH an ORDER BY . in which case, the BOTTOM 5 is just reverse the ORDER BY yourfield ASC  --> ORDER BY yourfield DESC and use TOP 5

Aneesh RetnakaranDatabase AdministratorCommented:
Hello Zaurb,

Use sp_HelpFile    or  sp_HelpDB to get the current size,  The maximus size Express Edition supports is upto 4GB

If you have some datatime column  or some identity column in that table, you can probably add  an "ORDER BY thatColumn DESC " at the end

Aneesh R
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>find out actual and maximum database size in SQL Server 2005
what "actual" size to you mean? the allocated file size? or the used space?

the max database size is 4GB in SQL Server 2005 Express edition

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

For selecting the Last 5 percent from a table, first sort it in the DESC order and then take the top 5 percent.

exec sp_helpDB will list you all the databases in your server and also with the otehr details like the DbSize, Owner, dbid etc.
ZaurbAuthor Commented:

The EXEC sp_helpDB gives me the size of the allocated database file size. But I need to find out how much space it still has for working. Let me explain.

We use ISA server which keeps its logs in SQL Server 2005 Express edition database. Of course, 4 gb is a maximum database file size allowed by Express edition. Once, we have had a problem when firewall service in ISA wouldn't work because there were no more space in ISA2006LOG database to write its logs.
Now, certainly, I can count how many rows are so far in firewalllog table, but since I don't know how many rows can take how much space, I was trying to find out not the size of the file, but the amount of rows, or data that can be still written into the database to run TRUNCATE statement prior my ISA stucks again.

ZaurbAuthor Commented:
Any ideas? I understand that the database file size is set to 4 gb in Express edition but how do I define amount of data written so far and how much can still be written prior the database gets out of space. Thank you!
I recommend right clicking on the database name in the Management Studio object explorer, choosing 'Properties' and under 'General', look at Database - Space Available.  Subtracting allocated size from space available should give an idea as to actual size.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now