2,187
edits
Difference between revisions of "MS SQL"
Jump to navigation
Jump to search
→Commands and Maintenance: Added "Memory Usage"
(→Commands and Maintenance: Added SELECT name,log_reuse_wait_desc FROM sys.databases;) |
(→Commands and Maintenance: Added "Memory Usage") |
||
| Line 146: | Line 146: | ||
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 */ | ||
</source> | </source> | ||
=== Manual Backup === | === Manual Backup === | ||
<source lang="sql"> | <source lang="sql"> | ||
| Line 170: | Line 170: | ||
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 | ||
=== Memory Usage === | |||
<source lang="sql"> | |||
DECLARE @total_buffer INT; | |||
SELECT @total_buffer = cntr_value | |||
FROM sys.dm_os_performance_counters | |||
WHERE RTRIM([object_name]) LIKE '%Buffer Manager' | |||
AND counter_name = 'Total Pages'; | |||
;WITH src AS | |||
( | |||
SELECT | |||
database_id, db_buffer_pages = COUNT_BIG(*) | |||
FROM sys.dm_os_buffer_descriptors | |||
--WHERE database_id BETWEEN 5 AND 32766 | |||
GROUP BY database_id | |||
) | |||
SELECT | |||
[db_name] = CASE [database_id] WHEN 32767 | |||
THEN 'Resource DB' | |||
ELSE DB_NAME([database_id]) END, | |||
db_buffer_pages, | |||
db_buffer_MB = db_buffer_pages / 128, | |||
db_buffer_percent = CONVERT(DECIMAL(6,3), | |||
db_buffer_pages * 100.0 / @total_buffer) | |||
FROM src | |||
ORDER BY db_buffer_MB DESC; | |||
</source> | |||
=== Fragmentation === | === Fragmentation === | ||