You are responsible for managing an instance of SQL Server 2008.
The SQL server contains a database named Custdb that is configured with the full recovery model.
Your company has a partner company named nutex.com .
The partner company also maintains its customer data on an instance of SQL Server 2008. You import thecustomer data from the partner company into the Custdb database.
After the import, users report that Custdb is performing slowly.
You investigate and discover that the transactionlog for the Custdb database has increased in size dramatically, and currently occupies most of the free space onthe hard disk.
You want to reclaim as much free space as possible. Which Transact-SQL statement should you use?
A.
DBCC FREEPROCCACHE
B.
DBCC INDEXDEFRAG
C.
DBCC SHRINKFILE
D.
DBCC CHECKDB
Explanation:
You should use the DBCC SHRINKFILE statement. Database Console Commands (DBCC) are Transact-SQLstatements that allow you to perform various maintenance tasks on a database, transaction log, filegroup, orindex. When you want to reclaim hard disk space, you can shrink a data or log file by using the DBCCSHRINKFILE
statement. The DBCC SHRINKFILE statement reduces the size of the specified data or log file forthe current database. You can use the following Transact-SQL statement to shrink a log file named Custdb_Log to 2 MB:
DBCC SHRINKFILE (Custdb_Log, 2);
You should not use the DBCC FREEPROCCACHE statement because this statement does not allow you toreduce the size of the data or log file to recover the hard disk space. The DBCC FREEPROCCACHE statementallows you to delete all elements from the plan cache, delete all workload groups from a specified resource pool,or specify a plan handle or SQL handle to delete a particular plan from the plan cache. You should not use the DBCC INDEXDEFRAG
statement because this statement does not allow you to reducethe size of the data or log file to recover the hard disk space. The DBCC INDEXDEFRAG
statement defragmentsindexes of the specified view or table. You should not use the DBCC CHECKDB statement because this statement does not allow you to reduce thesize of the data or log file to recover the hard disk space. The Check Database Integrity task of a maintenanceplan performs the same functions as the DBCC CHECKDB
statement. Both allow you to verify the allocation andstructural integrity of database objects. When you perform a database integrity check, an entry is recorded in theWindows Application log, which you can use to verify whether the database integrity check was performed for aparticular database. This entry is also recorded in the SQL Server log.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Maintain database integrity.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > DBCC (Transact-SQL) > DBCC SHRINKFILE (Transact-SQL)
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > DBCC (Transact-SQL)