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


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

Posted on 2006-04-26
Medium Priority
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
  • 2
  • 2
  • 2
  • +1
LVL 20

Expert Comment

ID: 16549409
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16549509
Hi jamesh1031,

sp_spaceused @updateusage = 'TRUE'

and again check sp_spaceused

remember 1MB = 1024 KB

Aneesh R!

Author Comment

ID: 16549624
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.
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16549642
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 35

Accepted Solution

James0628 earned 2000 total points
ID: 16559089
> 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

ID: 16559118
Thank you.
LVL 35

Expert Comment

ID: 16560041
You're welcome.


Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

580 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