cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Support told me "truncate" the tables „APM_ComponentStatus_Detail“ and „APM_PortEvidence_Detail“ and now historic data for all SAM components is gone

Hey there Thwacksters,

I am baffled right now. First some context:

We had a lot of issues with solarwinds orion right from the start but recently it was running pretty smoothly. Except that the additional website kept breaking for some reason.

So I went ahead an openend a ticket, to resolve the issue. I don't like opening tickets with solarwinds support because so far I did not really have a good experience with them. Most of the troubleshooting tips are "revert retention settings" and things like "add more polling engines". I already know these things but some things just are not easily possible. Reverting retention settings is just straight up one of the dumbest things ever. I don't do that for fun, you know? Anyway...

After a few days I got a response from solarwinds support with a lot of steps to try. I have to admit, I was surprised. Usually I do not get such a long list of things to try and fix it. So I went to work and tried the things that were easy to do.

One of them was the following:

  1. Orion DB is not in good health as automatic maintenance not working 100% due to Environmental and SQL server performance issues and Large Tables for - this literally is the NO#1 reason for overall performance. 

Table Name

Row Count

Size (kb)

APM_ComponentStatus_Detail

539410343

33009912

APM_PortEvidence_Detail

508330163

35911216

  Perform the steps in this KB on table above
https://support.solarwinds.com/Success_Center/Server_Application_Monitor_(SAM)/Knowledgebase_Article...

2018-08-13 03:06:33,061 [1] ERROR DatabaseMaintenanceGui.Program - Database Maintenance Error.
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I am not a database guy, I have no idea what "truncate" does. I did not do any research on what it does, because I would not expect solarwinds support to straight up tell me to delete literally gigabytes of valuable data. That is not a thing you do with monitoring.And not a single mention anywhere, that I will lose any of my data.

Oh boy, was I wrong.

When I came in today I wondered why some of my graphs are empty. I checked some more...all of them empty. The only data in there is from after I executed the "truncate" command. Then I realized what happened and I googled, what "truncate" does:

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

Apparently the solarwinds support does not care about historic data. The problem is: I do. My colleagues do. My bosses do. My customers do.

And the worst thing about this is that I think that the support guy did not even understand what he was doing. If I understand the KB article correctly you can truncate the Hardware tables listed in the KB, because there is no useful data anyway. It just takes up space and ressources and that's why you can truncate these tables. I do not think you should truncate any of the other tables, because they contain important data.

What do you guys think about this? Am I in the wrong for just doing what support said, or am I correct in my assumption, that the support guy got the KB article completely wrong? What do I do now? Anyone else with a similar experience?

I am just really disappointed and I can not believe, that the solarwinds support would tell you to delete data without a warning. deleting production data should be the very last step if nothing else helps.

0 Kudos
3 Replies
Level 12

If you need the missing data then restore from the SQL backup you took prior to your truncate commands. You should have one running each night/early morning? And definitely would suggest an additional backup to restore to prior to any kind of DB changes.

0 Kudos
Level 9

Hi... an advice: you must do a database backup or a rollback plan every time you do something related to the DB...

0 Kudos
Level 13

I was a DBA in a previous life, and I still occasionally cosplay one at work. You most definitely should have been warned that the TRUNCATE command would wipe out all the data in those two tables, and you should have been instructed to back up those two tables (and the whole database) before executing the command.

What database backup solution do you use? Some tools allow you to restore single tables. Otherwise, the only way to get that data back is to restore the database to a new location, then use your favorite data transfer tool to copy the data from those two tables in your restored database back into those two tables in your production database.

0 Kudos