SQL database grows forever, cant log into adnm

Avast Business Avast Distributed Network Manager
1

hello,

we have these symptoms that we NEED help with…

we have a dedicated win2003 server solely for avast adnm. it’s running SQL2000 with all the service packs and updates.

our adnm is dec2008 version 4.8.952, and we have about 300 clients connecting to it.

now - when we did a new install, it created a database just fine. as the clients started hitting it, the database obviously grows.

after 1 day the database is at around 40mb.
after 3 days the database is around 3gb
after 6 days the database is over 20gb
after 9 days the database is over 60gb

anything over 3gb, we cant log into the adnm anymore, we get ‘connection timed out’. however, the AMS maintenance program says the database checks out ok, is compatible, and the connection test is ok. sometimes, restarting both avast services and both sql services (agent) lets us log in.

so, i deleted the entire database and recreated it. i then made a sql backup of it after 1 day, and have a 40mb database with MOST of our clients in it. when i recreated the database, i told sql to limit it to 2gb.

now, after 3 days the database is 1.9gb, but we cant log into it… nor can we browse to it via sql enterprise manager - it just hangs forever. in this state, the sql service itself takes up 1.78gb of RAM (we have 4gb ram available). we have two dual core 2.8ghz xenon processors on this server

if i delete THAT database, and restore the backup, it works again right away.

the adnm is running on the same server as the sql server, but it doesnt make a difference where i run it from.

SO… what is making the sql database grow so big so fast that it doesnt even work anymore?

2

since u seem to keep installing ADNM all the time

maybe u can try to use a different system and try out AVAST with the MSDE database engine…
just for testing to see if the database grows then too, if so avast is putting something in the db continuously
if not then maybe there is something either still with avast or a setting in your sql instance…

worth the try since i dont experience this behaviour at this moment on MSDE enigine (later this year it will be installed on MS SQL 2008 instance)

3

thanks for the reply,

this is our 3rd avast server we’ve tried, and the first server dedicated totally to avast.

the other two servers were:

1st, a terminal server 2gb ram, single dual-core 2.8ghz processor, 20 users logged in remote desktop, avast was tied to it’s own sqlexpress running on this same server. the database grew rapidly like now, and as soon as it hit 2gb sqlexpress killed itself (2gb cap). avast support said to install on a different server, and on sql2000 or not-express.

2nd, a web server with no users logging in, 3gb ram, single dual-core 2.8ghz processor. avast was tied to a shared sql2000 database on the same server as adnm is installed (the web server). avast support says adnm should have it’s own dedicated server since we have 300 people hitting it.

current server, dual dual-core 2.8ghz with local sql2000 installed, this server does nothing but avast.

i’ve reinstalled adnm several times, deleted the database and restored backups several times… and even deleted the database and let it start fresh several times (without making ANY changes to adnm, like rules and stuff)… this problem continues…

4

did u try with the MSDE install??

when i check my MSDE install its only 83MB. tho i dont have 300 clients… i barely hit the 60 right now

5

ah yes, above, where i said “sql express” i meant to say MSDE (which has the 2gb limit - only good for 2 days for us)

6

with mssql 2000 it is possible to monitor what is coming into the database or so?

i dont know mssql 2000 management but i checked on google with:
mssql 2000 monitor growth

and i found a link to a freeware program, if it shows what it needs to show that i cannot say:

http://www.manageengine.com/products/opmanager/download-free.html

maybe u can get some leads from here on too (on the bottom is a link to another page which sounds interresting tho im not sure if it works):
http://vyaskn.tripod.com/track_sql_database_file_growth.htm

7

What about database files size? Especially log file…
What recovery model of Avast DB you use? (see Enterprise manager) If Full, try to switch to simple recovery model or try to shrink log file.

I have 60 managed client with ADNM on W2k3 Srv SQL2005 Express and DB size is 71 MB (55 MB data and 16 MB log file) I use simple recovery model, last version ADNM and clients, W2k3 with SP2 (and fixes)

8

hi there, and thank you both for the replies…

the recovery mode was set for simple, i’ve changed it to full to see what happens.

auto-shrink was already checkmarked.

first thing this morning, the avast.mdf is 3,082,560 kb and the avast_log.ldf is 768kb.
(in enterprise manager i’ve hard-limited it to 3gb)

