tblSoftware size exploded

[csac_it]

We have 575 assets, using Microsoft SQL LocalDB, and the database is running out of room because the tblSoftware table is up to 6,476,624 kb with an index of 2,927,720 kb (that's about 9.3 GB of the max of 10.2 GB for localDB).

Looking at the Software tab, there is one item, 'ms-resource:AppStoreName v. 1.0.61591.0' which has 25,840,476 entries (for only 575 assets mind you). The tblSoftware table has 25,855,269 lines, so presumably this one item accounts for more than 99.9% of the table size.

I have disabled SOFTWAREMSSTOREAPPS from scanning, but that doesn't seem to be getting rid of the entries. I have run Truncate logs and Shrink numerous times, with little effect.

How do I clean up the table? Thanks.

[msullivan]

I still had to turn off store app item scanning and run SQL query to delete from tblSoftware where MsStore=1 to DELETE all store apps from software inventory and shrink db, then re-enable store apps item scanning.

[csac_it]

That's what support said I should do. Tried that yesterday, (took quite some time to delete 25 million lines), then I tried truncating and shrinking, which I had to leave run overnight. Then this morning I started the IIS and lansweeper service, but got 'Update in progress 1 of 900' when connecting to the web interface, which after 20 minutes had not gone anywhere, so I followed :

https://community.lansweeper.com/t5/troubleshooting-your/update-in-progress/ta-p/64396

and restarted the lansweeper DB and Lansweeper service, and all the lines came back (it had gotten down to 5400 lines, now back up to 25 million again). So... starting again with the 'delete from tblSoftware where MsStore=1'.

[msullivan]

Yes, I just confirmed with latest 11.1.0.11 update and after deleting all store apps then turning storeapp scanning back on the ms-resource named ones started coming back on new scans. So I am turning off store app scanning for now and will wait until that get fixed as well. It does look like the update did possibly fix the software history problem though.

[ErikT]

@msullivan I have forwarded your feedback internally.
Thanks for sharing.

[csac_it]

I hadn't turned storeapp scanning back on yet, the database seems to have not completed deleting. This time I will try to be more patient.

[csac_it · Accepted solution]

This time it seems to have worked. Database size down to 667 MB (from 10.2 GB).

Two things I did different:

1) the instructions from support did not say to stop the lansweeper and IIS service. It seemed like I should have, but the first time through I decided to follow instructions and not stop them (that will teach me 🙂). The second (successful) time I stopped the services first.

2) When running 'delete from tblSoftware where MsStore=1' the first time, I don't think I allowed enough time for the command to complete. The second time I made sure the Maintenance tool said 'command completed' before proceeding to truncate, shrink and reindex.

I haven't turned SOFTWAREMSSTOREAPPS back on yet. We'll see if it continues to behave.

[csac_it]

I turned SOFTWAREMSSTOREAPPS back on. 'ms-resource:AppStoreName v. 1.0.61591.0' is appearing twice for many of the Windows computers. However, after running over the weekend the number of instances has not exploded like before, so I think it has been resolved.

[msullivan]

Thanks for the update, I will check mine as well.

[ErikT]

@csac_it 

Thanks for sharing!