查看数据文件的使用和增长情况

 

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

DECLARE @dbname VARCHAR(200),

@sql VARCHAR(8000)

   

SET @sql = ''

SET @dbname = ''   

 

DROP TABLE #TMP_ServerDrive

DROP TABLE #TMP_LogSpace

DROP TABLE #TMP_DBFileInfo

DROP TABLE #TMP_DataSpace

DROP TABLE #TMP_DB

 

 

CREATE TABLE #TMP_ServerDrive(

    [DriveName] VARCHAR(5) PRIMARY KEY,

    [FreeDriveSpace] BIGINT)

 

INSERT INTO #TMP_ServerDrive

EXEC master..xp_fixeddrives

 

 

CREATE TABLE #TMP_LogSpace (

    [DBName] VARCHAR(200) NOT NULL PRIMARY KEY,

    [LogSize] MONEY NOT NULL,

    [LogPercentUsed] MONEY NOT NULL,

    [LogStatus] INT NOT NULL)

 

SELECT @sql = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'

 

INSERT INTO #TMP_LogSpace

EXEC(@sql)

 

CREATE TABLE #TMP_DBFileInfo (

    [DBName] VARCHAR(200),

    [FileLogicalName] VARCHAR(200),

    [FileID] INT NOT NULL,

    [Filename] VARCHAR(250) NOT NULL,

    [Filegroup] VARCHAR(100) NOT NULL,

    [FileCurrentSize] BIGINT NOT NULL,

    [FileMaxSize] VARCHAR(50) NOT NULL,

    [FileGrowth] VARCHAR(50) NOT NULL,

    [FileUsage] VARCHAR(50) NOT NULL,

    [FileGrowthSize] BIGINT NOT NULL)

   

CREATE TABLE #TMP_DB (

    [DBName] VARCHAR(200) PRIMARY KEY

)

   

INSERT INTO #TMP_DB

SELECT DBName = LTRIM(RTRIM(name))

FROM master.dbo.sysdatabases

WHERE category IN ('0', '1','16')

    AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

--  AND NAME =@dbname

ORDER BY name

 

 

CREATE TABLE #TMP_DataSpace (

    [DBName] VARCHAR(200) NULL,

    [Fileid] INT NOT NULL,

    [FileGroup] INT NOT NULL,

    [TotalExtents] MONEY NOT NULL,

    [UsedExtents] MONEY NOT NULL,

    [FileLogicalName] sysname NOT NULL,

    [Filename] VARCHAR(1000) NOT NULL

)

 

SELECT @dbname = MIN(dbname) FROM #TMP_DB

 

 

 

WHILE @dbname IS NOT NULL

BEGIN

 

    SET @sql = 'USE ' + @dbname + '

        INSERT INTO #TMP_DBFileInfo (

            [DBName],

            [FileLogicalName],

            [FileID],

            [Filename],

            [Filegroup],

            [FileCurrentSize],

            [FileMaxSize],

            [FileGrowth],

            [FileUsage],

            [FileGrowthSize])

        SELECT DBName = ''' + @dbname + ''',

            FileLogicalName = SF.name,

            FileID = SF.fileid,

            Filename = SF.filename,

            Filegroup = ISNULL(filegroup_name(SF.groupid),''''),

            FileCurrentSize = (SF.size * 8)/1024,

            FileMaxSize =    CASE SF.maxsize WHEN -1 THEN N''Unlimited''

                            ELSE CONVERT(VARCHAR(15), (CAST(SF.maxsize AS BIGINT) * 8)/1024) + N'' MB'' END,

            FileGrowth = (case SF.status & 0x100000 when 0x100000 then

                            convert(varchar(3), SF.growth) + N'' %''

                        else

                            convert(varchar(15), ((CAST(SF.growth AS BIGINT) * 8)/1024)) + N'' MB'' end),

            FileUsage = (case WHEN SF.status & 0x40 = 0x40 then ''Log'' else ''Data'' end),

            FileGrowthSize = CASE SF.status & 0x100000 WHEN 0x100000 THEN

                                ((((CAST(SF.size AS BIGINT) * 8)/1024)* SF.growth)/100) + ((CAST(SF.size AS BIGINT) * 8)/1024)

                            ELSE

                                ((CAST(SF.size AS BIGINT) * 8)/1024) + ((CAST(SF.growth AS BIGINT) * 8)/1024)

                            END

        FROM sysfiles SF

        ORDER BY SF.fileid'

    

    EXEC(@sql)

 

    SET @sql = 'USE ' + @dbname + '

                DBCC SHOWFILESTATS WITH NO_INFOMSGS'

               

    INSERT INTO #TMP_DataSpace (

        [Fileid],

        [FileGroup],

        [TotalExtents],

        [UsedExtents],

        [FileLogicalName],

        [Filename])

    EXEC (@sql)

   

    UPDATE #TMP_DataSpace

    SET [DBName] = @dbname

    WHERE ISNULL([DBName],'') = ''

    

    SELECT @dbname = MIN(dbname) FROM #TMP_DB WHERE dbname > @dbname

 

END

 

 

SELECT 'DBName' = DFI.DBName,

    'FileLogicalName' = DFI.FileLogicalName,

    'Filename' = DFI.[Filename],

    'FileMBSize' = DFI.FileCurrentSize,

    'FileGrowth' = DFI.FileGrowth,

    'FileMBGrowth' = DFI.FileGrowthSize,

    'DriveName' = SD.DriveName,

    'DriveMBEmpty' = SD.FreeDriveSpace,

    'FileMBUsed' = CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),

    'FileMBEmpty' = DFI.FileCurrentSize - CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),

    'FilePercentEmpty' = (CAST((DFI.FileCurrentSize - CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT)) AS MONEY) / CAST(CASE WHEN ISNULL(DFI.FileCurrentSize,0) = 0 THEN 1 ELSE DFI.FileCurrentSize END AS MONEY)) * 100

FROM #TMP_DBFileInfo DFI

LEFT OUTER JOIN #TMP_ServerDrive SD

    ON LEFT(LTRIM(RTRIM(DFI.[FileName])),1) = LTRIM(RTRIM(SD.DriveName))

LEFT OUTER JOIN #TMP_DataSpace DSP

    ON LTRIM(RTRIM(DSP.[Filename])) = LTRIM(RTRIM(DFI.[Filename]))

LEFT OUTER JOIN #TMP_LogSpace LSP

    ON LtRIM(RTRIM(LSP.DBName)) = LTRIM(RTRIM(DFI.DBName))