November 5, 2006 at 11:04 am
Hi, I'm new to sql server. One of our jobs has the following errors seen in the history logs and log files:
Executed as user: MYLAUNCH\SqlManMusicdb30. Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) IAM page (56:6016902) for object ID 821838240 is incorrect. The table ID on page is 273592213; should be 821838240. The entry in sysindexes may be incorrect or the IAM page may contain an error. [SQLSTATE HY000] (Error 1102). The step failed.
Executed as user: MYLAUNCH\SqlManMusicdb30. Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) The log file for database 'FrameWork' is full. Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002). The step failed.
Can someone please provide some guidance/steps on to resolve this? Thanks a bunch.
November 6, 2006 at 5:16 am
The "Warning: Null value is eliminated by an aggregate or other SET operation." message means you had an aggregate function such as SUM() or AVERAGE() on data that included a null value that was ignored by the function. This is probably not an error, unless your data should not include any nulls.
The IAM (index allocation map) message indicates that a page allocated to one table seems to think it belongs to another table. You can look up the ID values from the message in sysobjects (2000) or sys.tables (2005) to find out which tables are involved. You can use DBCC CHECKTABLE to check the tables, or DBCC CHECKDB to check the entire database. Sometimes these errors are minor and can be repaired with no problems, sometimes you have to use REPAIR_ALLOW_DATA_LOSS (pay close attention to the second half of that word). Read the online documentation about these commands, especially the part about backing up the database before attempting any repair action. Some other good advice in the documentation is to restore an earlier database backup (to a new database, don't overwrite the current one) to see if you can get an uncorrupted copy of the tables. You might be able to merge the backup table with new records from the current table to get a good version of the table.
The "log file is full" message means there is no more room to write to the transaction log file. (If this is the same database as the IAM error, you need to deal with the log issue first.) The cause may be server configuration (need more/larger disks for the transaction logs), or you may not be running transaction log backups. You need to run BACKUP LOG to free up the active log. This will not shrink the actual file, the "active log" is the portion of the log file that has not been released. In extreme cases you might shrink other log files on the same disk to make room, or add a second log file on another disk to the database.
If the recovery mode of your databases is FULL or BULK_LOGGED then you need to regularly run a transaction log backup maintenance plan. If you don't, the transaction logs keep growing until you get this error. You can set the recovery mode of all database to SIMPLE to prevent this, but in case of disaster you have to go back to the last full backup. With a regular full backup and more frequent log backups, you can restore the database to any point in time (for example, one minute before somebody said "oops!").
November 6, 2006 at 10:22 am
Thanks Scott for getting me started. The problem is we've inherited this system and unfortunately there were no backups. Can I still get away with using DBCC?
November 6, 2006 at 10:52 am
Even if you don't have previous backups, backup what you have before proceeding. You could also try to copy whatever you can get out of the the corrupt tables using SELECT * INTO otherdb.dbo.tablecopy FROM table, or maybe BCP table OUT filename -c -T (that's a command-line utility to dump to a text file, not a T-SQL command).
DBCC CHECKDB ('<database name>') will check the database without modifying anything. It may tell you what level of repair is required.
DBCC CHECKDB ('<database name>') WITH REPAIR_FAST will fix minor errors and "should" not cause any data loss.
DBCC CHECKDB ('<database name>') WITH REPAIR_ALLOW_DATA_LOSS will fix allocation errors, but you may lose data. You can't predict how much damage it might do.
Either repair option will make all the table structures and allocation maps consistent, but if data was lost then there may be violations of foreign keys or other constraints in the remaining data. DBCC CHECKCONSTRAINTS ('<table name>') will find thse problems. It won't fix them, that is left as an exercise for the student.
You haven't said anything about how valueable the data is. If your goal is to just get the #$(&% thing working and blame the data loss on the jerks you inheirited it from, then repair away. If someone is going to jail if you don't recover the data, there are undocumented DBCC commands that can dump the contents of individual pages and let you try to extract the corrupted data. This would be time consuming and difficult for a beginner, you might try finding a consultant if you think it would be worthwhile.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply