File growth can cause system outages as SQL Server will have to wait for the file to grow before it can resume operations. Of course, this depends on the size of the database, the growth rate, etc; however, the size of your databases is something you will want to keep an eye on. The below script gives you this information and so you can better monitor these rates and grow your database during the maintenance window.
This query will only grab the information for the database you are connected to.
[sourcecode language=”sql”]SELECT s.name AS [Name]
, s.physical_name AS [FileName]
, s.size * CONVERT(FLOAT, 8) AS [Size] /*File size on Disk*/
, CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT)
* CONVERT(FLOAT, 8) AS [UsedSpaceInKB]
, (CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT) * CONVERT(FLOAT, 8))
/ 1024 AS [UsedSpaceInMB]
, ((s.size * CONVERT(FLOAT, 8)
– CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT) * CONVERT(FLOAT, 8)))
/ 1024 AS ‘Available Free Space In MB’ /*Matches SSMS file report*/
, (s.size * CONVERT(FLOAT, 8)
– CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS FLOAT) * CONVERT(FLOAT, 8))
/ (s.size * CONVERT(FLOAT, 8)) * 100 AS ‘Percent Free’
, growth
, max_size
, state_desc
FROM sys.master_files AS s
WHERE s.database_id = DB_ID()
[/sourcecode]
Getting a little fancy, we can grab the information for each database on the instance, but you would have to insert this into a table to do any ordering.
[sourcecode language=”sql”]
EXEC dbo.sp_MSforeachdb ‘ Use ? SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size], /*File size on Disk*/
CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8) AS [UsedSpaceInKB]
, (CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8)) / 1024 AS [UsedSpaceInMB]
, ((s.size * CONVERT(float,8) – CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8))) / 1024 AS ”Available Free Space In MB” /*Matches SSMS file report*/
, (s.size * CONVERT(float,8) – CAST(FILEPROPERTY(s.name, ”SpaceUsed”) AS float)* CONVERT(float,8)) / (s.size * CONVERT(float,8)) * 100 AS ”Percent Free”
, growth, max_size, state_desc
FROM
sys.master_files AS s
WHERE s.database_id = db_id()'[/sourcecode]
code does not work