Full Recovery, no tlog growth
-
We're running test conversions, loading lots of records and I see the data file autogrowing, but the 6GB tlog is nearly empty and not growing. Sql 2005 Enterprise. Database is in full recovery mode. I was going to implement frequent tlog backups as a performance item, not disaster recovery since this is a dev environment, but no full backup was performed before kicking off the conversion so I can't do that. Is the t log file not loading/growing because no full backup and / or no t log backups?
Data file autogrowth was set at 1mb and occuring very frequently so I bumped that up to 200mb.
-
I'm not terribly sure what performance gain you'll get from log backups. If you don't need logs, then put the DB in simple mode so you don't have to manage it at all.
How much SQL logs or doesn't log has nothing to do with when or how often the data file grows.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
I want to mimick our production environment where regular t log backups and log shipping occur, because ultimately that's where the real load will occur. I plan to size both the data and log files large enough to avoid auto growth altogether.
Perhaps the connection with performance and tlog backups was in a situation where the log file was not large and autogrowth performance hits would occur if the t log backups weren't happening.
-
Yeah, that sounds more plausible.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
So back to my original question, the t log file is not being used as far as I can see even though we're in full recovery. The data file is adding data and autogrowing occasionally. Tlog is doing nothing as far as I can see -- so is that because there is no full backup and thus no possibility of tlog backups?
The tlog file is 6GB containing only 64mb of data.
-
No SQL's going to log ops no matter what. Internally it has more to do with being able to recover pages and roll things back or forward in case of a failure than it has anything to do with you being able to recover. SQL's going to do its thing, which is logging ops, regardless of what you do.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Have you ever run a full backup on that database? Was this database created on the DEV system or restored from live?
If this is a brand new database created on the DEV system and there has never been a backup performed on that database, then SQL Server is actually treating the database as if it were in simple mode. It is done this way because you can't backup the transaction log anyways - so there really is no reason to keep the transactions available for backup.
Jeff
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 -
Jeff, Do you have documentation to support that SQL Server behaves that way? I've never heard this before. Are you saying that if you create a new DB and set it to FULL recovery mode, that SQL Server will not 'act' as if it is in FULL mode until you take a backup? Again, I would like to see the resource where you learned this.
-
John Rowan (6/19/2008)
Jeff, Do you have documentation to support that SQL Server behaves that way? I've never heard this before. Are you saying that if you create a new DB and set it to FULL recovery mode, that SQL Server will not 'act' as if it is in FULL mode until you take a backup? Again, I would like to see the resource where you learned this.Interestingly enough - it looks like 2000 and 2005 behave differently on this. I just ran a test (build a DB, and then create a table, insert some data, delete table, repeat from create table one x 50), and the 2005 log keeps growing, whereas the 2000 does not.
Again - it's not documentation by any means, but it sounds Jeffrey is onto something.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
The database being loaded was created with a script ( identical to production ) and then some basic config info was loaded. It is in full recovery on the dev system. No full backup has ever been taken and I know that blocks tlog backups so this is the only reason I can see that the ldf is not being manipulated by all of the inserts going on.
This doesn't really mimic a full production load since production has 6 million+ records in it already and log shipping is occuring. On the other hand more boxes will be in play then ( boxes that run the conversion code and generate the inserts )
-
Check this thread out. Maybe this will help you understand and come to conclusion about the Growth patter.
http://www.eggheadcafe.com/software/aspnet/32188763/transaction-log-hugeat.aspx
Maninder
www.dbanation.com -
One possibility is you can set a maximum size for the log file. It might be at that, if it's set.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
It's also possible that you just aren't filling up the log file. 6 Gig of log is quite a few transactions, even if they are big transactions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
6GB is a large log file. I've seen larger, but your system should be extremely busy to fill this.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply