July 21, 2010 at 5:01 am
Ya I have tried putting the database to SIMPLE mode but no luck.
But as i mentioned it's a just a test database am trying to produce for UAT. So do you guys think if i delete the tranction log after every update by detach and attach the file back will be effect the data records in the mdb file?
July 21, 2010 at 6:03 am
It could result in the database not attaching. It could result in your database being marked inconsistent and unusable. Do not delete the transaction log unless you're willing to throw the entire DB away and restore from backup.
If your DB is in Simple recovery, run a CHECKPOINT after committing each transaction. That will mark the log space as reusable. Just been in simple recovery is not sufficient.
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 21, 2010 at 6:55 am
they are absolutely right, deleting the log makes the whole thing unstable.
Before I knew better, I had situations when I first became an accidental DBA (before i became one on purpose) that we deleted a log file because we didn't understand how to reduce the size. Looking back it was in Full and all we needed was to place it in simple.
But I have also had situations where someone deleted a transaction log and could not re-attach the database.
there is an undocummented command to rebuild the transaction log. I found that out when I had a drive crash on a Prod system I was never told about and the log files and backups where on the same drive.
However it took me a while to find and I have it saved on my home laptop, but do not have the link here at work or I would post it.
I only found 2 instances of it on the web, one was a post on a message board from Paul Randal back in very early 2000's, and the other was a Quest White Paper from around mid 2000.
But needless to say this could lead to very bad things.
If simple model is not shrinking the log file for you then it means that part of the transaction is uncommitted or is still active. after running your batch take a look at the current activity or activity monitor, if it is dev you should have relativly little going on activity wise. take a look and see what is causing the log not to shrink as this may make Simple recovery work for you.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 21, 2010 at 7:08 am
Bradley B (7/21/2010)
If simple model is not shrinking the log file for you then it means that part of the transaction is uncommitted or is still active.
Truncating != shrinking.
The log shouldn't be shrinking. The checkpoint marks space within the log as reusable, it will not shrink the log file. Shrinking the log is not what's needed here, just marking the space within as reusable (truncating) to allow the next batch of updates to reuse that log space.
there is an undocummented command to rebuild the transaction log.
DBCC REBUILD_LOG. Only for SQL 2000. On 2005 it returns
Msg 2526, Level 16, State 3, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
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 21, 2010 at 7:15 am
sorry syntax you are correct. I meant internal shrinking not external, which is truncating. And yes the command was only for SQL 2000, I didn't mention that above but in early 2000 the advice on message boards wasn't about 2005....yet 😀
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 21, 2010 at 7:35 am
Bradley B (7/21/2010)
sorry syntax you are correct. I meant internal shrinking not external, which is truncating.
Truncating doesn't shrink anything, internal or external. All it does is mark any VLFs (virtual log files) that are completely inactive, and if necessary have been backed up, as reusable. Nothing changes size anywhere within the 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
July 21, 2010 at 7:46 am
Yes Agreed.
The transaction log does not shrink, unless speciffically commanded to do so. And then it is external.
It is made of of Virtual Log Files, they never get zero'ed out. It wraps around into one big loop and maintains the same size, the only thing that changes is which log files are marked as active, denoted by a 2 using DBCC SQLPERF(Logspace), inactive or not currently used VLF's are marked with a 0 in the same command. Using the same command you can see the log sequence that was used.
When it grows it gains additional VLF's that have no prior log sequencing that is visibile until they are utilized at least once, and they have not been used yet.
You can have to many VLF's, but I would say go over to Kimberly Trips blog, http://www.sqlskills.com/blogs/kimberly/ and between her and Paul thier advice should leave you good to go.
I would have recommended the growth to be at 4 GB but Paul had posted the article about 4 GB exactally having an error and you need to offset by choosing a number of > 4 GB, but it can be slight but it can't be 4 GB exactally. But his blog can shed more insight on that http://www.sqlskills.com/blogs/paul/.
Gail your blog is also incredibly useful as well, and it is well evidenced by your many articles on SQL Server Central and your posting that you know your stuff inside and out as well, http://sqlinthewild.co.za/.
I hope i didn't leave anything out this time :w00t:
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply