Memory Dump every 10 minutes

SQL Server Administration
1

Hi,

I think my database is corrupt, please see error below. I've tried to run the suggested commands but it would hang sql then eventually server. Certain queries against this DB will now hang SQL. Running a backup of the database will also hangup SQL. The database is 15GB with a 31GB ldf file. I don't have a good backup. I can't take the database/server down for a long period of time as this is a 24/7 production server. I'm thinking of taking the DB offline and copying the 2 database files to a backup server and attempt to rebuild. However, because the DB is somewhat large, I'm afraid taking it offline may also crash SQL. Does anybody know of a better options to recover from this error?

Thank You

Error: 8646, Severity: 21, State: 1.
2015-07-21 09:14:04.85 spid52 Unable to find index entry in index ID 1, of table 149575571, in database 'msdb'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

2

First let's see what the table name is. On my instance, that is "sysjobhistory", which you could definitely truncate without hurting SQL. Run these commands on your system to get the table name:

USE msdb
SELECT OBJECT_NAME(149575571)

3

Thank You for replying.

The do get a table name of Badger_MinuteSample. This is a table in the database.

4

That's not a critical table for SQL Server. I don't about for your app. But try truncating that table. If that doesn't work, try dropping the table.

5

I'm curious why you have a table with that name in MSDB - some user stuff in a system database?

Just asking in case, per chance, you ran the

SELECT OBJECT_NAME(149575571)

query in a different database (instead of in MSDB)

6

How recent a backup of MSDB do you have? it might be that no new jobs etc. have been created since then, and apart from losing history records it might be OK to restore that.

15GB is very big for MSDB ...

Beware that if you stop the SQL service (to copy the MSDB MDF/LDF files) there is a chance that the database may not come back online when you restart the service.

You could check the health of ALL your databases with DBCC CHECKDB - if the damage is only in MSDB then that won't be as bad as if you also have damage in your Application Databases.

7

The database has 3 tables that has environmental data the we can't lose. I've tried taking the server down to Safe-Mode and copy the mdf/ldf files, it didn't work. Windows would lock up at 1.5 minutes left in the copy, it failed twice both toward the end of copy. I think I may have bad sectors or something in the raid. I'm planning to perform a consistancy check later.

On a newer server, I've created a new database from Generate Script and directed applications to point to this new database.

Thank You

8

You have three Application Tables (for your environmental data) created in the MSDB system database? Or are they in a different database? (Because it seems that the MSDB database is corrupted, and - hopefully :slight_smile: - that's the only one.