Write SQL logs and clear logs

Create Database hufang
On Primary -- by default, it belongs to the primary main file group, which can be omitted
(
/* -- Specific description of the data file --*/
Name = 'hufang _ data', -- physical name of the primary database
Filename = 'f: \ project \ hufang_data.mdf ', -- physical name of the primary database
Size = 5 MB, -- initial size of the primary database
Maxsize = 100 MB, -- maximum file growth for the master database
Filegrowth = 15%
)

Log On
(
/* -- The specific description of the log file is the same as above --*/
Name = 'hufang _ log ',
Filename = 'f: \ project \ hufang_data.ldf ',
Size = 2 MB,
Maxsize = 10 MB,
Filegrowth = 15%

)

Go -- separate from subsequent SQL statements

========================================================== ==================================

Clear and delete logs

Method 1:
Step 1:
Backup log database_name with no_log
Or backup log database_name with truncate_only -- no_log and truncate_only are synonymous here. You can execute either of them.
Step 2:
1. compress all data and log files of a specific database and execute DBCC shrinkdatabase (database_name, [, target_percent]) -- database_name is the name of the database to be compress; target_percent indicates the percentage of available space in the database file after the database is shrunk.
2. compress data or log files in a specific database at a time, and run DBCC shrinkfile (file_id, [, target_size]) -- file_id is the ID of the file to be compress, to obtain the file ID, use the file_id function or search for sysfiles in the current database. target_size indicates the size of the desired file in megabytes (expressed in integers ). If not specified, DBCC shrinkfile reduces the file size to the default file size.

Either DBCC can contain the notruncate or truncateonly parameter. For more information, see help.

Method 2
(This method is generally successful in the sqlserver2000 environment, but not necessarily in sqlserver7 or below ):
Step 1:
Back up the entire database for emergency purposes.
Step 2:
After the backup is complete, run the following statement in query Analyzer:
Exec sp_detach_db yourdbname, true -- unload the registration information of this dB in MSSQL
Step 3:
Delete the log file or remove the log file from the directory where the log physical file is located.
Step 4:
Execute the following statement in query Analyzer:
Exec sp_attach_single_file_db yourdbname, 'd: \ MSSQL7 \ data \ yourdbname_data.mdf'
-- Register the database as a single file. If yes, MSSQL automatically generates a K log file for the database.

The preceding methods are valid in log clearing.
However, can I prevent SQL Server from generating log logs? The above methods seem to be invalid.
I have a case here:
My client's SQL Server generates 4-MB log every day and clears it every day, which is inconvenient. Is there a way to avoid log generation?

I analyzed the cause of the customer's log generation and tested it accordingly.
The customer clears the database every day and imports data from the total system to SQL Server. I feel that sqlserver does not generate much log during insertion, and the log is generated greatly when the entire database is deleted.
For example:
Select * into test_2 from B _bgxx
A total of 45000 records generate dozens of M logs. If
Delete from test_2
More than 80 m logs are generated, which is obviously a problem.

Although it can be changed:
Truncate table test_2
However, I still hope to find a method that does not generate logs. Just like Oracle does not generate an archive

========================================================== ======================================

Transaction log file is a file used to record database updates with the extension LDF.
In SQL Server 7.0 and SQL Server 2000, if the automatic growth function is set, the transaction log file is automatically expanded.
Generally, when the maximum number of transactions between two transaction log truncation occurs, the transaction log size is stable. Transaction Log truncation is triggered by the checkpoint or transaction log backup.
However, in some cases, the transaction log may become very large, resulting in exhausted space or full. Generally, when the transaction log file occupies sufficient disk space and cannot be expanded, you will receive the following error message:
Error: 9002, severity: 17, state: 2
The log file for database '%. * ls' is full.
In addition to this error message, SQL Server may mark the database as suspect due to the lack of transaction log extended space. For more information about how to recover from this situation, see the "insufficient disk space" topic in SQL Server online help.

In addition, transaction log extensions may cause the following situations:
· Very large transaction log files.
· Transactions may fail and may start to roll back.
· Transactions may take a long time to complete.
· Performance problems may occur.
· Blocking may occur.

Cause
Transaction Log extensions may occur for the following reasons or situations:
· Uncommitted transactions
· Very large transactions
· Operation: DBCC dbreindex and create Index
· During restoration from transaction log backup
· Client applications do not process all results
· Query times out before the transaction log is extended. you receive a false "log full" error message.
· Unreplicated transactions

Solution
When the log file is full and the SQL database cannot write the file, you can use either of the following methods:
One method: Clear logs.
1. Open the query analyzer and enter the command
Dump transaction database name with no_log
2. open the Enterprise Manager and right-click the database you want to compress -- all tasks -- shrink database -- shrink file -- Select log file -- select to shrink to xxm In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Another method is risky because the log files of SQL Server are not immediately written to the master database file. improper processing may cause data loss.
1: Delete log
Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database
2: delete log files
Attach Database Enterprise Manager-> server-> database-> right-click-> attach Database
This method generates a new log with a size of more than 500 K.

