2,187
edits
(→Daily Maintenance Plan: Moved backup to start) |
m (→Commands and Maintenance: Added "Show database recovery modes") |
||
(4 intermediate revisions by the same user not shown) | |||
Line 83: | Line 83: | ||
#* File extension: <code>bak</code> | #* File extension: <code>bak</code> | ||
#* File age: Select as appropriate | #* File age: Select as appropriate | ||
Be aware rhat the '''Reorganise Index Task''' can cause lots of activity to be generated in the transaction log, therefore if your recovery mode is ''Full'' (rather than ''Simple''), the transaction log can grow large very quickly. If performing on a database that's been running for some time this can be an issue. Consider setting the recovery mode to ''Simple'' for the first run, or perform at a time when if your transaction log fills you can recover without too much disruption to applications using the database. | |||
=== Hourly Transaction Log Backup === | === Hourly Transaction Log Backup === | ||
Line 136: | 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 145: | 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 154: | 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 170: | 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 === |