I'm not sure what to do - It hit the 2GB limit today, and I did a Compact and Repair, followed by splitting the database. The database from the split with the tables is only 1.81GB, which doesn't give me much to work with. Can someone point me in the direction I need to investigate? I probably need to look at SQL in the very near future, but anything that will give me some breathing room would be greatly appreciated. Thanks
Database hit 2GB, split only decreases to 1.81GB
- Thread starter cikVOU18
- Start date
- Local time
- Today, 13:35
- Joined
- Oct 29, 2018
- Messages
- 22,770
Yes, that's very possible. For example, you could split the table based on date, network, or computer names.
- Local time
- Today, 13:35
- Joined
- Oct 29, 2018
- Messages
- 22,770
Okay. Let us know how it goes. Good luck!
- Local time
- Tomorrow, 08:35
- Joined
- Jan 20, 2009
- Messages
- 12,832
Definitely the way to go.
- Local time
- Today, 15:35
- Joined
- Feb 28, 2001
- Messages
- 30,562
Yep, concur. When a single table reaches that kind of limit, you have a serious problem and dividing tables into pieces parts to store in separate back-ends will not help long-term. It is a stop-gap measure.
However, I have some experience with vulnerability scans. I was a U.S. Navy systems admin at the Navy Enterprise Data Center in New Orleans for 28 1/2 years. I held a Security+ certificate and was also certified for the operating system of my main beasts. As a result, I totally understand vulnerability scans. How many computers are you talking about?
Vulnerability scans tend to be HIGHLY repetitive which means that the tables in question are ripe for normalization. If you haven't patched a detected issue, you get the same result a second time. And if you have multiple systems, you get multiple findings with the same issue.
These results are often identified by some simple string, a CSV-number or KB issue number, or IAVA-number. If you made a table of vulnerabilities with an autonumber prime key, you can then associate the finding (vulnerable, not vulnerable, not applicable to this O/S, etc.) to a system and perhaps compress your results considerably. If that CSV contains what I think it contains, a LOT of it is highly redundant. Incredibly so.
With the Navy data center, we had twice-per-month scans because IAVA notices were published twice monthly. We had 80+ projects with over 1500 servers. We would get new notices of new types of vulnerability (perhaps a new virus or trojan, or a newly discovered code weakness) at a rate of perhaps 20-30 per month. We also tracked patching and current status, and the whole database (native Access, no external SQL involved) was less than 200 Mbytes. We never deleted a vulnerability identification listing but we would delete the server/vulnerability association listings after ALL servers had been checked and NONE of them remained vulnerable. Once that happened, we declared that vulnerability closed and set a date for archiving. We would then purge association records after being closed six months without being re-opened. We reached an equilibrium point where between purging obsolete associations and making new ones, we hovered in the 150+ Mbyte range for the back-end. It varied from month to month by about 10% depending on complexity of the patches and other workload issues, but it was NOWHERE near the Access 2 Gb file limit. The only growth factor was that we remembered notices and servers for a lot longer than we remembered the associations so those tables exhibited a certain level of "creep."
Our purges, by the way, were to an archiving file. We never actually threw away old associations. They just ceased to be of immediate relevance. But we tracked all system remediation theoretically "forever." So my question is whether you have fully normalized that database. Because I know for a hard fact that it is possible to do so.
However, I have some experience with vulnerability scans. I was a U.S. Navy systems admin at the Navy Enterprise Data Center in New Orleans for 28 1/2 years. I held a Security+ certificate and was also certified for the operating system of my main beasts. As a result, I totally understand vulnerability scans. How many computers are you talking about?
Vulnerability scans tend to be HIGHLY repetitive which means that the tables in question are ripe for normalization. If you haven't patched a detected issue, you get the same result a second time. And if you have multiple systems, you get multiple findings with the same issue.
These results are often identified by some simple string, a CSV-number or KB issue number, or IAVA-number. If you made a table of vulnerabilities with an autonumber prime key, you can then associate the finding (vulnerable, not vulnerable, not applicable to this O/S, etc.) to a system and perhaps compress your results considerably. If that CSV contains what I think it contains, a LOT of it is highly redundant. Incredibly so.
With the Navy data center, we had twice-per-month scans because IAVA notices were published twice monthly. We had 80+ projects with over 1500 servers. We would get new notices of new types of vulnerability (perhaps a new virus or trojan, or a newly discovered code weakness) at a rate of perhaps 20-30 per month. We also tracked patching and current status, and the whole database (native Access, no external SQL involved) was less than 200 Mbytes. We never deleted a vulnerability identification listing but we would delete the server/vulnerability association listings after ALL servers had been checked and NONE of them remained vulnerable. Once that happened, we declared that vulnerability closed and set a date for archiving. We would then purge association records after being closed six months without being re-opened. We reached an equilibrium point where between purging obsolete associations and making new ones, we hovered in the 150+ Mbyte range for the back-end. It varied from month to month by about 10% depending on complexity of the patches and other workload issues, but it was NOWHERE near the Access 2 Gb file limit. The only growth factor was that we remembered notices and servers for a lot longer than we remembered the associations so those tables exhibited a certain level of "creep."
Our purges, by the way, were to an archiving file. We never actually threw away old associations. They just ceased to be of immediate relevance. But we tracked all system remediation theoretically "forever." So my question is whether you have fully normalized that database. Because I know for a hard fact that it is possible to do so.
Similar threads
