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.
16  3830 
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 (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
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 (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
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.
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(DBNa me_log, 1)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBNa me_log, 1)
go
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 ,
|
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...
|
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
|
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...
|
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...
| | |
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,
|
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...
|
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...
|
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
|
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...
|
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,...
| | |
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
