August 7, 2009 at 6:44 am
Whooaaa! really? Well maybe it is just me but if I had someone on my DBA maintenance team/rotation that did not know what the different db recovery models were then I would start reevaluating my technical DBA interview skills real fast 🙂 I don't care if a developer knows what they are but if you are on the team and can change the db recovery model on any database and don't know what they are, then that is a problem..But maybe that is just me. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
August 7, 2009 at 6:53 am
Have a browse through the backup and admin forums here. It's kinda scary. The 'I'm in full recovery, what's a log backup, why's my log filling the drive' question comes up so often I wrote an article cause I was so sick of answering it.
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
August 7, 2009 at 6:58 am
I'll take your word for it Gail, but I must say with billions of dollars of corporations revenue on SQL Server databases nowadays this is truly a scary thing to even think about.....
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
August 7, 2009 at 7:06 am
I will admit that I shrink the log file at times. Whenever I restore production to one of our training or development servers after changing the database to the simple recovery model. No need to have the log file the size it exists in production as the number of transactions is MUCH lower.
August 7, 2009 at 7:32 am
Mark Horninger (7/14/2008)
Bill Whitman (7/14/2008)
oberhardt is correct.A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.
Probably don't want to shrink the log unless you absolutely have to..
Like, say, when it's 100GB in size?
August 7, 2009 at 7:47 am
When I became the involuntary DBA, I had a very nebulous idea about the difference between SIMPLE & FULL recovery modes. I inherited 5 servers where agent jobs were backing up and truncating logs every night, and no transaction logs were backed up. Some of the databases were in simple recovery mode, but most were not, which defeated the purpose of truncate even if the command had worked (it has a nasty habit of not flagging an error).
I got worried when I noticed logs were growing out of control - as they would without a transaction log backup.
It was Gail's article that set me right and sent me off to do some learning.
I also reviewed the backup policy so 2 databases are in FULL recovery mode with hourly transaction log backups. I also practiced a restore to a couple of points so that I am not taken by surprise if it becomes necessary.
Now I check every new database that's created, moved, restored as soon as I catch it.
Invariably the developers and 3rd party installers leave the DB in FULL recovery mode so I change it to SIMPLE and ask if they want point-in-time recovery by any chance.
Oh yes, and I removed all the jobs that truncated logs. I would never advise others to truncate except occasionally when re-growth is not anticipated.
If Gail hadn't stepped in, I would have posted an injunction against that practice!
August 7, 2009 at 7:56 am
sknox (8/7/2009)
Mark Horninger (7/14/2008)
Bill Whitman (7/14/2008)
oberhardt is correct.A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.
Probably don't want to shrink the log unless you absolutely have to..
Like, say, when it's 100GB in size?
Not necessarily. Last production database i worked with directly had a log set to 250GB and the DB used that space during the overnight jobs. If your database transaction rate and size and log backup frequency are such that the log needs to be 100GB, even if it is just for portions of the day, leave it at 100GB. There's no cost in having a large log file with lots of space unused.
If it's reached 100GB through an unusual, once-off transaction and it's highly unlikely to ever need to be that big again, sure, do a once-off shrink and drop it to the size that it does need to be.
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
August 7, 2009 at 7:57 am
Michael Oberhardt (7/14/2008)
I've had a few issues with rogue transaction logs as well (on MSDE at any rate). Even on autoshrink I've had them grow to over 10GB, and didn't even notice it.
Another thing I learned on this forum: never leave a DB in autoshrink mode.
On MSDE it doesn't matter ... if you're the only one using it. But production databases will suffer a huge performance hit, and fragmentation will soon kill them off.
August 7, 2009 at 8:15 am
very helpfull article
August 7, 2009 at 8:30 am
WOW....WOW.....
Discussion board is much more interesting and knowledgeable than the article. Rarely we come across a thread where discussion lasts so long. Truly a good thread to start a day.
And also a good article.
SQL DBA.
August 7, 2009 at 8:58 am
talltop (8/7/2009)
I'll take your word for it Gail, but I must say with billions of dollars of corporations revenue on SQL Server databases nowadays this is truly a scary thing to even think about.....
Why is that any more frightening or surprising than the fact that most drivers don't seem to know that their cars are equipped with turn signals?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2009 at 9:00 am
You don't have to detach the database to delete the log file, just shut down the sql server service. Then the log file is like any other file, as long as the person logged in to the box has the rights to it (and to shutting down the service).
August 7, 2009 at 10:01 am
Glen Moffitt (8/7/2009)
You don't have to detach the database to delete the log file, just shut down the sql server service. Then the log file is like any other file, as long as the person logged in to the box has the rights to it (and to shutting down the service).
Oh sure. An if you boot with a recovery floppy you can delete just about any file on the server. It's the consequences that will come back to bite you. We had a tech that did this exact thing on SQL 2000 before calling me. The reason that he called was that the SQL Server instance would not start! We found a backup from 10 days prior and restored that. Data loss resulted.
The fact that SQL 2005 lets you get away with deleting log files on a stopped instance and then pretending that everything is OK on restart leads to bad behavior.
Only one of the bad consequences is that it sticks the new log file in the default location. This is usually the system drive. So your run-away log file filled up the data partition (which is huge) and now you have the thing eating drive space like a cancer on my tiny OS partition? OK then you get to fix it we the server locks up and can't boot.
I'm looking forward to the 2008 option of taking "copy only" backups that don't crap on my log chains. If the cost of migrating to 2008 is only the instance installation (no DB changes) then this one feature might be enough to warrant the cost. Certainly should be on the list to upper management. "Here. By doing this we reduce the chance that one of our junior members could CAUSE downtime and data loss making us all look like the idiots we probably are."
ATBCharles Kincaid
August 7, 2009 at 10:07 am
Charles,
Copy-only backups were around in SQL Server 2005 (http://msdn.microsoft.com/en-us/library/ms191495(SQL.90).aspx) or is there some other new functionality I have missed?
[Edit to make link]
James
--
James Moore
Red Gate Software Ltd
Viewing 15 posts - 61 through 75 (of 113 total)
You must be logged in to reply to this topic. Login to reply