InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:59:58 GMT20http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxTo add to Steve's good words:Forget trying to [i]read[/i] the log. You need to understand (1) why it's needed (2) how it works and (3) how to use it.1. Why a t-log? Because there's no way to maintain a transactionally consistent database without it. That means either all or nothing in a "transaction" - loosely, a batch of commands consisting of INSERTs, UPDATEs, DELETEs, etc. - gets written to the d.b. Think of having "half" an engine rebuild on your car: can you drive now? Nope, you're screwed. You either put off the rebuild, or you bring it in and get the entire job finished. Only then can you drive the car. The analogy continues: say you get half-way through the engine rebuild and you realize you made a mistake removing vacuum hoses early on and you're on step 124 and you want to go back to the beginning and start over. This is like a ROLLBACK. If only car maintenance were so easy :-)2. How's it work? Read what others already posted on this thread. Changes you make to your data first get written to the transaction log, then later the updated data gets flushed from RAM to disk. Why does it get so big? If I INSERT a million rows into an otherwise empty d.b. and then issue a DELETE for those rows, how much data is in my d.b.? Nothing, right. But my t-log has 2 million transactions-worth stored in it. This is why, [b]left unchecked[/b], your transaction log can swell in size and why so many people make the misguided effort to make it smaller. "Misguided" b/c there [b]is[/b] a correct way to keep your transaction log from growing ever larger, and that means....3. If you're in FULL recovery mode (view this in SSMS by right-clicking your db, Properties, Options), then you need to manage your transaction logs. Read up on this. The basic idea is that you have 2 kinds of backups: full database backups (we'll leave differentials out of this) done, say, daily, and transaction log backups done, say, hourly. When you back up the transaction log, inactive portions of the log file are marked as re-usable for recording future transactions. So, if you're in FULL recovery mode, you must back up the transaction log. (Here's a nice link: [url=http://sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-III-Transaction-Log-Maintenance.aspx]http://sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-III-Transaction-Log-Maintenance.aspx[/url]. )++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Think of six white boards (your log file), and you're the teacher. You write stuff on the boards as you lecture, and your students follow along. You get to the end of your sixth white board, what do you do? "Oh, I don't need that stuff on boards 1 and 3 anymore - everyone's written that stuff down in their notes already." So, you erase boards 1 and 3, and you keep on writing as you talk.The bit about students taking notes on boards 1 and 3, and you wiping them out? That's the log backup. You don't need the stuff on the boards anymore, because that info. has been backed up. Those two boards are free to re-use. But - and here's the part to not forget - how much "log" space do you have now in total? 6 white boards' worth, that's how much. You erased the ink on the boards, you didn't pull the boards of the wall and toss them in the dumpster, did you? Your log file hasn't changed in size a bit. You just cleared inactive data off two boards so you could re-use them.So you keep teaching. You re-fill boards 1 and 3, but now they're all full again. This time, you've decided you need to KEEP all of the 6 white boards b/c they all contain vital, current information. You have a full transaction log. By default, SQL server will now grow the transaction log. So, you call maintenance, and they come and install 3 more white boards in your classroom. Voila! More space to write!! Great, huh? Well, maybe.... you lost a lot of class instruction time waiting for maintenance didn't you? That's the performance hit SQL takes when it has to grow the log file bigger, and [u]that's[/u] why folks here have recommended leaving the log file as big as it needs to be and NOT shrinking it. You wouldn't remove 5 of your 6 white boards from your classroom at the start of school when you KNOW you'll likely need 6 boards at some point during the semester, would you?Oh, and what if maintenance comes and says, "sorry, we can't fit any more white boards on these walls, there's no more room"? That's like your transaction log growing so big that your disk is full. No more data can be INSERTed! Class comes to a halt.School's out.HTH,RichTue, 15 Jun 2010 19:21:52 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxThe "log" is a misnomer, IMHO. It's more of a change tracking file for the db than a log.You shouldn't, in general, read the log. Especially now with auditing built into the product. Really the log is there to ensure that changes made to data are completely written to disk and committed in case power goes out, something corrupts, etc. Changes are written to the log, then once that is hard on disk, the changes are made to the data file.In terms of recovery, you use the log backup file to do a log restore. Typically if I backed up at 1:00am once a day, and then did a log backup every hour, when I did a restore, I'd restore:- the full backup (data as of 1:00am)- log backups, in order, starting at 2:00am (possible 1:00am) and continuing through failure. If I crashed at 8:02am, I'd have one full backup and then 7 log backups to restore. If you look up the RESTORE LOG command, you'll find syntax.In terms of your system, I think that you are not running log backups, which you need to do. You should schedule those when you set things up, and if you want an easy way to do it, use maintenance plans. You can delete log files older than a couple days if space is an issue. This will allow all the "records" in the log file to be marked as backed up and then that space is reused. Your log file will need to be the size of all the changes to data that occur in between log backups. If you run log backups hourly, you'll get an idea of how many changes are made to data in an hour.Does this make sense?Tue, 15 Jun 2010 15:09:53 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxThanks Steve and Rich for your replies. I actually have done a lot more research since posting this, and I am even more confused about the log file. :)I first had the toughest time trying to figure out how to even read or use the log file. I came across some expensive applications that would allow me to read the log file. I couldn't find any reason to justify that kind of money. And I still have now idea how to use the log file in the way everyone is talking about here. Is there something built into SQL Server that would allow me to restore to a specific point using my log file, or does that take another application purchase?I then read that I could use DBCC LOG to view the contents of the log file (it might have been in this thread or an article linked from it - I cannot remember where I found it now). This doesn't really give me any easily read data. I would have to get an understanding of the log file "code" to really understand what is going on. But, using this SQL, I found that my 1GB log file contained only 198 rows. Why is it so large then? The reason I am trying to figure all of this out is because we have a database that is created from script. When it is created, it has about 300k initial records in a table. There are some other tables that have some data, but not much. There are 20 or 25 tables that have nothing, and are used with our program. So really, all that has happen to this database is that the tables, indexes, stored procs etc.. have been created from script and some initial data has been imported. Why is the log file 1 GB? After I shrink it and then use the application against it for a little while for about 2 weeks, I ran DBCC LOG against it again. Right now, there are 1192301 rows in the log file, but it is only 768KB in size. Is there something I should check in our creation scripts?Should we just be shrinking it at the end of the script after is has been created?Thanks in advance for the help.Tue, 15 Jun 2010 14:58:38 GMTjruezhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxHow are you supposed to recover deleted or severely altered records if you have dozens or hundreds of users simultaneously inserting and updating records? How can you recover by going back to a point in time before a screw-up without undoing the 1000's of good updates and inserts done by other users?That's what I don't understand. If you're the only person using the db then having point in time recovery is brilliant, but with loads of users you can't jump back to undo what "Dizzy DB Dave" did without losing all the good changes done by other people. (Assume everyone logs in via a web app so everyone appears to SQL as the same web user)Maybe I have overlooked something?Tue, 15 Jun 2010 14:39:14 GMTGary7512http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxShort answer: you can't turn off the log. That's part of how ACID principles are maintained and you have an intact database system, with the log.So with that being the case, why shrink it if it grows again to the same size? When you take log backups, the space gets reused, so set those up, get a good stable size, and leave it alone, checking on it periodically (monthly).A few articles:[url]http://www.sqlservercentral.com/articles/64582/[/url][url][b][/b]http://www.sqlservercentral.com/articles/69476/[/url]Tue, 15 Jun 2010 14:35:12 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote]Thom, I am not sure I understand why that is a bad thing to do.Everyone, I could use some help with the following questions:Why is it bad to shrink the log file?[/quote]Because it will (probably) only need to grow again to something near its original size as you perform more transactions. The auto-growth of the t-log file will cause a performance hit each time.[quote]Why is a log file necessary if I am taking backups of database on a daily or sometimes hourly basis? If there is a crash, I would use the backup as the restore point. [/quote]As someone pointed out earlier, it's necessary b/c SQL won't work w/o one. [b]You[/b] don't "need" it [b]if[/b] you don't mind "sometimes" losing up to an hour's worth of work.:-) Seriously, if you or your business owners are satisfied with that risk of data loss (and you don't care about point-in-time recoveries), then you don't need to worry about transaction log backups and you could probably run in Simple recovery mode.Honestly, I could probably tolerate losing 1 hour or even 3 hours' work on our prime d.b., if it happened once every 5 years or so. But I like knowing that I can do point-in-time recoveries to undo a stupid mistake, especially since I'm the one who will likely execute that stupid mistake! Think about it: my users work on one row at a time with a beta-tested web app. containing lots of built-in error-checking. [b]I[/b] make updates across thousands of rows, often with one-off, ad hoc code that uses INT ID values I type by hand. Whose error is more likely to cause havoc system-wide?[quote]Our log file seems to grow very large, and can easily be shrunk down from over 1GB to about 1000K. I did not think that was a bad thing until I started reading through some of the posts on this thread.Thanks for all the information everyone is posting. I am learning a lot.Jarrod[/quote]If you want to manage your t-logs, read the excellent articles posted here by others who have written about this admittedly sometimes confusing subject. Yours, RichTue, 15 Jun 2010 14:02:44 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote]the log would grow to 30-40gb and then be shrunk to 5gb after the job[/quote]Thom, I am not sure I understand why that is a bad thing to do.Everyone, I could use some help with the following questions:Why is it bad to shrink the log file?Why is a log file necessary if I am taking backups of database on a daily or sometimes hourly basis? If there is a crash, I would use the backup as the restore point. Our log file seems to grow very large, and can easily be shrunk down from over 1GB to about 1000K. I did not think that was a bad thing until I started reading through some of the posts on this thread.Thanks for all the information everyone is posting. I am learning a lot.JarrodMon, 07 Jun 2010 13:11:29 GMTjruezhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxPersonal Motto, Don't Shrink the Log unless unexpected transactions occur. Prior DBA had incorporated shrinks into the maintenance jobs, shrink only jobs etc. This left what appeared to be lots of free space. We do quite a bit of batch loading over the weekends from mainframes, the log would grow to 30-40gb and then be shrunk to 5gb after the job. It appears 25-35gb free, so why not create a new database plenty of space... batch job crashes because of lack of disk space for the log file.On the subject of SNAP and NetApp, that is a new discussion that is taking place. Could someone direct me to a good overview of the SNAP with SQL Server and the pros/cons of using it.ThanksWed, 02 Jun 2010 11:54:04 GMTThom_Bolinhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote]bring a $75k+/year DBA[/quote]I don't know which economy you are in. Don't make everybody believe that is what a good DBA is worth. I say $150K. 75 you get a guy who shutdown database and delete that log file. Hahaha.... you have to fire the guy you hired.Wed, 02 Jun 2010 11:51:07 GMTjswong05http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Indianrock (5/29/2010)[/b][hr]Our IT management has talked about "snap" capabilities with our Netapp disk device. This reminds me of my post the other day about "DBA - Phantom job title."[/quote]Exactly - and an interesting thread to read.I would actually be pleased if the snap/clone type technology could be made to work. I would not particularly miss worrying about backups.Sadly, every demonstration I have ever been to has failed to work properly, or at least had at least one glaring flaw.No doubt it can be made to work, to some extent, on certain systems if you have enough money to spend.Maybe one day snap technology will make SQL Server backups a thing of the past - but that day is not today, for most people...Sun, 30 May 2010 00:02:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxOur IT management has talked about "snap" capabilities with our Netapp disk device. This reminds me of my post the other day about "DBA - Phantom job title." It may just be what I'm seeing in my company ( ~300 employees), or perhaps a trend for smaller companies. That is, "DBA by committee" where the various duties a DBA or DBA-team would handle are parceled out to various people, including some who have little if any sql server knowledge.As far as the implications for successful disaster recovery, the thing that works against us is how rarely you actually need to restore from whatever kind of backups you use. Add to that non-technical managers managing technical departments with a constant eye on the budget and where does it lead?If you've already gone 5 years without ever having a need to restore due to failure or corruption, why bring a $75k+/year DBA into a group of employees who have already established work habits and turf to protect ( some of these have found DBAs to be what they consider a road-block to rapid development in the past.Sat, 29 May 2010 13:14:59 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Gary7512 (5/29/2010)[/b][hr]Thanks Paul, I will take note of what you're telling me. It does seem like a good magic solution, but I suppose some fables warn the use of magic for self-gain comes at a price. :unsure:[/quote]Well put ;-)Sat, 29 May 2010 08:49:17 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxThanks Paul, I will take note of what you're telling me. It does seem like a good magic solution, but I suppose some fables warn the use of magic for self-gain comes at a price. :unsure:Sat, 29 May 2010 08:36:08 GMTGary7512http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Gary7512 (5/29/2010)[/b][hr]My colleague who is more technical than me says: "The way it works is that the Hyper-V VSS writer on the host tells VSS in the guest to get ready for a snapshot, so any VSS enabled apps running in the guest (including SQL) do prep themselves. If any VSS writer in any of the guests fails, this causes the entire backup to fail which is reported immediately by WSB (Windows Server Backup)."So Windows is telling SQL to say "cheese" for a photo, so it has several seconds to comb its hair in preparation. i.e. it's being told to put itself into a state where a backup can be made. I feel more confident now this has been explained to me but [b]I do accept we need to test out a restore [/b]to make sure it's working. It's a lot cheaper than testing out the airbags of your car! (The manufacturer tells you the airbags will work but no one ever tests them to make sure.)[/quote]Some higher-end SAN systems provide a similar service, issuing a Freeze & Thaw I/O commands at the appropriate moments. This provides a 'crash consistent' copy of the database - which is not the same as a fully transactionally-consistent backup. There's a whole bunch of complexity around consistency groups and so on.I don't think I am alone amongst DBAs in being inherently sceptical of these 'magic' solutions.Please do test this urgently - and test recovering from a 'snapshot backup' taken when high write activity is occurring - be sure to verify that 'in-flight' transactions are handled properly.Unless the 'magic' backups have some decisive advantage for your business I would personally always choose SQL Server backups.Sat, 29 May 2010 08:19:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Paul White NZ (5/28/2010)[/b][hr][quote][b]Gary7512 (5/28/2010)[/b][hr]The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! :-)[/quote]Do it now! A file backup taken with VSS will almost certainly not enable you to recover any data at all if disaster strikes.A SQL Server backup needs to be [i]transactionally[/i] consistent. A file backup has no way to achieve that.[/quote]My colleague who is more technical than me says: "The way it works is that the Hyper-V VSS writer on the host tells VSS in the guest to get ready for a snapshot, so any VSS enabled apps running in the guest (including SQL) do prep themselves. If any VSS writer in any of the guests fails, this causes the entire backup to fail which is reported immediately by WSB (Windows Server Backup)."So Windows is telling SQL to say "cheese" for a photo, so it has several seconds to comb its hair in preparation. i.e. it's being told to put itself into a state where a backup can be made. I feel more confident now this has been explained to me but [b]I do accept we need to test out a restore [/b]to make sure it's working. It's a lot cheaper than testing out the airbags of your car! (The manufacturer tells you the airbags will work but no one ever tests them to make sure.)Sat, 29 May 2010 07:42:05 GMTGary7512http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]tech 23202 (5/28/2010)[/b][hr]Alternatively Why do I need a transaction file at all?[/quote]Because SQL Server will not run without one :-)The transaction log records absolutely every change made to a database - how ever it occurs, with enough information to undo (rollback) any partially completed operations in case of, say, a power failure.[quote]For example, I have a database of literally millions of records. This database is updated in batch mode once or twice per year. The records are not updated at any other time. All of the rest of the activity in the database consists of reads, yes its just used for reporting. Since I back it up just prior to and immediately after the updates, why do I need transaction logs at all? And is there any way to tell MS/SQL to omit the transaction logging?[/quote]Set the database to READ ONLY. If no changes are possible (because the database is read only), SQL Server will not need to write to the transaction log (which can be as small as you like). Another side benefit is that SQL Server will not take any locks.You will still need a physical log file, but there will be no activity on it (at least until you take it out of read only mode for a batch update).Fri, 28 May 2010 20:08:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Gary7512 (5/28/2010)[/b][hr]The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! :-)[/quote]Do it now! A file backup taken with VSS will almost certainly not enable you to recover any data at all if disaster strikes.A SQL Server backup needs to be [i]transactionally[/i] consistent. A file backup has no way to achieve that.Separate point: once a transaction has committed, you cannot undo it from the transaction log. A complete sequence of log backups from the last full or differential backup is required to enable you to recover to the point of failure.You absolutely must have proper SQL Server backups of your data to recover from a disaster. You should also regularly test that your backup strategy works in practice.Fri, 28 May 2010 20:02:16 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Gary7512 (5/28/2010)[/b][hr]Jeffrey, that's a fair question. The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! :-)[/quote]Oh, definitely need to test - I don't think VSS is going to work well with SQL Server database files. I am not sure about that, but yeah - test it to make sure.Fri, 28 May 2010 17:49:42 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxJeffrey, that's a fair question. The backup uses VSS which takes a shadow copy of the drives and works around the issue of locked files. However, I have not actually tested restoring a sql data file. And now that you mention it I will add it to my list of things to do! :-)Fri, 28 May 2010 17:44:13 GMTGary7512http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Gary7512 (5/28/2010)[/b][hr]Jeffery, sorry I probably didn't explain that well. Our db server is a VM on Hyper-V. The Hyper-V host does an incremental backup using Windows Server Backup of everything including the db VM every 30 mins. In the event of a problem we can restore the entire host or the db VM or just the db files.[/quote]Have you tried restoring the system from these backups? I would be surprised if the database files could actually be restored, since they are locked by SQL Server and performing any kind of snap on the files without freezing SQL Server will not work.Since I don't know what is actually being done and how - I really cannot say whether or not this is appropriate.Fri, 28 May 2010 15:51:33 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxI've found it best to refer to transaction log backups as transaction log/incremental backups. I work with IT folks from many different backgrounds, along with managers with little technical background and it's a constant source of confusion. Since we don't have an official DBA and some of those who confuse the two are senior people not inclined to listen for very long, using the transaction log/incremental backup term helps a bit.Fri, 28 May 2010 15:08:13 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxJeffery, sorry I probably didn't explain that well. Our db server is a VM on Hyper-V. The Hyper-V host does an incremental backup using Windows Server Backup of everything including the db VM every 30 mins. In the event of a problem we can restore the entire host or the db VM or just the db files.Fri, 28 May 2010 14:30:09 GMTGary7512http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Gary7512 (5/28/2010)[/b][hr]Hi Jeffrey. We're using full recovery. We're taking an incremental backup of the entire server every 30 minutes so we can restore from bare metal or just a database file from any point in time. (We've virtualised the db server making backups 10x easier!) So if simple recovery will help keep the log file down we'll give that a shot. Thanks for your advice.Gary.[/quote]There are no incremental backups in SQL Server. There are:Full BackupDifferential BackupTransaction Log BackupI am not sure what you mean by incremental backups. If you are actually performing differential backups, then you are not managing the transaction logs appropriately when in FULL recovery model. When in FULL recovery model, you must perform frequent transaction log backups.If you are running Transaction Log backups - then you are hurting yourself by shrinking the log, since it will just grow as large as it needs to be to handle 30 minutes worth of transactions. If the transaction log is too large, you could increase the frequency to every 15 minutes.Switching to SIMPLE won't reduce the size of the transaction log. It just removes the requirement to backup the transaction log. You won't be able to restore to a point in time, but if you are okay with losing a days worth of data that would be fine.The transaction log will not shrink by itself - so making these changes won't make it smaller. If you make these changes, then you want to perform a one-off shrink to your normal size (see Kimberly's articles on how to size a transaction log). Then, you would monitor the size and once you have a stable size, perform another shrink - and manually grow to a size just larger than the size needed to handle your day to day operations.Fri, 28 May 2010 14:15:46 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxHi Jeffrey. We're using full recovery. We're taking an incremental backup of the entire server every 30 minutes so we can restore from bare metal or just a database file from any point in time. (We've virtualised the db server making backups 10x easier!) So if simple recovery will help keep the log file down we'll give that a shot. Thanks for your advice.Gary.Fri, 28 May 2010 14:00:19 GMTGary7512http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxTech 23202: Simple recovery mode minimizes the amount of data written to the log file and does not allow transaction log backups. I would think this is your best option for the situation you described. As to not needing the log file at all, I don't think I've ever tried to get by without one.Fri, 28 May 2010 12:33:08 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxAlternatively Why do I need a transaction file at all? For example, I have a database of literally millions of records. This database is updated in batch mode once or twice per year. The records are not updated at any other time. All of the rest of the activity in the database consists of reads, yes its just used for reporting. Since I back it up just prior to and immediately after the updates, why do I need transaction logs at all? And is there any way to tell MS/SQL to omit the transaction logging?Fri, 28 May 2010 12:11:37 GMTtech 23202http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Gary7512 (5/28/2010)[/b][hr]Hi. What can someone do if they don't want a big transaction log? Our db is 7GB and the log is 10GB. There's no way we're going to rollback any further than 60 minutes into the past so 95% of the log file is a waste of space, especially when it comes to taking a file backup each night.Everytime I truncate the log down to 1GB it jumps back to 10GB the next day (after the scheduled db backup). The log has 92% space used.The reason we'd never use the log to rollback is because our app is a website which has 1000's of users so we'd never want to undo the good transactions for the sake of 1 user who may have screwed up their data. I've followed various tutorials on truncation and log backups but ours always bloats up to 10GB. Any help would be very useful. Thanks.Gary.[/quote]Is your database in SIMPLE or FULL recovery model? If you can afford to lose up to 30 hours of data in a disaster, then you can use the SIMPLE recovery model and leave the log file alone (after it is sized appropriately).If that is too much risk, then use FULL recovery model and take frequent (every 15 minutes) transaction log backups.Either way, if the log file grows to 10GB - then it needs to be that size. Shrinking the log repeatedly and letting it grow is just causing performance issues and file fragmentation. Not to mention the large number of virtual log files that is going to cause additional performance issues.Read the article I link to in my signature on managing transaction logs. Also, you can review the articles here: [url]http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx[/url]Fri, 28 May 2010 11:33:00 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxHi. What can someone do if they don't want a big transaction log? Our db is 7GB and the log is 10GB. There's no way we're going to rollback any further than 60 minutes into the past so 95% of the log file is a waste of space, especially when it comes to taking a file backup each night.Everytime I truncate the log down to 1GB it jumps back to 10GB the next day (after the scheduled db backup). The log has 92% space used.The reason we'd never use the log to rollback is because our app is a website which has 1000's of users so we'd never want to undo the good transactions for the sake of 1 user who may have screwed up their data. I've followed various tutorials on truncation and log backups but ours always bloats up to 10GB. Any help would be very useful. Thanks.Gary.Fri, 28 May 2010 10:59:25 GMTGary7512http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxThanks for the article.Fri, 28 May 2010 10:15:23 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Jeffrey Williams-493691 (5/28/2010)[/b][hr][quote][b]Kevin Landry (5/28/2010)[/b][hr]Hi Paul,I have also considered the Bulk-Logged option, but I do not see how switching to Simple is any more dangerous than using the Bulk-Logged option?Switching to [u]ANY[/u] other recovery model (including Bulk) from FULL breaks the point-in-time recovery sequence. So using Bulk and Simple are equally "dangerous" in this switching scenario as [u]both[/u] require backups before and after the switch.[/quote]The problem with switching to SIMPLE recovery model is that you break the log chain. Once the log chain is broken, you cannot restore to a point in time past that break. To start transaction log backups again, you have to perform a full or differential backup to reestablish the log chain.Switching to bulk-logged does not break the log chain and you don't have to perform a full or differential to continue backing up the transaction log.Let's say we backup the database every morning at 1am. It takes 2 hours to backup the database - and you have a process in your maintenance plan that rebuilds indexes. In that step, you switch to simple recovery, rebuild the indexes, switch back to full - and then run your backup. Let's also say your rebuild process takes 1 hour, so the plan finishes at 4am every morning.At 9am the following day your system crashes. You have to restore from backups - so you grab the current backup and start to restore and find out that the backup is corrupted. So, you try to go to the previous backup - it's good and restores, so then you start applying the transaction logs to bring you current. You try to apply the transaction log backup that was taken at 4am (right after your current backup) and it fails, because you have broken the log chain.You now have lost 5 hours of data - and that could cripple your business.[/quote]I actually take a FULL before and directly after switching as my backups do not take long so my scenario is not as dire as the one you describe, but I see your point and will switch to Bulk rather than Simple. Thanks everyone for the education.Fri, 28 May 2010 10:11:11 GMTKL7http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxWith a pad. Set the log as large as it will grow with some pad in there in case the data activity is different.Fri, 28 May 2010 10:10:11 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxDon't know why this popped up first with today's date. I enjoyed, but thought I'd comment.I'd like to see more realistic numbers for a production database. Autogrowth at 1 MB can cause poor performance on a database with even moderate activity.Also, I like to set the log size to as large as I expect it to grow between backups.Fri, 28 May 2010 10:03:03 GMTjbutteryhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxJeffrey is right. Don't play with simple mode, unless you are going to run a full backup first, AND make sure you have a second copy off the machine before you make a switch.Mr. Murphy tends to strike when you least expect it. Don't leave a long window for him to do that in by switching to simple mode.Fri, 28 May 2010 09:16:11 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Kevin Landry (5/28/2010)[/b][hr]Hi Paul,I have also considered the Bulk-Logged option, but I do not see how switching to Simple is any more dangerous than using the Bulk-Logged option?Switching to [u]ANY[/u] other recovery model (including Bulk) from FULL breaks the point-in-time recovery sequence. So using Bulk and Simple are equally "dangerous" in this switching scenario as [u]both[/u] require backups before and after the switch.[/quote]The problem with switching to SIMPLE recovery model is that you break the log chain. Once the log chain is broken, you cannot restore to a point in time past that break. To start transaction log backups again, you have to perform a full or differential backup to reestablish the log chain.Switching to bulk-logged does not break the log chain and you don't have to perform a full or differential to continue backing up the transaction log.Let's say we backup the database every morning at 1am. It takes 2 hours to backup the database - and you have a process in your maintenance plan that rebuilds indexes. In that step, you switch to simple recovery, rebuild the indexes, switch back to full - and then run your backup. Let's also say your rebuild process takes 1 hour, so the plan finishes at 4am every morning.At 9am the following day your system crashes. You have to restore from backups - so you grab the current backup and start to restore and find out that the backup is corrupted. So, you try to go to the previous backup - it's good and restores, so then you start applying the transaction logs to bring you current. You try to apply the transaction log backup that was taken at 4am (right after your current backup) and it fails, because you have broken the log chain.You now have lost 5 hours of data - and that could cripple your business.Fri, 28 May 2010 08:48:16 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]James_DBA (1/5/2009)[/b][hr]Yes, as mentioned in my article, there is no performance benefit to multiple log files. It seems the only benefit is file management for disk space and other administrative tasks where you would want the log file in a smaller size (i.e. disk mirroring, or limited hard drive space).[/quote]I wonder how multiple smaller logs could improve manageability in a mirroring environment?Fri, 28 May 2010 07:57:55 GMTo-3463522http://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Indianrock (5/28/2010)[/b][hr]We do have the idata agent installed on a dev box so I guess we'll have to test that and see what it decides to do.[/quote]Yes definitely worth testing.[quote]Moving responsibility for backups/restores/DR to people who refuse to even open Management Studio was done against my advice. :-)[/quote]I am not surprised! There seems to be a small but noticeable trend in that direction, which worries me no end.Fri, 28 May 2010 07:56:36 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxIt would be pretty easy for me to add a switch to bulk-logged before reindexing every Saturday night and switch back to full-recovery afterwards, but I have no idea what commvault might do in that scenario. The differential it started when we manually went to simple recovery for reindexing during a conversion weekend, was unwanted. Our playbook called for a full backup and resumption of log backups at the end of the conversion.We do have the idata agent installed on a dev box so I guess we'll have to test that and see what it decides to do. Moving responsibility for backups/restores/DR to people who refuse to even open Management Studio was done against my advice. :-)Fri, 28 May 2010 07:43:01 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Indianrock (5/28/2010)[/b][hr]Did I understand correctly that if you went to bulk logged ( or simple?) before reindexing, you could then resume full recovery and log backups afterwards without doing anything else?[/quote]Yes! Nice and easy. See these Books Online links:[url=http://msdn.microsoft.com/en-us/library/ms190692.aspx]Backup Under the Bulk-Logged Recovery Model[/url][url=http://technet.microsoft.com/en-us/library/ms190203.aspx]Considerations for Switching from the Full or Bulk-Logged Recovery Model[/url] ...and related pages.[quote]The main concern for us is reindexing following major data loads (conversions), not the normal weekly re-indexing.[/quote]The BULK_LOGGED recovery model was designed to optimise large loads and the related index creations.Normal re-indexing can also benefit, as I hope I have indicated.[quote]One other oddity in our environment is our Systems Team has taken over all backups/restores using the Commvault idata agents. Since they really don't know sql, they use it in "wizard" mode. We saw that switching to simple recovery during the "reindex after conversion" caused Commvault to launch a differential backup on it's own -- it assumed log backups were no longer desired. We really didn't want that backup running at that point in our conversion weekend.[/quote]It seems to be doing the sensible thing - you cannot take a log backup after switching to SIMPLE recovery (without taking a full or possibly a differential backup first).Fri, 28 May 2010 07:36:24 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspxDid I understand correctly that if you went to bulk logged ( or simple?) before reindexing, you could then resume full recovery and log backups afterwards without doing anything else? Or was it: switch to bulk logged, reindex, resume full recovery, take differential and resume log backups?The main concern for us is reindexing following major data loads ( conversions), not the normal weekly reindexing. One other oddity in our environment is our Systems Team has taken over all backups/restores using the Commvault idata agents. Since they really don't know sql, they use it in "wizard" mode. We saw that switching to simple recovery during the "reindex after conversion" caused Commvault to launch a differential backup on it's own -- it assumed log backups were no longer desired. We really didn't want that backup running at that point in our conversion weekend.Fri, 28 May 2010 07:23:47 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic629390-1157-1.aspx[quote][b]Kevin Landry (5/28/2010)[/b][hr]I have also considered the Bulk-Logged option, but I do not see how switching to Simple is any more dangerous than using the Bulk-Logged option?Switching to any other recovery model (including Bulk) from FULL breaks the point-in-time recovery sequence. So using Bulk and Simple are equally "dangerous" in this switching scenario as both require backups before and after the switch.[/quote]The BULK_LOGGED recovery model provides greater protection and is more compatible with other engine features (like log shipping).One the index rebuild is complete, full point-in-time recovery is restored once the second log backup is complete. Although this log backup will include an image of every page changed by a minimally-logged operation, it is still typically a fast operation.By contrast, switching to SIMPLE to perform the index operations requires at least a differential backup. Depending on the number of extents that have changed since the last full backup, this operation may take quite some time (it might even be quicker to perform a full database backup).It is also possible that damage to a database physical file (in an area unaffected by the minimally-logged operations) might prevent a successful differential or full backup from completing, where a log backup (only possible under BULK_LOGGED) would succeed. Recovering a damaged page might well be possible without taking the database off-line (Enterprise only) whereas the SIMPLE scenario would require a full restore sequence in this circumstance.Switching to SIMPLE exposes you to more risk, for a longer time, and is less compatible with other features.Fri, 28 May 2010 07:21:56 GMTPaul White