| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2007-01-08 : 18:28:19
|
| is it good to run Check DB ( intergrity check in maintenance plan) before the full backup everyday?I am running DBCC checkDB command 12:30 am and run the full backup 2:30 am everydayIs it a good plan? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-08 : 18:29:05
|
| It doesn't matter when it runs, except that it should run in a maintenance window or when things are slow-ish.Tara Kizer |
 |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2007-01-08 : 18:45:54
|
quote:
Originally posted by tkizer
It doesn't matter when it runs, except that it should run in a maintenance window or when things are slow-ish.Tara Kizer
Thanks.. i ran it at 12:30 am which is after the business hour.. i only checked "check dataabse integrity" and "include indexes". did NOT check the box that reads "Attempt to repair any minor problems". Is that ok? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-08 : 18:50:46
|
| Yes! You definitely don't want to check that option. That "include indexes" I believe is not for integrity checks, but rather optimizations. How about just running DBCC CHECKDB on your own rather than via a maintenance plan so that you know exactly what it is doing. Check this out:http://weblogs.sqlteam.com/tarad/archive/2004/06/16/1606.aspxTara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-08 : 19:55:41
|
| I do:DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGSto reduce the screen clutter, and if doing it programmatic to make it easier to detect any Real Errors!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-08 : 20:08:40
|
| That's why we run DBCC CHECKDB in Query Analyzer after we've detected a problem.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-08 : 20:15:51
|
| Sorry Tara, I don't follow you.My intention was to just suggest adding "WITH NO_INFOMSGS" otherwise jung1975 will get lots of clutter which may make spotting that there is a real error harder.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-08 : 20:20:57
|
| How does it make it harder? The job will fail if a problem is encountered. You can then view the output plus run DBCC CHECKDB in Query Analyzer to get all of the messages in case job history cuts off.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-08 : 20:26:25
|
| Ah, I think I see.I was meaning when running it in QA to add the WITH NO_INFOMSGS to reduce the screen clutter and just show any errors.We also use WITH NO_INFOMSGS in our Scheduled Sproc and check the output for the normal "0 problems detected" message and treat any other output as an indication that a human needs to check it. When I was setting that up I thought it was harder to parse the output with the informational messages showing up.Personally I think it would be better if the Informational Messages was optional - rather than the default. It may be reassuring to know that its still running, but I think all the clutter is confusing to novices - when I was a novice I was never confident whether that stuff was an indication of errors, or not!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-08 : 20:29:48
|
| Why do you need to parse the output? The job will fail if any problems/errors/issues are detected, regardless of what options you pass DBCC CHECKDB. Aren't you notified of failed jobs?Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-08 : 22:26:52
|
| Didn't know it would cause the job to fail, that's worth knowing, thanks.I do get notification of failed jobs, but this harks back to when I wrote the Sproc to do CHECKDB on all DBs, and I deliberately damaged a DB to make sure that it would be detected ... hence all the parsing. But its so long ago I have no idea whether I actually tried a Job, or just made sure that the SProc detected the broken database - probably only the later, and sounds like I wasted my time!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 00:31:32
|
quote:
Originally posted by Kristen
Didn't know it would cause the job to fail, that's worth knowing, thanks.I do get notification of failed jobs, but this harks back to when I wrote the Sproc to do CHECKDB on all DBs, and I deliberately damaged a DB to make sure that it would be detected ... hence all the parsing. But its so long ago I have no idea whether I actually tried a Job, or just made sure that the SProc detected the broken database - probably only the later, and sounds like I wasted my time!Kristen
 Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-09 : 17:36:50
|
quote:
Originally posted by tkizer
Yes! You definitely don't want to check that option. That "include indexes" I believe is not for integrity checks, but rather optimizations. How about just running DBCC CHECKDB on your own rather than via a maintenance plan so that you know exactly what it is doing. Check this out:http://weblogs.sqlteam.com/tarad/archive/2004/06/16/1606.aspxTara Kizer
Ok, this is going to sound silly  , but, you say to put that particular script into a admin DB (assuming I create one). Ok, um, how do you put that into the DB, like you are suggesting?  Also, if I might add, when you run this script, you should only get feedback from the query analyer if there is a problem?thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 17:43:46
|
| Well once you've created the database, you run the scripts in that database. This just creates the objects. You then need to schedule them if you want them to run on a regular basis. You schedule them through SQL Server Agent.Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-09 : 18:00:47
|
quote:
Originally posted by tkizer
Well once you've created the database, you run the scripts in that database. This just creates the objects. You then need to schedule them if you want them to run on a regular basis. You schedule them through SQL Server Agent.Tara Kizer
Ok. I did the following.Created a new DB (Admin  ), opened up the analyzer, pasted in the script and ran it.The results are that it completed successfully.Is that correct, or did I miss something/do something wrong? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 18:05:04
|
| You've created the objects only. The next step depends what you want to do with them.Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-09 : 18:54:57
|
quote:
Originally posted by tkizer
You've created the objects only. The next step depends what you want to do with them.Tara Kizer
Oh. I sure have a lot to learn because I am still a little lost. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 19:01:38
|
| Well let's say you want to run the isp_DBCC_CHECKDB stored procedure as an ad-hoc query in Query Analyzer.You would type EXEC isp_DBCC_CHECKDB. It would then execute the code which checks for data corruption. If you wanted to run this on a regular basis, then you would schedule EXEC isp_DBCC_CHECKDB to run on a regular basis via SQL Server Agent. Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-10 : 04:40:33
|
| "then you would schedule EXEC isp_DBCC_CHECKDB to run on a regular basis via SQL Server Agent"thecoffeeguy: Don't forget to set up the schedule to mail you when it fails!Kristen |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2007-01-10 : 09:13:22
|
quote:
I deliberately damaged a DB
That sounds cool, Whats the best and most effective way to do the most damage Kristen ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-10 : 12:14:29
|
quote:
Originally posted by Jim77
quote:
I deliberately damaged a DB
That sounds cool, Whats the best and most effective way to do the most damage Kristen ?
Modify the FirstIAM column in sysindexes.Tara Kizer |
|
|
|
Next Page
|
check DB