LogFile size - general information

microsoft.public.sqlserver.server

19 Dec 2008 7:55 AM

Walter Sporn

Hello,

I am no expert in SQL server but for my job I have to manage an application
that works with MS SQL Server and so I tried to find answers for some of my
basic problems but I did not really get them. So I hope you can help me!

My application captures technical measurement data and writes it to a
database continously each minute. It is about 10 MB of data per day.

The problem is that the *.ldf file grows and grows. I understood that it
keeps all transactions to be able to restore them in case of a crash.

But I can not accept to get a daily growing file and so I have to decrease
the size from time to time.

As far as I understood there is the possibility to backup the database and
by that I can also force the *.ldf file to be cleared.

When I did that I saw that the file size not really gets smaller and that
this is by intention because the performance costs for the growing of a file
is too high and so the file size itself is kept and only the content is
cleared.

So I tried to find a way to backup the database each day and by that to
clear the *.ldf keeping the file size.

But unfortunately non of my backup commands and "shrink" calls worked so
far. As soon as I start my application after a database backup the *.ldf
file grows again.

So I think I still did not find the right way to use the commands. And to be
honest I did not really understand all shrink command possibilities
(database vs. file a.s.o.).

Can anybody help me? Either by explaining some backgrounds or by showing a
backup strategy that works for my purpose or by giving me links to resources
where I can find easy understandable information.

I am not a database manager but simply an application developer so I really
do not know all details of database management. So please talk to me like an
idiot ;-)

Thanks a lot !!!

19 Dec 2008 9:42 AM

Tibor Karaszi

Here's a backgrounder on transaction log management. Make sure you
read all the subsections:
http://msdn.microsoft.com/en-us/library/ms345583.aspx

And here's one on shrink in general:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"Walter Sporn" <WSporn1***@hotmail.com> wrote in message
news:%23lQgk8aYJHA.1528@TK2MSFTNGP03.phx.gbl...
> Hello,
>
>
>
> I am no expert in SQL server but for my job I have to manage an
> application that works with MS SQL Server and so I tried to find
> answers for some of my basic problems but I did not really get them.
> So I hope you can help me!
>
>
>
> My application captures technical measurement data and writes it to
> a database continously each minute. It is about 10 MB of data per
> day.
>
>
>
> The problem is that the *.ldf file grows and grows. I understood
> that it keeps all transactions to be able to restore them in case of
> a crash.
>
>
>
> But I can not accept to get a daily growing file and so I have to
> decrease the size from time to time.
>
>
>
> As far as I understood there is the possibility to backup the
> database and by that I can also force the *.ldf file to be cleared.
>
> When I did that I saw that the file size not really gets smaller and
> that this is by intention because the performance costs for the
> growing of a file is too high and so the file size itself is kept
> and only the content is cleared.
>
> So I tried to find a way to backup the database each day and by that
> to clear the *.ldf keeping the file size.
>
>
>
> But unfortunately non of my backup commands and "shrink" calls
> worked so far. As soon as I start my application after a database
> backup the *.ldf file grows again.
>
> So I think I still did not find the right way to use the commands.
> And to be honest I did not really understand all shrink command
> possibilities (database vs. file a.s.o.).
>
>
>
> Can anybody help me? Either by explaining some backgrounds or by
> showing a backup strategy that works for my purpose or by giving me
> links to resources where I can find easy understandable information.
>
>
>
> I am not a database manager but simply an application developer so I
> really do not know all details of database management. So please
> talk to me like an idiot ;-)
>
>
>
> Thanks a lot !!!
>
>
>
>

Are all your drivers up to date? click for free checkup

19 Dec 2008 1:03 PM

Kevin3NF

One option if you don;t need to restore to a certain point of time in the
event of a failure is to t change the "Recovery Model" of the database to
Simple. If you do suffer a db crash and need to restore, you will be able
to bring back whatever was in the database at the time of the last full
backup.

--

Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.

Twitter: Kevin3NF

Show quoteHide quote

"Walter Sporn" <WSporn1***@hotmail.com> wrote in message
news:%23lQgk8aYJHA.1528@TK2MSFTNGP03.phx.gbl...
> Hello,
>
>
>
> I am no expert in SQL server but for my job I have to manage an
> application that works with MS SQL Server and so I tried to find answers
> for some of my basic problems but I did not really get them. So I hope you
> can help me!
>
>
>
> My application captures technical measurement data and writes it to a
> database continously each minute. It is about 10 MB of data per day.
>
>
>
> The problem is that the *.ldf file grows and grows. I understood that it
> keeps all transactions to be able to restore them in case of a crash.
>
>
>
> But I can not accept to get a daily growing file and so I have to decrease
> the size from time to time.
>
>
>
> As far as I understood there is the possibility to backup the database and
> by that I can also force the *.ldf file to be cleared.
>
> When I did that I saw that the file size not really gets smaller and that
> this is by intention because the performance costs for the growing of a
> file is too high and so the file size itself is kept and only the content
> is cleared.
>
> So I tried to find a way to backup the database each day and by that to
> clear the *.ldf keeping the file size.
>
>
>
> But unfortunately non of my backup commands and "shrink" calls worked so
> far. As soon as I start my application after a database backup the *.ldf
> file grows again.
>
> So I think I still did not find the right way to use the commands. And to
> be honest I did not really understand all shrink command possibilities
> (database vs. file a.s.o.).
>
>
>
> Can anybody help me? Either by explaining some backgrounds or by showing a
> backup strategy that works for my purpose or by giving me links to
> resources where I can find easy understandable information.
>
>
>
> I am not a database manager but simply an application developer so I
> really do not know all details of database management. So please talk to
> me like an idiot ;-)
>
>
>
> Thanks a lot !!!
>
>
>
>