September 19, 2010 at 7:44 am
In the past (SQL 2005) I always dumped trans w/ truncate and dbcc shrinkfile each morning after my db backups. Now I want to create trans log backups and stay in full recovery.
In SQL 2008 the only way I have found to trunc the log is by switching to simple recovery (during production - SCARY) and then running dbcc shrinkfile - then back to full recovery.
Shouldn't the trans log backup be truncating my log file? I even set up a trans log backup maintenance plan through the GUI and that did not truncate my trans log either.
Would like to be creating trans log backup chains at 15 min. intervals at some point, but need to get this straightened out first.
Would appreciate any advice.
September 19, 2010 at 7:53 am
hi
what does the following return when executed against the database in question
DBCC LOGINFO
Hint: scroll down the results and check for a status of 2 near the end of the output!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2010 at 10:55 am
SkyBox (9/19/2010)
Shouldn't the trans log backup be truncating my log file? I even set up a trans log backup maintenance plan through the GUI and that did not truncate my trans log either.
It'll be truncating the log (marking space in the log as reusable). It will not however be shrinking the log. Shrinking a log is a bad thing to do, you're just forcing it to grow again and causing poor performance when it does so as well as causing internal log fragmentation which slows down 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
September 19, 2010 at 11:36 am
Perry Whittle (9/19/2010)
hiwhat does the following return when executed against the database in question
DBCC LOGINFO
Hint: scroll down the results and check for a status of 2 near the end of the output!
There are several rows returning the status of 2.
RESULTS:
FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
225395281922022621280
2327680262144202272640
22621445898242022826428000000010200061
226214485196820229264154000000008200011
226214411141122023026420229000000013700001
226214413762562023126420230000000008900010
226214416384002023226420231000000015000004
226214419005442023326420232000000015200001
226214421626882023426420233000000014500021
226214424248322023526420234000000015200001
226214426869762023626420235000000011000045
232768029491202023726420236000000015200001
232768032768002023826420237000000026400001
239321636044802023926420238000000027700012
239321639976962024026420239000000040700002
245875243909122024126420240000000039200001
225395248496642024226420241000000015900036
227033651036162024326420241000000015900036
225395253739522024426420241000000060700037
233587256279042024526420241000000060700037
225395259637762024626420245000000028700002
233587262177282024726420245000000028700002
225395265536002024826420247000000028400007
240140868075522024926420247000000028400007
225395272089602025026420249000000041100012
246694474629122025126420249000000041100012
225395279298562025226420251000000054300002
225395281838082025326420251000000054300002
227852884377602025426420251000000054300002
225395287162882025526420254000000017300007
225395289702402025626420254000000017300007
240960092241922025726420254000000017300007
225395296337922025826420257000000042900007
225395298877442025926420257000000042900007
2475136101416962026026420257000000042900007
2262144106168322026126420260000000055900002
2262144108789762026226420260000000055900002
2262144111411202026326420260000000055900002
2327680114032642026426420260000000055900002
2262144117309442026526420264000000026900007
2262144119930882026626420264000000026900007
2262144122552322026726420264000000026900007
2393216125173762026826420264000000026900007
2327680129105922026926420268000000040600004
2327680132382722027026420268000000040600004
2327680135659522027126420268000000040600004
2327680138936322027226420268000000040600004
2327680142213122027326420272000000027600009
2327680145489922027426420272000000027600009
2327680148766722027526420272000000027600009
2458752152043522027626420272000000027600009
2393216156631042027726420276000000053300005
2393216160563202027826420276000000053300005
2393216164495362027926420276000000053300005
2393216168427522028026420276000000053300005
2393216172359682028126420280000000040500007
2393216176291842028226420280000000040500007
2393216180224002028326420280000000040500007
2589824184156162028426420280000000040500007
2458752190054402028526420284000000078600014
2458752194641922028626420284000000078600014
2458752199229442028726420284000000078600014
2524288203816962028826420284000000078600014
2524288209059842028926420288000000066000010
2524288214302722029026420288000000066000010
2524288219545602029126420288000000066000010
2524288224788482029226420288000000066000010
2524288230031362029326420292000000060300173
2524288235274242029426420292000000060300173
2524288240517122029526420292000000060300173
2720896245760002029626420292000000060300173
2589824252968962029726420296000000104500007
2589824258867202029826420296000000104500007
2589824264765442029926420296000000104500007
2786432270663682030026420296000000104500007
2655360278528002030126420300000000117500001
2655360285081602030226420300000000117500001
2655360291635202030326420300000000117500001
2851968298188802030426420300000000117500001
2720896306708482030526420304000000130200006
2720896313917442030626420304000000130200006
2720896321126402030726420304000000130200006
2917504328335362030826420304000000130200006
2851968337510402030926420308000000142800009
2851968346030082031026420308000000142800009
2851968354549762031126420308000000142800009
2851968363069442031226420308000000142800009
2917504371589122031326420312000000130300002
2917504380764162031426420312000000130300002
2917504389939202031526420312000000130300002
2983040399114242031626420312000000130300002
2983040408944642031726420316000000155800004
2983040418775042031826420316000000155800004
2983040428605442031926420316000000155800004
21179648438435842032026420316000000155800004
21114112450232322032126420320000000194300002
21114112461373442032226420320000000194300002
21114112472514562032326420320000000194300002
21179648483655682032426420320000000194300002
21245184495452162032526420324000000194200004
21245184507904002032626420324000000194200004
21245184520355842032726420324000000194200004
21245184532807682032826420324000000194200004
21376256545259522032926420328000000207100002
21376256559022082033026420328000000207100002
21376256572784642033126420328000000207100002
21376256586547202033226420328000000207100002
21507328600309762033326420332000000232400009
21507328615383042033426420332000000232400009
21507328630456322033526420332000000232400009
21507328645529602033626420332000000232400009
21638400660602882033726420336000000257900012
21638400676986882033826420336000000257900012
21638400693370882033926420336000000257900012
21703936709754882034026420336000000257900012
21769472726794242034126420340000000296600004
21769472744488962034226420340000000296600004
21769472762183682034326420340000000296600004
21966080779878402034426420340000000296600004
21966080799539202034526420344000000347800004
21966080819200002034626420344000000347800004
21966080838860802034726420344000000347800004
22097152858521602034826420344000000347800004
22162688879493122034926420348000000373000013
22162688901120002035026420348000000373000013
22162688922746882035126420348000000373000013
22359296944373762035226420348000000373000013
22424832967966722035326420352000000423600027
22424832992215042035426420352000000423600027
224248321016463362035526420352000000423600027
224248321040711682035626420352000000423600027
226214401064960002035726420356000000434900062
226214401091174402035826420356000000434900062
226214401117388802035926420356000000434900062
228180481143603202036026420356000000434900062
228835841171783682036126420360000000514000008
228835841200619522036226420360000000514000008
228835841229455362036326420360000000514000008
230801921258291202036426420360000000514000008
232112641289093122036526420364000000564400028
232112641321205762036626420364000000564400028
232112641353318402036726420364000000564400028
232768001385431042036826420364000000564400028
235389441418199042036926420368000000600800075
235389441453588482037026420368000000600800075
235389441488977922037126420368000000600800075
236044801524367362037226420368000000600800075
238666241560412162037326420372000000665800052
238666241599078402037426420372000000665800052
238666241637744642037526420372000000665800052
239976961676410882037626420372000000665800052
242598401716387842037726420376000000743300114
242598401758986242037826420376000000743300114
242598401801584642037926420376000000743300114
243909121844183042038026420376000000743300114
247185921888092162038126420380000000821400002
247185921935278082038226420380000000821400002
247185921982464002038326420380000000821400002
247185922029649922038426420380000000821400002
251773442076835842038526420384000000544200296
251773442128609282038626420384000000544200296
251773442180382722038726420384000000544200296
252428802232156162038826420384000000544200296
257016322284584962038926420388000000968900085
257016322341601282039026420388000000968900085
257016322398617602039126420388000000968900085
257671682455633922039226420388000000968900085
262914562513305602039326420392000001088300012
262914562576220162039426420392000001088300012
262914562639134722039526420392000001088300012
262914562702049282039626420392000001088300012
268812802764963842039726420396000001192700002
268812802833776642039826420396000001192700002
268812802902589442039926420396000001192700002
270123522971402242040026420396000001192700002
276021763041525762040126420400000001314400055
2760217631175475200020400000001314400055
2760217631935692800020400000001314400055
2760217632695910400020400000001314400055
September 19, 2010 at 11:39 am
GilaMonster (9/19/2010)
SkyBox (9/19/2010)
Shouldn't the trans log backup be truncating my log file? I even set up a trans log backup maintenance plan through the GUI and that did not truncate my trans log either.It'll be truncating the log (marking space in the log as reusable). It will not however be shrinking the log. Shrinking a log is a bad thing to do, you're just forcing it to grow again and causing poor performance when it does so as well as causing internal log fragmentation which slows down database recovery.
If shrinking the log is s bad thing, then how do you recommend keeping the transaction log from hitting 100gb's and higher? I just want to keep the trans log at a manageable size.
Thanks guys
September 19, 2010 at 11:39 am
Looks like something's keeping the log active.
What's the value of log_reuse_wait_desc in sys.databases for this database?
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
September 19, 2010 at 11:42 am
SkyBox (9/19/2010)
If shrinking the log is s bad thing, then how do you recommend keeping the transaction log from hitting 100gb's and higher? I just want to keep the trans log at a manageable size.
Regular log backups.
There's nothing wrong with a 100GB tran log, if that's what the database needs based on activity and log backup frequency. Last big DB I worked on had a 250GB log. It regularly hit 80% full during the overnight jobs.
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
September 19, 2010 at 11:45 am
GilaMonster (9/19/2010)
Looks like something's keeping the log active.What's the value of log_reuse_wait_desc in sys.databases for this database?
log_reuse_wait_desc
DATABASE_SNAPSHOT_CREATION
NOTHING
LOG_BACKUP
DATABASE_SNAPSHOT_CREATION
NOTHING
LOG_BACKUP
LOG_BACKUP
September 19, 2010 at 11:53 am
SkyBox (9/19/2010)
GilaMonster (9/19/2010)
Looks like something's keeping the log active.What's the value of log_reuse_wait_desc in sys.databases for this database?
log_reuse_wait_desc
DATABASE_SNAPSHOT_CREATION
NOTHING
LOG_BACKUP
DATABASE_SNAPSHOT_CREATION
NOTHING
LOG_BACKUP
LOG_BACKUP
What's the value for this database? (The one that you did the LogInfo for)
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
September 19, 2010 at 11:53 am
GilaMonster (9/19/2010)
SkyBox (9/19/2010)
If shrinking the log is s bad thing, then how do you recommend keeping the transaction log from hitting 100gb's and higher? I just want to keep the trans log at a manageable size.Regular log backups.
There's nothing wrong with a 100GB tran log, if that's what the database needs based on activity and log backup frequency. Last big DB I worked on had a 250GB log. It regularly hit 80% full during the overnight jobs.
So for optimal performance, do you recommend hourly log backups? I plan on setting up trans log backup chains at 15 minute intervals when I get some to test this backup/restore method thoroughly.
With my old server I was molded into shrinking the log because I only had 50 gb's to spare on the log partition and not much more on the db partition. If I didn't shrink then the log drive would run out of space - at 2am or something.
On my new setup, I have a 500gb log partition and 2.6 terabyte data partition. I want to get things right now.
September 19, 2010 at 11:58 am
GilaMonster (9/19/2010)
SkyBox (9/19/2010)
GilaMonster (9/19/2010)
Looks like something's keeping the log active.What's the value of log_reuse_wait_desc in sys.databases for this database?
log_reuse_wait_desc
DATABASE_SNAPSHOT_CREATION
NOTHING
LOG_BACKUP
DATABASE_SNAPSHOT_CREATION
NOTHING
LOG_BACKUP
LOG_BACKUP
What's the value for this database? (The one that you did the LogInfo for)
Those results were from the same db. Here is the statement:
use databaseName (same as LogInfo)
select log_reuse_wait_desc from sys.databases
September 19, 2010 at 11:59 am
SkyBox (9/19/2010)
So for optimal performance, do you recommend hourly log backups?
Log backup frequency has nothing to do with performance. It has to do with data loss policies and desirable restore abilities.
If you're in full recovery and doing log backups it means that you need the ability to restore a database to the point of failure in case of a disaster. The interval between log backups should be determined by:
1) Activity in DB and desirable log size
2) Maximum allowable data loss in the case of a disaster.
If the system cannot be allowed to lose more than 30 min of data, no matter what, your log backup interval must be at most 30 minutes, it can be less.
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
September 19, 2010 at 12:00 pm
SkyBox (9/19/2010)
Those results were from the same db. Here is the statement:use databaseName (same as LogInfo)
select log_reuse_wait_desc from sys.databases
sys.databases returns 1 row for each database on the server, so no it is not for the same database, you've given me the log reuse reasons for every single database on the server without any way of telling which database is which.
A database can only have ONE log reuse reason at a time.
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
September 19, 2010 at 12:01 pm
What's the value for this database? (The one that you did the LogInfo for)[/quote]
This is the statement I am executing:
use dataBase (same db s LogInfo)
select log_reuse_wait_desc from sys.databases
September 19, 2010 at 12:06 pm
The sys.databases view is a server-wide view that contains one row for each database on the server. It returns the same no matter what database context you are in.
That list you've given me is useless because it's the log reuse for each individual database on the server and, since you did not include any other columns nor do I know what the name of the database that you're working with is.
What I need to attempt to diagnose the cause of the large active log is the specific log reuse wait for the specific database in question. Since sys.databases is a server-scoped view, USE database is not going to change its output.
May I suggest that you take a minute to look in books online for the sys.databases view to familiarise yourself with it before continuing.
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
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply