When is full recovery mode not full recovery mode
I was recently trying to develop a test for DBAs. The
scenario of the test was that you have taken over responsibility of a server
what needs to be done to make it production ready. The server is really badly
configured and the aim is for the tester to find them and make suggestions of
what needs to be done.
One of the tests I wanted to setup was a database with a full transaction
log. So I create a database, put a table in it and then insert lots of data, I
have a 1Mb log file so expect it to blow quite quickly. Eventually it blows and
I get an error saying the log is full. However I then run DBCC SQLPERF(LOGSPACE)
only to find the log is 30% full.
I'm confused, because I then try and do more inserts and they work.
Well thanks to Hillary Cotter and Kalen Delaney for pointing out
the error of my ways.
A database is in auto-truncate mode if one of the following is true:
- The db is in SIMPLE recovery mode
- The db has never been backed up
- The log has been truncated since the last full db backup
Which makes sense. So even in full recovery if you
haven't done a full backup then you can't use a transaction log backup so why
keep the transaction log.
-