Poniżej skrypt wyświetlający wielkość plików baz danych z ilością nie wykorzystanej powierzchni.

 
CREATE TABLE #DBInfo ( [DatabaseName] [varchar](100) NULL ,[FileSizeMB] [int] NULL ,[LogicalFileName] [sysname] NOT NULL ,[FileName] [sysname] NOT NULL ,[Status] [sysname] NOT NULL ,[Updateability] [sysname] NOT NULL ,[RecoveryMode] [sysname] NOT NULL ,[FreeSpaceMB] [int] NULL ,[FreeSpacePct] [varchar](7) NULL ) DECLARE @command VARCHAR(5000) SELECT @command = 'USE [' + '?' + '] SELECT ' + '''' + '?' + '''' + ' AS DatabaseName, CAST(sysfiles.size/128.0 AS int) AS FileSizeMB, sysfiles.name AS LogicalFileName, sysfiles.filename AS FileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct FROM dbo.sysfiles' INSERT INTO #DBInfo ( DatabaseName ,FileSizeMB ,LogicalFileName ,FileName ,STATUS ,Updateability ,RecoveryMode ,FreeSpaceMB ,FreeSpacePct ) EXEC sp_MSForEachDB @command SELECT * FROM #DBInfo DROP TABLE #DBInfo

Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl