SQL Server deletes a large amount of data a pit-daddy tour

Database 1kw data is not used, affecting the read and write speed, so to delete

Using the DELETE statement

The accident, which lasted half an hour, still did not see the end of the sign

Then forcibly ended (stopped the service)

After the service is opened, the database displays "Recovering"

Baidu, I'll know.

So, stop the service, cut the data to another folder

Recovery Services

Deleting a database

Start the service

Attaching a database

1 Child, Attachment completed (just noon to eat)

Ignorant of me again using the DELETE statement to remove

Two hours, really unbearable, stop deleting

Database display is recovering

Database file increased, MDF reached 31g,log ask Price reached 109g

Cut the file again to another folder, start the service, delete the database, start the service, attach the database

SQL Attach Database window crashes after 1h

I collapsed

Stop the service again

Cut database file, start service, delete database, stop service, attach MDF, do not log file, error cannot be attached

Baidu

Stop the service, cut the database file, restore the service, create a database with the same name, stop the service, delete the database MDF file with the same name, overwrite the original database MDF with the same name data MDF file

Open service, see data, pending status

Baidu

Execute the following code

　　

Use mastergoalter database test set single_usergoalter database test set EMERGENCYGODBCC CHECKDB (Test,repair_allow_data_ LOSS) Goalter database test set onlinegoalter database test set Multi_usergo


From: http://blog.csdn.net/zxr85/article/details/51943011

It's still running in half an hour.

I collapsed

Stop execution, the database is now a single user mode

Crash, Baidu

Execute the following code

Use master;  GO  DECLARE @SQL VARCHAR (MAX);  SET @SQL = '  SELECT @[email protected]+ '; KILL ' +rtrim (SPID) from  master: sysprocesses  WHERE dbid=db_id (' database name ');  EXEC (@SQL); ALTER database name SET multi_user;

Code from: http://www.cnblogs.com/amadeuslee/p/6608076.html

Database single-mode release

Refresh the data, miracles happen.

Database tables can be accessed, because only one table, the actual data to retain only more than 8w, so use

Select INTO statement, saving data to a temporary table due to insufficient disk space

Shrinking a Database

Execute the SELECT INTO statement

Execution of TRUNCATE TABLE, the size of the database file has not changed

Shrinking a Database

Execute INSERT INTO Select

All done.

This series of operations is not a production environment, it is the data on the local server

In this article a series of operations for reference only, the text of the miracle occurred in the paragraph, I was also very blind, for reference only

SQL Server deletes a large amount of data a pit-daddy tour