|
Home > Archive > MS SQL Server > February 2006 > turn off logging?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| mmiller 2006-02-28, 8:23 pm |
| Is there any way to stop SQL Server 2000 from logging? It is absolutely not
necessary
in my app.
Is SQL Server 2005 any different?
Thanks very much for your time,
Marc Miller
"Don't look back. Something might be gaining on you" - Satchel Paige
| |
| Christian Hamel 2006-02-28, 8:23 pm |
| Change the RECOVERY MODEL property of the database to "Simple" rather than
"Full"
"mmiller" <marc.miller@ct-enterprises.com> a écrit dans le message de news:
%23PjR4lGPGHA.2816@TK2MSFTNGP15.phx.gbl...
> Is there any way to stop SQL Server 2000 from logging? It is absolutely
> not necessary
> in my app.
>
> Is SQL Server 2005 any different?
>
> Thanks very much for your time,
> Marc Miller
> "Don't look back. Something might be gaining on you" - Satchel Paige
>
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| Hi
Can you elaborate a little bit?
Does your app connect to the database? What is the purpose?
"mmiller" <marc.miller@ct-enterprises.com> wrote in message
news:%23PjR4lGPGHA.2816@TK2MSFTNGP15.phx.gbl...
> Is there any way to stop SQL Server 2000 from logging? It is absolutely
> not necessary
> in my app.
>
> Is SQL Server 2005 any different?
>
> Thanks very much for your time,
> Marc Miller
> "Don't look back. Something might be gaining on you" - Satchel Paige
>
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| It will not perevent from logging. Even if SQL Server has 'SIMPLE' it is
still logging and the log file may grow up , hence I have asked to
elaborate his.
"Christian Hamel" < chamel@notyourbusine
ss.com> wrote in message
news:%23soPWpGPGHA.1216@TK2MSFTNGP14.phx.gbl...
> Change the RECOVERY MODEL property of the database to "Simple" rather than
> "Full"
>
>
> "mmiller" <marc.miller@ct-enterprises.com> a écrit dans le message de
> news: %23PjR4lGPGHA.2816@TK2MSFTNGP15.phx.gbl...
>
>
| |
| David Portas 2006-02-28, 8:23 pm |
| mmiller wrote:
> Is there any way to stop SQL Server 2000 from logging? It is absolutely not
> necessary
> in my app.
>
> Is SQL Server 2005 any different?
>
> Thanks very much for your time,
> Marc Miller
> "Don't look back. Something might be gaining on you" - Satchel Paige
You can't turn off logging but if you don't require log backups you
should select the Simple Recovery model so that the log is truncated at
checkpoint.
ALTER DATABASE db_name SET RECOVERY SIMPLE
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
| |
| Christian Hamel 2006-02-28, 8:23 pm |
| I know but it will log way less transactions which in many case is not a
problem while the full logged can be. Depends on his needs but might be a
start.
"Uri Dimant" <urid@iscar.co.il> a écrit dans le message de news:
uQwupsGPGHA.420@tk2msftngp13.phx.gbl...
> It will not perevent from logging. Even if SQL Server has 'SIMPLE' it is
> still logging and the log file may grow up , hence I have asked to
> elaborate his.
>
>
>
>
>
>
> "Christian Hamel" < chamel@notyourbusine
ss.com> wrote in message
> news:%23soPWpGPGHA.1216@TK2MSFTNGP14.phx.gbl...
>
>
| |
| mmiller 2006-02-28, 8:23 pm |
| Wow, great feedback! I'll try to answer the questions succinctly.
The recovery mode is set to simple. My app. does connect to the database and
runs processing
both from FoxPro and calls sprocs in the database.
The purpose of the app. is to populate a table with customer information,
one line per customer,
and then update the customers with the revenues that are generated from each
customer. This
requires updating the cust. table using approx. 15 other tables that are
populated from DTS jobs
from text sources. If there is a failure, the processing time is only about
25 min. total, so we can
just start from scratch rather than going through an entire restore. The
processing is checked and
balanced at 8 different points.
Thanks again all,
Marc Miller
"mmiller" <marc.miller@ct-enterprises.com> wrote in message
news:%23PjR4lGPGHA.2816@TK2MSFTNGP15.phx.gbl...
> Is there any way to stop SQL Server 2000 from logging? It is absolutely
> not necessary
> in my app.
>
> Is SQL Server 2005 any different?
>
> Thanks very much for your time,
> Marc Miller
> "Don't look back. Something might be gaining on you" - Satchel Paige
>
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| Hi,Christian
>I know but it will log way less transactions which in many case is not a
>problem while the full logged can be.
No , it will log the same amount of transactions( SIMPLE) ,but the LOG file
will be truncated as it reaches 70% full
"Christian Hamel" < chamel@notyourbusine
ss.com> wrote in message
news:%23T2hDwGPGHA.1676@TK2MSFTNGP09.phx.gbl...
>I know but it will log way less transactions which in many case is not a
>problem while the full logged can be. Depends on his needs but might be a
>start.
>
>
> "Uri Dimant" <urid@iscar.co.il> a écrit dans le message de news:
> uQwupsGPGHA.420@tk2msftngp13.phx.gbl...
>
>
| |
| Kalen Delaney 2006-02-28, 8:23 pm |
|
Hi Marc
The purpose of the transaction log is not just to help you, so you don't
have to re-enter transactions. It's actually more important for SQL Server's
own internal purposes.
One example is allocation.
If you are trying to insert rows into a table, and all pages currently
allocated to this table are full, SQL Server needs to allocate more pages.
SQL Server does the following:
1: Finds some free pages in the database file
2. Marks them as no longer available for anyone else
3. Assigns them to this table.
What if there were a failure between steps 2 and 3, maybe a crash of the OS,
which bring SQL Server down. YOU might say it's not a problem, you can just
reenter the new rows again. But SQL Server has already marked some pages as
being not available, that it has never used. So you try to insert again, and
again there is a failure. You can end up with lots of space in the file
marked as used, but no table is really using it. You can use up the whole
file with this mystery space, that is marked as no longer available, but no
table is using it.
Page allocations are written to the transaction log, so that SQL Server can
tell if the transaction they were part of actually succeeded or not. If not,
it can release those pages when it runs recovery upon system startup.
Without the log, you can end up with the problem described above, and some
worse ones. For example, what if you are inserting rows into tables with
lots of indexes, and SQL Server gets all the data inserted but doesn't
update the indexes. Then there is a failure. When SQL Server starts up,
should it keep the new rows around, even if the indexes don't know about
these new rows? That would be serious corruption. With the log, SQL Server
runs recovery on startup and makes sure that everything that completed is
reflected in the data, and everything that didn't complete is rolled back.
The purpose of the log is really to help SQL Server prevent inconsistent
structures; helping you redo your transactions is just an occasional bonus.
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"mmiller" <marc.miller@ct-enterprises.com> wrote in message
news:u%23x3K7GPGHA.3888@TK2MSFTNGP12.phx.gbl...
>
> Wow, great feedback! I'll try to answer the questions succinctly.
>
> The recovery mode is set to simple. My app. does connect to the database
> and runs processing
> both from FoxPro and calls sprocs in the database.
>
> The purpose of the app. is to populate a table with customer information,
> one line per customer,
> and then update the customers with the revenues that are generated from
> each customer. This
> requires updating the cust. table using approx. 15 other tables that are
> populated from DTS jobs
> from text sources. If there is a failure, the processing time is only
> about 25 min. total, so we can
> just start from scratch rather than going through an entire restore. The
> processing is checked and
> balanced at 8 different points.
>
> Thanks again all,
> Marc Miller
>
>
> "mmiller" <marc.miller@ct-enterprises.com> wrote in message
> news:%23PjR4lGPGHA.2816@TK2MSFTNGP15.phx.gbl...
>
>
>
|
|
|
|
|