Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    3

    Unanswered: Torn page detection

    Hi, We recently had one of our drives (RAID5) replaced because of failure. This has resulted in a torn page being detected on one of our smaller SQL 2000 databases. We have one or two larger SQL databases (30-70GB) on the same drive amongst several others and are now concerned that we may have a problem with these databases also. (The original torn page only came to light when a query was run on one of the tables). Is there anyway I can run a procedure on each of the databases to find out if we have any problems with torn pages? I obviously don't want to restore the databases as this will take a substantial amount of time that we just don't have at the moment. Any ideas that could put our minds at rest would be much appreciated.
    Many thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest DBCC CHECKDB.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Posts
    3

    Torn Page Detection

    Hi Pat
    I ran DBCC CHECKDB on the small database before I restored it and it showed nothing wrong. I know for sure that this database was suspect as one of the tables could not be accessed - torn page error - but it still gave no errors on the DBCC CHECKDB. I have checked all the other databases with the same result - no errors. Is there any code that can be used to run a COUNT(*) on all the tables in a database? This would presumably show up if there was a problem in the table structure or is there any other method of checking that the database is sound?
    Thanks for any help, being a 'newbie' it scares the life out of me that there could be problems being stored up for when the databases are used in future (they are not accessed every week, some tables, not even every month, just at certain times of the year).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd start with something like:
    Code:
    EXECUTE sp_msforeachtable 'SELECT Count(*) FROM ?'
    -PatP

  5. #5
    Join Date
    Nov 2002
    Posts
    3

    Torn Page Detection

    Many thanks Pat, just what I want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •