Database log files are too large to handle

Source: Internet
Author: User

A situation today, a database log file is too large, resulting in the use of excessive server hard disk space. The log files for the database need to be thin. Check the information on the Internet and share several links.

Because SQL2008 is optimized for file and log management, it can be run partially in SQL2005 and has been canceled in SQL2008.

such as: DUMP TRANSACTION library name with NO_LOG

Reference articles

http://jimshu.blog.51cto.com/3171847/932669

Http://www.cnblogs.com/TLLi/archive/2012/07/15/2592042.html

Http://www.cnblogs.com/tylerdonet/p/3550434.html

Http://www.cnblogs.com/qingyuan/p/4238071.html

Http://www.cnblogs.com/Joe-T/archive/2011/12/22/2298051.html

Self summary

1. Shrink data (one is interface operation, one is execute SQL statement, individual tends to execute SQL statement)

Interface operation

First

--Modify data to Simple type

ALTER database name SET RECOVERY Simple

GO

Second

Select the database that you want to shrink, right-to-the-task, and shrink-to-file

Third

ALTER database name SET RECOVERY full
GO

SQL statement Execution

--Check the log file name
Use database name
SELECT NAME, size from sys.database_files

General _log is the log file, record

Final execution statement

Database name Database name SET RECOVERY Simple   database name godbcc shrinkfile (N' database log name  '2  Database name Database name SET RECOVERY full   GO

The following are common statements

--Check the log file name
Use database name
SELECT NAME, size from sys.database_files

--View the RECOVERY_MODEL_DESC type of the database
SELECT NAME, Recovery_model_desc from sys.databases

--If it is full type, modify to Simple Type
ALTER DATABASE chinaknowledgedb SET Recovery Simple

--Shrink log file size (in units of M)
DBCC shrinkfile (N ' Chinaknowledgedb_log ', 10)

--Revert to full type
ALTER DATABASE chinaknowledgedb SET Recovery full

Summary: Shrinking logs Make sure to turn data into a simple mode

Database log files are too large to handle

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.