SQL database grows forever, cant log into adnm

[bryonTRN]

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?

[wpn]

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)

[bryonTRN]

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...

[wpn]

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

[bryonTRN]

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)

[wpn]

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

[Jan Nabelek]

after 3 days the database is around 3gb

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)

[bryonTRN]

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.

[Jan Nabelek]

rebooting the actual server hardware itself had no effect, still cant log into adnm anymore until i restore the databases.

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.

[bryonTRN]

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:

Code: [Select]

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??

[Jan Nabelek]

not being a sql guy...

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

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

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)?

[bryonTRN]

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.

[Jan Nabelek]

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

[bryonTRN]

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

[bryonTRN]

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