Understanding Database Size sp_spaceused / select name,filename, size from sys files

Posted on 2006-04-26
Last Modified: 2008-01-09
Can someone explain what I'm looking at here?

Using Windows Explorer: My .mdf file is 4,894,656 Kb
Using Windows Explorer: My log.ldf file is 1,024 kb

Running select name, filename, size from sysfiles I get:

.mdf 611,832 (what is this size? ...mb?)
log.ldf 128 (what is this size?)

Running sp_spaceused

database size 4780.94 mb
unallocated 3656.52 mb
reserved 1150384 kb
data 862776 kb
index size 274608 kb
unused 13000 kb
Question by:jamesh1031
    LVL 20

    Expert Comment

    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Hi jamesh1031,

    sp_spaceused @updateusage = 'TRUE'

    and again check sp_spaceused

    remember 1MB = 1024 KB

    Aneesh R!

    Author Comment

    I don't have many permissions...what will @update='TRUE' do?

    By the way, I don't think either one of you addressed my question.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Sometimes, if you drop some indexes, sql server wont update the sysnindexes table, in order to do update the index information in sysindexes table run the above or dbcc updateusage ('urdbdb') WITH COUNT_ROWS
    LVL 34

    Accepted Solution

    > Running select name, filename, size from sysfiles I get:
     > .mdf 611,832 (what is this size? ...mb?)
     > log.ldf 128 (what is this size?)

     If you look up sysfiles in the SQL help file, it says that size is in 8 KB pages.

     611832 * 8 KB = 4894656 KB
     128 * 8 KB = 1024 KB

     So, those are the same sizes that Windows Explorer gave you, but expressed in units of 8 KB.


    Author Comment

    Thank you.
    LVL 34

    Expert Comment

    You're welcome.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now