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

Posted on 2007-10-04
Last Modified: 2010-03-19

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!
Question by:Zaurb
    LVL 142

    Accepted Solution

    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

    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    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
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >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
    LVL 5

    Expert Comment

    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.
    LVL 1

    Author Comment


    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.

    LVL 1

    Author Comment

    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!

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    This article describes some very basic things about SQL Server filegroups.
    Read about achieving the basic levels of HRIS security in the workplace.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now