Question

I want to know the growth type (% or MB) for database files on SQL server 2000. I Used sys.database_files files on Microsoft SQL Server 2005 to get this information. I tried using sysfiles on Microsoft SQL Server 2000 for this, but it wasn t good enough.

Answer 1

这应有助于:

SELECT
  name,
  size,
  growth,
  status,
  size * 8 AS size_in_kb,
  size * 8 / 1024. AS size_in_mb,
  CASE WHEN status & 0x100000 > 0
       THEN growth 
       ELSE NULL END AS growth_in_percent,
  CASE WHEN status & 0x100000 = 0 
       THEN growth * 8 / 1024. END AS growth_in_mb  
FROM sysfiles

Answer 2

您可查询<代码>sysfiles。系统观点:

SELECT * FROM sysfiles

它将给每个档案提供为数不少的参照标准,包括现有规模、最大规模和增长(加上这一增长是固定页数还是百分比)。

见。

Answer 3

SELECT DB_NAME() AS DBName
,groupid AS FileGroup
,fileid AS FileID
,RTRIM(NAME) AS LogicalFileName
,filename AS PhysicalFileName
,CAST(size / 128.0 AS DECIMAL(10, 2)) AS Size_MB
,CAST(FILEPROPERTY(NAME,  SpaceUsed ) / 128.0 AS DECIMAL(10, 2)) AS SpaceUsed_MB
,CAST(size / 128.0 - (FILEPROPERTY(NAME,  SpaceUsed ) / 128.0) AS DECIMAL(10, 2)) AS AvailableSpace_MB
,CAST((CAST(FILEPROPERTY(NAME,  SpaceUsed ) / 128.0 AS DECIMAL(10, 2)) / CAST(size / 128.0 AS DECIMAL(10, 2))) * 100 AS DECIMAL(10, 2)) AS UsedPercentage
,CASE
    WHEN STATUS & 0x100000 > 0 THEN growth
    ELSE NULL
    END AS Growth_Percent
,CASE
    WHEN STATUS & 0x100000 = 0 THEN CAST(growth / 128.0 AS DECIMAL(10, 2))
    END AS Growth_MB
,CASE maxsize
    WHEN 0 THEN  No growth is allowed 
    WHEN - 1 THEN  File will grow until the disk is full 
    ELSE CONVERT(VARCHAR, CAST(maxsize / 128.0 AS DECIMAL(10, 2)))
    END AS MaxSize_MB 
FROM sysfiles

只是一种比更多的信息

友情链接