Log cleanup methods under SQL Server 2008 2

Log cleanup methods under SQL Server 2008 (2011-07-14 10:30:45) Transfer from http://blog.sina.com.cn/s/blog_4bdd3d0b0100wfvq.html

Tags: it

Every time the database log to see the explosion of some big head, so random search a way to get rid of the log, the next big head, and search for half a day, so or write a blog, do not look for a mess.

1. Set the database to Simple mode

Select the database you want to shrink, right-click Properties---options, select Simple Mode

2. Select tasks, shrink-file

3. Select Log

Or use the following statement

Code use[Master]
GO
AlterDATABASEDnnameSETRECOVERYSimpleWithNo_wait
GO
AlterDATABASEDnnameSETRECOVERYSimple
GO
UseDnname
GO
Dbcc shrinkfile  (N ' LogFileName ') Span class= "Apple-converted-space" > , 0,truncateonly)
GO
Use [master]
GO
Alter  Database dnname SET recovery full with no_wait
GO
ALTER database dnname  set recovery full
GO

SQL Server 2000 to clean up the log using the DUMP statement, but in SQL Server2008 , this statement is useless, if the log file is large, such as more than 1G, You need to truncate the log file to speed up SQL Server, and in SQL Server2008, change the method to:

There is a database xxdb under SQL Server 2008, the log file has exceeded 1G.

The previous use of the BACKUP statement-backup log xxdb with no_log has failed, and after reviewing MSDN, it was found that the standard truncation log statement provided by MS has changed to "BACKUP log statement does not specify with Copy_only"

Use the statement backup log xxdb to disk= ' X:\work\1.bak ' successfully back up the log file and truncate the log file. Here x: Represents the drive letter of the file you want to back up.

Then use DBCC shrinkfile (xxdb_log,10) to shrink the log file to 10M

Summarize:

Full-T-SQL statement that shrinks the log file:

BACKUP LOG xxdb to disk= ' X:\work\1.bak '
DBCC Shrinkfile (xxdb_log,10)
GO

--

BACKUP LOG <db_name> to disk=< ' BackupFileName ' >
DBCC Shrinkfile (<log_filename>,10)
GO

The inside of the x:\work is randomly set. Of course, it can be shrunk to 5M or 1 m, for example.

database data is invaluable, and one of the main tasks as an administrator is to back up the database on a schedule to find a balance between performance and data security. In other words, the purpose of the backup is to guard against possible hardware failures, natural disasters, or data being illegally tampered with. Backups can be divided into full backups, differential backups, and transaction log backups. A full backup backs up the entire database, containing the database files, the address of the files, and the log sequence number recorded in the transaction log from the start of the backup to the log sequence number at the end of the backup. A full backup is the basis for other backups, and no full backup will be able to make differential backups and transaction log backups. Step: Open SQL Server Management Studio, expand the Database folder under the server folderRight-click the database you want to back up, select the "Properties" option to change the "recovery mode" on the "Options" page to "full", which is primarily to preserve the log and prepare for the next thing log backup. Right-click the database that needs to be backed up, select Tasks, Backup, and in the Backup Database dialog box, select Backup type is full. Select a backup device select Overwrite all existing backup sets on the Options page to initialize a new device or overwrite an existing device suggest selecting "Verify backup after completion" to check the actual database and backup copy to ensure consistency Click OK to start the backup backup is complete, you can view it, openunder SQL Server Management Studio , under Object Explorer, expand Backup devices under Server objects, right-click the device Select the Properties option, and see our backup on the Media content pageDifferential backups record all the changes that have occurred to the database since the last full backup, the difference from a thing log backup is that the differential backup always starts with the previous full backup, and the thing log backup is the starting point for the above backup. When you perform a differential backup SQL Server reads the last log sequence number of the previous full backup and identifies the page that changed since the last full backup and backs up the entire extents (Extent) of the page where the change occurred. Step: Open SQL Server Management Studio, expand the Database folder under the server folderRight-click the database that needs to be backed up, select Tasks, Backup, and in the Backup Database dialog box, select Backup type is diff. Select a backup device select Append to existing backup set on the Options page, do not overwrite the existing full backup, and do not select Overwrite all existing backup sets to avoid overwriting existing full backups. It is recommended to select "Verify backup after completion" to check the actual database and backup copy to ensure consistency click "OK" to start the backup the last is a thing log backup, the thing log backup relies on a full backup but does not back up the database itself, only back up the things that have changed since the last thing log backup in the thing log. About the things log a little more, when a database uses a full or bulk log recovery model, a thing log backup is the only way to clear the old thing log from the things log, and when the database uses the simple recovery model, full and differential backups can clear the thing log. Step: Open SQL Server Management Studio, expand the Database folder under the server folderRight-click the database that needs to be backed up, select Tasks, Backup, and in the Backup Database dialog box, select the backup type as the thing log. Select a backup device select Append to existing backup set on the Options page, do not overwrite the existing full backup, and do not select Overwrite all existing backup sets to avoid overwriting existing full backups. It is recommended to select "Verify backup after completion" to check the actual database and backup copy to ensure consistency click "OK" to start the backup the most common backups are the above three, and in addition, there is a choice in a large database: filegroup backup, which backs up only a small portion of the database at a time, and does not discuss filegroup backups in this article. Backup strategy is designed to compare several backup methods, in a word to describe the existence is reasonable, each backup method has its advantages and disadvantages and the scope of adaptation, according to the actual work to consider the pure full backup advantage is the recovery process is the fastest in all policies, relative, its backup process is the slowest of all strategies, in addition, A pure full backup cannot purge the things log (using a full or bulk recovery model with a point-in-time recovery feature), which can be used as a supplementthe truncate_only clause executes a thing log backup, emptying only and not backing up the thing log. Complete & Differential backup The advantage is that the recovery process is faster in all policies, and its backup process is not the slowest in all policies, in addition, complete & Differential backups also fail to clear the things log (using the full or bulk recovery model and the point-in-time recovery feature), you can use the truncate_only clause to perform a thing log backup, emptying only without backing up the thing log. Complete & Things log backup The advantage is that the backup process is faster than full & differential backups But its recovery is the most troublesome, complete & Thing log backups can clear the things log, is essential for any database backup strategy.

Log cleanup under SQL Server 2008 Method 2