December 25, 2011 at 10:39 am
Hi Everybody,
Here in my Project I am doing data migration from one DB to another, When ever i am moving data with lot of validations(which are in my query). that time i am getting any error and my query is stopped...
The error it is showing that Transaction Log is full...
How can i over come this prob...
excuse me for my bad English.. Please help me Out..
December 25, 2011 at 11:34 am
A christmas present for you............
http://www.sqlservercentral.com/articles/Administration/64582/
---------------------------------------------------------------------
December 25, 2011 at 11:39 am
December 25, 2011 at 11:51 pm
Transaction Log mismanagement could be a reason but I won’t jump on that unless I am confident that ETL code is optimized. Most of the times bad code is the reason for huge transaction logs.
Adjusting Transaction Log would be your temporary solution. If data volume increases you have to adjust it once again next time.
December 28, 2011 at 12:05 am
Expand the log file size and set autogrow on; if your datatabase is production. If not you can put database in simple recovery mode.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 29, 2011 at 1:42 pm
If you are doing bulk inserts and the database that you migrating the data to needs to log the transactions, put the database into Bulked Logged mode. If you do not need to log all of the transactions, put the database in "Simple" Recovery mode. This will prevent the transactionlog from filling up.
December 29, 2011 at 1:56 pm
SQLFreeman-486832 (12/29/2011)
If you do not need to log all of the transactions, put the database in "Simple" Recovery mode. This will prevent the transactionlog from filling up.
All transactions are logged in all recovery models. Switching to simple does not means that SQL won't log certain operations, nor will it prevent the log from filling up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2011 at 7:03 am
Adding to Gila's Comment :
In simple recovery model, Log will be cleared once the checkpoint is fired or Free space falls below 10-30% of the total space ( depends on the db size).
Whereas, in Full recovery model Log database will be cleared only on Log backup.
December 30, 2011 at 7:23 am
chetanr.jain (12/30/2011)
Adding to Gila's Comment :In simple recovery model, Log will be cleared once the checkpoint is fired or Free space falls below 10-30% of the total space ( depends on the db size).
Whereas, in Full recovery model Log database will be cleared only on Log backup.
only if it is able to clear logspace, i.e. no active transactions preventing log truncation.
---------------------------------------------------------------------
December 30, 2011 at 7:26 am
chetanr.jain (12/30/2011)
In simple recovery model, Log will be cleared once the checkpoint is fired
Well, not quite. Checkpoint is what truncates the log (marks space as reusable), but there's no guarantee that it will make any space reusable if there are other things holding the log active
or Free space falls below 10-30% of the total space ( depends on the db size).
Not quite as simple as that...
From Books Online (events that cause a checkpoint)
The log becomes 70 percent full, and the database is in log-truncate mode.
A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:
* A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.
* An ALTER DATABASE statement is executed that adds or deletes a file in the database.
Whereas, in Full recovery model Log database will be cleared only on Log backup.
Again, log backups truncate the log, but if other things are holding the log active, the the log backup may not free any space at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2011 at 7:27 am
george sibbald (12/30/2011)
chetanr.jain (12/30/2011)
Adding to Gila's Comment :In simple recovery model, Log will be cleared once the checkpoint is fired or Free space falls below 10-30% of the total space ( depends on the db size).
Whereas, in Full recovery model Log database will be cleared only on Log backup.
only if it is able to clear logspace, i.e. no active transactions preventing log truncation.
And no mirroring, replication, active backup, snapshot being created or CDC are holding the log active.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2011 at 7:36 am
GilaMonster (12/30/2011)
george sibbald (12/30/2011)
chetanr.jain (12/30/2011)
Adding to Gila's Comment :In simple recovery model, Log will be cleared once the checkpoint is fired or Free space falls below 10-30% of the total space ( depends on the db size).
Whereas, in Full recovery model Log database will be cleared only on Log backup.
only if it is able to clear logspace, i.e. no active transactions preventing log truncation.
And no mirroring, replication, active backup, snapshot being created or CDC are holding the log active.
pah. You're just upset I've learnt how to type faster than you. 🙂
---------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply