SQL Server database shrinking

 

SQL code

  1. Using backup log database with no_log to clear logs and changing the fault recovery model in database properties to "simple" can greatly slow down log growth. If you adjust the Restoration Model to a simple one, it does not support time point restoration, but the log file will be small, if the data is important, we recommend that you change the database Restoration Model to be fully named after backup log database with no_log, the non-active logs will be truncated and the size of the physical log files will not be reduced, however, logical logs are reduced. After the database is reduced, the unactive virtual logs are deleted to release space and data is not damaged. If the log is truncated and the database is shrunk, you cannot use the latest full-database backup for restoration at a time point. We recommend that you back up the database immediately, just in case. 2. The steps for deleting the transaction log file of the primary database are as follows: (1), detach Database Enterprise Manager -- database -- Right-click the database for which you want to delete logs -- all tasks -- detach the database (2), and then delete the log file (3) and then attach the Database Enterprise Manager -- database -- Right-click the database -- all tasks -- attach the database. MDF is enough. 3. Detailed methods for compressing SQL databases and logs: SQL Server 2000 Basic tutorial-compressing a database may often cause excessive free space in the database due to data deletion after it is used for a period of time, in this case, you need to reduce the disk space allocated to database files and transaction log files to avoid wasting disk space. When there is no data in the database, you can modify the database file attribute to directly change the occupied space, but when there is data in the database, this will damage the data in the database, therefore, we need to use compression to reduce database space. You can select the "auto shrink" option in the database attribute options to enable the system to automatically compress the database, or manually compress the database. There are two ways to manually compress a database: 1. Use Enterprise Manager to compress the database. In Enterprise Manager, right-click the database to be compressed and choose "all tasks" from the shortcut menu) select the "shrink database (compressed database)" option, the dialog box shown in 6-10 will appear. You can select the compression mode of the database in the dialog box shown in Figure 6-10, or click "Files" in Figure 6-10 by using the compression plan or compressing a single file, the compressed database file dialog box shown in 6-11 appears. You can perform different compression settings for each database file. Click the "change" button in Figure 6-10. The compress plan editing dialog box shown in Figure 6-12 appears. You can specify the execution method of the compress plan. Click the "change" button in Figure 6-12. The "Edit cycle schedule" dialog box shown in Figure 6-13 appears. You can edit the plan execution cycle or time point. After setting, click "OK" to start compressing the database. After the compression is completed, a compressed information box is displayed. 2. Use the transact-SQL command to compress the database. You can use the DBCC shrinkdatabase and DBCC shrinkfile commands to compress the database. The DBCC shrinkdatabase command compresses the database, and the DBCC shrinkfile command compresses the specified files in the database. (1) DBCC shrinkdatabase command syntax: DBCC shrinkdatabase (database_name [, target_percent] [, {notruncate | truncateonly}]) the parameters are described as follows: · target _ percent indicates the percentage of unused space in the database size after the database is compressed. If the specified percentage is too large and exceeds the proportion of space not used before compression, the database will not be compressed. In addition, the compressed database cannot be smaller than the initial capacity set by the database. · Notruecate retains the remaining space in the database after the database is reduced and does not return it back to the operating system. If this option is not selected, the remaining space is returned to the operating system. · Truncateonly returns the remaining space after the database is reduced to the operating system. When using this command, SQL Server reduces the file to the last file allocation, but does not move any data files. If this option is selected, the target_percent option is invalid. Example 6-14: the unused space of the compressed database mytest is 20% of the database size. DBCC shrinkdatabase (mytest, 20) run the following results: DBCC execution completed. If DBCC printed error messages, contact your system administrator. (2) DBCC shrinkfiledbcc shrinkfile command to compress files in the current database. The syntax is as follows: DBCC shrinkfile ({file_name | file_id} {[, target_size] | [, {emptyfile | notruncate | truncateonly}]}) parameters are described as follows: · file _ ID: The identification number (ID) of the file to be compressed ). The file ID can be obtained through the file_id () function or the sp_helpdb system stored procedure described earlier in this chapter. · Target _ SIZE: Specifies the compressed file size. In MB. If this option is not specified, SQL Server will minimize the file size as much as possible. · Emptyfile indicates that this file is no longer used and all data in this file will be moved to other files in the same file group. After the command with this parameter is executed, the file can be deleted using the alter database command. Other parameters notruncate and truncateonly have the same meaning as those in the DBCC shrinkdatabase command. Example 6-15: compress the database file mydb_data2 in the database mydb to 1 MB. Use mydb DBCC shrinkfile (mydb_data2, 1) methods in the Enterprise Manager: 1. Open the Enterprise Manager 2. Open the database to be processed 3. Click the top menu> Tools> SQL query analyzer, open the SQL query analyzer 4. Enter code: Dump transaction [database name] With no_logbackup log [database name] With no_logdbcc shrinkdatabase ([database name]) in the input window. click the green triangle (or press F5) to perform the query. Wait until the status bar prompts that the processing is complete! Method in the program: compress database logs -- 1. clear log exec ('dump transaction ['+ @ dbname +'] With no_log ') -- 2. transaction Log truncation: exec ('backup log ['+ @ dbname +'] With no_log ') -- 3. shrink database files (if not compressed, the database files will not be reduced exec ('dbcc shrinkdatabase (['+ @ dbname +']) ') 4. Log reduction method: 1. perform the following steps: 1. Dump transaction upload name with no_log2, DBCC shrinkfile (logfilename) 3. Collect shard number 4. Set automatic receiver. 2. Remove the database, delete the log file, and then attach it. OK! Right-click Database -- all tasks -- detach or attach III. 1. backup log names with no_log, 2. DBCC shrinkfile (logfilename ), 3. Set the auto-collect threshold. 1. clear the log dump transaction database name with no_log 2. transaction Log truncation: backup log database name with no_log -- shrink database DBCC shrinkdatabase (Database Name) -- contract specified data file, 1 is the file number, you can query through this statement: select * From sysfiles DBCC shrinkfile (1) SQL2000 method: backup log database name with no_log backup log database name with truncate_only DBCC shrinkdatabase (database name) backup log dB with no_log -- truncation transaction log godbcc shrinkdatabase (N 'db') -- shrinking database godbcc shrinkfile (N 'db', 0, truncateonly) -- shrink the data file godbcc shrinkfile (N 'db _ log', 0, truncateonly) -- contract the log file go -- the contraction fails, possibly because the disk's swap space is too small, you need to increase the remaining disk space.