script for gathering data file information

• phani kumar gotety

  Ten Centuries

  Points: 1213

  More actions

  April 12, 2005 at 4:41 am

  #362392

  Hi all,

  Is there any script to get the following details

  data file size, is autogrow enabled ?, if yes what is the increment and final size

  thanks

  phani

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  April 12, 2005 at 5:21 am

  #552092

  Something like this perhaps:

  SELECT fileid

  , name

  , CEILING((size * 8) / 1024.0) AS SizeInMegabytes

  , CASE WHEN growth = 0 THEN 'No' ELSE 'Yes' END AS AutogrowEnabled

  , CASE WHEN growth = 0 THEN 'N/A' WHEN status & 1048576 0 THEN CAST(growth AS VARCHAR(10)) + '%' ELSE CAST((growth * 8) / 1024 AS VARCHAR(10)) + ' MB' END AS Growth

  , CASE WHEN growth = 0 THEN 'N/A' WHEN maxsize = -1 THEN 'Unlimited' ELSE CAST(CEILING((maxsize * 8) / 1024.0) AS VARCHAR(10)) + ' MB' END AS FinalSize

  FROM dbo.sysfiles

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/