Post reply

Query needed

  • 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

  • November 3, 2016 at 1:46 am

    #1910283

    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

  • November 3, 2016 at 1:50 am

    #1910284

    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’! **

  • November 3, 2016 at 3:52 am

    #1910300

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

  • November 3, 2016 at 5:19 am

    #1910311

    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

  • November 3, 2016 at 10:08 pm

    #1910515

    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/

  • January 9, 2017 at 1:58 pm

    #1921495

    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

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

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