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
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
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
sorry man it executed but returned nothing…what cud b d possible flaw,….
sir
my data base is very long how to resolve this issue can you su me
I have list of filepaths in a column &
i want to select filepath & size of physical file
like
List
1.jpg
2.jpg
in
D:/Project/P1 folder
i want to display file name, extension, FileExist and size From sql query
I need help with tsql to get the total disk space where the data or log file reside. If anybody knows, please help me.