Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

dump transaction options

Posted on 1998-08-03
2
Medium Priority
?
2,918 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 extract information from SQL Server on Database, Connection and Server properties

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