2,187
edits
m (→Commands: Minor update) |
m (→Commands and Maintenance: Added "Show database recovery modes") |
||
(16 intermediate revisions by the same user not shown) | |||
Line 67: | Line 67: | ||
#* Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories) | #* Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories) | ||
#* Older than 2 weeks | #* Older than 2 weeks | ||
# '''Back Up Database Task''' | |||
#* Backup type: Full | |||
#* Databases: All databases | |||
#* Backup will expire: Leave unticked | |||
#* Create a backup file for every database: Update as appropriate | |||
# '''Reorganise Index Task''' | # '''Reorganise Index Task''' | ||
#* All user databases | #* All user databases | ||
Line 74: | Line 79: | ||
#* Update: All existing statistics | #* Update: All existing statistics | ||
#* Scan type: Full scan | #* Scan type: Full scan | ||
# '''Maintenance Cleanup Task''' | # '''Maintenance Cleanup Task''' | ||
#* Delete files of the following type: Backup files | #* Delete files of the following type: Backup files | ||
#* 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 92: | Line 94: | ||
#* File extension: <code>trn</code> | #* File extension: <code>trn</code> | ||
#* File age: Select as appropriate | #* File age: Select as appropriate | ||
== Gain Sysadmin Rights == | == Gain Sysadmin Rights == | ||
Line 144: | Line 136: | ||
* No SQL services are running | * No SQL services are running | ||
* No ''Microsoft SQL Server Management Studio'' instances are running (including via RDP, check for <code>Ssms.exe</code> or <code>Ssms.exe *32</code> in ''Task Manager'' | * No ''Microsoft SQL Server Management Studio'' instances are running (including via RDP, check for <code>Ssms.exe</code> or <code>Ssms.exe *32</code> in ''Task Manager'' | ||
== 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]] | |||
<source lang="sql"> | |||
SELECT * FROM MASTER..SYSDATABASES /* Show databases */ | |||
EXEC sp_databases; /* Show databases */ | |||
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%' /* Show tables */ | |||
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */ | |||
EXECUTE sp_who2; /* Show current processes */ | |||
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> | |||
=== Manual Backup === | |||
<source lang="sql"> | |||
BACKUP DATABASE DatabaseName TO DISK='E:\bak\DatabaseName.bak'; /* Full database backup */ | |||
BACKUP LOG DatabaseName TO DISK='E:\bak\DatabaseName.trn'; /* Transaction log backup */ | |||
</source> | |||
=== Database Sizes === | |||
Use the following in order to find the sizes of databases on your server. | |||
<source lang="sql"> | |||
SELECT | |||
DB_NAME(db.database_id) DatabaseName, | |||
((CAST(mfrows.RowSize AS FLOAT)*8) + (CAST(mflog.LogSize AS FLOAT)*8))/1024 TotalSizeMB, | |||
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB, | |||
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB, | |||
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB, | |||
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB | |||
FROM sys.databases db | |||
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id | |||
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id | |||
LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id | |||
LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id | |||
</source> | |||
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 === | |||
<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 === | |||
List index fragmentation within a particular database... | |||
<source lang="sql"> | |||
USE database_name; | |||
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, | |||
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, | |||
indexstats.avg_fragmentation_in_percent | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id | |||
WHERE indexstats.avg_fragmentation_in_percent > 50 | |||
ORDER BY indexstats.avg_fragmentation_in_percent DESC; | |||
</source> | |||
Indexes identified as being fragmented can defrag'ed by using either of the following command... | |||
<source lang="sql"> | |||
ALTER INDEX IndexName ON TableName REORGANIZE; /* Online */ | |||
ALTER INDEX IndexName ON TableName REBUILD; /* Offline */ | |||
ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON); /* Online - 2008 Ent and above only */ | |||
</source> | |||
Alternatively, if you've got lots of indexes to defrag, you can create the REORGANIZE statements... | |||
<source lang="sql"> | |||
SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE;' | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id | |||
WHERE indexstats.avg_fragmentation_in_percent > 50; | |||
</source> | |||
...or the REBUILD statements... | |||
<source lang="sql"> | |||
SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD WITH (ONLINE = ON);' | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id | |||
WHERE indexstats.avg_fragmentation_in_percent > 50; | |||
</source> | |||
==== Rebuild All Indexes ==== | |||
Unless you are running SQL Enterprise, this needs to be run when your database is '''single-user mode'''. | |||
<source lang="sql"> | |||
USE DatabaseName /* Change to database name! */ | |||
DECLARE @TableName varchar(255) | |||
DECLARE TableCursor CURSOR FOR | |||
SELECT table_name FROM information_schema.tables | |||
WHERE table_type = 'base table' | |||
OPEN TableCursor | |||
FETCH NEXT FROM TableCursor INTO @TableName | |||
WHILE @@FETCH_STATUS = 0 | |||
BEGIN | |||
DBCC DBREINDEX(@TableName,' ',0) | |||
FETCH NEXT FROM TableCursor INTO @TableName | |||
END | |||
CLOSE TableCursor | |||
DEALLOCATE TableCursor | |||
</source> | |||
To put into Single-User Mode | |||
# Take database offline | |||
#* If database won't stop, look for processes still accessing the database and kill them | |||
# Put into single-user mode | |||
# Put database online | |||
#* You can only run commands from one console window/connection | |||
<source lang="sql"> | |||
/* Find processes and kill them*/ | |||
exec sp_who2 | |||
kill 32 | |||
/* Force offline - change database name */ | |||
USE master | |||
ALTER DATABASE DatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE | |||
/* Force into single-user */ | |||
USE master | |||
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE | |||
/* Put back into multi-user */ | |||
USE master | |||
ALTER DATABASE DatabaseName SET MULTI_USER | |||
</source> | |||
[[Category:Applications]] | [[Category:Applications]] | ||
[[Category:Microsoft]] | [[Category:Microsoft]] |