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 (http://blog.SQLAuthority.com)
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
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx
Thank you very much
Hi,
What is the equivalent in SQL 2000?
very helpful..Thank You
To check log space:
dbcc sqlperf (logspace);
As usual, big help finding something fast when I need it! Thanks.
This list the files and sizes for all attached databases on server …
SELECT db.name AS [DB Name]
,dbf.[physical_name] AS [File Name]
,dbf.[size] AS [File Size]
,db.[create_date] AS [Create Date]
,ss.[Last User Event]
,ss.[Last User Update]
FROM sys.[databases] AS db
LEFT OUTER JOIN sys.[master_files] AS dbf ON [db].[database_id] = [dbf].[database_id]
LEFT OUTER JOIN (SELECT database_id,max(last_user_update) AS [Last User Update], ISNULL(ISNULL(max(last_user_update),max([last_user_seek])),max([last_user_lookup])) AS [Last User Event]
FROM sys.[dm_db_index_usage_stats] GROUP BY database_id) AS ss ON ss.[database_id] = db.[database_id]
ORDER BY db.[name],dbf.[physical_name]
This script is great. I am new to learning DBA role. Is the output for file size reflected in Kilobytes or Megabytes?
we have sap server on msssql 2005 , 4 datafile (DATA1,DATA2…DATA4) with 26,880 MB each , unrestricted growth by 60 MB .Free space on Disk is 67,224 MB.Outof four data file , Three file status show ‘NO FREE SPACE IN FILE” and one shows autogrown with enough space to grow.
Free space on these datafiles shows (PCT_FREE) – 0.
Please advice why this message ,as our all data file are – autoextend enabled.
what would be the max. size of datafile .whats the work around for this….
thanks
Can you please give me the script to get the table wise size
CREATE TABLE #f
(
name VARCHAR(255),
rows INT ,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255)
)
INSERT into #f
EXEC sp_MSForEachtable ‘sp_spaceused ”?”’
SELECT * FROM #f
DROP TABLE #f
// Hope this will help
If doing in ver. 8 (wait, what?), change database_id to dbid, and sys.master_files to master.dbo.sysaltfiles.
Please prefer to use either sys.database_files or sys.sysfiles to get the db sizes. Try to avoid using sys.master_files dmv..
To answer why .. See the results below ..
USE tempdb
GO
SELECT SUM(SIZE)/128 FROM SYS.DATABASE_FILES;
– 29754 MB
SELECT SUM(SIZE)/128 FROM SYS.master_files
WHERE database_id = DB_ID(‘TEMPDB’)
– 18432 MB
Hi Pinal,
Putting new steps towards DBA very good script, Thank You.
Just quick question, The ‘growth’ field in ‘sys.master_files’ table, does it stamp date or time as well, like growth of the TestDatabase since this time ??
Thanks
[...] Find the Size of Database File – Find the Size of Log File A simple straight to the script blog post! [...]
Can you provide me a query that will list all the DBnames that have log file size more than 10 GB?
Hi Pinal-
Actually I just wanted to know only the user database information. For that I just tweak your query a bit.
SELECT Distinct DB.name As DatabaseName,
DB.recovery_model,
mf.name as logical_name,
mf.physical_name,
(size*8)/1024 SizeMB
FROM sys.master_files mf
join sys.databases DB
on mf.database_id = DB.database_id
WHERE mf.database_id > 4
The reason I put WHERE mf.database_id > 4 is to get only user databases list.First 4 database_id’s are for system databases.
Thank you!
Recovery Model: Simple
DB Size: 70 GB
Server Free Space: 145 GB
Every evening when we leave office LOG File is 20-30 GB or Less. But at midnight the log file size grows and takes all free space. The applications stops. In morning we shrink the log file down to 1 MB. All the day it works ok. We don’t run any process at midnight. Please advice how to handle the situation