RMS or SQL log file has grown out of control

Posted by mbonin on April 25, 2007, 5:36 pm

Please log in for more thread options

Hi there

We are currently running RMS 1.3 in a HQ environment. Recently one of our
stores
started getting a low memory error on their computer. Upon further
investigation we discovered the following:

C:Program FilesMicrosoft SQL ServerMSSQLData

the file which is called "our database name".ldf has grown to 23gigs and has
taken up all the memory on the computer

Has anyone seen this before?

This is only happeing in this one particular store.

One possible solution is we back up the database, delete it, and re create a
new database from our backup file. This should make a fresh log file,
however it may not solve the problem of the log files exponential growth.

Any help would be appreciated.

Thanks
Matt

Posted by cod on April 26, 2007, 1:48 am

Please log in for more thread options

mbonin wrote:
> the file which is called "our database name".ldf has grown to 23gigs
and has

> taken up all the memory on the computer

> Has anyone seen this before?

The LDF is the log file of SQL Server.. Do you have Enterprise Manager
(sql2000) or Sql Server Management Studio (sql 2005) to verify the
recovery model of your database? You have the "full" recovery model..
and each transaction (not RMS transaction, but sql transaction) is
stored in data file (.mdf) and in the log. To empty your ldf you must
run the shrink database in SQL Server, after the log transaction backup
(that create a checkpoint in the log) or change the recovery model in
"Simple".

cod

p.s. sorry for my english

Posted by mbonin on April 26, 2007, 2:00 pm

Please log in for more thread options

Cod, we are running sql 2000

As I said earlier we ran the DBCC shirnkfile (also tried shrinkdatabase) but
was unsucessfull in reducing the size of the log file.

in your post you mentioned this: To empty your ldf you must run the shrink
database in SQL Server, after the log transaction backup(that create a
checkpoint in the log) or change the recovery model in "Simple".

my question is i know how to run the shrink database command (thanks to
greg's post) but iam not sure how to do the backup/change the recovery model
in "simple", which you had mentioned.

Is not doing this causeing the shrinkdatbase command not to work?

Thanks,
Matt

"cod" wrote:

> mbonin wrote:

> > the file which is called "our database name".ldf has grown to 23gigs

> and has

> > taken up all the memory on the computer

> >

> > Has anyone seen this before?

> The LDF is the log file of SQL Server.. Do you have Enterprise Manager

> (sql2000) or Sql Server Management Studio (sql 2005) to verify the

> recovery model of your database? You have the "full" recovery model..

> and each transaction (not RMS transaction, but sql transaction) is

> stored in data file (.mdf) and in the log. To empty your ldf you must

> run the shrink database in SQL Server, after the log transaction backup

> (that create a checkpoint in the log) or change the recovery model in

> "Simple".

> cod

> p.s. sorry for my english

Posted by cod on April 26, 2007, 3:29 pm

Please log in for more thread options

mbonin wrote:
<cut/>

1st .. sorry for my english!

run OSQL from command line..

OSQL -u sa -p <password>

USE [yourdatabase]
GO

BACKUP LOG [yourdatabase] TO DISK = 'pathofbackup.bck'
GO

CHECKPOINT
GO

DBCC SHRINKFILE 'logfile' ...
GO

ALTER DATABASE [yourdatabase] SET RECOVERY SIMPLE
GO

QUIT
<ENTER>

bye
cod

Posted by GregDxb on April 26, 2007, 7:02 am

Please log in for more thread options

Yes...it is happening before also with RMS 1.2..

No need to delete and recreate the database..
no need to back up also...

Open store administrator
connect to your database
open query

---just run this query

DBCC SHRINKFILE (2, 0)

This is a one time process...Once done this it will ressolve your problem...

run this query afterwards,

ALTER DATABASE MYDATABASE
SET AUTO_SHRINK ON,
        AUTO_UPDATE_STATISTICS ON,
        AUTO_CREATE_STATISTICS ON,
        RECOVERY SIMPLE,
        TORN_PAGE_DETECTION ON

:)

"mbonin" wrote:

> Hi there

> We are currently running RMS 1.3 in a HQ environment. Recently one of our

> stores

> started getting a low memory error on their computer. Upon further

> investigation we discovered the following:

> C:Program FilesMicrosoft SQL ServerMSSQLData

> the file which is called "our database name".ldf has grown to 23gigs and has

> taken up all the memory on the computer

> Has anyone seen this before?

> This is only happeing in this one particular store.

> One possible solution is we back up the database, delete it, and re create a

> new database from our backup file. This should make a fresh log file,

> however it may not solve the problem of the log files exponential growth.

> Any help would be appreciated.

> Thanks

> Matt