MS SQL: Difference between revisions
(Re-ordered sections) |
m (→Commands and Maintenance: Added "Show database recovery modes") |
||
(15 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 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 143: | Line 147: | ||
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%' /* Show tables */ | 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) */ | 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> | </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]] |
Latest revision as of 13:42, 22 September 2019
Installation
SQL 2008 Standard
Instructions below made from MS SQL 2008 Standard install ISO. These notes were created when installing a SQL server for use with VMware vCentre, therefore may contain some specifics for that usage, see the following if you're installing for vSphere 5, it has much greater detail..
- http://lonesysadmin.net/2011/10/06/how-to-install-microsoft-sql-server-2008-r2-for-vmware-vcenter-5/
If installing in a VM, perform a snapshot 1st in case you make a mistake and want to reattempt from scratch.
- Create a local user account for MS SQL[1], eg SQL-DB, with a nice long password with only the following options checked
- User not allowed to change password
- Password never expires
- Run the setup.exe from the root of the ISO (or allow to autorun)
- The installer may need to install .NET and upgrade Windows Installer
- Click OK if so to allow
- Accept the .NET Framework EULA
- On completion on .NET Framework install, click Exit to proceed
- Click on Installation to bring up the install options
- Click on New SQL Server stand-alone... to launch the install wizard
- Assuming the Setup Support Rules check succeeds, click OK to proceed
- Fix any issues found in order to be able to proceed further
- If required, enter a valid product key, then click Next to proceed
- Accept the EULA, then click Next to proceed
- Click Install in order to install the Setup Support Files
- Assuming the Setup Support Rules check succeeds, click Next to proceed
- Fix any issues found in order to be able to proceed further, warnings don't prevent installation, but should be read to understand why they have occurred
- On the Feature Selection page, select the following features, and change the install paths if required, then click Next to continue
- Instance Features | Database Engine Services
- Shared Features | Client Tools Connectivity
- Shared Features | Integration Services
- Shared Features | Management Tools – Basic
- Shared Features | Management Tools – Complete
- On the Instance Configuration page, change the Instance root directory if you want to store the database somewhere else, click Next to proceed
- Click Next past the Disk Space Requirements page
- On the Server Configuration page, change the following then click Next to proceed
- Select Use the same account for all SQL Server services and enter the local account created earlier (eg for SQL-DB)
- Change SQL Server Agent service startup type to Automatic
- On the Database Engine Configuration page, change the following then click Next to proceed
- Change to Mixed Mode, and enter a nice long password for the SQL root account
- Add SQL administrators (at minimum I'd suggest the machine's local admin and yourself)
- Click Next' to proceed past the next few pages, and then Install
- Install should complete successfully, then click Next, and then Close to complete.
- Install the latest SQL service pack, then run Windows Update manually to ensure the install is fully patched
- Even if not asked to, reboot the machine, then log back in and ensure all SQL services have started correctly
- Limit memory available to SQL (optional)
- Start SQL Server Management Studio and login as one of the SQL administrators you added earlier
- Right-click over the SQL server and select Properties
- Go to Memory and set Maximum server memory (in MB) to something sensible [2]
- SQL will grab as much physical RAM as possible in order to cache and improve performance, therefore you need to limit what it can grab so that the OS, and any other applications will have enough space to run without paging
- Allow Local Launch (optional, but said to fix some scheduling and spurious event log error issues)
- Go into Component Services (Start | Administrative Tools | Component Services)
- Find, right-click and select Properties over Component Services | Computers | My Computer | DCOM Config | MsDtsServer100
- Go to the Security tab and within the Launch and Activation Permission section, click Edit
- Add the local SQL user (eg SQL-DB), and ensure Local Launch is checked
- Click OK as required to apply and exit
Additional notes...
- ↑ If you're creating a SQL server for a front-end service, then its usual to use domain service accounts to run SQL under. However, if you're creating a SQL server back-end infrastructure purposes (such as vCentre) which should be able to run as stand-alone services without dependence on services such as DNS or Active Directory servers then you must use a local account. Back-end core infrastructure should be as isolated as possible from other systems, so that when failures or disasters occur, impact is localised and you don't end up with the chicken and egg or deadlock situation where you can't bring one service up, because it depends on another, which can't be brought up because...[etc].
- ↑ If installing SQL on the same machine as vCentre, give SQL 25-50% of available RAM. For example, I'd use around 300 MB for a small lab/test environment of 1 or 2 ESX's, whereas if managing 40 ESX's with 1000 VM's you'd need around 2 GB.
Standard Maintenance Plans
Daily Maintenance Plan
- History Cleanup Task
- Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
- 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
- All user databases
- Compact large objects
- Update Statistics Task
- All user databases
- Update: All existing statistics
- Scan type: Full scan
- Maintenance Cleanup Task
- Delete files of the following type: Backup files
- File extension:
bak
- 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
- Back Up Database Task
- Backup type: Transaction Log
- Databases: All user databases
- Maintenance Cleanup Task
- Delete files of the following type: Backup files
- File extension:
trn
- File age: Select as appropriate
Gain Sysadmin Rights
In order to gain sysadmin control over a SQL instance you need to have local administrator access over the server, and be able to shut the SQL service down. You should only need to do this if nobody has sysadmin rights, and trying to add gain sysadmin rights or perform other tasks fails with errors similar to...
- User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
If your running in a virtual machine, take a snapshot 1st for piece of mind...!
- Login to the server as a local admin (any account with local admin rights)
- Stop the SQL Server service and any other SQL services
- If the database is running as a named instance the name will appear in brackets in the service name - note the instance name
- Ensure that any Microsoft SQL Server Management Studio instances are closed
- Start the SQL instance in single-user mode
- Open a command prompt in the
Binn
folder of the instance- EG
C:\Program Files\Microsoft SQL Server\MSSQL10.DB\MSSQL\Binn
- EG
- If running as a named instance noted above use (replace
<Instance>
)...sqlservr.exe -s<Instance> -m -c
- Otherwise use...
sqlservr.exe -s<Instance> -m -c
- In amongst other messages you should see the following to state that the DB is up and ready
SQL Server is now ready for client connections. This is an informational message; no user action is required.
- Open a command prompt in the
- Connect to the SQL instance (see below for probs)
- Open another command prompt in the
Binn
folder of the instance - If running as a named instance noted above use (replace
<ComputerName>
and<Instance>
)...sqlcmd -S<ComputerName>\<Instance>
- Otherwise use (replace
<ComputerName>
)...sqlcmd -S<ComputerName>
- Open another command prompt in the
- Add the required accounts into the sysadmin role (replace
<Login>
with Windows accountsp_addsrvrolemember '<Login>', 'sysadmin'
- EG
sp_addsrvrolemember 'DOMAIN\user', 'sysadmin'
- EG
GO
- Repeat the above two steps as required
- Restart the server
Source - http://support.microsoft.com/kb/937682
Unable to Connect in Single User Mode
When trying to connect to a SQL server instance in single user mode you received an error similar to...
- Login failed for user 'DOMAIN\user'. Reason: Server is in single user mode. Only one administrator can connect at this time.
This is caused by another user (or piece of software) having already looged into the SQL instance. Ensure that...
- Any applications that use the SQL instance are not trying to connect (disconnect the SQL server from the network if required)
- No SQL services are running
- No Microsoft SQL Server Management Studio instances are running (including via RDP, check for
Ssms.exe
orSsms.exe *32
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
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 */
Manual Backup
BACKUP DATABASE DatabaseName TO DISK='E:\bak\DatabaseName.bak'; /* Full database backup */
BACKUP LOG DatabaseName TO DISK='E:\bak\DatabaseName.trn'; /* Transaction log backup */
Database Sizes
Use the following in order to find the sizes of databases on your server.
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: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes
Table Sizes
Replace DatabaseName in the query below
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: https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database
Memory Usage
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;
Fragmentation
List index fragmentation within a particular database...
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;
Indexes identified as being fragmented can defrag'ed by using either of the following command...
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 */
Alternatively, if you've got lots of indexes to defrag, you can create the REORGANIZE statements...
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;
...or the REBUILD statements...
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;
Rebuild All Indexes
Unless you are running SQL Enterprise, this needs to be run when your database is single-user mode.
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
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
/* 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