How to calculate the total memory occupied by the database

See the sizes of mdf and log files

EDIT: Sql Server stores its db in mdf files(one or multiple). You need the lof file too. See where your db is stored and these files are files you need.

Be aware that if you are using FILESTREAM, the actual files are not in the db (mdf)

EDIT2:Books Online When you create a database, you must either specify an initial size for the data and log files or accept the default size. As data is added to the database, these files become full.

So, there is a file with some size even if you have no data..

By default, the data files grow as much as required until no disk space remains. ... Alternatively, SQL Server lets you create data files that can grow automatically when they fill with data, but only to a predefined maximum size. This can prevent the disk drives from running out of disk space completely.

If data is added (and there is no more space in the file)the file grows, but when it is deleted, it keeps its size, you need to shrink it...

The total size remains same when the data is available in the DB and if not....I have data in all the tables and i checked the total size it showed 11264 KB,I deleted the data from all the tables, still it showed the same 11264 KB, please help me in figuring out the exact method,I used ----- sp_helpdb db_name in sql query to calculate, any other method to do so??>Thanks

Aditya 2009-08-19 08:51:01

The disk space does not deallocate automatically. Do a complete backup and check the usage again. Then try shrinking (right-click on database in Management Studio, Tasks->Shrink).

Jonas Elfström 2009-08-19 08:53:29

so Jonelf, in tasks->shrink, I hav 2 options database and files,with data in db, the tasks->database showed, currently allocated space 32.63 MB, available free space 5.95MB,tasks->files showed,currently allocated space 11.00 MB, available free space 5.75 MB,with deleting data in tables and views, tasks->database showed, currently allocated space 32.63 MB, available free space 6.11 MB,tasks->files showed,currently allocated space 11.00 MB, available free space 5.81 MB,so, how can I figure out data occupied by db(with data-withoutdata is 6.11 MB - 5.95 MB ??

Aditya 2009-08-19 09:09:41

Sorry, now I've read that twice and I do not understand your question.

Jonas Elfström 2009-08-19 09:20:15

Jonelf, what I explained is,When I shrinked the DB and Files (tasks->shrink) in management studio, I have 2 diff values for DB , with data available in each table and views and without data in table and views. SO the diff b/w these 2 values is the size occupied by data in table??Please correct me if I am wrong :)Thanks

Aditya 2009-08-19 09:34:50

ansaurus

tags:

views:

answers:

How to calculate the total memory occupied by the database

related questions