Tag Info

Hot answers tagged

11

Yes, there is a suspect_pages table. Here is more good info on DBCC CHECKDB http://blogs.msdn.com/b/cindygross/archive/2010/06/13/dbcc-checkdb-database-integrity.aspx


10

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query. The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when ...


8

The following is a compilation of results that I read up on. You will find vastly more information in the linked blogs and documents. First, it can happen that DBCC CHECKDB won't detect inconsistencies if you turn off checksum or torn_page verification. A quote from Paul Randal in this post: You're right - if torn-page or checksum isn't turned on then ...


8

If the sequence is: DBCC CHECKDB reported errors. REPAIR_REBUILD fixed the database, no errors reported. DBCC CHECKDB reporting errors again. You probably have failed or failing disks in an array or some other component of the IO subsystem is broken, or breaking. Personally, I'd want off the problem server ASAP! Take a tail-log backup. Restore last ...


7

You cannot repair this database, and the repair you've potentially made has not been complete. You should make sure to understand why your database is becoming corrupt - check your disk system as that's the most likely culprit. Then restore your database from a backup - but make sure to run DBCC CHECKDB on that very backup to ensure it's not corrupt itself. ...


7

First, why did you use these tools instead of simply restoring from a backup? My guess is, you didn't have a backup, or you didn't have one that was recent enough to be usable. Okay, we'll deal with that later. In the meantime, if I were in your shoes, I would update my resume and sprinkle fairy dust all over it create a new, empty database, and start ...


6

Have you tried what Paul Randal recommends? Creating, detaching, re-attaching, and fixing a suspect database Create a new dummy database with the exact same file layout and as close as possible to the file sizes of the detached database Shutdown SQL Server Swap the corrupt database files Re-start SQL Server Use emergency-mode repair


6

Are there other processes operating on the same tables? Indexes being rebuilt? If so, then you could be hitting the situation that Jonathan Lewis describes here: http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/


6

Your database is corrupted. Time to restore from a good backup. If you have no backups, is time to learn about The Importance of Testing your Recovery Plan. An investigation into the reliability of your hardware is also warranted, watch for warning and errors in the system logs.


6

As you have an old backup with the correct schema, the problem database is online and you've successfully queried several tables, I'd be inclined to try get a dump of the raw data as fast as possible. Shut down the applications accessing the database. BCP export the data, table by table to files. As disk corruption could be the source of your woes, export ...


5

I finally found how to do it. I've found some very old backups. The data changed meanwhile, and it was important to have an up-to-date version of this data, which meant that it wasn't possible to just restore the old backup. Instead, I did the following, thanks to Michael Eklöf on ServerFault: Copy the current .mdf and .ldf files. List the filegroups of ...


5

I do know a consultant who does this kind of work, but it's nowhere near cheap - think mid-five-figures to start the project, paid in advance. It's not for the faint of heart and you only want to go that route as a last resort.


5

I ran into a similar issue. However, I was unable to get the database out of suspect as the backups were suspect as well. I used the exporting functionality in SSMS to move the tables and data to new blank database. This however does not move all of your stored procedures, views, functions, etc. I have a copy of RedGate's SQL Compare (which I highly ...


5

PostgreSQL prior to 9.3 did not have block checksums. The feature was added in 9.3 (long after this question was posted). To answer your needs, I would probably develop my own checksumming (triggers?) - working on attribute values, not data pages.


5

When a database is detached, it should shut down cleanly. This file has been shadow copied or has been taken from a crashed SQL Server Instance or such. That is, the MDF is not from a cleanly detached, shutdown database. So, possible solutions.. I don't think this will work thought but please check. Try SP_ATTACH_SINGLE_FILE_DB from a query window. It is ...


5

You may not need to restore from backup just yet. As a sysadmin, execute DBCC PAGE (2, 5, 65424, 3). Look for the Metadata: IndexId value. If it is 0 (heap) or 1 (clustered index) you need to restore from backup. If it is not 0 or 1, you can rebuild the index. Run that command, and let me know what you find. Hope this helps, Matt


