August 7, 2009 at 1:36 pm
GilaMonster (8/7/2009)
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
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 "When grabbing a copy of a production database for testing purposes, alway check this box". Now I have to write up something for the team "Wen grabbing the database write this script."
Life would bee too easay if life was easy. :rolleyes:
ATBCharles Kincaid
August 7, 2009 at 1:44 pm
Charles Kincaid (8/7/2009)
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 "When grabbing a copy of a production database for testing purposes, alway check this box". Now I have to write up something for the team "Wen grabbing the database write this script."
Are you using differential backups? If not, there is no reason to use copy-only on full database backups.
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 2:25 pm
Lynn Pettis (8/7/2009)
talltop (8/7/2009)
Uh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....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.
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...:)
"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 2:51 pm
That'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't
Besides, 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.
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 2:54 pm
GilaMonster (8/7/2009)
Charles Kincaid (8/7/2009)
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 "When grabbing a copy of a production database for testing purposes, always check this box". Now I have to write up something for the team "Wen grabbing the database write this script."Are you using differential backups? If not, there is no reason to use copy-only on full database backups.
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 they 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.
ATBCharles Kincaid
August 7, 2009 at 3:09 pm
Hey 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! 🙂
Jimmy
"I'm still learning the things i thought i knew!"August 7, 2009 at 3:21 pm
imSQrLy (8/7/2009)
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.
This what you looking for?
http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx
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 4:19 pm
talltop (8/7/2009)
Lynn Pettis (8/7/2009)
talltop (8/7/2009)
Uh oh! thread hijack...GSquared, stay on point dude. We are talking about corporate production databases, not automobiles... apples and oranges....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.
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...:)
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.
August 7, 2009 at 9:11 pm
talltop (8/7/2009)
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. 🙂
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.
August 8, 2009 at 2:03 am
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.
good job !
Thanks
Jose
August 8, 2009 at 2:30 am
jose (8/8/2009)
I think I would have detach and attach single fileEXEC 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.
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_LOG
Both 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 - http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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 8, 2009 at 5:22 am
GilaMonster (8/7/2009)
Mark Horninger (8/7/2009)
GilaMonster (8/7/2009)
Bill Whitman (7/14/2008)
A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.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.
the only time I would do that is maybe on a dev server if space is a premium...
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.
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.
August 9, 2009 at 6:08 pm
I think i would have cried then punched the technician for being so Stupid!!!!!!
Great Result !!!!
August 10, 2009 at 8:43 am
The 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.
August 11, 2009 at 12:51 am
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.
Viewing 15 posts - 91 through 105 (of 113 total)
You must be logged in to reply to this topic. Login to reply