top of page
Search

Find SQL Server Database Data and Log File Size in GB

Writer's picture: Kunal RanpuraKunal Ranpura
Jul 29, 20191 min read

SELECT DB_NAME([database_id])AS [Database Name],

[file_id], name, physical_name, type_desc, state_desc,

CONVERT( bigint, size/128000.0) AS [Total Size in GB]

FROM sys.master_files

ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);


-- Things to look at:

-- Are data files and log files on different drives?

-- Is everything on the C: drive?

-- Is TempDB on dedicated drives?

-- Is there only one TempDB data file?

-- Are all of the TempDB data files the same size?

-- Are there multiple data files for user databases?

63 views2 comments

Recent Posts

See All

SQL Server Profiler Trace

--Import multiple trace file in sql Table. --All the rollover files will be automatically imported --provide number tracefile to capture...

0 comments

Find All Primary Key in SQL Server

select schema_name(tab.schema_id) as [schema_name], pk.[name] as pk_name, substring(column_names, 1, len(column_names)-1) as [columns],...

0 comments

SQL Server find Database User Permission

--Execute the following script against the User Database where you need to find DB User permission. DECLARE @sql VARCHAR(2048) ,@sort INT...

0 comments

2 Comments

Xiaogang Zheng
Xiaogang Zheng
Oct 21, 2022

Where is the 128000.0 coming from? 1024*1024/8 = 131072. You just use a roughly value or has some reason? If use roughly value, why don't use 130000?

Like
Kunal Ranpura
Kunal Ranpura
Oct 21, 2022
Replying to

Hi Zheng,


CONVERT( bigint, size/128000.0) will give the ballpark value. The exact value you will get by (1024*1024)/8 = 131072, you have to remove the convert function.


However since we are converting it to bigint in this query, it will take the nearest integer value.


Please see the description at https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver16


Like
bottom of page