SQL Administratoren benötigen oft detaillierte Informationen zu verschiedenen Datenbanken, wie Z.B. Used Space einer Datenbank oder einer Log Datei.
Um mit geringem Aufwand diese Informationen zu sammeln sollte man dieses SQL Script ausführen. (Kompatibel ab SQL Server 2005)
Als Output erhält man folgende Tabelle:
![[image%255B10%255D.png]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqC345oHocODWs-eegjQfJLD4dilpkdHNVi1a1szSuLlbEDdZDjL_IT03LlaY5LMvm6L7UsznEV_tuqDpYN973F9mpjGkxYuVSNm60wAzl5lVEAU_UIPjgOvNIpfn6CcvpN52uSD5NAxpQ/s1600/image%25255B10%25255D.png)
DatabaseName, LogicalFileName, RecoveryMode, FileSizeMB, UsedSpaceMB, FreeSpaceMB, FreeSpacePct, PhysicalFileName, Status
SQL-Script:
DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
UsedSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
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(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' )/128.0 AS int) AS UsedSpaceMB,
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,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
UsedSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command
SELECT
--ServerName,
DatabaseName,
LogicalFileName,
RecoveryMode,
FileSizeMB,
UsedSpaceMB,
FreeSpaceMB,
FreeSpacePct,
PhysicalFileName,
Status
--Updateability,
--PollDate
FROM @DBInfo
--WHERE LogicalFileName NOT LIKE '%_log'
ORDER BY
ServerName,
DatabaseName
![image_thumb[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRhmDC98hLwDuWGirElkC4aen3jpAoAGA0_tc5wXkvmrifm4zoRz6qFsbn0WicLmUkV_CnjUKOcaWULEW7DMoTPxDju9cGyiMKyLfFCnReY1W5f0H31AE7cc0CRu592cOvVLvImm-t2WfV/s1600-h/image_thumb%25255B4%25255D%25255B2%25255D.png "image_thumb[4]")
![image_thumb[2]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0B8vVMcc_pi4hPxRTemrvcYynXD3EMTghmdU13HsL4ryISMlgzMzjkSkW4KyXnrxSYxhe6mvNgbY7wOlpjmREsl_z3o9SaNQdgALAWJRiEaVI8Wxch9MKDI01xjYj6BjflVRyVy8n4iqd/s1600-h/image_thumb%25255B2%25255D%25255B2%25255D.png "image_thumb[2]")
![image_thumb[7]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxFmTVC4mtTUsj_WLxpVXF3zR6gLT44hiA4LITaSaXbgSPXUBDRPXd3FhGDrn7dNW4PHUNixf_GyVJrzWwa97Gz83oBlGLZ7JY3eALyWNIdc2T-ckL72mrsPAOjznBIgalQm_8J51fYisI/s1600-h/image_thumb%25255B7%25255D%25255B2%25255D.png "image_thumb[7]")
