SQL Database, CPU usage, memory...

[impuls]

Hello,
I'm experiencing high memory usage by SQL server, over 1GB. Is this normal?
Also, there is high CPU usage from time to time by services:
sqlservr.exe
Avast.Sbc.Service.exe
it goes together up to 80%...

System:
Windows 2003 R2 Server
Two Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
2GB RAM
...

How can I clear database for old logs. For example:
Computer Login Audit trail: 1385 pages
Shield log: 286087 pages

Thanks
mIRO

[impuls]

Here is the image that show CPU usage from two above mentioned processes.

[wpn]

SQL server is always a memory hog, no surprise there.
SQL also reliefs memory that it doesnt use, when the rest of the system needs it, but it likes to claim memory
search on google for this  "sql 2008 memory usage"  (without quotes) lots of interresting info and suggestions for it

(this is assuming that you use the 2008 express that comes with SBC)

the high CPU load i cant explain other then a lot of swapping because of the limited memory that you have in the server.
Is the server itself sluggish? You can also see a lot of disk activity?

maybe avast is updating (either sbc checking online and reporting or clients checking and reporting?) or something?
What are roles does the server have in your network?

[impuls]

Thanks for reply wpn, memory usage by SQL server i something I can deal with. I monitored the usage and it varies, from 500MB to 1000MB...but the CPU usage is what I'm concerned. High CPU usage happens even on non-working hours, sometimes it's calm but most of the time is like on the picture I posted earlier.
Because of the small amount of memory I have, there is a lot of page faults...but I doubt that such all high CPU usage is related with swapping...
Roles: application server, DC, print server.

[wpn]

The computer processor searches for instructions that are stored in the RAM memory of the computer to be executed. If those instructions are not stored in the RAM memory (swapped state), they will have to be transferred from the hard disk (part of the swapping process)

meaning basically:
if you page a lot because of small memory, the processor load runs up too because it needs to take care of the paging and pointing to where the memory is on disk and which memory blocks can be swapped and the swapping itself too.
Swapping is nothing more then putting unused memory blocks onto the disk for later use freeing up memory that is needed for another operation.

So yes it could be the swapping that is causing AND higher load on the CPU and a high load on the disks.

[impuls]

You're right wpn, CPU usage is got lower now, after installing more RAM memory.

Do you know how to clear some log files, I got one, ERRORLOG, weights 4GB.
Also, how to clear Computer login audit trail (there are over 100k entries), Shield log, Scan log...it can't be cleared using predefined Jobs: Remove unused server logs or Clean unused database entries...


Thanks
mIRO

[impuls]

I just noticed that ERROR log growed on almost 6GB, that's just in less that 10 hours !?!?

After restart, SQL started a new log file. The BIG one (and the new log file) is full of these errors:

2011-12-26 21:11:19.43 spid53      Error: 1827, Severity: 16, State: 1.
2011-12-26 21:11:19.43 spid53      CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.
2011-12-26 21:11:19.43 spid53      Error: 1105, Severity: 17, State: 2.
2011-12-26 21:11:19.43 spid53      Could not allocate space for object 'dbo.ProviderStatus' in database 'avast_sbc_db' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I checked DATA folder and avast_sbc_db.mdf file weights about 10.8GB !

How can I solve these mess...?

[impuls]

SOLUTION:

AVAST uses a MS SQL Server 2008 Express free database which supports 10 GB of storage per database, so I reached the limit

I've found one table that has 29 million entries: dbo.ProvideResults
The database is filled since I've installed Administration Console, three and a half months...and probably none of the entries wasn't deleted. According to the name, table contains scan results.
So, I deleted all entries older than one month back in the past (and the transaction log with its 5GB), now the database weights 4GB and I installed newest version of Admin console which will probably solve this problem according to the reply from the AVAST support:
"...it will run automatic script on the current database which should remove all unnecessary data."
I got almost 200 users, therefore, the database will fill quickly and we will see the results

mIRO

[wpn]

sorry was on vacation

of course i was right about swapping   thats why im in this business and not a boxershort model   [phun intended]

goodluck with the setup and come back if you got other issues