591
Chapter 22: Maintaining the Database
22
If the AUTO_UPDATE_STATISTICS_AYSNC option for the database is set to ON, you can place the
database in single-user mode but you cannot connect to the database because the background
thread used to update the statistics takes a connection against the database. You can query the
is_auto_update_stats_async_on column in the sys.databases catalog view to check if the
AUTO_UPDATE_STATISTICS_ASYNC option is set to ON. If this option is set to ON, fi rst set the AUTO_
UPDATE_STATISTICS_AYSNC option for the database to OFF using the ALTER DATABASE command.DBCC offers two repair modes:■ (^) REPAIR_REBUILD: Performs a repair that does not lead to any data loss; however,
this repairs only problems found in nonclustered indexes.
■ (^) REPAIR_ALLOW_DATA_LOSS: Performs the repairs and fi xes corrupted database
structures. As the name suggests, this mode can result in data loss.
There used to be a third repair mode called REPAIR_FAST in earlier versions of SQL Server. This mode still exists in
SQL Server 2012, but it does not perform any activity and is kept only for backward compatibility.
The following example places the AdventureWorks2012 sample database in single user mode
and runs DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. It then sets the
database back to multiuser mode.
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
BEGIN TRANSACTION;
DBCC CheckDB ('AdventureWorks2012', REPAIR_ALLOW_DATA_LOSS);
--Check for any data loss
--ROLLBACK TRANSACTION if data
--loss is not acceptable else COMMIT TRANSACTION;
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;
Result (abridged):
DBCC results for 'AdventureWorks2012'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
...
DBCC results for 'sys.sysrowsets'.
There are 291 rows in 3 pages for object "sys.sysrowsets".
...
c22.indd 591c22.indd 591 7/31/2012 9:24:29 AM7/31/2012 9:24:29 AM
http://www.it-ebooks.info