InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 06:32:39 GMT20http://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxThank you for the article. I found the section titled "How does SQL use the log?" particularly well written.I hope I am not the only one that feels this way, but one cannot go over these concepts enough times, no matter how much DBA experience one has.... :-)Thu, 08 Nov 2012 13:51:07 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxExcellent article! And thanks for clearing up, once and for all, the issue over full backups/log chain. It was always my understanding that full backups break the the log chain, and now I know this is not the case.Sat, 22 Sep 2012 14:36:27 GMTSQLmountainhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxTake a read through this: [url]http://www.sqlservercentral.com/articles/Recovery+models/89664/[/url]Mon, 14 May 2012 10:57:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxNow i got...Thanks :-)Sun, 13 May 2012 13:14:14 GMTbest_yunushttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]best_yunus (5/12/2012)[/b][hr]Can we do point in time recovery in Bulk loged recovery model?If yes then how?[/quote]Yes but only if there were no minimally logged transactions in the final log file that you want to recover up to the point in time.Sun, 13 May 2012 00:15:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxCan we do point in time recovery in Bulk loged recovery model?If yes then how?Sat, 12 May 2012 22:18:01 GMTbest_yunushttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxGreat article, company I contract for are constantly changing the recovery model and shrinking the log then changing the RM back - have presented them with the URL for this, fingers crossed they will see sense! ;-)[b][i]qh[/i][/b]Thu, 16 Feb 2012 07:19:57 GMTquackhandle1975http://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]GilaMonster (1/12/2012)[/b][hr]...Speaking as a [i]occasional[/i] trainer and presenter..[/quote]And a darned good one too, may I say ! :w00t:Thu, 12 Jan 2012 06:55:23 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]SQLPhil (1/12/2012)[/b][hr]It'll be nice to have the opportunity to turn round and inform the instructor for once! ;-)[/quote]:-DJust be polite if you do. Speaking as a occasional trainer and presenter, there's nothing worse than a student who thinks he's right and you're wrong and makes a public issue out of it.Thu, 12 Jan 2012 06:43:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxThank you very much Gail for clarifying all of this (and for the excellent article on COPY_ONLY). You have certainly added a lot of clarity to my understanding of backups and restores.Speaking amongst my fellow DBA colleagues we were all under the (misguided) understanding that full backups broke the log chain. In fact, even in some training courses we've been told that this has been the case. It'll be nice to have the opportunity to turn round and inform the instructor for once! ;-)Thu, 12 Jan 2012 06:24:25 GMTSQLPhilhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]SQLPhil (1/12/2012)[/b][hr]But having said that, it begs the question what is the point of copy_only backups now?[/quote]This: [url]http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/[/url]You wouldn't be the first to argue with me about full backups breaking the log chain, it's an (unfortunately) commonly held belief. They've never broken the log chain though.Thu, 12 Jan 2012 04:16:33 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxEchoing everyone else's comments - great article!However, I was all prepared to query your statement in the article: [quote]It is important to note that while a full or differential backup starts a log chain, full and differential backups don't break the log chain[/quote]My understanding was always that performing a Full backup [b]would [/b]break the log chain, and if you wanted to restore to a given point using your log backups, you would have to restore from the last full backup. That's what I thought copy_only backups were introduced for, to ensure an ad-hoc full backup would not disrupt the log chain.However, having now tested it against a 2008 R2 instance sure enough taking a full backup does [b]not[/b] break the log chain, so I have learned something new today! Thank you! :-)But having said that, it begs the question what is the point of copy_only backups now?Thu, 12 Jan 2012 03:48:44 GMTSQLPhilhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxBy mistake I choose one star I'm sorry this is great article thanks a lotTue, 10 Jan 2012 06:00:51 GMTlital-494950http://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxWhat a great book title :). To give you an idea of how far out of my element I am..I have to read this after I pass my CPA exams, 1 down 3 to go.Fri, 06 Jan 2012 11:08:18 GMTBad_Karmahttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxGood luck in you search. In the meantime, may I suggest this? [url]http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/[/url]Fri, 06 Jan 2012 10:57:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxVery enlightening. I was actually shrinking my log files in the first and last step of an ETL run LOL. We need a real DBA so badly.Fri, 06 Jan 2012 10:35:16 GMTBad_Karmahttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxDBCC ShrinkFile(<name of log file>,<size log file was before abnormal growth>)Wed, 04 Jan 2012 02:29:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxCan you recommend the good algorithm for the log to be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.Wed, 04 Jan 2012 02:01:34 GMTMichael Pregerhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]jswong05 (1/3/2012)[/b][hr]This article again shows SQL Server's shortcomings.1) default to full recovery mode (Oracle is opposite)[/quote]Default is whatever model is set to. If you want new databases defaulting to simple recovery, set model to simple recovery[quote]2) not able to free unused space without side effecct. (Oracle does not have this problem)[/quote]What exactly do you mean by side effect? Fragmentation of the data files?[quote]3) restore commands have to actually specify name of backup files (Oracle RMAN does not need to)[/quote]So how do you chose which backup you want to restore? (especially if restoring a backup from another server)And there is a way in SQL to restore without specifying file names - backup devices. Also Management Studio can automatically pick up lists of backups if you select 'database' rather than 'device' and the backup history is intact. SQL 2012's even better in that regard[quote]4) select can block insert, update .... (not in Oracle)[/quote]Read committed snapshot and snapshot isolation levels, introduced in SQL 2005. Readers no longer block writers and vis versa. It's your choice now whether you want locking or row versioning for isolation.Wed, 04 Jan 2012 01:56:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]RLB (1/3/2012)[/b][hr][quote]The only time a log should be shrunk is if some abnormal database activity (or failed log backups) has resulted in the log growing far beyond the size it needs to be for the database activity. In this situation, the log can be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.[/quote]I didn't see a recommendation for it, but what is the best method or T-SQL command to shrink the log back to its normal working size and keep the log file fragmentation to a minimum. Or, put another way, what is the best method to recreate a new log?[/quote]It's in Kimberly's article that I referenced.Shrink log to 0, regrow (in maybe a couple of steps) to the desired size.Wed, 04 Jan 2012 01:52:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote]The only time a log should be shrunk is if some abnormal database activity (or failed log backups) has resulted in the log growing far beyond the size it needs to be for the database activity. In this situation, the log can be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.[/quote]I didn't see a recommendation for it, but what is the best method or T-SQL command to shrink the log back to its normal working size and keep the log file fragmentation to a minimum. Or, put another way, what is the best method to recreate a new log?Thanks,BobTue, 03 Jan 2012 20:56:59 GMTRLBhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxGreat Article Gail.Tue, 03 Jan 2012 19:31:41 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxThis article again shows SQL Server's shortcomings.1) default to full recovery mode (Oracle is opposite)2) not able to free unused space without side effecct. (Oracle does not have this problem)3) restore commands have to actually specify name of backup files (Oracle RMAN does not need to)4) select can block insert, update .... (not in Oracle)Despite, some people again and again preached "not to use existing features". None of the SQL Server professionals really try to convince Microsoft to make the product better. If I pay for space usage and I see space is not going to be reused, I will want to free up the unused space allocation and keep db running smoothly. I am not talking about auto-shrink, auto-grow jojo. I am talking about get the steady db to allocate adequate space (like 20% free for growth) so it works well until next maintenance window and you pay less (specially virtual environment).Jason[url=http://dbace.us]http://dbace.us[/url]Tue, 03 Jan 2012 19:24:20 GMTjswong05http://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxI wrote a stored procedure that generates "restore script" by entering a date-time parameter. (We can also do this with LSN or transaction mark). I also have a version that can change location and database name to turn it into a QA/Test refresh script. I will publish them at SQL-Saturday#107. Oracle does that automatically. Oracle DBA hasn't need to be concerned which backup file to use. Restore command by datetime, SCN or restore point. Oracle has one leg up smarter.If you have every 15 minutes transaction log backup (correctly), your maximum data loss can only be up to 15 minutes.Re-writing is good. BOL is also re-written over years. Some previous ones may not have so much information.Be careful. She is black-belt. :-PJasonhttp://dbace.usTue, 03 Jan 2012 16:18:19 GMTjswong05http://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]Robert J. Browning (1/3/2012)[/b][hr]This is a very good article, and most of the information in it transcends all versions of SQL Server, so the fact it is over 3 years old is moot. [/quote]It's actually not 3 years old. This re-publication was a complete rewrite of the article. The original was a little too vague on many points and left too much out. So what you read is brand new (well, written late October last year)[quote]However, the statement "The growth increment must not be an exact multiple of 4GB. There's a bug in SQL if that exact size is used.", piqued my curiosity. To which version of SQL Server does THIS statement apply? Certainly not all versions! [/quote]Yes, all current versions. (haven't checked if it's still there in 2012, and probably not the really old versions, ie pre SQL 7).[url]http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx[/url]That blog post is from May 2010.Tue, 03 Jan 2012 15:54:30 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[font="Courier New"]This is a very good article, and most of the information in it transcends all versions of SQL Server, so the fact it is over 3 years old is moot. However, the statement "The growth increment must not be an exact multiple of 4GB. There's a bug in SQL if that exact size is used.", piqued my curiosity. To which version of SQL Server does THIS statement apply? Certainly not all versions! Wouldn't it have been fixed by now? And, what happens if one does specify a growth increment that is an exact multiple of 4GB? That would be very useful information. Does the growth increment limitation apply only to t-logs, or does it also affect database files.[/font]Tue, 03 Jan 2012 15:38:07 GMTRobert J. Browninghttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxGail, a minor typo:[quote]In full recovery model transaction log entries are kept for both database integrity and database recovery purposes. Inactive log records are retained in the transaction log until a [b]lob[/b] backup occurs.[/quote]Thanks for a great article,RichMon, 25 Oct 2010 12:52:42 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]GilaMonster (7/10/2010)[/b][hr][quote][b]Chris Houghton (7/10/2010)[/b][hr]Am i right in thinking then that data doesn't "move" from the transaction log (ldf) to the data file (mdf). The database engine modifies data pages in the buffer, these changes are written in the log cache which must be recorded in the log file before the dirty pages can be flushed to the data file.[/quote]Yup. Absolutely. The log records in the log cache will be written to disk before the data pages are or when the transaction is committed, whichever happens first. The data pages will be written to the data file at some point later.[/quote]:-D Thank you, I think it's starting to sink in.Sat, 10 Jul 2010 15:27:20 GMTChris Houghtonhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]Chris Houghton (7/10/2010)[/b][hr]Am i right in thinking then that data doesn't "move" from the transaction log (ldf) to the data file (mdf). The database engine modifies data pages in the buffer, these changes are written in the log cache which must be recorded in the log file before the dirty pages can be flushed to the data file.[/quote]Yup. Absolutely. The log records in the log cache will be written to disk before the data pages are or when the transaction is committed, whichever happens first. The data pages will be written to the data file at some point later.Sat, 10 Jul 2010 13:47:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxAm i right in thinking then that data doesn't "move" from the transaction log (ldf) to the data file (mdf). The database engine modifies data pages in the buffer, these changes are written in the log cache which must be recorded in the log file before the dirty pages can be flushed to the data file. Locking mechanisms are used to prevent the system users from reading uncommitted data from the database unless the system is specifically designed to allow this via specific isolation levels? Then once committed are the locks released?Apologies for asking what to many must be elementary questions.P.S. for those as ill-informed as myself, this SQL Server 2000 I/O basics article proved helpful. Old technology but i'm hoping the principals are similar.http://technet.microsoft.com/en-us/library/cc966500.aspxSat, 10 Jul 2010 13:05:33 GMTChris Houghtonhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspx[quote][b]sameer.kasi200x (7/8/2010)[/b][hr]So the multiplexing of transaction log may decrease performance. [/quote] No. It won't have any effect on performance[quote]I mean are the transactions spread across or they are written to a one log (for one transaction) and then to second log (for next transaction or may be another transaction running in parallel) ?[/quote]Neither.Say there are two transaction log files for a DB. SQL uses one, beginning to end, then uses the second one, beginning to end, then goes back to the first one and starts again from the beginning (assuming that portion of the log has been marked as reusable). SQL will not use two log files in parallel.Thu, 08 Jul 2010 05:51:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxSo the multiplexing of transaction log may decrease performance. I mean are the transactions spread across or they are written to a one log (for one transaction) and then to second log (for next transaction or may be another transaction running in parallel) ?Thu, 08 Jul 2010 05:30:59 GMTsameer.kasi200xhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxNo use whatsoever. SQL uses transaction logs serially, it does not stripe log records across log files nor use them in parallel.Thu, 08 Jul 2010 05:21:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxAny suggestions/recommendations on multiplexing for Transaction Logs. Is it of any use (like the multiplexing for Datafiles may help reduce the disk contention and also helps administering the space usage properly and avoids running into no disk all of a sudden).Thu, 08 Jul 2010 05:13:46 GMTsameer.kasi200xhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxThanks Gail - it is nice to have educational material like this.Wed, 07 Jul 2010 16:37:19 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxSnapshot and merge none. Transactional the log reader reads the changes from the transaction log and writes them through to the distributorTue, 06 Jul 2010 06:53:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxWhat role does the transaction log play in replication?Tue, 06 Jul 2010 06:48:20 GMTGary Joneshttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxAll modifications are always logged regardless of recovery model. In bulk logged, some operations can be minimally logged, which means less log impact (log growth), but larger log backups and some limitation on point-in-time restore.Tue, 06 Jul 2010 05:52:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxWhen in Bulk-Logged Recovery mode is everything logged? Is there a chance that certain transaction won't be logged? Or is the only downside that the transaction log backup will take longer?Tue, 06 Jul 2010 05:43:11 GMTjwilderhttp://www.sqlservercentral.com/Forums/Topic594769-1390-1.aspxHI Gail,[i]"Transaction log entries are considered active until the data pages that were modified by that transaction have been written to disk. Once that occurs, the log entries are considered inactive and are no longer necessary for database recovery."[/i]There may be situations where the modified data pages flushed to the disk (data file) by the check point but still the transaction would not have been completed. So the log entries cannot be incative untill it records the commit of transaction as this will be needed for the recovery purpose.Tue, 06 Jul 2010 03:32:18 GMTSubhash-63067