of course, i wasn’t able to log into the admn - this is expected and normal now for us.

i killed the 2 avast services, and sql and sqlagent… restarted them and now i can log in to admn again. i’ll post back here after an hour or so and let you know what the database and log files get to once the clients start hitting them again.

the avast management service killed itself sometime over the weekend

i’m checking out those freeware tools now, hopefully one of them will tell me what data is being added so much and that will point to a cause.

EDIT: after a few hours, the MDF is 3,390,848 kb (3.4gb) and the LDF is 467mb

EDIT2: a few hours later than the last edit, the LDF got up to 850mb and i wasn’t able to log into adnm anymore. in this current state, i’ve restarted the services like before but this time it doesnt let me in anymore. at this point, i have to delete/restore the databases again if i ever want to use ADNM. as this was happening, i already had ADNM still open from before, but anything i tried to do resulted in “a timeout waiting for the server”. rebooting the actual server hardware itself had no effect, still cant log into adnm anymore until i restore the databases.

9

And did you try DBCC CHECKDB command before restore? Are there any errors inside
DB?
It must be something wrong inside DB. When you restoring, are you restore from the same backup? Did you try create brand new database?
I had problem with database some months ago and I need to use 4 months old backup for restore. Restore from newer backup did not solve my problem.

10

hello, thanks for the note about DBCC

currently, at the time of this posting, here’s our file sizes:
avast.mdf = 4,513,280kb (4.5gb)
avast_log.LDF = 5,095,872kb (5.1gb)

yeah, the log file is 500mb bigger than the inflated database.

here’s the result of the dbcc checkdb:


DBCC results for 'avast'.
DBCC results for 'sysobjects'.
There are 214 rows in 4 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 85 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 806 rows in 13 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 253 rows in 47 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 50 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 14 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 1102 rows in 5 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 18 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'Property'.
There are 625 rows in 8 pages for object 'Property'.
DBCC results for 'Event'.
There are 24179131 rows in 364812 pages for object 'Event'.
DBCC results for 'LongName'.
There are 0 rows in 0 pages for object 'LongName'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'ShortName'.
There are 90 rows in 4 pages for object 'ShortName'.
DBCC results for 'Session'.
There are 10 rows in 1 pages for object 'Session'.
DBCC results for 'Result'.
There are 97 rows in 2 pages for object 'Result'.
DBCC results for 'Queue'.
There are 672 rows in 4 pages for object 'Queue'.
DBCC results for 'ScanStat'.
There are 345 rows in 4 pages for object 'ScanStat'.
DBCC results for 'InstallStat'.
There are 0 rows in 0 pages for object 'InstallStat'.
DBCC results for 'File'.
There are 3 rows in 1 pages for object 'File'.
DBCC results for 'AssocObject'.
There are 2 rows in 1 pages for object 'AssocObject'.
DBCC results for 'AccessControlList'.
There are 24 rows in 1 pages for object 'AccessControlList'.
DBCC results for 'User'.
There are 3 rows in 1 pages for object 'User'.
DBCC results for 'Object'.
There are 378 rows in 4 pages for object 'Object'.
DBCC results for 'EngGroup'.
There are 3 rows in 1 pages for object 'EngGroup'.
DBCC results for 'Engine'.
There are 341 rows in 20 pages for object 'Engine'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'avast'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

not being a sql guy at all, i can see one thing that stands out…

DBCC results for ‘Event’.
There are 24179131 rows in 364812 pages for object ‘Event’.

wow, 24 million rows in the event object… anything i can do to LIMIT how many events are reported??

11

me to… :slight_smile: but more suitable is use DBCC CHECKDB WITH NO_INFOMSGS (view errors only)

too many rows… :slight_smile:
What about source of DB? Is it brand new DB or is it DB restored from earlier backup?
Write more about “client tasks” or jobs settings (sorry I don’t know precise translation because I have only Czech version ADNM console) or server tasks. Something must be wrong in your db or your settings.
Is default maintenance task “Clean Records Older Than 3 Months” active and running (weekly)?

