New to Sql Server, running SQL Server 2000.

Our transaction log file backups occasionally fail as the size of the
transaction log gets really huge. We'd like to schedule additional
transaction log backups. Does that require an exclusive on the database
or can the db be used during a transaction log backup?

Also, does switching to a bulk mode recovery model before a bulk
operation then switching back to full recovery mode after present any
issues?

Thanks in advance.

DataPro wrote:
> New to Sql Server, running SQL Server 2000.
>
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?
>
> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

It doesn't make sense to switch back and forth. You can permanently
switch to bulk logged because different logging for bulk operations is
all this recovery model is about.

See Microsoft's documentation for more details:

http://technet2.microsoft.com/Window....mspx?mfr=true

Kind regards

robert

DataPro (datapro01@yahoo.com) writes:
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?

Translog backups can be taken without conflict for other users. Else
it would be a quite meaningless operations. Some sites back up their
transaction log every 15 minutes or so.

> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

In difference to Robert, I think this makes sense. I believe we do this
in the maintenance procedure that we ship with our product. We switch
to bulk-logged before starting reindexing of tables, and then switch
back to full when it's done.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog wrote:
>> Also, does switching to a bulk mode recovery model before a bulk
>> operation then switching back to full recovery mode after present any
>> issues?
>
> In difference to Robert, I think this makes sense. I believe we do this
> in the maintenance procedure that we ship with our product. We switch
> to bulk-logged before starting reindexing of tables, and then switch
> back to full when it's done.

Hm, I can see that it makes sense if you do this for *some* bulk
operations only. My understanding of the question was whether it makes
sense to do it always for bulk operations. And in that case I don't
think it does: if I switch from "full" to "bulk logged" before any bulk
op and switch back afterwards I get the same behavior as if "bulk
logged" was used all the time.

Kind regards

robert

Many thanks for the thoughts. Being brand new to SQL Server (coming
over from DB2) have much to read and learn.

Here is my situation. An application upgrade occurred over the weekend
which made several database changes. Now the active log is at 12 Gigs.
Taking a full database backup and a log backup is not truncating the
log. Issuing a backup log truncate only has no effect.

I ussed a dbcc loginfo against the database and it returned 308 rows. I
am assuming that these 'virtual logs' represent active non-committed
transactions.

Is that right?

We are talking to the application vendor asking him why this occured.
In the meantime,,,,

Any thoughts on how I can reduce the size of this log? From what I've
read about shrinkfile it doesn't appear to be relevant here.

Any thoughts or suggestions would be appreciated.

DataPro (datapro01@yahoo.com) writes:
> Here is my situation. An application upgrade occurred over the weekend
> which made several database changes. Now the active log is at 12 Gigs.
> Taking a full database backup and a log backup is not truncating the
> log. Issuing a backup log truncate only has no effect.

It has, but that will not shrink the log file. (Unless autoshrink is in
effect, but you don't want that.) Only give you a lot of free space in
the file.

Note that TRUNCATE_ONLY invalidates the transaction log, so you need to
take a full backup after this operation.

> Any thoughts on how I can reduce the size of this log? From what I've
> read about shrinkfile it doesn't appear to be relevant here.

DBCC SHRINKFILE is indeed your guy. Be sure to set a target size when
you run it, so you don't make the log too small. In that case, it will
have to grow again, and that will only take performance from the system.
And the file may become fragmented on disk.

For things like an application upgrade, the best strategy may be to set
the recovery mode to simple, and set back to full recovery when it the
upgrade is done. Again, you need to take a full backup in this situation.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Here is what the vendor is recommending:

1. Back up db and log via Windows Explorer or normal db backups
2. Stop services (application...EAS)
3. Disconnect/detach database
4. Delete log via Windows Explorer
5. Reconnect/reattach database.....log will dynamically be allocated

Does this make sense?
They also want autoshrink turned on but its my understanding some SQL
DBA's don't want that. Your thoughts?

Thanks in advance.

Well SQL Server definetely seems to be a different animal.

Here are some questions a newguy like myself would have about the
logging.

The documentation says that once a backup log truncate only is done, a
full backup should be taken as the recovery ability of the system has
been degraded. Why is that, if that command merely archives committed
transactions?

A SELECT @@TRANCOUNT yields a count of zero.
dbcc opentran(eas) says zero also.
dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
that they are open transactions. Why don't they show up in trancount
then?

If a log backup supposedly makes the log 'reusable' then why would a 12
gig log grow another Gig right within a few hours of the backup with
minimal database use?
Wouldn't most of that space be reusable.

Please straighten me out on my thinking

Thanks

We shrink the transaction log every two hours without any noticeable
performance hit, but our total database size is less than 500 MB. I
have played with the simple logging one time and had problems with it,
so we have been sticking with our two hour shrink process (added to our
two hour backup process) which works fine for now.

DBCC SHRINKFILE(DBName_log, 1)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBName_log, 1)
go

pb648174 (google@webpaul.net) writes:
> We shrink the transaction log every two hours without any noticeable
> performance hit, but our total database size is less than 500 MB. I
> have played with the simple logging one time and had problems with it,
> so we have been sticking with our two hour shrink process (added to our
> two hour backup process) which works fine for now.
>
> DBCC SHRINKFILE(DBName_log, 1)
> BACKUP LOG DBName WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(DBName_log, 1)

This appears completely pointless to me. If you have full recovery,
you are losing all the benefits by truncating the transaction log
every two hours. If you have simple recovery, there should not be
any need to shrink the file, unless there has been any extraordinary
actions going on.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx