Resolving SQL Server database tablespace does not automatically release issues

The query slows down when the SQL Server database has been frequently truncated after the data is encountered in the project.

I directed the data to another library and found that the query was quick.

The underlying reason is that deleting the data does not release the tablespace, the solution is as follows:

1 /*--Special attention2  3 Please follow the steps, do not take the previous steps, please do not follow the steps4 failure to do so may damage your database.5  6  7 generally not recommended to do 4th, 62 steps8 The 4th step is unsafe, potentially damaging the database or losing data9 6th step if the log reaches the upper limit, subsequent database processing will fail and cannot be resumed until the log is cleaned up.Ten --*/  One   A --all of the following library names refer to the library names of the databases you are working with -   - 1. Emptying the log the DUMP  TRANSACTIONMydpc withno_log -   - 2. Truncate the transaction log: - BACKUP LOGMydpc withno_log +   - 3. Shrink the database file (if not compressed, the database file does not decrease +Enterprise Manager--Right-click the database you want to compress--all tasks--shrink the database--Shrink the file A --Select the log file--in the contraction mode to choose to shrink to XXM, here will give a allowed to shrink to the minimum number of M, directly enter this number, OK. at --Select Data File--select shrink to XXM in shrink mode, here will give a minimum number of m allowed to shrink to, enter this number directly, OK -   - You can also use SQL statements to complete - --Shrinking a database - DBCCShrinkdatabase (library name, size required) -   in --shrinks the specified data file, 1 is the file number, which can be queried by this statement: SELECT * from Sysfiles - DBCCShrinkfile (1)  to   + 4In order to maximize the reduction of the log file (if it is a SQL7.0, this step can only be done in Query Analyzer) - A. Detach the database: theEnterprise Manager--Server--database--right--detach database *   $ B. Delete the log file from My ComputerPanax Notoginseng   - c. Additional databases: theEnterprise Manager--Server--database--right--Attach database +   A This method will generate a new log with a size of only more than 500 k the   + or in code: - The following example detaches pubs and then attaches a file in pubs to the current server.  $   $ A. Separation - EXECsp_detach_db@dbname = 'Library name'  -   the B. Deleting a log file -  Wuyi C. Re-attach the EXECsp_attach_single_file_db@dbname = 'Library name',  -   @physname = 'c:\Program Files\Microsoft SQL Server\mssql\data\ library name. mdf'  Wu   - 5in order to automatically shrink in the future, do the following settings: AboutEnterprise Manager--Server--right-click Database-Properties--Options--select "Auto Shrink" $   - --How SQL statements are set: - EXECsp_dboption'Library name','autoshrink','TRUE'  -   A 6If you want to keep the log from growing too much later +Enterprise Manager--Server--right-click Database--Properties--transaction log the --limit file growth to XM (x is the maximum data file size you allow) -   $ --How SQL statements are set up: the Alter DatabaseLibrary name Modifyfile(Name=Logical file name, maxsize= -)

The simple point is:

dump  transaction 数据库名  with no_log backup log 数据库名  with no_log dbcc shrinkdatebase(数据库名)

Resolving SQL Server database tablespace does not automatically release issues