2,187
edits
(→Database Sizes: Added note) |
m (→Commands and Maintenance: Added "Show database recovery modes") |
||
(One intermediate revision by the same user not shown) | |||
Line 149: | Line 149: | ||
EXECUTE sp_who2; /* Show current processes */ | EXECUTE sp_who2; /* Show current processes */ | ||
SELECT name,log_reuse_wait_desc FROM sys.databases; /* Show why transaction log can't grow */ | SELECT name,log_reuse_wait_desc FROM sys.databases; /* Show why transaction log can't grow */ | ||
SELECT name,recovery_model_desc FROM sys.databases ORDER BY name /* Show database recovery modes */ | |||
</source> | </source> | ||
Line 175: | Line 176: | ||
Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes | Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes | ||
=== Table Sizes === | |||
Replace '''DatabaseName''' in the query below | |||
<source lang="sql"> | |||
USE <DataBaseName> | |||
SELECT | |||
t.NAME AS TableName, | |||
i.name as indexName, | |||
sum(p.rows) as RowCounts, | |||
sum(a.total_pages) as TotalPages, | |||
sum(a.used_pages) as UsedPages, | |||
sum(a.data_pages) as DataPages, | |||
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, | |||
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, | |||
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB | |||
FROM | |||
sys.tables t | |||
INNER JOIN | |||
sys.indexes i ON t.OBJECT_ID = i.object_id | |||
INNER JOIN | |||
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id | |||
INNER JOIN | |||
sys.allocation_units a ON p.partition_id = a.container_id | |||
WHERE | |||
t.NAME NOT LIKE 'dt%' AND | |||
i.OBJECT_ID > 255 AND | |||
i.index_id <= 1 | |||
GROUP BY | |||
t.NAME, i.object_id, i.index_id, i.name | |||
ORDER BY | |||
object_name(i.object_id) | |||
</source> | |||
Source: https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database | |||
=== Memory Usage === | === Memory Usage === |