Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to shrink the database log in SQL SERVER, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Microsoft OLE DB Provider for SQL Server error '80040e14'

The log for database 'mis1' is full. Please back up the transaction log of the database to free up some log space.

The log was shrunk using a simple method:

1 、 BACKUP LOG.... WITH NO_LOG

2. Shrink the database

OK, synchronization is back in progress.

Answer:

Can you check whether the log mode of the database is full? The log generated by this mode is huge, just change it to a simple mode, and then set the database to shrink automatically.

Two methods of clearing SQL SERVER logs

In the process of using, we often encounter the situation that the database log is very large. Here we introduce two processing methods.

Method one

In general, the shrinking of the SQL database can not greatly reduce the size of the database. Its main function is to reduce the size of the log. This operation should be done regularly to prevent the database log from being too large.

1. Set the database mode to simple mode: open SQL Enterprise Manager and click Microsoft SQL Server-- > SQL Server group in the console root directory-- > double-click to open your server-- > double-click to open the database directory-- > Select your database name (such as Forum)-- > then right-click to select Properties-- > Select options-- > Select "simple" in the failure recovery mode. Then press OK to save.

2, right-click on the current database, look at all the tasks in the shrinking database, generally inside the default settings do not need to be adjusted, directly click to determine.

3. After shrinking the database, it is recommended to reset your database properties to standard mode. The operation method is the same as the first point, because logs are often an important basis for restoring the database in some abnormal cases.

SQLSERVER says:

The BACKUP LOG database name WITH NO_LOG | TRUNCATE_ONLY can truncate the transaction log.

However, after I did the above in the database, the transaction log remained unchanged.

The first step is to truncate the inactive transaction log without shrinking the database, and only after the second operation does the database clean up the transaction log and delete the truncated inactive transactions. and shrink the transaction log file to the appropriate size.

When using SQL Server, the log file in the database becomes larger and larger and needs to be deleted. I first separate the database, right-click on the database-> all tasks-> detach the database, after separation, you can delete the Log file, at the same time, you can also back up the data. After that, you can add that database to the additional database in all tasks. SQL Server will prompt you that the LOG file does not exist, ask if you want to create a new one, and choose yes, so that the LOG file will be empty. This method only takes a few minutes, but in these minutes, you can't access the database. I think it's only suitable for small applications, but we have to find other ways for large systems.

-compress log and database file size

/ *-pay special attention

Please follow the steps, do not do the previous steps, please do not do the following steps

Otherwise, it may damage your database.

-- * /

1. Clear the log

DUMP TRANSACTION library name WITH NO_LOG

two。 Truncate the transaction log:

BACKUP LOG database name WITH NO_LOG

3. Shrink the database file (if it is not compressed, the database file will not be reduced

Enterprise Manager-right-click the database you want to compress-all tasks-shrink database-shrink files

-- Select the log file-- choose to shrink to XXM in the contraction mode. Here, you will give a minimum number of M allowed to shrink to. Enter this number directly and confirm it.

-- Select the data file-- choose to shrink to XXM in the contraction mode. Here, you will give a minimum M number that is allowed to shrink. Enter this number directly and confirm it.

You can also use SQL statements to do this.

-- shrink the database

DBCC SHRINKDATABASE (customer profile)

-- shrink the specified data file. 1 is the file number. You can query it through this statement: select * from sysfiles

DBCC SHRINKFILE (1)

4. To maximize the reduction of log files (in the case of sql 7. 0, this can only be done in the query Analyzer)

a. Detach the database:

Enterprise manager-- server-- database-- right-- detach database

b. Delete the LOG file from my computer

c. Attach database:

Enterprise Manager-Server-Database-right-attach Database

This method will generate a new LOG with a size of only more than 500K.

Or use the code:

The following example detaches the pubs and then attaches a file in pubs to the current server.

a. Separate

EXEC sp_detach_db @ dbname = 'pubs'

b. Delete log files

c. Re-attach

EXEC sp_attach_single_file_db @ dbname = 'pubs'

@ physname ='c:\ Program Files\ Microsoft SQL Server\ MSSQL\ Data\ pubs.mdf'

5. In order to automatically contract later, make the following settings:

Enterprise Manager-Server-right database-Properties-options-Select "Auto shrink"

-- SQL statement setting method:

EXEC sp_dboption 'database name', 'autoshrink',' TRUE'

6. If you want to keep its log from growing too big in the future,

Enterprise Manager-Server-right Database-Properties-transaction Log

-- limit file growth to xM (x is the maximum big data file size you allow)

-- how to set the SQL statement:

Alter database database name modify file (name= logical file name, maxsize=20)

After reading the above, do you have any further understanding of how to shrink database logs in SQL SERVER? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.