March 22, 2004 at 8:49 am
Hi, I'm confused at different things I've been reading and hearing in discussion groups regarding BACKUP and inactive transactions
My thought was that the method to properly truncate a log (whuich clears the inactive transactions) was ONLY through backing up the T-LOG procedure. But why does Enterprise Manager (2000) have a setting under the Options tab "Remove inactive entries from transaction logRemove inactive entries from transaction log" when performing a "Database-Complete" OR a "Transaction Log" method?
Many thanks. Jeff
March 22, 2004 at 1:31 pm
Backup log will by default removes inactive part of the log after it successfully backup the log. Complete backup does not have any effects on inactive entries. Of course you can just truncate the log without backing up.
March 22, 2004 at 3:36 pm
Not sure how it does it but I don't see anything different under Profiler with or without it on, but I only have one Test BD with a LogFile not set to autotruncate.
March 24, 2004 at 8:20 am
My understanding is that when you perform a transaction log backup from the SQL Server Enterprise Manager Backup Database dialog box, if the 'Remove inactive entries from transaction log' checkbox under the Options tab is selected the backup will be performed without the NO_TRUNCATE option. If you don't select this option it should add the NO_TRUNCATE option.
The working of this option was a problem with SQL Server 2000 base version and should have been corrected with latest service pack. I am surprised Profiler didn't pick up the difference.
Francis
March 24, 2004 at 8:30 am
I was too, but I am fully patched. I assmue it does that as well.
March 24, 2004 at 8:31 am
HI, not sure what you mean by profiler? Also, I'm using MSSQL 2000 SP3/SP3a?
Many thanks. Jeff
March 24, 2004 at 8:39 am
MS SQL Profiler is a tool that you can use to do traces on your system and see exactly what commands are being passed to SQL Server. For example if you want to know what EM is doing: create a trace under Profiler, trun on all T-SQL and stored procedure event (or anything else you want) filter by your id or database you connect to and start the trace. Everything EM does is ultimately turned into T-SQL.
There are a number of good articles on using Profiler. It can be a very useful debugging tool.
Francis
March 24, 2004 at 10:20 am
Great tip. I'll try it with the backup process within EM ...
Many thanks. Jeff
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply