none
Looking for a script which gives Drive,Diskspacefree,Databasename,Filename,Filetype,Filesize,Spaceused,spacefree,free(%),MaxsizeMB,autogrowthsize,Filegroup

    Question

  • Hi All,

    I need a T-script  for sql 2005 and 2008 which should give output as:

    Looking for a script which gives Drive,Diskspacefree,Databasename,Filename,Filetype,Filesize,Spaceused,spacefree,free(%),MaxsizeMB,autogrowthsize,Filegroup

    Tuesday, December 24, 2013 9:12 PM

Answers

  • refer below article...

    http://simplesqlserver.com/category/sql-server/file-sizes/


    Regards,
    Praveen D'sa
    MCITP - Database Administrator - 2008
    sqlerrors

    Wednesday, December 25, 2013 4:29 AM
  • In the list below, everything that is in bold face is available in sys.master_files:

    Drive
    Diskspacefree,
    Databasename
    Filename
    Filetype
    Filesize
    Spaceused,
    spacefree,
    free(%),
    MaxsizeMB
    autogrowthsize
    Filegroup

    Everything in bold face is in sys.master_files. Diskspacefree is also fairly simple; you can get it with xp_fixeddrives.

    Filegroup requires you sys.filegroups in every database, although you can hardcode "PRIMARY" for the file_id = 1.

    To get space used per file, it appears that you need to need to visit every database and run fileproperty(filename, 'SpaceUsed') to get the data.

    You need to revisit the topics for the views and function I have listed here. xp_fixeddrives is undocumented, but the output is very simple to grasp.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 24, 2013 11:08 PM

All replies

  • In the list below, everything that is in bold face is available in sys.master_files:

    Drive
    Diskspacefree,
    Databasename
    Filename
    Filetype
    Filesize
    Spaceused,
    spacefree,
    free(%),
    MaxsizeMB
    autogrowthsize
    Filegroup

    Everything in bold face is in sys.master_files. Diskspacefree is also fairly simple; you can get it with xp_fixeddrives.

    Filegroup requires you sys.filegroups in every database, although you can hardcode "PRIMARY" for the file_id = 1.

    To get space used per file, it appears that you need to need to visit every database and run fileproperty(filename, 'SpaceUsed') to get the data.

    You need to revisit the topics for the views and function I have listed here. xp_fixeddrives is undocumented, but the output is very simple to grasp.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 24, 2013 11:08 PM
  • refer below article...

    http://simplesqlserver.com/category/sql-server/file-sizes/


    Regards,
    Praveen D'sa
    MCITP - Database Administrator - 2008
    sqlerrors

    Wednesday, December 25, 2013 4:29 AM
  • Thank you for the reference to my blog. To add to this answer, xp_fixeddrives will give you the free space for drives, but not mount points. If you don't know what a mount point is then don't worry about it because your company probably isn't using them. However, if they are then you need to look in WMI at win32_volume to get accurate info as that is not available through SQL Server.
    Tuesday, December 31, 2013 6:22 AM