Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

transaction log

Posted on 2009-12-18
7
Medium Priority
?
182 Views
Last Modified: 2012-05-08
there is a full transaction log issue sql 2000. when i looked i couldnt find any backup of the database. so if there is an emergency, should i take a full backup, followed by transaction log backup and shrink the log file?
0
Comment
Question by:sqlserverdba
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26083312
you should create a backup for your system.. Something like this
 Full backup : one in a week
 differential data backup : once per day
 trnasaction log backups : at least once in 30 min
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26083319
>so if there is an emergency
this depends.. if it is a disk failure, you never gonna get a chance to do the backup
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 26084989
Mmm... I differ from that comment.

There are two paths to follow when performing a database backup plan, differential and transactional, but not both of them at the same time.

I prefer to use transactional because it keeps so healthy the log file, of course to stay away from problems I do it mirrored in different places.

Remember sqlserverdba that a log backup moves the content to the backup file and then the log file get cleared.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:sqlserverdba
ID: 26091675
what i wanted to know was
1. during business hours in case its an emergency do i take a full backup? because there seems to be no backup whatsoever (this is an internal database).
2. So will users be affected if i take full backup, transaction log backup and possibly a shrinkfile command?. What will happen to current users and transactions?
3. because transaction log is full, do i take backups and truncate the log?
0
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 2000 total points
ID: 26097923
The users will be affected by a slow system during your full backup and the shrinking file process.

In emergency, I prefer to do a full backup and a log backup.

The log is automatically truncated when you do a log backup of your database.    The transactions that are currently in execution are not affected by the backup due to backup only takes the commited transactions.
0
 
LVL 1

Author Comment

by:sqlserverdba
ID: 26101438
so can i just do full backup followed by log backup to free log space and dbcc shrinkfile to make the transaction log available? will any transactions be lost if i follow this process? Is it the solution to this scenario..
0
 
LVL 15

Assisted Solution

by:jorge_toriz
jorge_toriz earned 2000 total points
ID: 26101776
Well, you can avoid the dbcc shrinkfile step, the only thing that you want is to free space of your log file, with the full backup and log backup you achieve this objective.

Yes, no transactions will be lost.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question