July 20, 2010 at 7:09 am
Need urgent suggestion, is there a way I can disable logging in SQL Server 2005? as i am just trying to update approx 150 fields in table from another database table with approx 2minlion records.
I have limited space for log file of around 10 gigs and it can not be extended.
For some wierd reason the log file is getting piled up and it's trucating the update stament with error msg.
"The transaction log for database "Test" is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
Please suggest me how can i disable logging transaction log while i am running my update stats.
Thanks much in advance
July 20, 2010 at 7:34 am
Read this article by Paul Randal on logging and recovery
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
then check your database recovery model.
before you execute your statement type in the following command
dbcc sqlperf(logspace)
and check how full your log is.
it is not a far stretch to think that 2.5 million rows would cause 10 GB worth of logging depending on data types involved regardless of recovery model.
add a 2nd transaction log on a different disk with more room, to allow this transaction to occur.
There is a lot of info out there, Paul & Kim over at SQLSkills.com really know their stuff looking over their blogs may yield additional answers.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 8:16 am
Mate..tnx for that link..but i just want to know is there a way i still can run my updates with out logging them on log file?
July 20, 2010 at 9:10 am
Sorry but it is not possible, turning off logging would break the Durability part of the A.C.I.D. properties of a database.
http://support.microsoft.com/kb/59462
Microsoft has not allowed it since 7.0 was rolled out.
I think your best bet would be if you could temporarily create a 2nd log file on a drive that had more room, perhaps you could drop that log file after the update after that portion of the log is no longer in use.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 9:11 am
My Oracle DBA suggested breaking the update into seperate statements by some sort of range value, and dumping/shrinking the transaction log in between updates.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 9:12 am
Can you do the updates in batches? Couple hundred thousand rows at a time with either a log backup (full/bulk logged recovery) or checkpoint (simple recovery) in between to mark the log space as reusable?
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
July 20, 2010 at 9:14 am
Bradley B (7/20/2010)
... and dumping/shrinking the transaction log in between updates.
Dump (backup to mark as reusable) yes, but why shrink? It'll grow again on the next batch and take time and resources to do so.
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
July 20, 2010 at 9:17 am
I only tossed that out becase of the 10 GB limit, you are right though it would grow again in the next batch, so a shrink would not help.
Simple recovery would be the best option for this unless SAL's state there must be point in time recovery.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 20, 2010 at 12:21 pm
Hi
Try to switch to Bulk Logged recovery model and take frequent Transaction Log backups. This would do minimal Logging and truncate the TL.
But this would depend on your criticality of the DB.
With BLogged recovery model you can still do a point in time restore provided the last TL backup didn't have a bulk operation.
Check this link for "How-To"
http://www.mssqltips.com/tutorial.asp?tutorial=5
Thank You,
Best Regards,
SQLBuddy.
July 20, 2010 at 1:54 pm
sqlbuddy123 (7/20/2010)
HiTry to switch to Bulk Logged recovery model and take frequent Transaction Log backups. This would do minimal Logging and truncate the TL.
But this would depend on your criticality of the DB.
With BLogged recovery model you can still do a point in time restore provided the last TL backup didn't have a bulk operation.
Check this link for "How-To"
http://www.mssqltips.com/tutorial.asp?tutorial=5
Thank You,
Best Regards,
SQLBuddy.
This will only work if the update is performed in smaller batches, like Gail pointed out above. If all done in a single transaction - the log entries for the transaction will not be cleared and the log will continue to grow.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 20, 2010 at 2:27 pm
sqlbuddy123 (7/20/2010)
Try to switch to Bulk Logged recovery model and take frequent Transaction Log backups. This would do minimal Logging and truncate the TL.
Normal updates are fully logged regardless of the recovery model. There's no such thing as a minimally logged update. There are a whole lot of limitations and restrictions as to what can be minimally logged under bulk-logged or simple recovery.
From Books Online:
The following operations, which are fully logged under the full recovery model, are minimally logged under bulk-logged recovery model:
* Bulk import operations (bcp, INSERT ... SELECT * FROM OPENROWSET(BULK...), and BULK INSERT).
* text, ntext, and image operations using the WRITETEXT and UPDATETEXT statements when inserting or appending new data. Note that minimal logging is not used when existing values are updated.
* SELECT INTO operations.
* Partial updates to large value data types, using the UPDATE statement's .WRITE clauses when inserting or appending new data. Note that minimal logging is not used when existing values are updated.
* If the database is set to the bulk-logged recovery model, some INDEX DDL operations are minimally logged whether the operation is executed offline or online.
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
July 20, 2010 at 3:39 pm
I was trying to help Virgo out as it was mentioned as urgent.
By the way thanks for the information.
SQLBuddy
July 21, 2010 at 1:23 am
Thank you all for the suggestions.
I was able to do my updates in batch, every time detaching the db and deleteing the log file and attaching the mdf file back ,as it was a test database, which was pretty painfull.
Since i was just trying to update my UAT environment from production database tables it would have been great where we have option to completely disable the transaction log as log is of not that important for test data.
Tnx all n open for few more any good suggestion to do my updates!!!!
July 21, 2010 at 1:40 am
The general rule is to never, ever, ever delete your transaction log. If you ever feel the need to do this, take a deep breath, and don't do it.
I assume that your database is in the FULL recovery model.
If you put your database into the SIMPLE recovery model, then every time you finish a transaction, the log may truncate itself - there's a bunch of rules, but generally it's when the log is 70% full. When this happens, a CHECKPOINT operation runs, which will complete any data page writes to the mdf file, and truncate the transaction log. So, after every batch (assuming you have no other open transactions or other reasons preventing the log from being truncated), the log will be reused. No need to delete it.
Afterwards, put the database back into the FULL recovery model, and take a full backup.
July 21, 2010 at 2:02 am
virgo (7/21/2010)
I was able to do my updates in batch, every time detaching the db and deleteing the log file and attaching the mdf file back ,as it was a test database, which was pretty painfull.
Do not ever delete the transaction log. You're asking for an inconsistent and unusable database. The log is not an optional part of the database and SQL will not always recreate it.
To mark space in the log as reusable, take a log backup (full/bulk-logged recovery) or run a checkpoint (simple recovery)
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply