Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    74

    Question Unanswered: Determining database size

    Forgive the easy question but I'm afraid it might be also a trick question and I'd like to hear the experts' opinion. I am using SQL Server 2005 Express edition and I know the limitation is 4GB per database. So far none of my users is anywhere near the limit but I have to be prepared for when that day finally comes. As it stands, they use a single database through a program so I have full control over it. There are no fancy backup programs on the system so no fancy recovery models and automatic shrinking can be done - data is only inserted in that database.

    My question is simply how can I determine programmatically (I use ADO.Net but it can execute SQL commands just fine) the size of the database as it relates to the limitation? That is, I don't know whether it is the amount of data stored - with or without overhead, or it is simply the size of the *.mdf file (maybe together with the *.ldf file), or whether the 4GB is 4 billion bytes or 2^32 bytes - I just want the same method that the SQL Server is using so that, for example, I can bring up a warning at 90% full and lock out the user at 99% full.

    I'd appreciate any insights.

    Kamen

  2. #2
    Join Date
    Aug 2005
    Posts
    75
    for the file sizes, take the page count for the mdf file in the sys.files table and multiply it by 8. That'll give you the size of the file in Kilobytes.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sp_helpdb

    .
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Dec 2005
    Posts
    74
    Thank you very much. Now, since the documentation says nothing about the expected format of the 'db_size' parameter, and after executing 'sp_helpdb' I see it is an "informative" string (e.g., " 53.1 MB"); and assuming I extract the correct number of bytes (which is not obvious - again, is "MB" 10^6 bytes or 2^20 bytes; and assuming I can expect this format to be consistent across implementations and versions of SQL server, etc..., the big question is: can I compare this to the advertised maximum size of 4GB? That is, if the 'db_size' field says "2.0 GB" (or "2000 MB"), can assume the database is exactly 50% full (or empty, depending on whether you are an optimist or a pessimist )?

    Kamen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •