Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The Gambler Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 9:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 2,265, Visits: 1,315
Everyone's way is right in their own eyes, but every man pays the price for their lack of vision. If there are protections to take advantage of and one chooses not to take them, their lack of wisdom is only slightly larger then their pride.

Not all gray hairs are Dinosaurs!
Post #1431077
Posted Thursday, March 14, 2013 10:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:48 PM
Points: 18, Visits: 819
TravisDBA (3/14/2013)
Also, to the VLDB issue, you can run DBCC CHECKFILEGROUP on the INDIVIDUAL filegroups (which you should be using on a VLDB anyway) which cuts that VLDB up into smaller more manageable chunks..J


Will checking individual filegroups reduce the 50 hours that he currently needs?
Post #1431122
Posted Thursday, March 14, 2013 11:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
johnbrown105 56149 (3/14/2013)
TravisDBA (3/14/2013)
Also, to the VLDB issue, you can run DBCC CHECKFILEGROUP on the INDIVIDUAL filegroups (which you should be using on a VLDB anyway) which cuts that VLDB up into smaller more manageable chunks..J


Will checking individual filegroups reduce the 50 hours that he currently needs?


The idea of using DBCC CHECKFILEGROUP instead of using DBCC CHECKDB at one time is to use a "divide and conquer" approach to check the integrity of one file group each night of the week, for example. In this way, you address the issue of a DBCC checkdb taking all day to complete and the "sum of the whole" times is probably in most cases going to take a lot less total time. But even if it didn't, let's just say for argument sake, that is still no reason, not to do it at all.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1431158
Posted Thursday, March 14, 2013 1:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 206, Visits: 2,020
A lot of good points here. But there is always the option of running DBCC on a mirror or restored copy of the database. Now this is going to cost money, for the storage, server and license. But here is where the DBA can prove his/her value to the business by identifying the cost, and presenting the information to his/her management.

Post #1431207
Posted Thursday, March 14, 2013 1:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:57 PM
Points: 88, Visits: 714
Hello,
We run DBCC everywhere.... but I guess it's not enough to run it if we don't look at the results.

My group provides outsourcing IT services, and we support over 1,500 databases on 100 serves, across different domains and networks for this single customer.
We use the Maintenance Plans to run DBCC, as part of the standard plans, but I'm pretty sure we don't look at the results.

Does anyone know how to automate the sending of any errors on DBCC?
How can I create a test database that has corruption to test it out?


Thanks

Miguel
Post #1431226
Posted Thursday, March 14, 2013 1:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 1,233, Visits: 2,737
We took over a company some years ago with 100 SQL2000 dbs. CHECKDB was never run. I found two that had minor corruption that would have not allowed them to upgrade to 2005. I got one of them fixed and one I could not. It wasn't a serious issue in MSFTs eyes and it was going away so I didn't bother. I got checkdbs scheduled and low and behold within a few months one failed. I got it fixed but the point was it happened.


Post #1431245
Posted Thursday, March 14, 2013 2:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
MiguelSQL (3/14/2013)How can I create a test database that has corruption to test it out?


Thanks

Miguel


Miguel,

Try one of Paul Randal's corrupt sample databases he uses in his DBCC demos at:

http://www.sqlskills.com/pastconferences.asp



"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1431253
Posted Thursday, March 14, 2013 2:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
Markus (3/14/2013)
We took over a company some years ago with 100 SQL2000 dbs. CHECKDB was never run. I found two that had minor corruption that would have not allowed them to upgrade to 2005. I got one of them fixed and one I could not. It wasn't a serious issue in MSFTs eyes and it was going away so I didn't bother. I got checkdbs scheduled and low and behold within a few months one failed. I got it fixed but the point was it happened.


An ounce of prevention is worth a pound of cure as my grandmother used to say. Expect the best, prepare for the worst. Also, employ jobs/stored procedures that check those SQL Error logs daily like clockwork!!! If there is a integrity problem developing in the early stages, you are likely to see it start to surface there first.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1431260
Posted Thursday, March 14, 2013 6:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 3,122, Visits: 11,401
I run DBCC CHECKDB daily on all databases when I can or at least weekly if the maintenance window is too small to do it daily. I have a stored procedure that performs the checks and sends an email to DBAs if it finds an error, and I set that up as a scheduled job on every server. I always run DBCC CHECKDB on the system databases every day. They are small but critical so there is no reason not to check them every day.

While it is not that common to get errors, I have seen it a number of times. We had an installation of 300+ servers with thousands of databases, and got actual database corruption about once every year or so.

If you do get errors, it’s a sign that you have some kind of hardware issue that you need to address, so ignoring it is a bad move. When you look in the system event log and see thousands of IO errors reported it’s a real bad feeling.

When DBCC CHECKDB takes a long time to complete it’s an indication that the IO throughput on the system is not really adequate for the job. When I setup a new system I do IO testing with SQLIO, and then run DBCC CHECKDB on any existing database that I am moving to the new server. If DBCC CHECKDB runs slower than it did on the old system or even not much faster, I consider that a show stopper and halt conversion until the IO issues are resolved.




Post #1431308
Posted Friday, March 15, 2013 5:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 5:14 AM
Points: 1,345, Visits: 981
Hi all,

@Steve:
You can run physical only, which does some checks, but not all of it.

yes, and we do that on the production server.

The only solution is to really get a good second box and throw some horsepower on it. I'd try to run it every week.

yes, agree, and we have done that. Only problem is that a restore (from disk not from tape) takes 3 days, and DBCC CHECK_DB another 2 days.
That leaves very little time for other use of the box.
My manager thinks that's a little expensive.
So we restore about once a month, and do the check_db most of the times.

The other thing which is a "cheap" check, but is semi-safe for me, is to run a SELECT * from all tables and if they all complete, at least you have all your data accessible.

That would only check the clustered index, and it will also cost a lot of DiskIO and CPU. Not sure if it will upset the cache too much.

@Andrew: Thank you for the links for SQLSkills. Here they are a well-known high quality source of information. I've almost finished watching the 40+ hours of video. It has taken two years because I only have an hour or so every week. But time well-spent.

@TravisDBA: Thank you for the link. It is futher spelled out in the videos. But nice and crisp link.

@Michael Valentine Jones:
sends an email to DBAs if it finds an error

How do you check that you get the email?
I mean; are you sure that you receive all emails?

I do IO testing with SQLIO

We did that too, and it took a long time for the SAN Vendor (HP) to realise that they were not delivering the promised IO with their recommended setup.
"Thin Provisioning" I think they called it. We currently have 9 GB/s. It sure helps on a busy system.


We too use Ola Hallengrens stored procedure "DatabaseIntegrityCheck" at [url=http://ola.hallengren.com/][/url]
It is better than anything else I've seen so far.

Best regards,
Henrik Staun Poulsen
Stovi Software



Post #1431453
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse