InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 19:17:59 GMT20http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxThanks, Gethyn. :)Thu, 27 Jan 2011 15:04:31 GMTbcbhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]bcb (1/27/2011)[/b][hr]Well everybody, I'm pleased my comment about starting a db maintenance job with DBCC Checkdb inspired such discussion. Just to add some clarification... I have all of my sql agent jobs set to email me if a job fails. An interesting feature of SQL Server is that if a sql agent job has a checkdb step, and the step completes successfully but checkdb discovers corruption, SQL will still quit the job at that point with failure, and I get my email notification. :) (Now, I assume that is still true... I'm not sure because the last time I had db corruption was ten years ago, I believe in SQL 7.0.) Anyhow, hope that clarifies.As an aside...I kinda chuckle when SQLServerCentral calls me grasshopper just because I hardly ever comment on articles. I've been a dba for 17 years.[/quote]BCB thanks for the clarification, that name gets given to you based on the number of posts you make, and number of QODs you get right, not on how long you have been 'doing the job'. Thanks for your contribution and the points you made, they are very informative.Thu, 27 Jan 2011 14:26:06 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxWell everybody, I'm pleased my comment about starting a db maintenance job with DBCC Checkdb inspired such discussion. Just to add some clarification... I have all of my sql agent jobs set to email me if a job fails. An interesting feature of SQL Server is that if a sql agent job has a checkdb step, and the step completes successfully but checkdb discovers corruption, SQL will still quit the job at that point with failure, and I get my email notification. :) (Now, I assume that is still true... I'm not sure because the last time I had db corruption was ten years ago, I believe in SQL 7.0.) Anyhow, hope that clarifies.As an aside...I kinda chuckle when SQLServerCentral calls me grasshopper just because I hardly ever comment on articles. I've been a dba for 17 years.Thu, 27 Jan 2011 13:14:26 GMTbcbhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxHi Abi, I'm pleased you enjoyed the article.Thu, 27 Jan 2011 12:24:16 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxGethyn:Good article, and moreover good discussions. I like the BCB idea of checking database integrity before the backup. I have added Check Database Integrity Task in my daily full database backup plan. Thanks,Thu, 27 Jan 2011 07:50:06 GMTAbi Chapagaihttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxTaggs, please do continue to present your point of view; I find that the most valuable part of almost all really useful articles here are the discussions!In this case, as Gethyn says, there's more than one way, each with benefits and disadvantages. 1) You can stop taking backups after the first sign of corruption; if you immediately notice that, and immediately correct it, and your prior backups are still available, this may well be the best choice.2) You can continue taking backups even after the first sign of corruption; if you do not immediately notice that, particularly if you keep backups for a long time, you later have more choices on backups to restore. If you don't keep backups for a long time, especially if days or weeks of business transactions have occurred since the first sign of corruption, and the corruption is easy to handle (oh... we don't care _that_ much about that index/table/data anyway!), then you can at least still execute a normal restore for other reasons (that disk that caused corruption completely failed (as did another in the raidgroup), etc.), and deal with the corruption later, rather than tell the business "Well, we can restore... to five weeks ago."Thu, 27 Jan 2011 07:30:12 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]Taggs (1/27/2011)[/b][hr]Sorry Gethin you are totally right.I was only trying to be helpful by giving another DBA (and others that read these forums) something for consideration. I think it is probably best I keep my opinions to myself in the future if they just turn into an argument which are not helpful for anyone!Sorry![/quote]Sorry I wasn't intending to argue with you, and your opinion is valid and helpful. I was just trying to explain the benfits of the one approach. With most things in SQL Server there is more than one way to do things, I was merely supporting the point that running checkdb before a backup is a good idea. And discussion is the best way to learn and is helpful to alot of people, so please do stay involved and discuss things.Thu, 27 Jan 2011 07:23:44 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxSorry Gethin you are totally right.I was only trying to be helpful by giving another DBA (and others that read these forums) something for consideration. I think it is probably best I keep my opinions to myself in the future if they just turn into an argument which are not helpful for anyone!Sorry!Thu, 27 Jan 2011 07:09:43 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote]The point I am making to think about is, if you have a corrupt database that will restore at least you have a fighting chance. If you have a corrupt database you can't get in to, no backup because it was stopped due to CheckDB and all good backups have expired/been overwriten/removed. Where do you go from here?[/quote]If your corruption is fixable without data loss or restoring then that's fine. Using Grasshopper technique he will have some confidence that his last backup will be good with no corruption in it. There's not alot of point of restoring a corrupt database with a corrupt database backup, assuming you can get it to restore. When you uncover corruption it needs to be dealt with immediately but its not always possible to fix without data loss. Paul's blog and this article from Gail Shaw http://www.sqlservercentral.com/articles/Corruption/65804/ describe perfectly how deal with a corrupt db and how you fix corruption is entirely dependant on the results of Checkdb.Let's look at it from the other way. You run a backup nightly then run checkdb, checkdb shows up corruption. You follow Gails artilce and it suggests that you restore from a good clean backup, which backup do you use? the one taken just before the checkdb? what if that backup has corruption in it, your restore has fixed nothing and you still have corruption. If then have to restore from the backup from the previous backup, assuming checkdb comlpeted cleanly after it, you can have some confidence that the corruption occured between the running of CHECKDB jobs and you will need to use the older backup. All the while, the time it takes (downtime) to fix is ever increasing.In truth the answer depends on the circumstance, but there is definately some beneift in using Grasshoppers technique.Thu, 27 Jan 2011 06:26:27 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxHi Gethyn,We run Checkdb within another action plan and if problems are reported we look at the database then. I think you may be missing the point I am trying to help put across for Grasshopper to think about. Your last good backup may be from last night (in a good senerio) or you may not have one at all if your backup is not running because of a CheckDB detects an error and stops it running. Consider the two senerios below[b]Good Senerio [/b]This corruption may be one value in a record that is corrupted and easily fixed (as we have had). If you pick this up just before the [i]next[/i] backup you could have lost a days worth of transactions fixing the corruption with a database restore from last night. (may be less if using transaction backups and you can find out when the corruption occured)[b]Bad Senerio[/b]Your database, due to corruption, may be getting further corrupted until the point where it is unrecoverable. You have no backups because they have not run due to CheckDB stopping the backups running. Where do you restore from? You may only keep a couple of days backup for one reason or another or a low expiry (these are all options open to the DBA depending on his/her requirements).The point I am making to [i]think about[/i] is, if you have a corrupt database that will restore at least you have a fighting chance. If you have a corrupt database you can't get in to, no backup because it was stopped due to CheckDB and all good backups have expired/been overwriten/removed. Where do you go from here? Al:)Thu, 27 Jan 2011 04:49:37 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]Taggs (1/27/2011)[/b][hr]You may run into trouble but surely it would be better to have something to try and restore (corrupt or not) rather than nothing?[/quote]The way to recover a from database corruption, is to restore from a 'good' backup. If your database is corrupt and you backup said corrupt database there is no point in trying to restore from that backup. You still have a corrupt database. Which is way the poster suggested running checkdb before the backup so you know, at least have a good idea, that there is no corruption in the backup.There are other ways to fix corrupt databases, but that's a little out of scope of this article. If you want to learn more about corrupt databases checkout Paul Randal's blog here http://www.sqlskills.com/BLOGS/PAUL/category/Corruption.aspx Paul used to manage the Storage Engine Team for SQL Server 2005 and 2008 (I believe) and he is the person who wrote DBCC CHECKDB Thu, 27 Jan 2011 03:44:01 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxYou may run into trouble but surely it would be better to have something to try and restore (corrupt or not) rather than nothing?Thu, 27 Jan 2011 03:15:43 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]Taggs (1/27/2011)[/b][hr]Grasshopper :Surely if your Database is corrupt it is all the more reason to back it up? At least if you have a backup you have something to restore and then try and fix. If the database corrupts further and you don't have a backup to restore you may find yourself in trouble.[/quote]You may run into problems trying to retore a corrupt database. You whould backup your databases regulary and perform checkdb regulary to find any corruption as soon as possibleThu, 27 Jan 2011 02:54:37 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxGrasshopper :Surely if your Database is corrupt it is all the more reason to back it up? At least if you have a backup you have something to restore and then try and fix. If the database corrupts further and you don't have a backup to restore you may find yourself in trouble.Thu, 27 Jan 2011 02:40:35 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxGood information...Another point to make, I had to learn at current employer, is timezone on your SQL Servers. I imagine most DBAs deal with servers within their on timezone but we actually have our servers set to UTC since we go across multiple timezones within the US. Something to be aware of if you may do consulting work or something.I have also heard of guys who setup the maintenance plan but leave the schedule blank. They then use a SQL Agent job and in one of the steps call the maintenance plan when they want it. That way when you are doing a lot of things maybe altering data, you can call that backup maintenance plan more than once to cover yourself. Then also if you have a mix of things you do on your DB that require T-SQL but other things setup easier in maintenance plan, it can give you more flow control within a SQL Agent job. I had never thought about doing it like that when I heard it.Wed, 26 Jan 2011 15:28:47 GMTShawn Meltonhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]Rahul The Dba (1/26/2011)[/b][hr]nice articleThanks[/quote]Thanks Rahul I'm glad you like it.Wed, 26 Jan 2011 13:12:58 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]bcb (1/26/2011)[/b][hr]Nice, Gethyn. But i like to run DBCC Checkdb as first step... If checkdb fails, the job quits and you don't end up backing up a corrupt database.[/quote]Another good point.Wed, 26 Jan 2011 13:12:13 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxnice articleThanksWed, 26 Jan 2011 13:05:55 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxNice, Gethyn. But i like to run DBCC Checkdb as first step... If checkdb fails, the job quits and you don't end up backing up a corrupt database.Wed, 26 Jan 2011 13:00:09 GMTbcbhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxNadrek, Thanks for your comment, this article was meant to be about maintenance/housekeeping of backup files. The points you raise are good points and although its not a 'how to...' article I do have a blog post here http://www.gethynellis.com/2010/10/sql-server-recovery-models.html that looks at the SQL Server recovery models and what you need to do in each.Wed, 26 Jan 2011 11:53:50 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxA good basic article about of two of the four major backup problems I commonly see:1) Not having backups at all2) Not having transaction log backups at all (unlimited log file growth)3) Not cleaning up after your backups (unlimited backup size growth)4) Not restoring your backups as a test 4a) Not doing PITR restores as a testI'd at least put a note about T-Log backups in your article; many accidental DBA's overlook the absolute necessity of the for Full and Bulk-Logged recovery model databases.Wed, 26 Jan 2011 11:18:51 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxjasona.workJust something to think about. [i]Consider [/i]seperating out a database backup maintenance plan. If the plan fails you may end up with no backups at all! :crazy: If you seperate them out tand one plan fails at least you get the other databases backed up.Wed, 26 Jan 2011 08:23:03 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]jasona.work (1/26/2011)[/b][hr]Thank you! I'd not looked at the History Cleanup task, so I didn't know what it might do (yes, I'm rather new to managing an SQL Server, and don't have a mentor)So, I took a look at a maintenance plan I had set up to backup several DBs on a server, and checked the msdb size. 108GB!! So I added in a History cleanup at the end, and will see if that shrinks it down tonight when the plan executes!Thank you from a novice!Jason A.[/quote]Jason, just a word of warning make sure that the cleanup task when deleting 108GB of data does not block other scheduled backups occuring, it maybe an idea to cleanup in smaller chunks instead of one large hit.Wed, 26 Jan 2011 08:15:20 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxThanq for your article.Wed, 26 Jan 2011 08:04:40 GMTphani31http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxThank you! I'd not looked at the History Cleanup task, so I didn't know what it might do (yes, I'm rather new to managing an SQL Server, and don't have a mentor)So, I took a look at a maintenance plan I had set up to backup several DBs on a server, and checked the msdb size. 108GB!! So I added in a History cleanup at the end, and will see if that shrinks it down tonight when the plan executes!Thank you from a novice!Jason A.Wed, 26 Jan 2011 07:39:06 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxNice article!Wed, 26 Jan 2011 07:04:04 GMTcy-dbahttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]Martin Hawley (1/26/2011)[/b][hr]Good article and led me to check our backup plans set up by a contractor. Discovered that there is no clearing of the backup and restore history and msdb has grown from 12Mb to over 3Gb!Thanks for the heads up.[/quote]I'm pleased you found it useful.Wed, 26 Jan 2011 04:01:10 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxYes, We store 2 days SQL backups locally but also copy these backups on to our 3rd party corporate backup system.Wed, 26 Jan 2011 04:00:32 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxGood article and led me to check our backup plans set up by a contractor. Discovered that there is no clearing of the backup and restore history and msdb has grown from 12Mb to over 3Gb!Thanks for the heads up.Wed, 26 Jan 2011 03:34:14 GMTMartin Hawleyhttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspx[quote][b]Taggs (1/26/2011)[/b][hr]I usually perform the clean up before the backups, that way, it will make the most efficient use of disk space and backups are less likely to fail due to lack of space.[/quote]That's a fair point the only thing I would say to that is make sure you don't leave yourself in a situation where you deleted the backup and don't have a new one. (If you only keep one backup)Wed, 26 Jan 2011 02:43:53 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxI usually perform the clean up before the backups, that way, it will make the most efficient use of disk space and backups are less likely to fail due to lack of space.Wed, 26 Jan 2011 02:18:23 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1053641-2895-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Backup+%2f+Restore/72234/">Backup and Housekeeping with Maintenance Plans</A>[/B]Tue, 25 Jan 2011 21:31:56 GMTGRE (Gethyn Ellis)