Question
1: How to delete 10 million rows in the full mode?
Step 1 -
deleting in chunks - maybe 10,000 records per time (divide and conquer)
Step 2 -
keeping the row counts
Step 3 -
using dbcc Shrinkfile as below (see http://support.microsoft.com/kb/907511)
To
shrink a transaction log file that has little free space in SQL Server 2005,
follow these steps:
1)
Back up the transaction log file to make most of the active
virtual log files inactive. Therefore, the inactive virtual log files can be
removed in a later step. To do this, run a Transact-SQL statement that is
similar to the following Transact-SQL statement.
BACKUP
LOG TestDB TO DISK='C:\TestDB1.bak'
2)
Shrink the transaction log file. To do this, run a Transact-SQL
statement that is similar to the following Transact-SQL statement.
BCC
SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
Note:
In this statement, is a placeholder for the name of the transaction log
file. In this statement, is a placeholder for the target size that you
want the transaction log file to be. The target size must be reasonable. For
example, you cannot shrink the transaction log file to a size that is less than
2 virtual log files.
3)
If the DBCC SHRINKFILE statement does not shrink the transaction
log file to the target size, run the BACKUP LOG statement that is mentioned in
step 1 to make more of the virtual log files inactive.
4)
Run the DBCC SHRINKFILE statement that is mentioned in step 2.
After this operation, the transaction log file should be similar to the target
size.
Question
2: How to handle the suspect mode?
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
