Query needed

Question

Post reply

Query needed

ramyours2003

SSChampion

Points: 12268
More actions
November 3, 2016 at 1:29 am

#331708

I need a query to get the data file size , used space , free space , database growth and auto growth settings of all databases , can any one help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 1

Try this query:

DECLARE @database_id int

DECLARE @database_name sysname

DECLARE @sql_string nvarchar(2000)

DECLARE @file_size TABLE

(

[database_name] [sysname] NULL,

[groupid] [smallint] NULL,

[groupname] sysname NULL,

[fileid] [smallint] NULL,

[file_size] [decimal](12, 2) NULL,

[space_used] [decimal](12, 2) NULL,

[free_space] [decimal](12, 2) NULL,

[name] [sysname] NOT NULL,

[filename] [nvarchar](260) NOT NULL

)

SELECT TOP 1 @database_id = database_id

,@database_name = name

FROM sys.databases

WHERE database_id > 0

ORDER BY database_id

WHILE @database_name IS NOT NULL

BEGIN

SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10)

SET @sql_string = @sql_string + 'SELECT

DB_NAME()

,sysfilegroups.groupid

,sysfilegroups.groupname

,fileid

,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size

,convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as space_used

,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as free_space

,sysfiles.name

,sysfiles.filename

FROM sys.sysfiles

LEFT OUTER JOIN sys.sysfilegroups

ON sysfiles.groupid = sysfilegroups.groupid'

INSERT INTO @file_size

EXEC sp_executesql @sql_string

--Grab next database

SET @database_name = NULL

SELECT TOP 1 @database_id = database_id

,@database_name = name

FROM sys.databases

WHERE database_id > @database_id

ORDER BY database_id

END

--File Sizes

SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, fileid, name, file_size, space_used, free_space, filename

FROM @file_size

--File Group Sizes

SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, SUM(file_size) as file_size, SUM(space_used) as space_used, SUM(free_space) as free_space

FROM @file_size

GROUP BY database_name, groupid, groupname

Igor Micev,My blog: www.igormicev.com

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

You can find most of these values in the [master].[sys].[master_files] table. The size value is in PAGE, so you'll need to divide by 128 to convert to MBs.

If you need to find the filling/usage of a file you have to select the "fileproperty([logical_name],''SpaceUsed'')" from within the database. The code below executes this for each database:

EXEC sp_MSforeachdb

'USE [?];

SELECT

[name]

, DB_ID() as [DatabaseID]

, [type_desc] as [Type]

, /128.0 as [SizeMB]

, fileproperty([name],''SpaceUsed'')/128.0 as [SizeUsedMB]

, (/128.0) - (fileproperty([name],''SpaceUsed'')/128.0) as [FreeMB]

, [physical_name] as [FilesystemPath]

FROM sys.database_files'

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ramyours2003 SSChampion Points: 12268 More actions · Answer 3

thanks for the replies , I also need the database growth as well

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ramyours2003 (11/3/2016)
I also need the database growth as well

You'll find that in sys.master_files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gracechristopher06 Old Hand Points: 305 More actions · Answer 5

query to get data file size,space fallow that link

https://www.mssqltips.com/sqlservertip/1629/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases/

Sreejith! SSCrazy Points: 2401 More actions · Answer 6

Try this query :

select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN
CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN
CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size)
END AS [max_size]
, physical_name
from sys.master_files mf