recovery model simple

Question

recovery model simple

LOVER OF SQL

SSCommitted

Points: 1900
More actions
November 1, 2013 at 2:20 pm

#300614

Howdy Everybody,
My database is Simple (Recovery Model) and when i generated backup, the log file was to become big 30gb)... that's is normal using recovery model simple?
today:
data file 80gb
log file: 1gb (after shrink)
I think stranger because i was thing impossible generated log file in recovery model simple... I think that generated log file only in full recovery model.. but in simple also?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It might not be normal, but it's completely possible. Some process might have needed that the transaction log grew up to 30GB and if it's a normal process, you shouldn't shrink the log.

There's a great Stairway series on transaction log management, you could read all of it, but there's an article dedicated to transaction log on simple recovery model. http://www.sqlservercentral.com/articles/Stairway+Series/73782/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 2

I've been saw these article, but i continue not understand why my transaction log to become big when i've simple recovery model...

George M Parker SSCertifiable Points: 5412 More actions · Answer 3

Simple recovery allows the log file to be cleared of transactions without having to take a backup of the transaction log. Regardless, it will still grow if something requires that amount of log space in order to complete. More than likely it's a batch process or a database maintenance task such as an index rebuild. If your log requires 30GB of space to perform that function, then it's best to leave it that size and not shrink it, otherwise it will just auto-grow the next time that same process runs.

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 4

the problem is because my disk have a 20gb free, and tlog is bigger then 20gb...

George M Parker SSCertifiable Points: 5412 More actions · Answer 5

If you have the default trace enabled, you should be able to query it to find out when your transaction log is growing. From that, hopefully you can identify any SQL jobs or batch processes that are causing it to grow.

Here's a link to an article that explains how to query the default trace for file growth:

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

kevaburg SSCoach Points: 18131 More actions · Answer 6

George M Parker (11/2/2013)
Simple recovery allows the log file to be cleared of transactions without having to take a backup of the transaction log. Regardless, it will still grow if something requires that amount of log space in order to complete. More than likely it's a batch process or a database maintenance task such as an index rebuild. If your log requires 30GB of space to perform that function, then it's best to leave it that size and not shrink it, otherwise it will just auto-grow the next time that same process runs.

Just to clarify.....In SIMPLE recovery mode, transactions are cleared once they are committed, the reason that transaction log backups and point-in-time recovery is not possible. Where large transactions are processed without periodic commits you can expect to see the transaction log grow proportionately (or disproportionately) in a short period of time.

The next point is that if your log file grows to an abnormally large size as the result of an operation such as a bulk load, it could be more than reasonable to shrink the log file down and let it retain its optimal size in normal operation. In this case that might be the answer.

Information that isn't present here is: Exactly how much of that log file is actually filled with data and how long has it been in that state? How often are large transaction log intensive operations performed? Is the recovery mode changed before each operation and then changed back once it is complete? Is a full backup and transaction log backup performed directly after changing backup to FULL?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

kevaburg (11/4/2013)
Just to clarify.....In SIMPLE recovery mode, transactions are cleared once they are committed,

In simple recovery, the log is marked reusable by a checkpoint, not a commit. That's assuming the log records aren't needed for something else (eg 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TomThomson SSC Guru Points: 104773 More actions · Answer 8

LOVER OF SQL (11/2/2013)
I've been saw these article, but i continue not understand why my transaction log to become big when i've simple recovery model...

You read the article Luis linked? Including this statement:

Although, SIMPLE mode significantly eases the burden of transaction log management, it's a mistake to assume that, if you're using this mode, you can completely forget about managing the log. The transaction log is still playing a vital role in the day-to-day operation of the database, and you still need to size and grow the transaction log appropriately, according to the nature and frequency of transactions to which the database is subjected. Just because the log is auto-truncated, it does not mean that hefty and long running transactions cannot cause the log to expand rapidly, and cause you trouble if you haven't sized it correctly

what did you think that meant when you read it?

Tom

George M Parker SSCertifiable Points: 5412 More actions · Answer 9

GilaMonster (11/4/2013)
kevaburg (11/4/2013)
Just to clarify.....In SIMPLE recovery mode, transactions are cleared once they are committed,
In simple recovery, the log is marked reusable by a checkpoint, not a commit. That's assuming the log records aren't needed for something else (eg replication).

Thanks Gail. That's why I carefully worded my post and intentionally left out the words committed and checkpoint.

kevaburg SSCoach Points: 18131 More actions · Answer 10

George M Parker (11/4/2013)
GilaMonster (11/4/2013)
kevaburg (11/4/2013)
Just to clarify.....In SIMPLE recovery mode, transactions are cleared once they are committed,
In simple recovery, the log is marked reusable by a checkpoint, not a commit. That's assuming the log records aren't needed for something else (eg replication).
Thanks Gail. That's why I carefully worded my post and intentionally left out the words committed and checkpoint.

And that is a mistake I make every time I talk about the transaction log in SIMPLE recovery mode.....one day I will get it right! 😉

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

LOVER OF SQL (11/2/2013)
the problem is because my disk have a 20gb free, and tlog is bigger then 20gb...

is it a dedicated log disk ?

Use another bigger disk to accomodate big log .

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)