topic Re: SQL Database log file in Server Management - Systems Insight Manager

SQL Database log file

Joe_328 — Wed, 01 Jun 2005 13:19:48 GMT

Hi there,

Environment;

Windows 2003, HP SIM v4.1 and about monitoring about 450 managed systems.

Query;

The log files have been steadily increasing and stand at 97gb on a 143gb disk. At the moment the log file is configured to unrestricted growth but it's behaviour is contrary to another HP SIm box.

Does anyone know of any reason why this would be happening? Can I delete this log file, and then restrict the size of the new one? any advice would be appreciated.

Thank you

Joe

Re: SQL Database log file

James Kennedy_4 — Wed, 01 Jun 2005 13:24:54 GMT

On the SQL Server, Change the database type to "Simple" and set it to "auto-shrink". It'll stay around a few MB then.

Re: SQL Database log file

Claas Kruhoefer — Thu, 02 Jun 2005 03:16:58 GMT

Hello Joe,

if you are using MS SQL server this should help

created a DB Maintenance plan for the insight db.

added to the transactionlog backup step a second step with this SQL states
------
use Insight_v1_0
DBCC SHRINKfile(Insight_v1_0_log, 0)
------

let this run every 30 minutes an kill all transactionlogbackup that are older than 2 hrs, then it should not grow beyond 200 MB

you will surely have to adjust the statement to oyur db names and filegroups names, but after that it will work.

hope this helps

Re: SQL Database log file

Joe_328 — Thu, 02 Jun 2005 07:44:00 GMT

Hi James,

Thanks for your reply. Just to clarify, What effect if any will this have? Will it reduce/ Shrink the already huge transactio logs or do I have to delete the existing transaction log file.

Also will this setting (auto shrink) not have an adverse effect on database performance.

Please forgive me as my SQL knowlegde is limited.

Thanks

Joe

Re: SQL Database log file

Joe_328 — Thu, 02 Jun 2005 08:28:37 GMT

Hi Claas,

Thanks for your reply.

Please forgive me as my SQL knowlegde is limited.

I am not totally clear on your suggestion.

The Insight manager database is not currently being backed up via SQL but by an enterprise solution.

I have read that it would be a good idea to create a backup maintenance plan for database and transaction logs, change the recovery mode to simple as suggested by James and this should work.

What do you think

Cheers

Re: SQL Database log file

James Kennedy_4 — Thu, 02 Jun 2005 09:45:05 GMT

Joe,

At the size of the log files you have right now, it'd be very difficult to do any backups. You definately want to get it shrunk down, and then you can setup a backup plan if you desire.

Backing up the logs aren't that important unless you require the ability to do a restore to the exact moment of a loss. Generally I don't consider the SIM database logs that important, as they will recreate themselves based on newly collected data.

Re: SQL Database log file

Joe_328 — Thu, 02 Jun 2005 11:45:12 GMT

Thank for your help James

Re: SQL Database log file

vasudevahospattan — Sat, 27 Mar 2010 12:02:16 GMT

Hi James / Joe

I am facing same problem my sql data mdf log files are increasing facing problem while starting the SM

Please help me how to delete these files

I dont have SQL so much Knowledge.

size is incresing please help needed critical

REgards
VAsu