SQL Server - TX log troubles

Asked By DanDeCourse

10-Jun-08 10:48 AM

Hello
Using SQL 2005

We had someone setup a few small but crucial databases
We have some "jobs" that run at nightthat export data out
We often find in the morning that a job will fail and thatthe unfderlying
reason is the TXlog is FULL      the recovery model = FULL

we see the original developer gave the TXlog file an enormous start size
we have a dedicated 36gb drive and he sizes the TXlog file to be like 30gb
to start and to grow  1mb auto grow  !!!!!

Can we change this once established ?   I though the log file would
dynamically grow and shrink itself within that established file size


recommendations ????

thanks

10-Jun-08 12:01 PM

Dan,

If a database is in FULL recovery model, in addition to doing a BACKUP
DATABASE it is also necessary to BACKUP LOG for that database.  Only the
backup of the transaction log will free log space for reuse.  (Maybe this is
being done, but you don't mention it, so make sure that the backups are
happening.)

Second, the frequency of log backups needs to match the amount of updates
that are being run against it, from a minimum of daily up to every few
minutes.  It may be that your log backup is running, but during the nightly
exports will need to run more frequently.

In your note you mentioned that autogrow was set to 1 megabyte.  If that was
not a typo for 1 gigabyte, then it is quite possible that the tiny 1 mb
autogrow cannot keep up with the growth caused by a large set of changes.
In that case, you might get a failure because of that cause.

The physical log file will not shrink.  It will free up internal space which
can be reused for future logging activity.  In general, you don't want to
shrink the file, but if is grew uncontrolled and needs to be reset to a more
reasonable size, then shrink it.  This article is the standard reference:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp

RLF

10-Jun-08 12:53 PM

Russell  thanksfor the reply,


The only backup that I am aware of is our complete  server backup (We user
ARCSERVE from CA) that is  run each night......so any type of "backup" that
is a feature or facility within SQL server I dont think that is being
done.......
due to the nature of this DB gets populated with data imported from a
different (Oracle) DB..I think the mechanics hhere is just that a large
fast...and NO that was not a typo...it IS 1mb


How do I do this SQL log back up ?

10-Jun-08 01:15 PM

Dan,

If ARCSERVE is also using the SQL Backup Agent, it can do SQL Server
backups.  Have those responsible for backups check and (if it is the correct
action for you) include log backups in their planning.   If is strictly an
ARCSERVE file backup then the SQL Server databases are not being backed up.
To do a database and a log backup to disk (obviously some other disk than
the one that is filling up) the basic SQL Server commands would be:

BACKUP DATABASE MyDatabase
TO DISK '\\ServerName\ShareName\FolderName\MyDatabase_<<Date and Time>>.BAK

BACKUP LOG MyDatabase
TO DISK '\\ServerName\ShareName\FolderName\MyDatabase_<<Date and Time>>.TRN

Of course, if you have no real use for the transaction log's data, you don't
need to back it up at all.  That may be your case if ALL the data is coming
from Oracle and this is just a snapsnot.  If the database also includes
other locally maintained data, then you will need to decide how much you can
afford to lose and whether transaction logs are the tool you want to use.

In order to change to SIMPLE mode so that the server can automatically free
transaction log space for log entries that are earlier in time than the
oldest currently open transaction.

ALTER DATABASE MyDatabase
SET RECOVERY = SIMPLE

Of course, if one transaction caused 30 GB of transactional logging, you
would still have a problem.  So, keep the transaction sizes small enough to
avoid overflowing the log.

And, if you still need to autogrow, make the autogrow big enough to get out
of its own way.  Certainly 1GB is better than 1MB.

RLF

10-Jun-08 01:20 PM

Fix typo:

BACKUP DATABASE MyDatabase
TO DISK '\\ServerName\ShareName\FolderName\MyDatabase_<<Date and Time>>.BAK'

BACKUP LOG MyDatabase
TO DISK '\\ServerName\ShareName\FolderName\MyDatabase_<<Date and Time>>.TRN'

10-Jun-08 02:07 PM

Are these commands given out a CMd prompt or somewhere within the MGMT studio?

10-Jun-08 02:10 PM

russell, sorry to pester.......how do I  check the values of the
log_reuse_wait   and log_reuse_wait_desc  in catalog view......I cant seem to
understand this catlog view...........is this a matter of checking properties
or running a query?

Dont you love us greehorns?

10-Jun-08 03:35 PM

Dan,

From the query window in SQL Server Management Studio or as a SQL Agent Job
Step or as a script that can be run from the command line utility OSQL (or
if SS2005 you can use SQLCMD).  All described in the SQL Server Books Online
if you know what to look for.

If this needs to be run at an off-hours time, SQL Agent is a better answer.

Also, if you use a Maintenance Plan (also in the BOL) it will create a job
for you and automate some of the file naming issues.  (Of course, if you
have backup files where you never did before, you have another space
management issue to track.)  And, again, make sure that you and whoever runs
your backups are not in conflict with one another.

RLF

10-Jun-08 03:59 PM

Dan,

This is also running a query.  Everything worthwhile is from running a
query. :-)  Read about what the data means in:
http://technet.microsoft.com/en-us/library/ms178534.aspx  (Be sure and read
the Note: on  LOG_BACKUP toward the bottom of this article.)

A simple query is:

SELECT name AS DatabaseName,  log_reuse_wait , log_reuse_wait_desc
FROM sys.databases
ORDER BY name

Of course we love greenhorns.  (Of course, we also worry that they are going
to hurt themselves.  So always think through any advice you receive, and
double-check the links that get posted.)

RLF