There have always been many questions in the forums related to SQL Server, regarding the size of various objects in SQL Server and maintaining them, so, I have thought to put all the helpful information in the form of an article, and here it is.
How do I find the Size of a database?
You can always get it from the management studio, right click and then properties.
Suppose, if you want to do it through the query analyzer, to get the database size for all the databases then you can do it using
This system stored procedure would return you a list of databases and their sizes in KB, and the result for the same would be like the one given below,
DATABASE_NAME DATABASE_SIZE REMARKS
master 4864 NULL
model 1984 NULL
msdb 19968 NULL
tempdb 36360 NULL
If you want to get more details, like, the database id, compatibility level, created date and who is the owner in a single stroke you can make use of the system stored procedure
the status column is not displayed as the result was too lengthy…
name db_size owner dbid created status compatibility_level
master 4.75 MB sa 1 Apr 8 2003 90
model 1.94 MB sa 3 Apr 8 2003 100
msdb 19.50 MB sa 4 Oct 14 2005 100
tempdb 47.82 MB sa 2 May 27 2009 100
And there is one more system stored procedure which would be of great use for the system administrators who want to keep a watch on the used/unused, allocated/ unallocated and the size of indexes, it is
example reluts for the same are displayed below.
database_name database_size unallocated space
master 4.75 MB 1.04 MB
reserved data index_size unused
3032 KB 1320 KB 1208 KB 504 KB
How do I find the size of Log file, and how much is used?
There is a standard DBCC Command particularly for this,
Note: – The DBCC (Database Console Commands) Statements are provided in the transact SQL server programming language for checking physical and logical consistency and also for fixing most of the issues.
And the result of the same would be like, given below
Database Name Log Size (MB) Log Space Used (%) Status
master 0.7421875 68.42105 0
tempdb 15.5 53.2006 0
model 0.4921875 45.23809 0
msdb 0.7421875 63.15789 0
I want a list of all the files in a database with their sizes?
You can get the above information by running the below query in the desired database, this will return you a list of files, with their locations and size details.
name fileid filename filegroup size maxsize growth usage
master 1 C:\Program Files\DATA\master.mdf PRIMARY 4096 KB Unlimited 10% data only
mastlog 2 C:\Program Files\DATA\mastlog.ldf NULL 3136 KB Unlimited 10% log only
How do I find the Size of a table?
That’s bit simple; we need to just use the system stored procedure with a parameter of the table name for which we want to find the size sp_spaceused, Let me take an example here, suppose, there is a table in the msdb database with the name backupfile and I want to see the size statistics for that file, then, I would run the below code
and the result for that would be
name rows reserved data index_size unused
backupfile 314 328 KB 184 KB 16 KB 128 KB
How do I find free space in a drive on which I have my SQL Server running?
Most of the times, people cannot log into a server and they want to get the size details of the disk from query analyzer, for this there is an extended stored procedure provided directly, one thing that needs to be kept in mind is it gives the amount off free space left in the disk drive, and make sure that it never hits “ZERO”. And also it gives that numbers to us in MB…
And the result for that would be some thing like below
drive MB free
Okay, that’s fine, till now we have see how to find the size of a database, size of a log file, size of a table and free space in the disk drives. Now let us take couple of issues that people face in general
Many people face these issues nearly on a daily/weekly basis…below are some answers for the same, there can be more methods off tackling the same issues
Temp DB is out of space, what should I do?
Depending on the server on which it is happening firstly, you have to tackle this by shrinking the log file, for this we have the DBCC SHRINKFILE Statement
For shrinking the log file, I can use a statement like this,
DBCC Shrinkfile(2,1) – where 2 is the file id and 1 is the amount of free space that is to be left.
If that doesn’t help you, then go and shrink the data files by using the statement
DBCC Shrinkfile(1,1) — one by depending on the number of data files that you have.
Yes, even now if you are not able to fix it, then I think check the possibilities, and restart you SQL server services, that’s it issue resolved. But then, make sure that you place your Tempdb at a place where it has space to GROW!!!
My database Log file is full, what shall I do…..???
Same principle, first try and shrink the log file, most of the time it works out!!!
Worst case scenario; detach the database, by either go to all tasks or doing an
EXEC sp_detach_db ‘MyDB’
Then re attach the mdf file using
EXEC sp_attach_single_file_db @dbname = ‘MyDB’, @physname = N’C:\Program Files\Microsoft SQL Server\ Data\MyDB_Data.mdf’;
If the database is not for a transactional system then we can keep the recovery mode of the database to Simple so that we don’t face these kind of issues as the log file is truncated ever time a COMMIT is done.
That’s it for now in this article, hope you have found some basic points, all at one place, and think it helps, please let me know what you have felt by voting this article.