I encountered the situation recently where I needed to find the size of the log file. When I tried to find the script by using Search@SQLAuthority.com I was not able to find the script at all. Here is the script, if you remove the WHERE condition you will find the result for all the databases.
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
57 Comments. Leave new
Wonderful article Pinal.
Thanks for the script, any way to add the last backup of DB and log files to it?
Though sys.master_files has tons more info but I still like dbcc sqlperf (‘logspace’) if its only about getting the free log space.
Hi, Pinal
Seems sp_helpdb ‘AdventureWorks’ does the same job
No it doesnt…..
SP_Help doesnt give you any detail of the filegroups you have in your databases, but from this view you already get it (in case you use it).
Thanks for the script.
Hello Yusuf,
You are right, there is no better way to get log size of all databases. There are few other methos to know the size and free space in database files but there is no simple alternate for the output of sqlperf (‘logspace’).
Regards,
Pinal Dave
Thank you. This saves lot of my time. So far we are using DBCC SHOWFILESTATS.
Just realized. The above statement returns only allocated space of each file. But DBCC SHOWFILESTATS returns total the used extents.
Hi Pinal,
What about sp_databases. Is it not the same?
Thanks
Vivek
One Suggestion , you can get a Db,Table, Size calculator utility from your trainees.
The user needs to give input of the no. of types of columns and they get the estimated size result….
It will be a very good utility for many people …
Pinal – as usual you deliver precisely what I look for, thanks!
Yes fly,
sp_helpdb ‘AdventureWorks’ does the same job
Hi Pinal,
Can you please provide me the script to find the free space of all the databases in sql server 2005?
Thanks in advance
Hi,
What is the recommended database file size in MS SQL 2005? as of now, one of my production database having one data file and size is 70 GB. And it is set to auto grow.
will it cause any performance issue if it continiously grow? is there any recommended size we can restrict and create secondary file once it is reached that size?
Can you give some tips to usage on data file size in MS SQL 2005 database? Thanks in advance!
regards,
Ratna Kumar.
Hi,
This gives the initial size not the current size of the log file.
If you are looking for the initial size, isnt it better to right click, properties, select “file” and look for the initial size in the gui?
What is the difference in using the Script?
– Shiyam
Hi Shiyam,
You are right that you can Right Click and know the file size,
but think of if you have 76 Databases on the instance and you want to send a report to your manager then would you click on each of the databases and copy /paste one by one? Obviously NOT,, then the the benefit of the script comes to play… just run the script and in few sec you have report for every database even for 347 databases on one instance..
thanks Pinal,,, lots of SQL Server experts and instructors (even officially from Microsoft) suggest to go to MS website or their blogs, but my first choice is yours because I find answer which is very too the point and some time exact what I want. Great work.
Shoaib
from Canada
You have some integer math up there. It should be: (size*8)/1024.
It’s not clear in my earlier comment, but the period at the end of my sentence is supposed to be a decimal point. Let me try again: “(size*8)/1024.0”.
Also read this post
hi can you please help me for configuring mirroring through script.
Thanks for your help and for taking the time to help others, Greetings from Merida, Yucatan, Mexico, Erika Gamboa
And if you alter it just a little bit, you can have them summed by database:
SELECT DB_NAME(database_id) AS DatabaseName,
SUM((size*8)/1024) SizeMB
FROM sys.master_files
group by DB_NAME(database_id)
order by DB_NAME(database_id)
Also make sure to know about implicit convertions