Sprawdzanie ilości wolnego miejsca na dysku w SQL Server

Poniżej najprostsze zapytanie zwracajace ilość miejsca na dysku na którym znajdują się bazy danych. UWAGA! Zapytanie to nie działa w wersji SQL2008 !

EXEC MASTER..xp_fixeddrives

drive MB free
----- -----------
C     43948
D     16985
E     34184
F     6148
G     1514

(5 rows affected)

Innym zapytaniem możemy sprawdzić miejsce na dyskach powiązanych z naszymi bazami danych. Dyski na których nie ma baz nie będą wyświetlane.

SELECT DISTINCT
SUBSTRING(dovs.logical_volume_name,1,20) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,20) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

LogicalName          Drive                FreeSpaceInMB
-------------------- -------------------- -------------
SYSTEM               C:\                  43956

(1 row affected)

Po modyfikacji wyświetla ilość wolnego miejsca w powiązaniu z naszymi bazami.

SELECT DISTINCT
SUBSTRING(DB_NAME(dovs.database_id),1,25) DBName,
SUBSTRING(dovs.logical_volume_name,1,20) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,20) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

DBName                    LogicalName          Drive                FreeSpaceInMB
------------------------- -------------------- -------------------- -------------
baza_testowa              SYSTEM               C:\                  43954
AdventureWorks2012        SYSTEM               C:\                  43954
master                    SYSTEM               C:\                  43954
ReportServerTempDB        SYSTEM               C:\                  43954
msdb                      SYSTEM               C:\                  43954
ReportServer              SYSTEM               C:\                  43954
tempdb                    SYSTEM               C:\                  43954
Northwind                 SYSTEM               C:\                  43954
DataWarehouse             SYSTEM               C:\                  43954
FGRestoreTEST             SYSTEM               c:\                  43954
TriggerDatabase           SYSTEM               C:\                  43954
CDCDatabase               SYSTEM               C:\                  43954
SSISDB                    SYSTEM               C:\                  43954
model                     SYSTEM               C:\                  43954

(14 rows affected)

Mozemy też wyświetlić jeszcze bardziej szczegółowo w rozbiciu na konkretne pliki

SELECT DISTINCT
SUBSTRING(DB_NAME(dovs.database_id),1,25) DBName,
SUBSTRING(mf.physical_name,1,70) as PhysicalFileLocation,
SUBSTRING(dovs.logical_volume_name,1,15) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,10) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

DBName                    PhysicalFileLocation                                                   LogicalName     Drive      FreeSpaceInMB
------------------------- ---------------------------------------------------------------------- --------------- ---------- -------------
SSISDB                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf                   SYSTEM          C:\        43953
ReportServer              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf             SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf                  SYSTEM          C:\        43953
SSISDB                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf                   SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2012.ndf                                                  SYSTEM          c:\        43953
ReportServerTempDB        C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.ldf   SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FGRestoreTEST.mdf                                           SYSTEM          c:\        43953
msdb                      C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf                  SYSTEM          C:\        43953
AdventureWorks2012        C:\SQLData\AdventureWorks2012_log.ldf                                  SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2013.ndf                                                  SYSTEM          c:\        43953
Northwind                 C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\Northwind.ldf                SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FGRestoreTEST_log.ldf                                       SYSTEM          c:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf           SYSTEM          C:\        43953
DataWarehouse             C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\DataWarehouse_log.ldf        SYSTEM          C:\        43953
TriggerDatabase           C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\TriggerDatabase.mdf          SYSTEM          C:\        43953
baza_testowa              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\baza_testowa.mdf             SYSTEM          C:\        43953
ReportServerTempDB        C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf       SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf                   SYSTEM          C:\        43953
AdventureWorks2012        C:\SQLData\AdventureWorks2012_Data.mdf                                 SYSTEM          C:\        43953
CDCDatabase               C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\CDCDatabase.mdf              SYSTEM          C:\        43953
model                     C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf                 SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2011.ndf                                                  SYSTEM          c:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf           SYSTEM          C:\        43953
msdb                      C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf                 SYSTEM          C:\        43953
DataWarehouse             C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\DataWarehouse.mdf            SYSTEM          C:\        43953
model                     C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf                    SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf           SYSTEM          C:\        43953
Northwind                 C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\Northwind.mdf                SYSTEM          C:\        43953
ReportServer              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServer_log.ldf         SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2010.ndf                                                  SYSTEM          c:\        43953
baza_testowa              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\baza_testowa_log.ldf         SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2014.ndf                                                  SYSTEM          c:\        43953
master                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf                   SYSTEM          C:\        43953
CDCDatabase               C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\CDCDatabase_log.ldf          SYSTEM          C:\        43953
master                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf                  SYSTEM          C:\        43953
TriggerDatabase           C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\TriggerDatabase_log.ldf      SYSTEM          C:\        43953

(36 rows affected)
Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz