Microsoft Engineers do not specify some statements to log to the log in SQL Server. Problem reply.
my question:
You can specify some statements to log in the SQL Server.
For example, when I cleaned a log list, because I didn't delete it, I just deleted a certain time period. Such as SQL below
DELETE TABLE1 WHERE TIME <2004-03-01 '
Due to the large deletion, the log is recorded, after deleting, generating 3,4G logs is normal. After deleting, you have to clean up the log, very trouble, especially when the hard disk space is not very big, it is more depressed, you have to delete a little bit.
When deleting, the log of setting the database is the smallest, which is not feasible, because my normal log wants to still record. I don't want to clean up, affecting my other execution logging.
Microsoft engineer's answer:
Depending on your description, you want to let SQL Server record logs when you delete certain records in the delect Table table. And do not want to set the log of the database is the smallest (because you want other normal logs to still record).
==============================================
Based on my understanding, this is basically unable to implement. The SQL Server engine is designed to log your DELETE operation. There is no way to enforce some statements to log in the log. For this point, you can see the "The SQL Server Engine" topic next to the "SQL Server Architecture" section in the "SQL Server Architecture" section of the "SQL Server Architecture" section. Among them, various SQL Manager includes:
The Row Operations Manager and The Index Managerthe Page Manager and The Text Managerthe Transaction Manager and Lock Manager
The most important of these is Transaction Manager, Page Manager, and Row Operations Manager. In general, both SQL Server performs logging according to different levels of recovery mode for any data. I believe you know this.
==============================================
However, SQL Server does not log logging on the following operations:
-------------------------------------------------- ------------------------1. Truncate Table operation
2. SELECT INTO, BCP (BULK INSERT / BULK UPDATE), CREATE INDEX, and the TEXT / Image Data Type, and the like. 3. If you use Full Recovery Mode, all operations will be recorded (also including database backups, etc.)
==============================================
Based on the above principles, as well as the requirements of your deletion. Although I don't necessarily fully meet your requirements, according to my experience, I will give you the following suggestions:
1. If you are executing DELETE TABLE1 WHERE TIME <'2004-03-01' Due to more records involved, logging is also large (3-4g), if it is feasible, I suggest you try the following way:
- Select the record to the new table you need to keep. If you use Full Recovery Mode - according to the number of SELECT INTO, the log may be a big select * inTo table2 from table1 where time> = '2004-03-01'
- Then Truncate Table1 directly. Whether it is recovery mode without logging TRUNCATE TABLE TABLE1
- Finally, the table2 is renamed Table1ec sp_rename 'table2', 'table1'
2. Since your disk space is limited, you need to select the appropriate recovery mode, and see this section below for Auto_Shrink and the Recovery Options section in this document:
Setting database options: http://msdn.microsoft.com/library/default.asp? URL = / library / en-us / createdb / cm_8_des_03_6ohf.asp
3. About the selection of database backup policies and recovery modes, the following technical documentation provides a good example:
Backup and recovery of related databases: http://msdn.microsoft.com/library/default.asp? URL = / library / en-us / admin ql / ad_bkprst_9ttf.asp
==============================================
In summary, SQL Server will make log records for Detle Table, and we can't force this log record. Because SQL Server Data Storage and Logging Engine Design To better maintain data integrity and reproduce the database crash or disaster, you can recover some of your transactions. So in principle, we have no way to use a specific statement to not log this Delete action.
In addition, I published my own opinion on your question and provided you with some suggestions. I don't know if I can help you on your side. If I suggest the first case, the amount of data involved is not very large (if the recovery mode is not Full, it is better), then you can use the SQL Server to do TRUNCATE operation without logging, try this method. But there is a little need to remind you that Truncate Table will not log record, so once Truncate, you will not be able to recover this table. So before, you need to confirm this! ==============================================
I hope that I will answer your questions in detail, and I hope that the recommendations I offer will help you solve the problem. If you have any questions or need further help, please post directly in the news group, I am very happy to continue to help you!
Billy Yao [Microsoft]
Microsoft Global Technology Center Microsoft Dynamics Partner Technical Support
