MSSQL Log Files are MASSIVE!!!!

[Sam_Brown]

Installed Kaspersky Admin Kit bout three weeks ago and it stopped working today.

 

Log files on the SQL Server are now 118GB and rising. Disk now only has 10meg free on it!

 

A) How do I decrease the log files?

B) How do I stop it happening again?

C) Is this why I can't get the Admin Kit working?!?!

 

Thanks in advance

[gshaw]

Wow that's some huge logfiles, is that just being caused by KAV admin kit?

[Sam_Brown]

Believe so.

 

I've copied the log file now for safe keeping and truncated the original so it's down to 188MB as opposed to 120GB but I'm worried it's going to grow again over night! It just shot up from 60MB to 178MB in a few minutes!

 

I've no idea why it's going up so quickly though. Kind of worrying.

[maniac]

Kaspersky is quite a hungry program when it comes to SQL, but it shouldn't cause that sort of problem. Our kaspersky database is about 1GB with the associated log file being about 470MB and that's on a 2 year old installation. What version of Kaspersky admin kit are you running and what version of SQL?

[Sam_Brown]

8.0.2 Admin Kit

2k8 SQL

[maniac]

Yes, version 8.0.2 is the latest admin kit. The full version number of the absolute latest version is Version: 8.0.2163

 

I suggest keeping an eye on it initially and if the same behaviour repeats itself, it would be well worth giving their support a call, I've found them to be very helpful in the past. I've no suggestion to make myself except checking you have the very latest version of the admin kit.

 

Mike.

[jamesb]

What recovery mode is the SQL database set to?

[Sam_Brown]

It was set to full. Although as a temporary solution I've turned it to Simple. At least until the boss gets back.

[jamesb]

That should at least keep the logs from exploding - seen the same thing many a time with SharePoint.

[TheCrust]

Our AVG install does the same thing on occasion - while the database itself is reasonable, the transaction log file is getting distinctly towards the ruddy massive side now and is regularly knocking on the defined upper limit. When it does, the AVG admin server stops working.

 

A backup with truncate does nothing, neither does a shrinkfile. The only thing I can do is grant it more space.

 

Me no likey that one and I'd love to know what it's up to - I really must get to the bottom of it when I have more time....

[Sam_Brown]

Hi TheCrust,

 

As well as settig the Recovery Mode to simple as above you can also edit the database properties to limit the size of the T-Logs.

 

You can manually truncate the T-Logs with a T-Sql command and then set the size of the T-Logs under database properties to something manageable like 1GB.

[jinnantonnixx]

Are you actually backing up the database?

 

Ever-expanding log files are a sign of a backup problem. The log file is not being truncated.

 

Here's a nice little SQL script that shows the physical and logical sizes of the files in use by a database.

 

 

use [the_name_of_your_database] 
go

dbcc showfilestats

SELECT	Name, Filename,

CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],

CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [space Used (MB)],

CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

FROM dbo.sysfiles a (NOLOCK)