473,856 Members | 1,630 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question on Transaction Log Backups

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.

May 23 '06 #1
16 3836
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
May 23 '06 #2
DataPro (da*******@yaho o.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, es****@sommarsk og.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
May 23 '06 #3
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
May 24 '06 #4
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.

May 24 '06 #5
DataPro (da*******@yaho o.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, es****@sommarsk og.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
May 24 '06 #6
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.....lo g 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.

May 24 '06 #7
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

May 24 '06 #8
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(DBNa me_log, 1)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBNa me_log, 1)
go

May 24 '06 #9
pb648174 (go****@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(DBNa me_log, 1)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBNa me_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, es****@sommarsk og.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
May 24 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
7581
by: Thiko | last post by:
Hi I take one nightly full database backup at 02:00 and backup the transaction log to one backup set every 15mins. The commands to do this are as follows and are set up to run as database jobs: -- Database Backup BACKUP DATABASE TO WITH NOINIT ,
1
8847
by: m3ckon | last post by:
If someone could help me with this I will be in debt to you! I am a .net developer who is working on a system that has a sql server 2000 backend. On the weekends, I work from home and I have MSDE on my laptop. This morning I went to update the main SQL server as I added some new views .. however I managed to import the data too!! Therefore I have overwritten my local data to the main sql server database, which has ressulted in loss of...
4
1489
by: James Cheung | last post by:
Is it possible to load both the SQL 7 database and transaction log backups to SQL 2000 ? I assume it will perform the upgrade during the load. Thanks, James
1
1465
by: malcolm | last post by:
In sql server 2000, how do you manually go and truncate the logs? Does this only occur when you create a backup? all I want to do is manually shrink the log files. I have backups, so I don't need them. I actually backup the db once a week and back up the logs every day, yet the logs are getting real big. I thought this was configured in the backup and/or the maintanance plan. I've done a restore (from the db backup) manually hundreds of...
2
3249
by: francois1 | last post by:
I am running a website with a SQL Server database attached. My transaction logs are full and my hosting co. won't allocate more disk space for me. I need to delete my database transaction logs and asume I will need to run an SQL script to do this. Problem: I do not have MS Enterprise Manager of any database utility on my website apart from MS Access. Where can I download a free SQL tool that I can use to delete the transaction logs...
4
13227
by: yashgt | last post by:
Hi, We have created a SQL server 2000 database. We observe that the transaction log keeps growing over time. We are now about to run out of space. We have been periodically shrinking the database. Nevertheless the size has increased. I would imagine that a transaction log can be eliminated if we stop the database. Can that be done? Is there a way to completely wipe off the transaction log? Thanks,
0
1667
by: flobroed | last post by:
Hi, I've a question regarding the transaction-log backup on SQL-Server 2000. We have implemented a low cost replication. Every evening we make a full backup and beginning at 7 to 18 we make transaction-log backups which are restore (no recovery) to the "standby-server". The full backups are restored every evening. Today i noticed something strange. Yesterday the last transaction log was made at 19 and afterwards applied to the standby...
4
4223
by: Trevor Best | last post by:
I have a client with 150MB database, the transaction log file is nearly 23GB. It's a PITA for me to backup his data and restore it on my server as it takes about 30 minutes to restore as it re-creates a 23GB file with no transactions in it :-\ As well as the space taken up by it, does it do any harm being that size? e.g. does it decrease performance? While it's there, considering the database is backed up daily, does it actually do...
4
18239
by: Brian D | last post by:
In MS SQL 2005 when you do a Full Backup does it also backup and truncate the transaction logs or do I need to back the transaction logs up separately? Thanks. Brian
0
9918
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9763
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11060
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10697
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9532
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7932
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7097
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5762
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4175
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.