Note: The first method is recommended.

If you do not want it to become larger later.
Use in SQL2000:
Right-click the database and choose Properties> Options> fault recovery> model> select simple model.
Or use an SQL statement:
Alter database name set recovery simple

 
In addition, for example, the database attribute has two options, which are related to the growth of transaction logs:
Truncate log on checkpoint
(This option is used for sql7.0. in SQL 2000, the fault recovery model is selected as a simple model)
When the checkpoint command is executed, if the transaction log file exceeds 70% of its size, the content will be cleared. This option is often set to true in the development database.
Auto shrink
Regularly checks the database. When the unused space of database files or log files exceeds 25% of its size, the system will automatically reduce the file so that the unused space is equal to 25%. When the file size does not exceed the initial size when it is created, the file size after the file is not reduced must be greater than or equal to its initial size for the transaction the log file can be reduced only when it is backed up or the truncate log on checkpoint option is set to true.

Note: Generally, the default attribute of the database established is set, but the database attribute is changed in case of exceptions. please clear the log and check the preceding attributes of the database to prevent transaction logs from being full again.

Bytes ----------------------------------------------------------------------------------------------

The templog. LDF file in SQL Server is already 10 Gb. How can I safely delete it? Reward score: 5-resolution time: Today, the system prompts that disk D is not enough space. In the SQL server data directory, templog. LDF files occupy nearly 10 Gb of space. I want to know how to safely delete it. or clear it.Question added:I found a command online. Backup log database name with no_log Deleted, hoping there will be no adverse consequences. Haha. Questioner: splaybow-trial level 1 Best Answer We recommend that you change the transaction logs of the database to limit the maximum file growth and regularly back up logs and data. Before performing the following operations, we recommend that you back up the entire database: 1: logs overflow caused by small transactions. The system can start normally. Solution: Expand the Database Log space: Alter database name on device name = Quantity (MB) Sp_logdevice Database Name, device name Clear logs Dump transaction database name with no_log (no_truncate) 2: log overflow caused by large transactions. The system cannot be started normally or the database cannot be recovered for a long time. Solution: Forcibly clear logs. If you cannot recover the database or have a recent backup, you can forcibly clear the logs. The consequences of using this method may completely damage the database. Perform the following steps: I. Start SQL Server in-V mode (logs are not detected) Ⅱ modify the database status to-32768 (blocking) Update sysdatabases set status =-32768 where name = Database Name Iii. Authorize sybase_ts_role (sybase_ts_role is the special administrator permission of SQL Server. This role is not required in daily database management) Sp_role "grant", "sybase_ts_role", SA Set role "sybase_ts_role" IV clear logs DBCC rebuild_log (Database Name, 1, 1) After completing the preceding steps, restart SQL Server. If the database can be started normally, the database will be restored. If the database cannot be started, you can only create a new database. ========================================================== ====================================== Compressed logs 1: truncate transaction logs: Backup log database name with no_log 2: Clear logs Dump transaction database name with no_log Again: Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select to contract to xxm in the contract mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK. 3: delete log 1: Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database 2: delete log files 3: attach the Database Enterprise Manager-> server-> database-> right-click-> attach Database This method generates a new log with a size of more than 500 kb. Then set the database to automatically contract Or use the code: The following example separates pubs and attaches a file in pubs to the current server. 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 prevent it from increasing in the future Enterprise Manager -- server -- Right-click Database -- properties -- transaction log -- limit file growth to xm (X is the maximum data file size you allow) -- SQL statement settings: Alter database name Modify file (name = logical file name, maxsize = 20) 5. Set to auto contract Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

Bytes -------------------------------------------------------------------------------------------------------

Recover full backup, differential backup, and transaction log backup

The questions are as follows:
A database administrator designs a backup policy for the database: Make a full backup of the database at every Monday; make a differential backup for the database at every day from Tuesday to Sunday; A transaction log backup is performed on the database every day at an interval of 30 minutes (the start time is and the end time is). If the database crashes at on a certain Wednesday, please write down the steps for restoring the database.

The answer is as follows:
-- 1. Restore the last full backup
Restore database dbtest from fullbackup with norecovery
-- 2. Restore the last differential backup
Restore database dbtest from diffbackup with norecovery
-- 3. Resume transaction logs from the last differential backup
Restore log dbtest from logbackup with norecovery, file = 1 -- if all logs use the logbackup Device
Restore log dbtest from logbackup with norecovery, file = 2
Restore log dbtest from logbackup with norecovery, file = 3
....
Restore log dbtest from logbackup with file = n -- the last one cannot be added with norecovery.

Http://topic.csdn.net/u/20100702/20/534b9600-bc88-45ba-85c3-58b6608210ff.html? Seed = 1849987175 & R = 66686874 # r_66686874