5

I can only speak to MS SQL Server not MySQL I'm afraid. SQL Server breaks up a large database into small pieces called PAGES that are 8k in size. There are a couple of advantages here. With a large text file any time you make a chance you re-write the entire file. With a SQL Server when you make changes you only write down the PAGES that have changed. ...


5

If your log file is corrupt, my concern is that a backup/restore would retain the corruption. My approach (which would probably be faster to complete) would be: Run a full backup. Detach database. Delete/rename the log file. Attach the database and rebuild the log file. Take a second full backup. To attach the database and rebuild the log file, it's ...


4

I would say to try to use a different storage engine like InnoDB. It sounds like you were using MyISAM which isn't crash safe. If you want to continue using MyISAM and it crashes in the future, you can fix it with an external program called myisamchk. http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html myisamchk --force --key_buffer_size=512M ...


4

First up, well done for trying to get to grips with how WAL works. It's often misunderstood but when people grasp the concept it's a genuine lightbulb moment for understanding databases. We need to re-word your description of the steps that occur to commit a transaction: Database documents the change in the write-ahead log (WAL). Database updates the ...


4

The very first thing you should do when you encounter database corruption is to capture a filesystem copy of the data directory tree while PostgreSQL is not running and save it somewhere safe. Once you have done that, if you do not have a backup which you can restore you might want to try pg_resetxlog. Note that the documentation has this recommendation: ...


4

UPDATE: it looks like this is a bug in the Debian/Ubuntu packaging of PostgreSQL, where the init scripts - extremely unsafely - kill -9 the postmaster and remove postmaster.pid. See this post on pgsql-general. See: https://bugs.launchpad.net/debian/+source/postgresql-common/+bug/1042556 http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=686060 ...


4

Your database is corrupt. The good news is that this is the tempdb database (database ID 2) so fixing this is easy, just restart the database instance. Now you have a bigger problem, figuring out why it happened. Start by running chkdsk on the drive that the problem file is on. Next you'll need to look at the storage itself and see if there's any ...


4

Here's a short step-by-step walkthrough: disconnect users and disable incoming connections to the database make a copy of database file (or two copies) and work on that use GFIX with -v option to validate the database file use GFIX with -v and -f to do full validation If problem is not too serious, you can try to backup the broken db and restore under a ...


4

ASPECT #1 The first thing that caught my eye was this line InnoDB: Error: trying to load index PRIMARY for table / This indicates you have a table using the InnoDB Storage Engine What is interesting about InnoDB is the way a PRIMARY KEY is stored. It is stored in a structure called the gen_clust_index, or more commonly known as the Clustered Index. ...


4

Do not take the database offline. Also do not shut down the server. Either might make the database totally inaccessible. Take a full "copy only" backup. Try to read the backup file with Red-Gate datacompare use this query with the allocation_unit_id values you have in the checkdb output: SELECT o.name,p.index_id,p.partition_number FROM ...


4

Further investigation shows that this is a bug in DBCC CHECKDB. A Microsoft Connect bug has been opened: Unfixable DBCC CHECKDB error (that is also a false positive and otherwise strange). Fortunately, I was able to produce a repro so that the bug can be found and fixed. The bug can be hidden by playing with the database schema. Deleting an unrelated ...


4

You should restore it from your backup (which you can do by right clicking on "Databases" and selecting restore database)


4

the error is myfile.mdf is not a primary database file. It means the original database had more files. You need to attach all files, including the log (*.ldf) and secondary files. Usually these are named *.ndf but in your case they appear to had been named *.mdf, like the primary. It can also happen that you renamed files during your 'recovery'. If ...


3

To clear the WAL files, see pg_resetxlog. The data directory on Ubuntu 12 should be /var/lib/postgresql/9.1/main Note that pg_resetxlog is located in /usr/lib/postgresql/9.1/bin, not in /usr/bin, so it's not necessarily in $PATH. Also it should be run as the postgres user. To clear and recreate the entire cluster if you don't care about the data, run: ...



Only top voted, non community-wiki answers of a minimum length are eligible