Tag Info

Hot answers tagged

9

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

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 ...


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 ...


6

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. ...


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/


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

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.


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

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 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. ...


4

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 ...


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

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 ...


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. ...


3

Rebooting temporarily clears the problem, and the error moves around... this strongly points to defective system RAM causing corruption of the OS cache. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. You likely already know this part, but with ...


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: ...


3

If you have a recent backup, consider using that. Otherwise, before you do anything else, make a copy of the directories containing the data you wish to recover. You may wish to use http://www.forensicswiki.org/wiki/Ddrescue to make a very low-level copy of the partition. It works in a way that will tolerate faults in the underlying storage medium. From ...


3

There are those rare times when an InnoDB table gets stuck in the ibdata1 deeply that you must try forecing mysql to start up in spite of it. Try using innodb_force_recovery in /etc/my.cnf (Option values 1-6) [mysqld] innodb_force_recovery = 1 then restart mysql. This should at least allow a mysqldump mysqldump -u... -p... --routines --triggers ...


3

Paul Randal has several Sample corrupt databases to play with: DemoDataPurity - 192-MB SQL Server 2005 database with a single 2570 (data purity) error DemoFatalCorruption1 - 1-MB SQL Server 2005 database with a corrupt system table (that allows CHECKDB to complete) DemoFatalCorruption2 - 1-MB SQL Server 2005 database with a corrupt system table (that ...


3

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 ...


3

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 ...


2

Paolo, can you please provide the mysql error log? If one isn't available, you can add one by adding this line to your my.cnf file [mysqld] log-error=/var/log/mysql/mysql.err One possible idea to narrow down your research. You already know how to remove the ibdata and iblog files to create new ones. If you just want to test your mysql installation. ...


2

See the Oracle Database Backup and Recovery Users Guide. In particular the section titled Recovering After Losing of All Members of an Online Redo Log Group. Your course of action will depend on other factors you can read about in the documentation.



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