InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 17:05:35 GMT20http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Hemantjemmy (8/11/2009)[/b][hr]Hi,I think I have missed two word in above comment which created confusion.What i meant was truncate & shrink transactional log file only after taking full backup.If your database is in Full Recovery Mode, it is good to truncate & shrink transaction log file regularly after full backup.[/quote]I know this an old thread bought back to life but as it was surpriseingly not picked up on before....This is still a bad idea, a very bad idea.Sat, 16 Oct 2010 17:11:12 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Bharathi-902714 (10/18/2009)[/b][hr]I tried to re-produce the scenario and ended up failing :-(When DbLive.mdf is renamed to DbBad.mdf, the database name in the Mgmt Studio does not change??? As a result, I was not able to create another fake DbLive database. Please advice.Regards,Bharathi[/quote]Ther is a differnce between logocal file names nd physical file names. They do not have to be the sames. In fact most of the time they are not. The physical file name typically has an extention (.ndf, .ldf) but the locical name does not.Sat, 16 Oct 2010 15:29:43 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxWOW!Really interesting;)Nice job man;)Sat, 16 Oct 2010 09:12:06 GMTashkan sirooshttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxI tried to re-produce the scenario and ended up failing :-(When DbLive.mdf is renamed to DbBad.mdf, the database name in the Mgmt Studio does not change??? As a result, I was not able to create another fake DbLive database. Please advice.Regards,BharathiSun, 18 Oct 2009 09:20:44 GMTBharathi-902714http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxHi,I think I have missed two word in above comment which created confusion.What i meant was truncate & shrink transactional log file only after taking full backup.If your database is in Full Recovery Mode, it is good to truncate & shrink transaction log file regularly after full backup.Tue, 11 Aug 2009 22:27:07 GMTHemantjemmyhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxHi,I think I have missed two word in above comment which created confusion.What i meant was truncate & shrink transactional log file only after taking full backup.If your database is in Full Recovery Mode, it is good to truncate & shrink transaction log file regularly after full backup.Tue, 11 Aug 2009 22:25:16 GMTHemantjemmyhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxHi,I think I have missed two word in above comment which created confusion.What i meant was truncate & shrink transactional log file only after taking full backup.If your database is in Full Recovery Mode, it is good to truncate & shrink transaction log file regularly after full backup.Tue, 11 Aug 2009 22:23:27 GMTHemantjemmyhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxI am still surprised on how people emit opinions after (hopefully) having read this thread and stating again obviously wrong comments.....Tue, 11 Aug 2009 06:38:11 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Hemantjemmy (8/11/2009)[/b][hr]good story. One more lesson to be learned from above story to add the Maintenance Job of Truncating Transactional log files regularly so that you dont run out of space.[/quote]Bad idea. Very bad idea.Back them up, yes. Truncate them, no.Please read through this - [url=http://www.sqlservercentral.com/articles/64582/]Managing Transaction Logs[/url]Tue, 11 Aug 2009 01:01:18 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxgood story. One more lesson to be learned from above story to add the Maintenance Job of Truncating Transactional log files regularly so that you dont run out of space.Tue, 11 Aug 2009 00:51:40 GMTHemantjemmyhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxThe same happend to me due to the entire array failure where db logs were stored. I had to do the same steps for 69 db's.Mon, 10 Aug 2009 08:43:25 GMTDarek Raczynski-388506http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxI think i would have cried then punched the technician for being so Stupid!!!!!!Great Result !!!!Sun, 09 Aug 2009 18:08:17 GMTdarrinfhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]GilaMonster (8/7/2009)[/b][hr][quote][b]Mark Horninger (8/7/2009)[/b][hr][quote][b]GilaMonster (8/7/2009)[/b][hr][quote][b]Bill Whitman (7/14/2008)[/b][hr]A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.[/quote]Absolutely not. That's terrible advice to be giving.Backup the log, sure, though more often than nightly is usually necessary. Truncate and shrink are not things that should be done to the log on a regular basis, if at all.[/quote]the only time I would do that is maybe on a dev server if space is a premium...[/quote]On a dev server I'd just set all the DBs to Simple recovery. That way there's no need to worry about log backups in the first place.[/quote]the instance I was thinking about had alot of nightly crap that caused the log to expand... then the stuff the next day on the other DB's would run out of disk space.The right answer was to add disk - but it wasn't an option for that client.Sat, 08 Aug 2009 05:22:50 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]jose (8/8/2009)[/b][hr]I think I would have detach and attach single file EXEC sp_detach_db @dbname = 'pubs'EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.[/quote]sp_attach_single_file_db is deprecated in SQL 2005 and will be removed in a future version. The replacement is CREATE DATABASE ... FOR ATTACH_REBUILD_LOGBoth sp_attach_single_file_db and CREATE DATABASE ... FOR ATTACH_REBUILD_LOG require that the database was shut down cleanly. If it was not, the log cannot be rebuilt and an attempt to do so will produce the errors that the article discussed.For more info see - [url]http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/[/url]Sat, 08 Aug 2009 02:30:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxI think I would have detach and attach single file EXEC sp_detach_db @dbname = 'pubs'EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.good job !ThanksJoseSat, 08 Aug 2009 02:03:22 GMTjose-276033http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]talltop (8/7/2009)[/b][hr]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. :)[/quote]The problem is that many of these people are accidental DBA's. They have found themselves doing the job because noone else would. So it is easy to see how it happens. The scary part is that many of these folks don't take initiative to learn more about SQL Server until there is a problem and then everything is an emergency.Fri, 07 Aug 2009 21:11:24 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]talltop (8/7/2009)[/b][hr][quote][b]Lynn Pettis (8/7/2009)[/b][hr][quote][b]talltop (8/7/2009)[/b][hr]Uh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....[/quote]Hate to tell you, but discussion threads here on SSC due tend to go off on tangents at times. Happens all the time and is part of being a SQL Server Communitity, not just a SQL Server Forum. In fact if you carefully read the tagents, you will find them quite enlightening. Plus, they usually come back to the main topic on their own.[/quote]Hmmm.... this is the first forum I have seen that allows thread hijacking. Most forum admins are all over that. For just one example, try that on the SQLMag forums and see what happens.. I have seen people get banned on that forum for consistently doing that...:)[/quote]That is the difference between a Community (SSC) and a Forum (SQLMag). It is one of the reasons I am an active participant on SSC and not on the other forums very often except perhaps as a lurker.I have learned more here because of things going off topic at times then I have anywhere else. It is amazing what little tidbits of information on can pickup if you look.Fri, 07 Aug 2009 16:19:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]imSQrLy (8/7/2009)[/b][hr]I was just checking on the latest posts and noticed a ranking i haven't seen before of "Mr or Mrs. 500". I never paid too much attention to the rankings but this one caught my eye. What does that one mean, and is there a list of all the different rankings. [/quote]This what you looking for? [url]http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx[/url]Fri, 07 Aug 2009 15:21:30 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxHey everyone, I am amazed that my article led to so many posts, even if many have nothing to do with it. I was just checking on the latest posts and noticed a ranking i haven't seen before of "Mr or Mrs. 500". I never paid too much attention to the rankings but this one caught my eye. What does that one mean, and is there a list of all the different rankings. Oh yes do not call me a thread hijacker! :)Fri, 07 Aug 2009 15:09:47 GMTimSQrLyhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]GilaMonster (8/7/2009)[/b][hr][quote][b]Charles Kincaid (8/7/2009)[/b][hr]Rats! Thanks. I had seen a number of articles and posts showing the lovely check box. :pinch: It would have been so nice to be able to put out a memo to the support staff [i]"When grabbing a copy of a production database for testing purposes, always check this box".[/i] Now I have to write up something for the team [i]"Wen grabbing the database write this script."[/i][/quote]Are you using differential backups? If not, there is no reason to use copy-only on full database backups.[/quote]Yep. Full.I like full recovery mode as I've seen it save my butt numerous times like power outages (cuts) and such. I don't want to interfere with client backup procedures. If I take a full backup in full mode and the client is using some third party backup solution now [b][i][u]they[/u][/i][/b] start getting log growth because their solution is not doing log backups.The "copy only" thing lets me leave their stuff alone. I get all the benefits. Good recovery, minimal hassles with backups, small log growth.Fri, 07 Aug 2009 14:54:48 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxThat's SQLMag, not SSC. Thread topics drift here, Steve doesn't intervene unless there are personal insults flying around, which doesn't happen often. Sometimes they drift back on topic, sometimes they don'tBesides, Gus didn't hijack the thread, he made an analogy. It wasn't as if he posted saying something like "Hey, I need help in fixing some problem completely unrelated to the current thread". That kinda thing is better in a new thread, if only cause more people will see it.Fri, 07 Aug 2009 14:51:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Lynn Pettis (8/7/2009)[/b][hr][quote][b]talltop (8/7/2009)[/b][hr]Uh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....[/quote]Hate to tell you, but discussion threads here on SSC due tend to go off on tangents at times. Happens all the time and is part of being a SQL Server Communitity, not just a SQL Server Forum. In fact if you carefully read the tagents, you will find them quite enlightening. Plus, they usually come back to the main topic on their own.[/quote]Hmmm.... this is the first forum I have seen that allows thread hijacking. Most forum admins are all over that. For just one example, try that on the SQLMag forums and see what happens.. I have seen people get banned on that forum for consistently doing that...:)Fri, 07 Aug 2009 14:25:41 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Charles Kincaid (8/7/2009)[/b][hr]Rats! Thanks. I had seen a number of articles and posts showing the lovely check box. :pinch: It would have been so nice to be able to put out a memo to the support staff [i]"When grabbing a copy of a production database for testing purposes, alway check this box".[/i] Now I have to write up something for the team [i]"Wen grabbing the database write this script."[/i][/quote]Are you using differential backups? If not, there is no reason to use copy-only on full database backups.Fri, 07 Aug 2009 13:44:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]GilaMonster (8/7/2009)[/b][hr]The fact that it's not in the GUI doesn't mean it's not an available feature. 2005 has the copy_only feature, but it's missing from the dialog and so can only be used with scripted backups[/quote]Rats! Thanks. I had seen a number of articles and posts showing the lovely check box. :pinch: It would have been so nice to be able to put out a memo to the support staff [i]"When grabbing a copy of a production database for testing purposes, alway check this box".[/i] Now I have to write up something for the team [i]"Wen grabbing the database write this script."[/i]Life would bee too easay if life was easy. :rolleyes:Fri, 07 Aug 2009 13:36:58 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]talltop (8/7/2009)[/b][hr]Uh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....[/quote]Hate to tell you, but discussion threads here on SSC due tend to go off on tangents at times. Happens all the time and is part of being a SQL Server Communitity, not just a SQL Server Forum. In fact if you carefully read the tagents, you will find them quite enlightening. Plus, they usually come back to the main topic on their own.Fri, 07 Aug 2009 13:35:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx... I just took some time to read trough your posts on this site and realize that it indeed seems to be your way of expression, and again, I did not imply that you were attacking anyone in particular, but it seemed to me it was more than disagreeing to a particular post... Anyway, Don't worry and be happy...Oh and you (as well as many others) seem to frequently mix the Forum Name/Handle with the rank.... in your case it would be "Valued Member" ;-)Fri, 07 Aug 2009 11:47:46 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxOld Hand,I was expressing my PERSONAL opinion that I DO consider that elementary type of db ignorance of DBA team members so common as frightening. Particularly, on production databases holding a companies revenue worth possible millions of dollars. This is much different than handling vegetables as far as I am concerned. I was not speaking for anyone else or condemning anyone else. I was just stating my opinion on the matter. I even prefaced this specifically in the prior post by saying " but that is just me.." I was not judging anyone else and apologize if it was taken that way. I still do not see where this can be construed as personally attacking anyone though....forum members are entitled to have an dissenting opinion as long as they are not personally attacking anyone and I still do not think that I was attacking anyone in particular...Disagreeing? Yes. Attacking? No..Fri, 07 Aug 2009 11:31:05 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]talltop (8/7/2009)[/b][hr]Uh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....[/quote]No, you were talking about people being ignorant of things that can have multi-million-dollar costs in aggregate.You apparently consider it shocking and frightening that some DBAs (probably most in my experience) aren't fully familiar with the differences between Simple and Full recovery, why you would pick one over the other, what they do, and what they're for. You mentioned that was frightening.I asked how common ignorance on something that many people deal with daily, with expensive possible side-effects of the ignorance, can be frightening, when it's as common as use of turn-signals. Tens of millions of people drive every day, and I think about six of us use our turn signals regularly. That piece of ignorance/apathy/whatever costs lives, ocassionally, and millions of dollars per year in car repair/replacement.The two are connected by a common thread of people doing things they consider safe and normal, but only because they are ignoring/ignorant of the potential consequences.I didn't make the parable as literal as all that, because I assumed (incorrectly) that you'd be up to the task of drawing the connection yourself.People, DBAs and otherwise, engage in risky and ignorant behavior constantly. If you use doorknobs without using disinfectant every time, you yourself are at great risk, actually. Have you ever rubbed your eyes with your hands without first sanitizing your hands with medical-grade disinfectants? How about eating fresh fruit/vegetables? There are literally millions of such risks that you encounter every day. So, why be shocked at a relatively unimportant one? Why frightened?That's not an excuse for ignorance. I just don't find emotionalizing the reaction has any value. Don't emote about it, just handle it and educate people for next time. Much less stress in life that way, and much more enjoyment.Fri, 07 Aug 2009 11:19:33 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxWe all can disagree... and that is what makes some threads more interesting..maybe my choice of words was not correct.... I did not mean attacking them personally, but their opinions..... read your responses to Gail for example... but then again. maybe that's your way of expressing yourself....Fri, 07 Aug 2009 11:18:42 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxDisagreeing with someone is much different than personally attacking them. Show me anywhere I have attacked anyone personally?Fri, 07 Aug 2009 11:13:11 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]talltop (8/7/2009)[/b][hr]Uh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....[/quote]Umm... we seem to have a little attitude problem here? Most of your posts seem to be overly attacking previous posters opinions.. Cool down dude...:cool: We're all trying to help here and while some post information that might not be accurate most do add bits and pieces of valuable information.Fri, 07 Aug 2009 11:01:02 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Ol'SureHand (8/7/2009)[/b][hr][quote][b]Michael Oberhardt (7/14/2008)[/b][hr]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.[/quote]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.[/quote]Agreed, but if you have SQL 2008 you can inforce a server-wide policy on this and you will be rest assured that any database on all your db servers will not inadvertently have this checked. Again, an ounce of prevention is worth a pound of cure. Yet another one of many reasons to ramp up to SQL 2008 as soon as you can. Travis.Fri, 07 Aug 2009 10:55:25 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxIf you are the one in charge of DB backup and recovery, you better know everyday how much space you still have on each host, on the data drive, log drive and backup drive. I like to keep 30% - 50% so we don't run into emergency. Your boss may object the budget but if s/he does not get a call in the night, s/he better come up with the money.:-PFri, 07 Aug 2009 10:40:15 GMTjswong05http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxFirst thing that came to my mind was to try an 'undelete' utility. Windows only logically releases the disk space. if the physical location of the LDf was not reassigned for other storage, you might get lucky and recover the file.Fri, 07 Aug 2009 10:34:57 GMTRob Garveyhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxUh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....Fri, 07 Aug 2009 10:29:16 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Charles Kincaid (8/7/2009)[/b][hr][quote][b]Richard M (8/7/2009)[/b][hr]Charles, copy-only backups were introduced SQL Server 2005...... See [url=http://msdn.microsoft.com/en-us/library/ms186865%28SQL.90%29.aspx]here[/url][/quote]None of my 2005 SSMS installs have the magic check box. Not even recent Express instsalls. What are we doing wrong?[/quote]The fact that it's not in the GUI doesn't mean it's not an available feature. 2005 has the copy_only feature, but it's missing from the dialog and so can only be used with scripted backupsFri, 07 Aug 2009 10:25:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxYou just need to grab the script and add they keyword COPY_ONLY to it.. IE set up the backup via SSMS and hit Ctrl-Shift-C rather than OK then paste into a query window and add COPY_ONLY.HTH,JamesFri, 07 Aug 2009 10:23:58 GMTJames Moore-280591http://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Charles Kincaid (8/7/2009)[/b][hr]I'm looking forward to the 2008 option of taking "copy only" backups that don't crap on my log chains.[/quote]Maybe I'm misunderstanding you, but if you're talking about full backups, a full backup has never broken the log chain, whether it's normal or copy-only and this goes many versions back.[url]http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx[/url][url]http://www.sqlskills.com/BLOGS/PAUL/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx[/url]Fri, 07 Aug 2009 10:20:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspx[quote][b]Richard M (8/7/2009)[/b][hr]Charles, copy-only backups were introduced SQL Server 2005...... See [url=http://msdn.microsoft.com/en-us/library/ms186865%28SQL.90%29.aspx]here[/url][/quote]None of my 2005 SSMS installs have the magic check box. Not even recent Express instsalls. What are we doing wrong?Fri, 07 Aug 2009 10:15:47 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic533132-1335-1.aspxCharles, copy-only backups were introduced SQL Server 2005...... See [url=http://msdn.microsoft.com/en-us/library/ms186865%28SQL.90%29.aspx]here[/url]Fri, 07 Aug 2009 10:08:27 GMTRichard M.