help with transaction log

[bobh]

Hi,

I'm new to SQLServer from the standpoint of creating dbs and tables,
I'm an Access person but I now have SQLServer responsibilities so, I
recently had a tables' transaction log fill up which caused the Access
select query to not run/hang and then timeout. I was reading thru
this forum and came across this

"If your database is not critical set the recovery to SIMPLE
for that database. In Simple recovery model
the transaction log will be cleared on each checkpoint . "

my question are;
how do I get this SQLServer db and/or table to do a "checkpoint" so it
clears the log file?

is there a way to turn off having a tranaction log file? I have a
table that is just a accumulation table of monthly data, the only
thing that can be done is append records so I don't realy need a
transaction log file for that table.

thanks
bobh.

[Jeroen Mostert]

Post by bobh
I'm new to SQLServer from the standpoint of creating dbs and tables,
I'm an Access person but I now have SQLServer responsibilities so, I
recently had a tables' transaction log fill up which caused the Access
select query to not run/hang and then timeout. I was reading thru
this forum and came across this
"If your database is not critical set the recovery to SIMPLE
for that database. In Simple recovery model
the transaction log will be cleared on each checkpoint . "
my question are;
how do I get this SQLServer db and/or table to do a "checkpoint" so it
clears the log file?

Checkpointing occurs automatically with small intervals, but can also be
forced with the CHECKPOINT statement (which is almost never necessary).
Under FULL recovery, this won't do anything to reduce the size of the
transaction log -- it is your responsibility to make regular transaction log
backups (which shrinks the log). These backups allow you to do point-in-time
restores.

If you can live with losing the data of an entire day if your database goes
south, you can put your database in SIMPLE recovery and then you will only
be able to restore the database from any full backup (and differentials)
that you make. This means you don't have to do transaction log backups and
the log will not grow indefinitely. The downside is that you will not be
able to restore to any arbitrary point in time, but only to those points
where you made a backup.

To do this from Management Studio, right-click on the database, choose
"Properties", then "Options". In scripting terms, it's ALTER DATABASE ...
SET RECOVERY SIMPLE / SET RECOVERY FULL.

Post by bobh
is there a way to turn off having a tranaction log file?

You've just asked the most common and natural question that people new to
professional relational database management systems ask (Access cannot be
counted as one of these). The answer is a big and resounding no. The
transaction log is necessary to ensure data consistency even in the face of
things like power failure, network outages or even human error. SQL Server
can no more turn it off than you or I could stop breathing. It's your
responsibility to manage the size of the log. In return, SQL Server keeps
your data as safe as it can.

Post by bobh
I have a table that is just a accumulation table of monthly data, the
only thing that can be done is append records so I don't realy need a
transaction log file for that table.

The transaction log is database-wide and keeps the whole of the database
consistent. Turning it off for specific tables isn't possible either.
However, if you insert rows using a bulk insert operation, and the recovery
model is set to BULK, the bulk insert is minimally logged (meaning the
entire contents of the table isn't also written to the transaction log). I
won't expand on that in this post because it sounds like more complication
than you need.

Consult Books Online for more information on how recovery models work:
http://msdn.microsoft.com/library/ms189275

[bobh]

On 2012-10-24 19:12, bobh wrote:> I'm new to SQLServer from the standpoint of creating dbs and tables,

Post by bobh
I'm an Access person but I now have SQLServer responsibilities so, I
recently had a tables' transaction log fill up which caused the Access
select query to not run/hang and then timeout.  I was reading thru
this forum and came across this
"If your database is not critical set the recovery to SIMPLE
for that database. In Simple recovery model
the transaction log will be cleared on each checkpoint . "
my question are;
how do I get this SQLServer db and/or table to do a "checkpoint" so it
clears the log file?

Checkpointing occurs automatically with small intervals, but can also be
forced with the CHECKPOINT statement (which is almost never necessary).
Under FULL recovery, this won't do anything to reduce the size of the
transaction log -- it is your responsibility to make regular transaction log
backups (which shrinks the log). These backups allow you to do point-in-time
restores.
If you can live with losing the data of an entire day if your database goes
south, you can put your database in SIMPLE recovery and then you will only
be able to restore the database from any full backup (and differentials)
that you make. This means you don't have to do transaction log backups and
the log will not grow indefinitely. The downside is that you will not be
able to restore to any arbitrary point in time, but only to those points
where you made a backup.
To do this from Management Studio, right-click on the database, choose
"Properties", then "Options". In scripting terms, it's ALTER DATABASE ...
SET RECOVERY SIMPLE / SET RECOVERY FULL.

Post by bobh
is there a way to turn off having a tranaction log file?

You've just asked the most common and natural question that people new to
professional relational database management systems ask (Access cannot be
counted as one of these). The answer is a big and resounding no. The
transaction log is necessary to ensure data consistency even in the face of
things like power failure, network outages or even human error. SQL Server
can no more turn it off than you or I could stop breathing. It's your
responsibility to manage the size of the log. In return, SQL Server keeps
your data as safe as it can.

Post by bobh
I have a table that is just a accumulation table of monthly data, the
only thing that can be done is append records so I don't realy need a
transaction log file for that table.

The transaction log is database-wide and keeps the whole of the database
consistent. Turning it off for specific tables isn't possible either.
However, if you insert rows using a bulk insert operation, and the recovery
model is set to BULK, the bulk insert is minimally logged (meaning the
entire contents of the table isn't also written to the transaction log). I
won't expand on that in this post because it sounds like more complication
than you need.
Consult Books Online for more information on how recovery models work:http://msdn.microsoft.com/library/ms189275
--
J.

Thanks for your reply................

This db gets an append of a certain kind of transactions at each month
end and all other connection to it is 'select' data only. I have setup
a database maintenancce plan for this database to do a full backup of
this db each month after the monthly append.
If I understanding what you said above then setting the database to
'recovery simple' will keep the transaction log at a minimum and with
'checkpointing' done automatically the transaction log will get
cleaned at each checkpoint, right???

when a 'checkpoint is done is there a log of that fact that I can see
somewhere??
bobh.

[Erland Sommarskog]

Post by bobh
This db gets an append of a certain kind of transactions at each month
end and all other connection to it is 'select' data only. I have setup
a database maintenancce plan for this database to do a full backup of
this db each month after the monthly append.

If the only occasion when then database gets written to is this end-of-month
activity, simple recovery makes sense. If the database would die before the
backup, I assume that you redo the load.

But if you after all have updates in between you don't want to lose, you may
want to consider whether you want to lose those changes.

Post by bobh
If I understanding what you said above then setting the database to
'recovery simple' will keep the transaction log at a minimum

More precisely, the log will be as big as your biggest transaction needs. If
this monthly appears loads on 1GB in a single transaction and requires full
logging, you will need at least 1GB in log space, probably more. SQL Server
must be able to roll back the transaction if it never commits.

Post by bobh
when a 'checkpoint is done is there a log of that fact that I can see
somewhere??

No, just like few people have a log of every breath they take.

[Erland Sommarskog]

Post by bobh
is there a way to turn off having a tranaction log file? I have a
table that is just a accumulation table of monthly data, the only
thing that can be done is append records so I don't realy need a
transaction log file for that table.

Just enforce Jeroen's post: if you one day find the database is not
accessible for whatever reason, for instance disk crash, will you just
shrug your shoulders and say "data come and data go, and that was that"?

In other words, how much of this data can you afford to lose? This will
determine your backup and restore needs.