12
me to... :-) but more suitable is use DBCC CHECKDB WITH NO_INFOMSGS (view errors only)
there are, apparently, no errors: CHECKDB found 0 allocation errors and 0 consistency errors in database 'avast'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
too many rows... :-) What about source of DB? Is it brand new DB or is it DB restored from earlier backup? Write more about "client tasks" or jobs settings (sorry I don't know precise translation because I have only Czech version ADNM console) or server tasks. Something must be wrong in your db or your settings. Is default maintenance task "Clean Records Older Than 3 Months" active and running (weekly)?

the DB was deleted and recreated from the ams maintenance program, and grew to this.

the task ‘clean records’ wasnt actually running at all, but it is now, daily, deleting records older than 5 days. however, i dont think this will help because it’s aparently adding 12 million rows per day…

the only scheduled client tasks are to do a full scan on thursday nights… and to update program daily.
the only server-side tasks are (now) the 5-day cleanup that runs per day, and a db-backup that runs per day.

we have a custom client task ‘scan-now-with-reboot’ that is executed manually as needed.

infections are of course set to email me immediately, but we rarely have any infections at all.

frequently, the ‘daily update’ client task never actually completes… 80% of the clients report as 'finished, 15% show as 'failed 0x20000011), and 5% show as “waiting” forever.

13

I hope that it is time to call Alwill support. See website support page and call tomorrow (with respect time zone :slight_smile:

14

i actually emailed them via support.avast.com with ‘high’ priority and just got a response back today (7 days later).

their suggest was to keep deleting the database and making a new, blank one until all of the clients are done reporting their queue’s.

i’ve asked them to look at this thread, as i’ve been deleting the database almost daily for 3 months now, letting it create a new database itself and not restoring

15

NEW INFO…

yesterday i deleted the database via the asw maintenance program, and recreated a new database (didnt restore anything).

today, i can log into the admn which is in and of itself a miracle… but with these issues:

  1. running the database job ‘clean records older than X days’, hangs forever.
  2. i cant run ‘dbcc checkdb’ because it too hangs forever
  3. my file sizes are (after 18 hours) this:
    MDF = 1.18gb
    LDF = 9.02gb

i cant tell you how many rows/pages are in my Event table because, i cant run dbcc…

EDIT: after 30 minutes, the clean-records job finished. now, my file sizes are MDF 1.18gb, and LDF 11.2gb.
before the job ran, i had 5.8 million rows in Event, after the job finished i’m down to 10,000 rows, but growing at 200 rows per second.

NOTE: i have changed the job to “delete records older than 1 day”, the results of this are what is above

thoughts?

you know what, i think i’m just going to script these and be done with it:

every hour:

use avast
delete from event
where objectid > 1

And this thing once per day:

USE avast
GO
DBCC SHRINKFILE (avast_log, 1400)
GO

16

what is the complete data that is being inserted that generates so much rows??

17

i would love to find out… but… i can only view the top 1000 lines without sql crashing out (cant display 24 million lines i guess)

there’s columns… guid, date/time, workstation name, error code in hex

note that even after i delete the database and let it make a new one, without restoring, i still am getting the SAME event logs from the clients, even back thru 2008

the clients just keep sending the same info

18

can u post some of those lines?

guid, date/time, workstation name, error code in hex

specialy the hex code draws my attention

did u remove all the clients yet or at least let them look for the internet server instead of your AMS server so you
have some more peace finding out?..

sounds more like some of your clients or all are generating errors that they want to put in the AMS for u to see but it happens extremely much

another thing can u check on your clients (or at least a client that u see in those first few lines u can look at) if their logs are filling with something else too?

19

sure, here’s some lines

i tried to get the tabs right, if you copy/paste into wordpad it’ll format properly i think

http://pastebin.com/m3e5585d0

20

is this a cut out from a clean recent db??? cuz when i see the dates i see 2008 coming by too
which i dont believe should be coming in…

tho i need to check (if possible to find without alwil shedding a light on the event id’s) what the event id’s mean

i see a lot of 7FFF0021 parameters comming by…

can u go to the avast maintainance tool and tell me how much licenses you got??

becuz in the sticky thread the hex code 7FFF0021 returns back as Not enough licenses

when i do as in the sticky thread says then the error with 0xA410
seems to translate to 42000 in decimal
when i do net helpmsg i get an error telling me its not a valid windows network message number
when i google net helpmsg 42000 i see a lot of postings about databases and diskspace shortage

so how much space is on the disks from your database partition? :slight_smile: (i just need to ask, i know im a *sshol some time, but then again just following clues here and admitted i just looked at the information google showed instead of reading the actual pages)