BESDeletedComputerRemover and IsDeleted=1

(imported topic written by bobk91)

Recently we ran BESDeletedCompRemover.exe on BFEnterprise, after a long period of not running it. We currently run BESExpiredComputerRemover twice a day with a 30-day period, with no issues. So BESDeletedCompRemover.exe was then run once, but it looks like it terminated or was terminated for some reason. For that first run it deleted a large number of rows from the actionresults and questionresults tables, but it wasn’t able to complete (or it was killed by someone externally). So we ran it again the next day, and it then did the fixletresults tables, but afterwards the number of rows in COMPUTERS with IsDeleted=1 still seems abnormally high, roughly the same as it was before we ran it the first time.

  • Should the rows with IsDeleted=1 in COMPUTERS also have been deleted by the second run?

  • Assuming yes, then could the fact that it got aborted the first time somehow be the cause of this?

run log-----------------------------------------------------

START: BESDeletedCompRemover_6_0 v.1_1_6_0 started at: Wed Aug 29 08:51:20 2007

BES Computer Removal From Database started

There are 87605 computers that may be deleted. Do you want to remove them from the database? (Yes/No) Deleting action results… this may take a while…

Deleted 5684042 action results records.

Deleting question results… this may take a while…

Deleted 15647725 question results records.

Deleting fixlet results… this may take a while…

START: BESDeletedCompRemover_6_0 v.1_1_6_0 started at: Thu Aug 30 02:00:05 2007

BES Computer Removal From Database started

There are 87645 computers that may be deleted. Do you want to remove them from the database? (Yes/No) Deleting action results… this may take a while…

Deleted 27859 action results records.

Deleting question results… this may take a while…

Deleted 52799 question results records.

Deleting fixlet results… this may take a while…

Deleted 32849970 fixlet results records.

Computer removal completed. A total of 32930628 result rows were deleted from the database.

END: BESDeletedCompRemover_6_0 completed at: Thu Aug 30 02:28:05 2007

check

select count(*) from QUESTIONRESULTS where ComputerID in (select ComputerID from COMPUTERS where (ISDeleted=1))

select count(*) from ACTIONRESULTS where ComputerID in (select ComputerID from COMPUTERS where (ISDeleted=1))

select count(*) from FIXLETRESULTS where ComputerID in (select ComputerID from COMPUTERS where (ISDeleted=1))

select count(*) from COMPUTERS where (ISDeleted=1)

58321 (ok)

31519 (ok)

144610 (ok)

109169 (not ok or we are missing something)

(imported comment written by SystemAdmin)

Hey Bobk,

The tool actually doesn’t remove the rows from the COMPUTERS table. It basically leaves one row per computer in this table so that if the computer comes back it recognizes the old entry for that computer and adds new data back to the existing computer. The main reason for doing this is that the BES Console and Web Reports don’t know how to handle rows being deleted from the COMPUTERS table. They will basically not notice that the row is gone and continue to display all the data on that computer until the cache is refreshed. If we simply mark that one row with ISDeleted=1, they will notice that the computer is gone and discard its data.

(imported comment written by SystemAdmin)

Oh, also, it shouldn’t cause any problems to have the tool interrupted half way through. It uses the row in the computers table to search for QUESTION/FIXLET/ACTION RESULTS to delete and since that row is left behind it will basically pick up where it left off on subsequent runes.

(imported comment written by bobk91)

Thanks. So to restate your answer, having all these IsDeleted=1 rows in the COMPUTERS table, even after running BESDeletedCompRemover.exe, is normal and expected and there is nothing to be done about it - that is the final end state for a computer’s data within our BES install and those remaining rows will be in COMPUTERS forever.

So then given that, when you run the tool each time and the tool says “There are 1234 computers that may be deleted” how does it come up with that number? It can’t be counting rows with IsDeleted=1. Does it look at one of the results tables like ACTIONRESULTS to see for how many rows in COMPUTERS, do rows still exist in ACTIONRESULTS?. Reason being we ran it today again, and it only said ~300 computers may be deleted, so it must be looking at something to know what’s “newly deleted”.

Some feedback that I would provide on the tool would be that a) it should have a optional preview mode, where it tells you what it is going to do (how many rows etc for each table) without actually doing anything, and it should b) also have an c-l option where you can specify the number of computers to be deleted for this run (it would just use “top nnn” or “set rowcount nnn” as appropriate internally). That would be good for people who have a large number of rows to delete but don’t want to do them all at once for locking or other reasons.

Thanks

(imported comment written by SystemAdmin)

Yes, we leave one row in the COMPUTERS table forever for each computer. This is to allow BES Consoles to synchronize themselves with the current state of the database and discard results if computers have been deleted when they weren’t running.

When it tells you have many computers may be deleted it does look for data in the tables to see if any exists and gives you a total based on that. So, if a row exists in the computers table with IsDeleted=1 but all of its data has already been deleted then it won’t be counted in this total.

Feedback:

a) You can use the -c option to count rows prior to deletion. If you don’t want to delete anything, it will prompt you prior to deletion and you can abort. See the help file for details, -h commandline switch or (http://support.bigfix.com/bes/install/BESDeletedCompRemover_help.txt).

b) The easiest way to do this is to just set the expiration period back further in time so that fewer computers are deleted. Let it run once at this longer time period, than bring it closer to the current time, repeat. This way you’ll delete some subset of total number of computers you want to delete each time you run it. I’ll look into doing an explicit batched deletion operation but I think we avoided this for some reason.

(imported comment written by SystemAdmin)

Oh, it looks like you are using an older version (1_1_6_0) as well. You should download the most recent build as that version doesn’t have the counting options.

http://support.bigfix.com/bes/install/downloadutility.html#besdelcompremover

(imported comment written by bobk91)

Thanks Tyler. THis is very informative.

Bob

(imported comment written by SystemAdmin)

FYI, the BES Deleted Computer Remover tool has been updated with a batched mode. If run in batched mode, recommend about 250,000 rows per batch, the tool will not lock the database as much and other operations like FillDB and the consoles will not block behind it. The down side is that it will take longer to run since it is stopping and resetting itself. I would recommend using batched mode if you have never run the tool before and have a large buildup (over 1 millions rows) or are running low on disk space. The batched mode will allow the database to shrink between deletions and help prevent the transaction log from growing too large.