Veritabani boyutu takibi
Veritabanlarının boyutlarını takip edebilmek için aşağıdaki sorguyu kullanabilirsiniz.
1: ;WITH DataBase_Size (SqlServerInstance, DatabaseName, DatabaseSize, LogSize, TotalSize)
2: AS
3: -- Define the CTE query.
4: (SELECT @@SERVERNAME SqlServerInstance,
5: db.name AS DatabaseName,
6: SUM( CASE
7: WHEN af.groupid = 0 THEN
8: 0
9: ELSE
10: af.size / 128.0E
11: END
12: ) AS DatabaseSize,
13: SUM( CASE
14: WHEN af.groupid = 0 THEN
15: af.size / 128.0E
16: ELSE
17: 0
18: END
19: ) AS LogSize,
20: SUM(af.size / 128.0E) AS TotalSize
21: FROM master..sysdatabases AS db
22: INNER JOIN master..sysaltfiles AS af
23: ON af.[dbid] = db.[dbid]
24: WHERE db.name NOT IN ( 'distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb' ) -- Sistem databaseleri
25: AND db.name NOT IN ( 'Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW' ) -- Sample databaseler
26: GROUP BY db.name)
27: -- Define the outer query referencing the name.
28: SELECT SqlServerInstance 'Sunucu Adı',
29: DatabaseName 'Database Adı',
30: DatabaseSize 'Database Boyutu',
31: LogSize 'Log boyutu',
32: TotalSize 'Toplam Boyut'
33: FROM DataBase_Size
34: ORDER BY TotalSize DESC;
