2,187
edits
Difference between revisions of "MS SQL"
Jump to navigation
Jump to search
m
→Commands and Maintenance: Added "Show database recovery modes"
(→Daily Maintenance Plan: Added "Reorganise Index Task" transaction log note) |
m (→Commands and Maintenance: Added "Show database recovery modes") |
||
| (3 intermediate revisions by the same user not shown) | |||
| Line 138: | Line 138: | ||
== Commands and Maintenance == | == Commands and Maintenance == | ||
For performance issues, see http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ | |||
Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also [[MySQL]] | Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also [[MySQL]] | ||
| Line 147: | 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 156: | Line 159: | ||
=== Database Sizes === | === Database Sizes === | ||
Use the following in order to find the sizes of databases on your server. | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT | SELECT | ||
| Line 172: | 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 === | ||