Power Purge issue

RobertWright · Experienced Joined: 13 Feb 2006 Posts: 131

I was trying to do a power purge after having to restore my NMP SQL database and received the following error:

== 6/14/2012 8:53:16 PM ==============================================
Module: ThreadPowerPurge
Source: PowerPurge
PowerPurge encountered an error...

Error:Warning: Fatal error 824 occurred at Jun 14 2012 8:53PM. Note the error and time, and contact your system administrator

== 6/14/2012 8:56:15 PM ==============================================
Module: ThreadPowerPurge
Source: PowerPurge
PowerPurge encountered an error...

Error:The statement has been terminated

Any assistance with this issue will be greatly appreciated.

administrator · Posted: Fri Jun 15, 2012 11:01 am Post subject:

I also received two bug reports from you yesterday that I haven't had time to analyze. However, this error sort of trumps everything else. SQL Server has detected a "torn page", which simply means that it tried to access a page of data that was not written correctly to disk. There are a lot of reasons that this could happen, though it is very rare:

1) Power interruption while a disk write is occurring
2) Failing hard disk or disk controller
3) Inconsistencies in the file system

Here are some links on what it is and how to deal with it:

http://www.sqlserverclub.com/articles/sql-server-msg-824-torn-page-detected.aspx

http://data-base-recovery.blogspot.com/2011/09/how-to-resolve-error-824-in-sql-server.html

http://technet.microsoft.com/en-us/library/ms174425.aspx

If you end up restoring from a backup, the potential exists that the torn page is in your backup as well. SQL Server will try to detect torn pages sooner if torn page detection is enabled (page verify in newer versions).

Regards

administrator · Posted: Fri Jun 15, 2012 2:54 pm Post subject:

I've analyzed the bug reports I received and it does appear that these reports are symptoms of underlying database corruption. You'll need to repair the damage to your database or restore from a good backup in order to reliably continue. I would recommend that you stop using NMP with this database until then as you could further damage your database and get it into a state where you can no longer recover it, assuming it isn't already too late.

Regards

RobertWright · Experienced Joined: 13 Feb 2006 Posts: 131

I think it's already too late. I tried creating a new database but I couldn't maintain a connection for more than two or three minutes without multiple SQL Server errors, such as Communication Link Failure or Name Pipe error. I tried to run a maintenance plan that starts with a database integrity check and it wouldn't run. A virus scan showed no viruses but some PUPs and a rootkit were detected and removed. My desktop wouldn't boot into Windows 7 so I spent time in Safe mode to get Windows to load correctly but I'm still seeing file corruption issues. I figure I'll have to reload Windows to square things away.

Has any work been done with tne new SQL Sever 2012? Thought I'd load it if I'm reloading Windows.

administrator · Posted: Sat Jun 16, 2012 11:32 am Post subject:

I haven't used SQL 2012 as of yet. This will be the last version of SQL that supports OLEDB (from what I've read anyway), so you should be able to connect using any of the connection types that NMP can use as long as you have the appropriate provider installed. I'm not sure that if you only install 2012, that the SQL Native Client 11 will recognize SQL Native Client (SNAC) 10 (2008/2008R2) connections unless you also install the SNAC stack for 2008/2008R2. At some point, I'm sure NMP will natively support SNAC 11, but it doesn't as of today.

NMP supports:

* SQLOLEDB
* SQLNCLI
* SQLNCLI10

If you can connect you should be fine.

Regards

RobertWright · Experienced Joined: 13 Feb 2006 Posts: 131

I was having trouble installing Windows 7 on a new 2 TB HDD so I ran the latest version of MemTest and it immediately detected a bad memory stick. This is the first time in 17 years of working with PCs that I've had a memory stick go bad. I removed it, reinstalled the original HDD, rebooted, and all my troubles disappeared. SQL Maintenance plan ran with no issues. Maintenance sweep in NMP and a Power Purge ran correctly. I had no problems downloading headers and files.

Now I have another decision to make. Do I spend money to buy replacement memory for a three yr old PC or do I put that money towards building a new PC?

administrator · Posted: Mon Jun 18, 2012 1:53 am Post subject:

You should also verify your database to make sure there isn't any permanent damage.

Regards

RobertWright · Experienced Joined: 13 Feb 2006 Posts: 131

My maintenance plan contains the following steps in this order.

Check Database Integrity
Shrink Database
Rebuild Index
Back Up Database (Full)

Will this plan check for permanent damage to my database?

administrator · Posted: Mon Jun 18, 2012 10:42 am Post subject:

It should. Keep in mind that with the latest versions of NMP you no longer need steps 2 and 3. NMP does that as part of a power purge.

Regards

RobertWright · Experienced Joined: 13 Feb 2006 Posts: 131

I had another power purge question. I noticed that if I choose Tools - Purge Trash - Power Purge, nothing happens. If I go down to the status bar and right-click on the trash can to get Purge Deleted Messages - Quick Power Purge to start a Power Purge session. I had 160,000 headers and it took just over 6 hours to go through the deleting the messages and optimizing the database. I'm wondering what might be going on that would cause this to run so long.

administrator · Posted: Mon Jun 25, 2012 10:42 am Post subject:

Not sure. 6 hours is a long time for a Power Purge. However, I did the first thing you tried and the Power Purge ran for me. Should be exactly the same as the second method. Both menus link to the same procedure.

I would suggest reviewing the powerpurge.log file and it should tell you which table(s) caused the long delay. Then you may be able to take a look at your database and see if the data in those tables makes sense. With the problems you were having earlier, you may have built up data in your database that never got deleted properly.

Also, when you upgrade your SQL Server version, are you also upgrading your database to the latest compatibility level, and enabling the newer forms of corruption detection?

Regards