Solved

dump transaction options

Posted on 1998-08-03
2
2,902 Views
Last Modified: 2008-02-01
the no_log option does exactly what truncate_only option does except that the operation of dumping the transaction itself is not logged. what is the use of logging that operation, can it be rolled back, and how much space it would take anyway?? where is the information logged? the dumptrdate column in the sysdatabases table changes irrespective of the options provided with the dump transaction command.
0
Comment
Question by:sajalk
2 Comments
 
LVL 2

Accepted Solution

by:
jbiswas earned 50 total points
ID: 1089362
All dump operations are written to the transaction log. The reason you cannot use the dump tran with truncate_only when the log is completely full is because that operation gets written to the log before it is actually truncated. However you don't have that space in the log. It is only in these situations that you should use the dump tran with no_log. No this cannot be rolled back but the tran log keeps info on all operations done, that affect the database. The dumptrdate changes on executing any kinda dump.

Use dump transaction with truncate_only to truncate the log when you are not concerned with the recovery of recent transactions (for example, in an early development environment)


Use dump transaction with no_log to truncate the log without recording the event  only when your usual method of dumping the transaction log (either the standard dump transaction command or  dump transaction with truncate_only) fails because of insufficient log space.

In production databases you should immediately dump the database after using either of these options.The dump transaction with no_log command frees very little space in the transaction log. If you continue to load data after entering dump transaction with no_log, it is possible to fill the log completely, causing any further dump transaction commands to fail. Use the alter database command to allocate additional space to the database.



All occurrences of dump tran with no_log are reported in the SQL Server error log. The message includes the user ID of the user executing the command. Messages indicating success or failure are also sent to the error log. no_log is the only dump option that generates error log messages.
0
 

Author Comment

by:sajalk
ID: 1089363
however, i still do not understand the purpose of using truncate _only option. especially, because the action is written to the transaction log which i have no way of seeing, whereas, with no_log, at least the nt error log would have the entry of the action. also, no_log gives you a little more room.
i would appreciate if care to reply.

thanx much.
sajal.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now