Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

dump transaction options

Posted on 1998-08-03
2
Medium Priority
?
2,923 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 200 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

824 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