RMS or SQL log file has grown out of control

Posted by Ahmed Nashat on April 26, 2007, 9:03 am

Please log in for more thread options

: quoted-printable

GregDxb,

Setting Auto_Shrink on might affect performance specially if you have =
heavy transaction load (retail store for example) and/or your database =
size is not small, I don't recommend auto shrinking the database since =
the simple recovery model is enough to eliminate the unwanted increase =
in database size.

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

>=20

> No need to delete and recreate the database..

> no need to back up also...

>=20

> Open store administrator

> connect to your database

> open query

>=20

> ---just run this query=20

>=20

> DBCC SHRINKFILE (2, 0)

>=20

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

problem...

>=20

> run this query afterwards,

>=20

> ALTER DATABASE MYDATABASE

> SET AUTO_SHRINK ON, =20

> AUTO_UPDATE_STATISTICS ON,=20

> AUTO_CREATE_STATISTICS ON,=20

> RECOVERY SIMPLE,=20

> TORN_PAGE_DETECTION ON

>=20

> :)

>=20

> "mbonin" wrote:

>=20

>> Hi there

>>=20

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

of our=20

>> stores

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

>> investigation we discovered the following: =20

>>=20

>> C:Program FilesMicrosoft SQL ServerMSSQLData

>>=20

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

and has=20

>> taken up all the memory on the computer

>>=20

>> Has anyone seen this before?

>>=20

>> This is only happeing in this one particular store.=20

>>=20

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

create a=20

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

file,=20

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

growth.

>>=20

>> Any help would be appreciated.

>>=20

>> Thanks

>> Matt

------=_NextPart_000_000C_01C78814.1BC57F80
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable

=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2900.3059" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV>GregDxb,</DIV>
<DIV> </DIV>
<DIV>Setting =
Auto_Shrink on=20
might affect performance specially if you have heavy transaction load =
(retail=20
store for example) and/or your database size is not small, I don't =
recommend=20
auto shrinking the database since the simple recovery model is enough to =

eliminate the unwanted increase in database size.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"GregDxb" <<A=20
size=3D2>GregDxb@discussions.microsoft.com</A>>=20
wrote in message <A=20
face=3DArial=20
A><FONT=20
face=3DArial size=3D2>...</DIV>> =
Yes...it is=20
happening before also with RMS 1.2.. > > No need to delete =
and=20
recreate the database.. > no need to back up also... > =
 > Open=20
store administrator > connect to your database > open =
query >=20
 > ---just run this query > > DBCC SHRINKFILE (2, =
0) >=20
 > This is a one time process...Once done this it will ressolve =
your=20
problem... > > run this query afterwards, > > =
ALTER=20
DATABASE MYDATABASE > SET  AUTO_SHRINK ON,  >=20
AUTO_UPDATE_STATISTICS ON, > AUTO_CREATE_STATISTICS ON, > =
RECOVERY=20
SIMPLE, > TORN_PAGE_DETECTION ON > > :) > =

 > > "mbonin" wrote: > >> Hi =
there >>=20
 >> We are currently running RMS 1.3 in a HQ environment.  =

Recently one of our >> stores >> started getting a =
low memory=20
error on their computer.  Upon further >> investigation =
we=20
discovered the following:  >> >> C:Program=20
FilesMicrosoft SQL ServerMSSQLData >> >> the file =
which is=20
called "our database name".ldf has grown to 23gigs and has >> =
taken up=20
all the memory on the computer >> >> Has anyone seen =
this=20
before? >> >> This is only happeing in this one =
particular=20
store. >> >> One possible solution is we back up the =

database, delete it, and re create a >> new database from our =
backup=20
file.  This should make a fresh log file, >> however it =
may not=20
solve the problem of the log files exponential growth. >> =
 >>=20
Any help would be appreciated. >> >> =
Thanks >>=20
Matt</BODY></HTML>

------=

Posted by mbonin on April 26, 2007, 1:46 pm

Please log in for more thread options

Greg

Thanks for the post

we ran the DBCC shrinkfile command (also tried DBCC shrinkdatabase) but
niether of those commands actually changed the size of our log file (still 23
gigs) in the directory i pointed out earlier.

Any suggestions?

"GregDxb" wrote:

> 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

Posted by GregDxb on April 29, 2007, 4:58 am

Please log in for more thread options

I hope before you run the query, you must have connect you store
administrator to the right database.

I have these for so long but only thru the query I gave you was rectified.

if your are using query analyzer-->point it the database ("mystoredata")

or if you using store administrator--> connect it first then test the
database connection just to make sure it is connected properly...

after which, you run the query I gave you...

DBCC SHRINKFILE (2, 0)

Let me know still if it is not working.

"mbonin" wrote:

> Greg

> Thanks for the post

> we ran the DBCC shrinkfile command (also tried DBCC shrinkdatabase) but

> niether of those commands actually changed the size of our log file (still 23

> gigs) in the directory i pointed out earlier.

> Any suggestions?

> "GregDxb" wrote:

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

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