none
TSQL DISK USAGE

    Question

  • Hi all,

    How to find the DISK USAGE report through TSQL statements.??

    My concern is to find the space used by the datafile using TSQL statements .

    any ideas??

    thanks in advance.

    Wednesday, March 14, 2012 4:40 AM

Answers

All replies

  • Have a look at the below links.

    http://msdn.microsoft.com/en-us/library/ms175903.aspx

    http://msdn.microsoft.com/en-us/library/cc280506.aspx

    You can query the dmvs or use xp_fixeddrives

    dm_os_performance_counters

    Wednesday, March 14, 2012 5:47 AM
  • Hi,

    Please check these queries:

    SELECT DB_NAME(mf.database_id) AS DatabaseName, mf.physical_name, size_on_disk_bytes
    FROM sys.dm_io_virtual_file_stats(null, null) AS divfs
    JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id
    
    Go
    
    SELECT database_id, file_id, type_desc, name, physical_name
    size, max_size, growth FROM sys.master_files

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    Wednesday, March 14, 2012 6:01 AM
  • CREATE table DriveTable (Drive varchar(10),[MB Free] int)
    INSERT into Drivetable Exec xp_fixeddrives 


    SELECT *,[MB Free]/1024 AS GB FROM DriveTable

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 14, 2012 6:32 AM
    Moderator
  • Dear All,

    Thanks for the quick response.

    I am looking for the exact size of the datafile for example

    in DISK USAGE report we have

    space reserved =2.93GB

    space used =1.44GB

    i want to find out that space used through TSQL

    i guess in question itself i was not clear i hope this will let you understand more

    thanks in advance

    Wednesday, March 14, 2012 7:04 AM
  • What do you mean space used through TSQL???? Take a look at sp_spaceused stored procedure

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, March 14, 2012 7:25 AM
    Moderator
  • if you observe the above disk usage report i will get the space used. that space used i want to find

    • Edited by sushil naik Wednesday, March 14, 2012 8:08 AM
    Wednesday, March 14, 2012 7:34 AM
  • Hi all,

    after lot of search i found the way to find the actual datafile size.


    select Name,
    (convert(float,size)) * (8192.0/1048576) File_Size,
    (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
    ((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
    from sysfiles
    order by
    fileproperty(name,'IsLogFile')

    I hope this might help.

    Thursday, March 15, 2012 5:50 AM
  • SELECT instance_name AS DatabaseName, 
           [Data File(s) Size (KB)], 
           [LOG File(s) Size (KB)], 
           [Log File(s) Used Size (KB)], 
           [Percent Log Used] 
    FROM 

       SELECT * 
       FROM sys.dm_os_performance_counters 
       WHERE counter_name IN 
       ( 
           'Data File(s) Size (KB)', 
           'Log File(s) Size (KB)', 
           'Log File(s) Used Size (KB)', 
           'Percent Log Used' 
       ) 
         AND instance_name not in ('_Total','mssqlsystemresource')
    ) AS Src 
    PIVOT 

       MAX(cntr_value) 
       FOR counter_name IN 
       ( 
           [Data File(s) Size (KB)], 
           [LOG File(s) Size (KB)], 
           [Log File(s) Used Size (KB)], 
           [Percent Log Used] 
       ) 
    ) AS pvt


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Thursday, March 15, 2012 6:08 AM
    Moderator
  • Hi Uri damant,

    This script is perfect. but the DATAFILE(S)size(KB) is showing the space reserved itself.

    Can you please once campare the results of below script with your script


    select Name,
    (convert(float,size)) * (8192.0/1048576) File_Size,
    (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
    ((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
    from sysfiles
    order by
    fileproperty(name,'IsLogFile'

    Thursday, March 15, 2012 8:40 AM
  • See this one

    Select f.physical_name, f.size/128. As 'Filesize in MB', 
    u.unallocated_extent_page_count/128. As 'Unused in MB'
    From sys.dm_db_file_space_usage u
    Inner Join sys.database_files f On u.file_id = f.file_id;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 15, 2012 12:15 PM
    Moderator
  • Dear Uri Dimant,

    Thanks a ton for reply. i have 1 doubt

    As the above query the output is as below

    Physical_name    Filesize in MB    Unused in MB
    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks.mdf    179.937500    4.125000

    if i deduct the (filesize in MB - Unused in MB) as (179.93 - 4.125)= 175.805

    this should be the used space of the datafile. but according to Disk Usage report the used space is 137MB.

    and the same result i am getting with the output of the below script


    use AdventureWorks
    select Name,
    (convert(float,size)) * (8.0/1024) File_Size,
    (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
    ((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free,GETDATE() [Date_Modified]
    from sysfiles
    where fileid=1
    order by
    fileproperty(name,'IsLogFile')
    select fileproperty(name,'SpaceUsed') from sysfiles

    -------------------------------------------------------------------

    OUTPUT

    ---------------------------------------------------------------------

    Name    File_Size    MB_Used    MB_Free    Date_Modified
    AdventureWorks_Data    179.925984    137.4375    42.5    2012-03-15 18:09:37.020

    Correct me if i am wrong.

    Thanks again for your valuable reply

    Thursday, March 15, 2012 12:42 PM
  • Hi Sush104,

    try this:

    CREATE TABLE #files( 
        [dbname] [sysname] NOT NULL,
        [name] [sysname] NOT NULL,
        [physical_name] [nvarchar](260) NOT NULL,
        [size] [int] NOT NULL,
        [max_size] [int] NOT NULL,
        [growth] [int] NOT NULL
      )
    EXEC sp_MSforeachdb '
    insert into #files
    select ''[?]'',name,physical_name,size,max_size,growth
    from [?].sys.database_files'
    SELECT [dbname]
          ,[name]
          ,[physical_name]
          ,[size]
          ,[max_size] 
          ,[growth]
      FROM #files 


    Regards, http://shwetamannjain.blogspot.com

    Friday, March 16, 2012 6:03 AM
  • Hi shweta,

    We can get result of the above statement from sys.master_files tables. why to write such big statement !!??

    My concern is to find the space of the data which is present in the datafile.

    Once have look on this query


    use AdventureWorks
    select Name,
    (convert(float,size)) * (8.0/1024) File_Size,
    (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
    ((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free,GETDATE() [Date_Modified]
    from sysfiles
    where fileid=1
    order by
    fileproperty(name,'IsLogFile')
    select fileproperty(name,'SpaceUsed') from sysfiles

    Correct me if i am wrong

    regards

    sushil

    Friday, March 16, 2012 6:27 AM
  • The following article deals with somewhat related topic, the configuration of disk resources for best performance:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    Simplest command for file space usage:

    EXEC AdventureWorks2008.sys.sp_spaceused


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, March 17, 2012 12:29 AM
    Moderator