Monday, 2 October 2017

Getting useful information of data & log files for all databases

It is a common DBA task to check the unused space of all database files in order to make the decision of extending the file sizes and provide them with more disk hard space in the likely event of running out of it. Today I have two scripts I would like to share with you to get that info easily. The first one is to report some very important information about every data & log file for all databases such as name, file size in GB, space used in GB, free space in GB and percentage, growth settings in tandem with other information at database level such as collation, compatibility level, owner, and more. Here you are:

USE master
IF EXISTS (SELECT name FROM master.sys.tables WHERE name='TmpFileSpace')
    DROP TABLE TmpFileSpace

CREATE TABLE [dbo].[TmpFileSpace](
    [DatabaseName] [nvarchar](128) NULL,
    [FileName] [sysname] NOT NULL,
    [FileSizeGB] [decimal](10, 2) NULL,
    [SpaceUsedGB] [decimal](10, 1) NULL,
    [SpaceFreeGB] [decimal](10, 1) NULL,
    [SpaceFree%] [decimal](10, 1) NULL
EXEC sp_MSforeachdb '
USE [?]
INSERT INTO master.dbo.[TmpFileSpace]([DatabaseName], [FileName], [FileSizeGB], [SpaceUsedGB], [SpaceFreeGB], [SpaceFree%])
SELECT DB_NAME() DatabaseName, name FileName, 
CAST(size/128.0/1024.0 AS DECIMAL(10,2)) SizeGB, 
CAST(FILEPROPERTY(name,''SpaceUsed'') /128.0/1024.0  AS DECIMAL(10,1)) SpaceUsedGB, 
CAST((size - FILEPROPERTY(name,''SpaceUsed'')) /128.0/1024.0 AS DECIMAL(10,1)) SpaceFreeGB, 
CAST(((size - FILEPROPERTY(name,''SpaceUsed''))/(size*1.0)) *100 AS DECIMAL(10,1)) [SpaceFree%]
FROM sys.database_files'

SELECT DatabaseName,db.collation_name,db.compatibility_level, SUSER_SNAME(owner_sid) OwnerName,
db.page_verify_option_desc, db.is_auto_close_on, 
db.is_auto_create_stats_on,db.is_auto_shrink_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, DatabaseName,  fs.FileName,
fs.FileSizeGB , fs.SpaceUsedGB, fs.SpaceFreeGB, fs.[SpaceFree%],
  physical_name, cast(size/128.0/1024.0 as decimal(10,2)) FileSizeGB,
FROM sys.master_files mf
INNER JOIN sys.databases db ON mf.database_id = db.database_id
INNER JOIN TmpFileSpace FS ON mf.database_id=db_id(Fs.DatabaseName) AND

DROP TABLE [TmpFileSpace]

But if you only want to get information about the size in GB, space used in GB, free space in GB of all data & log files for a specific database you can use this:

USE [YourDatabaseName]
SELECT DB_NAME() DatabaseName, name FileName, 
CAST(size/128.0/1024.0 AS DECIMAL(10,2)) SizeGB, 
CAST(FILEPROPERTY(name,'SpaceUsed') /128.0/1024.0  AS DECIMAL(10,1)) SpaceUsedGB, 
CAST((size - FILEPROPERTY(name,'SpaceUsed') ) /128.0/1024.0 AS DECIMAL(10,1)) SpaceFreeGB, 
CAST(((size - FILEPROPERTY(name,'SpaceUsed') )/(size*1.0)) *100 AS DECIMAL(10,1)) [SpaceFree%]
FROM SYS.database_files

That is all for now. Let me know any remarks you may have. 

No comments:

Post a Comment

Let me know any remarks or questions you may have. Please write down your name.

HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE