bibi92
asked on
Bulk insert and transactional log
Hello,
When I execute this query, the transactional log is full but the recovery model is simple, how can I modify this for reduce logging
Bulk Insert ETL.dbo.I$_ETL_Stats_ufac
From 'd:\test.bcp'
With (
MaxErrors=999999999,
CodePage='ACP',
FieldTerminator = '~~',
RowTerminator = '\n'
)
Thanks
Regards
bibi
When I execute this query, the transactional log is full but the recovery model is simple, how can I modify this for reduce logging
Bulk Insert ETL.dbo.I$_ETL_Stats_ufac
From 'd:\test.bcp'
With (
MaxErrors=999999999,
CodePage='ACP',
FieldTerminator = '~~',
RowTerminator = '\n'
)
Thanks
Regards
bibi
It means your Transaction Log is too small for the Bulk Insert and it needs to be increased appropriately.
ASKER
Hello, I have always check it, I want to know if it's possible to reduce the logging.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks regards bibi
>>you can reduce logging if database is in BULK_LOGGING mode <<
If by "BULK_LOGGING mode" you are referring to the Bulk-logged Recovery Model and you are saying that it will log less than Simple Revocery Model, than clearly that is incorrect and you have not done your homework, as I suspect the author will very soon discover.
This is from SQL Server BOL:
Simple Recovery Model
The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. The simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data. Therefore, under the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead.
Generally, for a user database, the simple recovery model is useful for test and development databases or for databases containing mostly read-only data, such as a data warehouse. The simple recovery model is inappropriate for production systems where loss of recent changes is unacceptable. In such cases, we recommend using the full recovery model.
Bulk-logged recovery model
This recovery model bulk logs most bulk operations. It is intended solely as an adjunct to the full recovery model. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up. The tradeoffs are bigger log backups and increased work-loss exposure because the bulk-logged recovery model does not support point-in-time recovery.
And here is the kicker you failed to explain to the author (also from BOL):
Important:
Under the full recovery and bulk-logged recovery models, log backups are essential. If you do not want to take log backups, use the simple recovery model.
If by "BULK_LOGGING mode" you are referring to the Bulk-logged Recovery Model and you are saying that it will log less than Simple Revocery Model, than clearly that is incorrect and you have not done your homework, as I suspect the author will very soon discover.
This is from SQL Server BOL:
Simple Recovery Model
The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. The simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data. Therefore, under the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead.
Generally, for a user database, the simple recovery model is useful for test and development databases or for databases containing mostly read-only data, such as a data warehouse. The simple recovery model is inappropriate for production systems where loss of recent changes is unacceptable. In such cases, we recommend using the full recovery model.
Bulk-logged recovery model
This recovery model bulk logs most bulk operations. It is intended solely as an adjunct to the full recovery model. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up. The tradeoffs are bigger log backups and increased work-loss exposure because the bulk-logged recovery model does not support point-in-time recovery.
And here is the kicker you failed to explain to the author (also from BOL):
Important:
Under the full recovery and bulk-logged recovery models, log backups are essential. If you do not want to take log backups, use the simple recovery model.
And here is more information that was not shared from SQL Server BOL:
If you must switch from the simple recovery model to the full recovery model, we recommend that you:
1. Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
2. Schedule regular log backups and update your restore plan accordingly.
Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
If you must switch from the simple recovery model to the full recovery model, we recommend that you:
1. Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
2. Schedule regular log backups and update your restore plan accordingly.
Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
Check if you have set a maximum size on the transaction log or if you are actually running out of disk space