Database shrinking Problems and Solutions

Database statmemberdata now has 40 GB of log files. Use DBCC shrinkdatabase to compress this log. The process is as follows:
1. Restart the data server (via remote control software)
2. Remotely connect to the data server through the SQL Server Query Analyzer
3. Remote Control of Data Server Service suspension
4. In 10 minutes, run the following command in the remote query Analyzer:
DBCC shrinkdatabase (statmemberdata)
The following error occurs in a few minutes:
"Unable to contract log file 2 (statmemberdata_log) because all logical log files are in use.

(The number of affected rows is 2)

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator ."

After restarting the data server, the SQL Server service is immediately suspended and DBCC shrinkdatabase runs after a while, the prompt "all logical log files are in use" has been tried many times.

Solution:

1: Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database
2: delete log files
3: attach the Database Enterprise Manager-> server-> database-> right-click-> attach Database
This method generates a new log with a size of more than 500 kb.
Then set the database to automatically contract
Or use the code:
The following example separates pubs and attaches a file in pubs to the current server.

Exec sp_detach_db @ dbname = 'pubs'
Exec sp_attach_single_file_db @ dbname = 'pubs ',
@ Physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs. MDF'

2: Clear logs
Dump transaction database name with no_log

Again:
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select to contract to xxm in the contract mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

3: If you want to prevent it from increasing in the future
Enterprise Manager-> server-> database-> properties-> transaction log-> limit file growth to 2 MB

-- Compress logs1: truncate transaction log: backup log database name with no_log2: clear the log dump transaction database name with no_log and then: enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select to contract to xxm in the contract mode, here we will provide a minimum number of M allowed to be reduced. Enter this number directly and confirm it. 3: delete log1: detach Database Enterprise Manager-> server-> database-> right-click-> detach database 2: delete log file 3: attach Database Enterprise Manager-> server-> database-> right-click-> attach database this method to generate a new log with a size of more than 500 kb, and then set the database to automatically contract or use code: the following example separates pubs and attaches a file in pubs to the current server. Exec sp_detach_db @ dbname = 'pubs' exec sp_attach_single_file_db @ dbname = 'pubs', @ physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs. MDF'

Use the following statement for databases with two database files:

Exec sp_attach_db @ dbname = 'pubs ',
@ Filename1 = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs. MDF ',
@ Filename2 = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs1_data.ndf'

If the database has two log files, the above method will not work.
Delete one of the log files as follows:
Transaction Log truncation: backup log otsp with no_log clear log dump transaction otsp with no_log
Delete Log File Enterprise Manager-> server-> database-> right-click-> properties-> log file-> Select Delete second log file-> OK

Attach the database according to the log deletion method described above.

4: If you want to prevent it from increasing your Enterprise Manager-server-right-click Database-properties-transaction log-limit file growth to xm (X is the maximum data file size you allow) -- SQL statement setting method: Alter database name Modify file (name = logical file name, maxsize = 20) 5. set to auto contract Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

-- If not, close the process opened by the user and execute the following statement use mastergoif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_killspid] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [p_killspid] gocreate proc p_killspid @ dbname varchar (200) -- Name of the database to shut down the process as declare @ SQL nvarchar (500) Declare @ spid nvarchar (20) declare # TB cursor forselect spid = cast (spid as varchar (20) from master .. sysprocesses where dbid = db_id (@ dbname) open # tbfetch next from # TB into @ spidwhile @ fetch_status = 0 begin exec ('kill' + @ spid) fetch next from # TB into @ spidend close # tbdeallocate # tbgo -- usage exec p_killspid 'statmemberdata' godbcc shrinkdatabase (statmemberdata) godrop proc p_killspid