Kā apskatīt datu bāzes failu izmēru un atrašanās vietu

Aprakstīts, ka iegūt informāciju par visiem datu bāzu failiem uz servera.

Ja ir nepieciešamība apskatīt konkrētās datu bāzes failus, var izmantot CV skatu sys.database_files:

Select *
From sys.database_files

Bet tādā veidā iegūst informāciju par vienas datu bāzes failiem.

Ja vajag par visu datu bāzu visiem failiem, tad varētu iepriekšējo vaicājumu sakombinēt ar procedūru sp_msForEachDB. Otrs varians,- izmantot sys.master_files:

Select DB_NAME(database_id) AS [Database Name], Name,  physical_name,
    Cast(Round(size * 8/1024,0) as nvarchar) + ' MB' Size
From sys.master_files
Order By DB_NAME(database_id)

[2012-06-21 labots] Bieži ir svarīgi redzēt, cik ir brīva vieta datu bāzes failos. Zemāk skripts, kas parāda visu instances datu bāzu failu izmērus un brīvo vietu tajos (skriptā tiek izmantota globāla temporālā tabula). Visi izmēri ir parādīt MB. Ja tiek izmantoti Retinātie faili (Sparse files), tad jāpievērš uzmanība faktiskajam izmēram uz diska, nevis loģiskajam izmēram. tempdb datu bāzei sys.master_files tiek uzrādīts sākotnējais izmērs, tādēļ rezultātos tas netiek attēlots, jo ir atšķirīgs no reālā. Offline datu bāzēm nevar noteikt fiziskā faila izmēru:

Create Table ##pagaidu
(
    database_id int,
    file_id int,
    FreeSpace decimal(18,2)
)
Exec sp_msforeachdb '
Use [?];
-- IF atfiltrē snapshots
IF (select source_database_id from sys.databases where database_id = DB_ID()) Is Null
    Insert Into ##pagaidu (database_id, file_id, FreeSpace)
        Select DB_ID() database_id, file_id,
        Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
            Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) As FreeSpace
        From sys.database_files
';
Select DB_NAME(mf.database_id) AS [Database Name],
    mf.Name,  mf.physical_name PhysicalName, mf.type_desc,
    Cast(Round(fs.size_on_disk_bytes/(1024.0*1024.0),2) as decimal(18,2)) SizeOnDisk,
    -- tempdb sys.master_files (mf) rāda inicializācijas nevis reālo izmēru.
        Case When DB_NAME(mf.database_id) = 'tempdb'
            then null -- labāk neko kā kaut ko samudžinātu
            else Cast(Round(CAST(mf.size AS BIGINT) * 8/1024.0,2) as decimal(18,2))
        end
     Size,
    p.FreeSpace,
    Case
        When fs.size_on_disk_bytes < cast(mf.size as bigint) * 8192
        Then 'Sparce' End IsSparceFile,
    d.state_desc
From sys.master_files mf
    Left Join sys.databases d on mf.database_id = d.database_id
    Left Join sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) fs
        On mf.database_id = fs.database_id and mf.file_id = fs.file_id
    Left Join ##pagaidu p
        On mf.database_id = p.database_id and mf.file_id = p.file_id
Order By DB_NAME(mf.database_id)

drop table ##pagaidu

[2012-02-15 papildināts] Pēc raksta, vēlējos piefiksēt, ka TempDB ir nianses (sys.dm_db_file_space_usage).

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

[2012-09-11 papildināts] Lai uzzinātu cik brīvas ir SQL Server var izmantot divas metodes. Pirmā no tām izmanto procedūru, kas nav dokumentēta msdn:

EXEC master.dbo.xp_fixeddrives

Otrā metode parāda vietu iz diska izmantojot DMV skatu sys.dm_os_volume_stats, bet tas pieejams tikai no SQL Server 2008 R2 SP2 un parāda brīvo vietu tikai uz tiem diskiem, uz kuriem ir izvietoti datu bāzu faili:

Select volume_mount_point,
    Cast(total_bytes/1024.0/1024/1024 as decimal(18,3)) TotalSpaceGB,
    Cast(available_bytes/1024.0/1024/1024 as decimal(18,3)) FreeSpaceGB   
From sys.master_files AS f
    Cross Apply sys.dm_os_volume_stats(f.database_id, f.file_id)
Group By volume_mount_point, total_bytes, available_bytes
OPTION (RECOMPILE);

Vēl varētu noderēt- Brīdinājums par izsīkstošu diska vietu.