May 29, 2010 at 8:49 am
Gary7512 (5/29/2010)
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:
Well put 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2010 at 1:15 pm
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." 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.
May 30, 2010 at 12:02 am
Indianrock (5/29/2010)
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."
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...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 2, 2010 at 11:51 am
bring a $75k+/year DBA
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.
Jason
http://dbace.us
😛
June 2, 2010 at 11:54 am
Personal 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.
Thanks
June 7, 2010 at 1:11 pm
the log would grow to 30-40gb and then be shrunk to 5gb after the job
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.
Jarrod
June 15, 2010 at 2:02 pm
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?
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.
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.
As someone pointed out earlier, it's necessary b/c SQL won't work w/o one. You don't "need" it if 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. I 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?
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
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,
Rich
June 15, 2010 at 2:35 pm
Short 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:
June 15, 2010 at 2:39 pm
How 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?
June 15, 2010 at 2:58 pm
Thanks 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.
June 15, 2010 at 3:09 pm
The "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?
June 15, 2010 at 7:21 pm
To add to Steve's good words:
Forget trying to read 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, left unchecked, your transaction log can swell in size and why so many people make the misguided effort to make it smaller. "Misguided" b/c there is 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: http://sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-III-Transaction-Log-Maintenance.aspx. )
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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 that's 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,
Rich
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply