teach you how to clear SQL log
1. Open Query Analyzer and enter the command dump TRANSACTION database name with NO_LOG2.
Then open Enterprise Manager--right--the database you want to compress--all tasks--shrink the database--Shrink the file--Select the log file--shrink to XXM in the shrink mode.
This gives you a minimum m number that you want to shrink to, just enter the number and make sure.
There are two ways to clear log：
1. Automatic Removal method
Open database option Trunc Log on chkpt, so that the database system automatically clears log every once in a while.
The advantage of this approach is that it is performed automatically by SQL Server without human intervention, and that the log overflow is generally not present; The disadvantage is that only log is cleared and no backup is done.
2. Manual removal method
Execute the command "DUMP transaction" to clear the log.
The following two commands can clear the log: The DUMP TRANSACTION with Truncate_onlydump transaction with no_log usually deletes inactive portions of the transaction log to use DUMP TRANSACTION witht rancate_only command, this command also makes necessary concurrency checks when it is written to the transaction log.
Sybase provides a "dump transaction withno_log" to handle some of the most pressing situations, and using this command is risky, and SQL Server pops up a warning message.
In order to ensure the consistency of the database as much as possible, you should use it as a "last resort".
The above two methods only?? Clear the log instead of a log backup, and if you want to back up the log, you should perform the DUMP TRANSACTION database_name to Dumpdevice command.
PS: A better way to separate the database, after the direct deletion of the log, and then in Query Analyzer with the EXEC sp_attach_single_file_db ' database name ', '. mdf file path ' command to attach the database.
Over. It's good to see somewhere else.
Database log operations first provide a complex method for compressing logs and database files as follows:
1. Empty log dump TRANSACTION library name with NO_LOG
2. Truncate transaction log: BACKUP log database name with NO_LOG
3. Shrink database files (if not compressed, database files will not reduce Enterprise Manager--right key you want to compress the database--All tasks--shrink the database--Shrink the file
--Select Log file--in the shrinkage method to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, you can determine
--Select the data file--in the contraction way to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, OK
You can also use SQL statements to complete
--Shrinking database DBCC SHRINKDATABASE (Customer information)
--Shrink the specified data file, 1 is the file number, you can query through this statement: SELECT * from SYSFILESDBCC shrinkfile (1)
4. To minimize log files (in the case of SQL 7.0, this step can only be done in Query Analyzer) A. Detach Database: Enterprise Manager--server--database--right--detach database B. Delete log files on my Computer C. Append database: Enterprise Manager--server--database--right--Append database This method will generate a new log with a size of more than 500 K or code: The following example separates pubs and attaches a file in pubs to the current server. A. Separate e x e c sp_detach_db @dbname = ' pubs ' B. Delete log file C. Additional e x e c sp_attach_single_file_db @dbname = ' pubs ', @physname = ' c:\Pr Ogram Files\Microsoft SQL Server\mssql\data\pubs.mdf ' 5. In order to be able to shrink automatically later, do the following settings: Enterprise Manager--server-right key database-attributes--Options--select "Auto Shrink"-- SQL statement set by: E X e C sp_dboption ' database name ', ' autoshrink ', ' TRUE ' 6. If you want to keep it up in the future. Enterprise Manager-server-right Key database-attribute-transaction log-limits file growth to XM ( X is the maximum data file size that you allow.--sql statement setting: ALTER DATABASE name modify file (name= logical filename, maxsize=20) Special Note: Please follow the steps, without taking the previous steps, Please do not do the following steps or you may damage your database. Generally do not recommend to do 4th, 62 steps 4th unsafe, it is possible to damage the database or loss of data 6th step if the log reaches the upper limit, subsequent database processing will fail, and the log will not recover until it is cleaned up. In addition to provide a more simple method, I have tried and recommended that everyone use. Simpler way: 1. Right-Build the Database Properties window--The failover model--set to Simple 2. Right-build database All Tasks-Shrink Database 3. Right-Build Database Properties Window--a failover model--set to bulk-logged logging many friends have encountered a problem where the update or DELETE statement forgets the WHERE clause, or if the WHERE clause is not accurate enough, causing serious consequences after execution, which Data recovery can only take advantage of the transaction log backup, so if your SQL does not have a corresponding full-library backup or cannot back up the log (Truncate Log on Checkpoint option is 1), then data recovery is not possible., or recover only the data from the most recent backup. The following is a brief description of the recovery data method: 1 If there is a whole-library backup (or there are multiple differential or incremental backups) before the misoperation, the first thing to do is to make a log backup (if Trunc is not used to make the log file larger). Log on chkpt. Option 1 Then you're dead. Backup log dbname to disk= ' fileName ' 2, restore a whole-library backup, note that you need to use with NORECOVERY, if there are other differences or incremental backups, Restore the RESTORE Database dbname from disk= ' FileName ' with Norecovery3, restore the last log backup that is just a log backup, specify the restore time point to the moment before the error restore log dbname from disk= ' fileName ' and stopat= ' date_time ' These operations can be done in SQL Server Enterprise Manager, not very difficult ... When the log file is full and the SQL database cannot write to the file, there are two methods: one way: Empty the log. 1. Open Query Analyzer and enter the command dump TRANSACTION database name with NO_LOG2. And then open Enterprise Manager--right you want to compress the database--All tasks--shrink the database--Shrink the file--Select the log file--in the shrink option to shrink to XXM, This gives you a minimum m number that you want to shrink to, just enter the number and make sure. Another approach has a certain risk, because SQL Server log files are not immediately written to the database master file, such as improper handling, can result in loss of data. 1: Delete log Detach Database Enterprise Manager-> Server-> Database-> Right key-> Detach database
---------------------
The SQL2005 transaction log full solution opens the Web site today and suddenly finds an error in SQL 2005: The transaction log for database ' mybase_db ' is full. To find out why you cannot reuse space in the log, see the Log_reuse_wait_desc column in sys.databases.
Check the Internet, and finally found a solution:
Copy Code code as follows:
--Back up the database first
--Truncate transaction log
Backuplog Mybase_dbwithno_log
Go
--Shrinking the database
Dbccshrinkdatabase (mybase_db)
Go
OK, it's done.
----------------
--sql2005 Scripts for automatic backup
DECLARE @DBName varchar (200)
Set @DBName = ' reportserver$sql2005 '
--Truncate Log
DUMP TRANSACTION @DBName with no_log
--Shrinking the database
DBCC shrinkdatabase (@DBName, truncateonly)
--Backing up the database
Use master
DECLARE @Version varchar (20)
DECLARE @DateAppend varchar (20)
DECLARE @BasePath varchar (200)
DECLARE @BakPath varchar (200)
--Set the base directory for backup
Set @BasePath = ' f:\tmp '
--set version, each version of the backup put in a different place
Set @Version = ' V6.1 '
--Set the full path of the backup
Set @BakPath = @BasePath + ' + ' + @Version + ' \db.bak '
Use master
--Create a backup device, if present, without establishing
if exists (SELECT * from sysdevices where name= ' Ctos_db_bak ')
Begin
EXEC sp_dropdevice ' Ctos_db_bak '
DECLARE @tmpcmd varchar (100)
Set @tmpcmd = ' del ' + @BakPath
EXEC sp_configure ' show advanced options ', 1
Reconfigure
EXEC sp_configure ' xp_cmdshell ', 1
Reconfigure
EXEC master.. xp_cmdshell @tmpcmd
EXEC sp_configure ' show advanced options ', 1
Reconfigure
EXEC sp_configure ' xp_cmdshell ', 0
Reconfigure
End
EXEC sp_addumpdevice ' disk ', ' Ctos_db_bak ', @BakPath
--Backing up the database
BACKUP DATABASE @DBName to Ctos_db_bak
------------------------------------
It is recommended that you change the transaction log of the database, limit the maximum file growth and regularly back up logs and data. It is best to back up the entire database as a whole before handling the following:
1: The log overflow caused by a small transaction, the system can start normally.
Solution:
To enlarge the database log space:
ALTER DATABASE name on device name = Quantity (M)
Sp_logdevice database name, device name
Clear Log
Dump TRANSACTION database name with NO_LOG (no_truncate)
2: The large things caused by the log overflow, the system can not start normally in a long time or the database can not be restored
Solution:
forcibly empty the log.
In the event that you are unable to recover the database or have a recent backup, you can use the method of forcibly emptying the log. The consequences of adopting this approach are likely to destroy the database completely. The following steps are performed:
Ⅰ start SQL SERVER in-V (do not detect logs)
Ⅱ Modify database state to-32768 (blocking state)
Update sysdatabases set status=-32768 where name= database name
Ⅲ Authorized Sybase_ts_role permissions (sybase_ts_role for SQL Server Special Administrator privileges, not required in day-to-day database management)
Sp_role "Grant", "Sybase_ts_role", SA
Set role "Sybase_ts_role"
Ⅳ Clear Log
DBCC REBUILD_LOG (database name, 1, 1)
After you complete the steps above, restart SQL Server. If the database starts correctly, the database is restored and the database can only be recreated if it cannot be started.
================================================================= Compressed Log
1: Truncate the transaction log:
BACKUP LOG database name with NO_LOG
2: Empty the Log
DUMP TRANSACTION Library name with NO_LOG
Again
Enterprise Manager--right key to the database you want to compress--all tasks--shrink the database--Shrink the file--select log file--in the contraction way to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, OK
3: Delete Log
1: Detach Database Enterprise Manager-> Server-> Database-> Right key-> Detach database
2: Delete log file
3: Additional database Enterprise Manager-> Server-> Database-> Right key-> attached database
This method generates a new log with a size of more than 500 k
And then set this database to automatically shrink
or in code:
The following example separates pubs and attaches a file in pubs to the current server.
Copy Code code as follows:
EXEC sp_detach_db @dbname = ' pubs '
EXEC sp_attach_single_file_db @dbname = ' pubs ',
@physname = ' c:\Program Files\Microsoft SQL Server\mssql\data\pubs.mdf '
4: If you want to not let it grow later
Enterprise Manager--server--right key database--attribute--transaction log--Limit file growth to XM (x is the maximum data file size you allow)
How the--sql statement is set:
ALTER DATABASE name modify file (name= logical filename, maxsize=20)
5. Set to Auto shrink
Enterprise Manager--server--right key database--Properties--options--Choose "Auto Shrink" sql2005 The use of backup log with no_log in the simple recovery model is meaningless?
--First step: Empty log DUMP TRANSACTION [Yzga] with no_log--Step two: Truncate transaction log BACKUP log [Yzga] with no_log--step three: Shrink database DBCC Shrinkdataba SE ([Yzga]) ========================================================== log: BACKUP log with TRUNCATE_ONLY or with NO_ is not recommended LOG. The transaction log should be automatically truncated using the simple recovery model. For more information, see the Help and Support Center in http://go.microsoft.com/fwlink/events.asp. No_log | Truncate_only by discarding all logs except the active log, you can delete the inactive log section and truncate the log without backing up the replication log. This option frees up space. Because log backups are not saved, it is not necessary to specify a backup device. No_log and truncate_only are synonymous. Note: This option will be removed in a future version of SQL Server. You should avoid using this option for new development work and plan to modify the applications that currently use it. When the log is truncated using NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately perform the backup DATABASE to perform a full or full differential backup. Note: Although you can manually truncate the transaction log with this option, we strongly recommend that you do not do so because it disconnects the log chain. You will not be able to provide media failure protection for your database until the next full or full differential backup. Manually truncate the log only in very special cases and immediately create a data backup. Note: If you do not want to make a log backup, set the database to a simple recovery model.
