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

Editor to share with you how to delete or compress the database log in SQL Server 2000 Universe 2005 Compact 2008, I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Since database log growth is set to "unlimited", log files are bound to be very large over a long period of time. A 400g database has 600G LOG files, which takes up a lot of disk space. Since the main purpose is to do OLAP, the database itself will not change much, so the log will not have much effect, so find a way to shrink the database log file to very small or delete it.

After searching for relevant solutions on the Internet, the answers are rich and colorful, but there are not many solutions that really work. Here is a method found on csdn. This method describes the difference between SQL Server 2005 and SQL Server 2008 in shrinking database logs, and it is helpful. At the same time, this method is very efficient, shrinking 600G logs to 10m in less than 30 seconds.

Finally, the code is attached:

Methods for SQL Server 2000

DUMP TRANSACTION [jb51] WITH NO_LOGBACKUP LOG [jb51] WITH NO_LOGDBCC SHRINKDATABASE ([jb51])

Where jb51 is the database name

Methods for SQL Server 2005

Backup Log [jb51] WITH no_logGODUMP TRANSACTION [jb51] WITH no_logGOUSE jb51 DBCC SHRINKFILE (2) GO

Note: because SQL Server 2008 optimizes file and log management, the above statements can be run in SQL2005 but have been cancelled in SQL2008.

User [master] GOALTER DATABASE jb51 SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE jb51 SET RECOVERY SIMPLE-- simple mode GOUSE jb51GODBCC SHRINKFILE (11, TRUNCATEONLY) GOUSE [master] GOALTER DATABASE jb51 SET RECOVERY FULL WITH NO_WAITGOALTER DATABASE jb51 SET RECOVERY FULL-restore to full mode GO above is all the contents of this article entitled "how to delete or compress database logs in SQL Server 2000 and how to compress database logs". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.