October 18, 2010 at 1:16 pm
Why, when I have a database set to Simple Recovery, do I still see the .ldf grow? The development team set a database to Simple Recovery on Friday last week, as they found no need for a point in time recovery. They then proceeded to shrink the .ldf and release the unused space which took the .ldf down to an incredibly small 2MB (which is what they had it set to do in the DBCC ShrinkFile request).
Now, today, after a ton of modifications in the database and I am certain a few bulk inserts, the .ldf is around 12GB and growing - why is that?
Thanks
C
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
October 18, 2010 at 1:21 pm
chris.s.powell (10/18/2010)
Why, when I have a database set to Simple Recovery, do I still see the .ldf grow? The development team set a database to Simple Recovery on Friday last week, as they found no need for a point in time recovery. They then proceeded to shrink the .ldf and release the unused space which took the .ldf down to an incredibly small 2MB (which is what they had it set to do in the DBCC ShrinkFile request).Now, today, after a ton of modifications in the database and I am certain a few bulk inserts, the .ldf is around 12GB and growing - why is that?
Because in simple recovery, like in all other recovery models, all operations are logged. The log can only be marked as reusable to the beginning of the oldest active transaction.
Either you have long running transactions or large data modifications in a single transaction. Or maybe replication.
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
October 18, 2010 at 1:24 pm
Thank you Gail -
Will the .ldf return to the initial size of 2MB after all transaction have completed?
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
October 18, 2010 at 1:26 pm
To add to what Gail said, yes, your T-Log can still grow. Simply you always need enough space for your largest transactions to occur, in whatever restore method you decide on. You just don't need to back them up to get the space re-usable when they've completed in Simple mode. It can be a lot more complex than that but start there. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2010 at 1:26 pm
chris.s.powell (10/18/2010)
Will the .ldf return to the initial size of 2MB after all transaction have completed?
Nope, and you won't want it to. You'll need that space the next time you do it and it will slow things down if it has to regrow.
EDIT: See below, Gail's much more descriptive. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2010 at 1:29 pm
chris.s.powell (10/18/2010)
Will the .ldf return to the initial size of 2MB after all transaction have completed?
No and honestly that's the last thing that you want to happen. Growing the log is an expensive and time-consuming operation. It's something that you want to happen as little as possible. When the log grows, all activity in the DB has to stop and wait for the log growth to finish.
In addition, multiple small grow operations (and I'll assume you're on default auto-grow settings) will result in lots and lots of VLFs, that'll slow down backups (yes, full backups too) and database recovery.
Please read through this - Managing Transaction Logs[/url]
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
October 18, 2010 at 1:31 pm
Craig Farrell (10/18/2010)
It can be a lot more complex than that but start there. 🙂
Kalen Delaney did a talk for the 24 hours of PASS earlier this year titled "What's simple about simple recovery?" The conclusion at the end was that there's virtually nothing simple about it.
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
October 18, 2010 at 1:32 pm
The short answer is that in Simple Mode, you cannot recover this space unless you use the Shinkfile command. All the simple recovery mode does is reuse the space in the already bloated transaction log. After running DBCC SHRINKFILE once, one it again with the 'Truncate Only' option and you should have your log file back down to a manageable size.
When the transaction logs grow to an unacceptable limit, you must immediately back up your transaction log file. While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space.
The backup operation or the Truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part inside the log file.
To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:
# Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
# Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
# Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
# Design the transactions to be small.
# Make sure that no uncommitted transactions continue to run for an indefinite time.
# Schedule the Update Statistics option to occur daily.
October 18, 2010 at 1:34 pm
GilaMonster (10/18/2010)
Craig Farrell (10/18/2010)
It can be a lot more complex than that but start there. 🙂Kalen Delaney did a talk for the 24 hours of PASS earlier this year titled "What's simple about simple recovery?" The conclusion at the end was that there's virtually nothing simple about it.
Yeah, well, I like to try to help with my bitesized chunks. 🙂 After all, how does one eat the elephant hiding behind the trifle? 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2010 at 1:39 pm
upstart (10/18/2010)
After running DBCC SHRINKFILE once, one it again with the 'Truncate Only' option and you should have your log file back down to a manageable size.
The truncate only option on shrink is only for data files. Refer Books Online.
When the transaction logs grow to an unacceptable limit, you must immediately back up your transaction log file.
Not applicable in Simple recovery
p.s. Nice copy-paste from a microsoft kb article. Next time you copy-paste something from the net, attribute it. Posting without attribution (implying the work is your own) is plagarism and is not tolerated here
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
October 18, 2010 at 1:44 pm
Last question on the subject, as all of the information you have provided is extremely helpful, when our development team has a "once in a blue moon" bulk insert (which will cause the log file to grow - say over 7GB of data being inserted), what is the best way to regain the space back? Running the DBCC Shrinkfile to a size more closely to what I am seeing today?
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
October 18, 2010 at 1:46 pm
chris.s.powell (10/18/2010)
Last question on the subject, as all of the information you have provided is extremely helpful, when our development team has a "once in a blue moon" bulk insert (which will cause the log file to grow - say over 7GB of data being inserted), what is the best way to regain the space back? Running the DBCC Shrinkfile to a size more closely to what I am seeing today?
In theory... yes. However, is your space that big a deal? If so, can they iterate the bulk so one goes in as the last closes so they don't have to do the log file size manipulations?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply