SQL DB full with logs

Hi Experts,

we have several DB´s on a SQL Cluster.
But all logs go to the same log file.
Is it right ?

And how to clean up all the transaction logs ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raj-GTSystems EngineerCommented:
I don't think you can't have all logs go to the same file, same folder perhaps. The logs are made of multiple files and it's not a single file. The logs will only clear when you run a backup against the DB. You can also enable 'Simple' recovery model on a database to automatically truncate the logs, but then you will not be able to do point in time recovery.

Read this link for some more information: http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Thanks,
Nimal
0
Tony303Commented:
I agree, I don't think it is possible to have all the DB's going to one transaction log.
If you need point in time restores, create a maintenance plan to backup the transaction logs on a regular basis between full backups. I do daily full and hourly log backups. Most data to lose will be an hour.
0
Eprs_AdminSystem ArchitectAuthor Commented:
yes you are right, there are different log files
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Eprs_AdminSystem ArchitectAuthor Commented:
when I have 270GB of logfiles, how to avoid this big data ?
When the backup is done all logfiles are at zero again ?
0
Raj-GTSystems EngineerCommented:
Yes. The backup will reduce the log file size again. You are better off changing the recovery model of the databases to simple if you don't need up to the minute recovery. Speak to the application vendor first in any case and see what's the best option.
0
Eprs_AdminSystem ArchitectAuthor Commented:
ok thanks I will go for 2 hourly incremental backups after the full.
0
Eprs_AdminSystem ArchitectAuthor Commented:
But what can I do with the rest of the transaction logs ?
I still have 200 GB logs after the full backup was successful and the incrementals also successful.
How to force the SQL to write all these pages to the DB ?
0
Tony303Commented:
Hi,

Set the recovery to SIMPLE

DBCC SHRINKFILE('MyDatabase_Log', 100)
(100 being 100MB)


Then set it recovery mode back to FULL.
0
Tony303Commented:
Wait, are you doing incremental backups or Transaction Log backups?
0
Eprs_AdminSystem ArchitectAuthor Commented:
First I make a full backup, daily.
Then I want to do each 3 hours an incremental backup METHOD LOGS.
And one more incremental backup each 3 hours METHOD DIFFERENTIAL.

But now I still have 200GB of log files and I want to get rid of it.
But I will not loose any data.
0
Tony303Commented:
Hi Eprs,

Let's look a the backup thing first.
I think that having a Differential Backup AND a Log Backup every 3 hours are actually doubling effort for the same thing. Basically, you don't need the log backup at the same time as the differential from a restore point of view.

See the "Minimise Work-Loss Exposure" in the MS Books online...
http://technet.microsoft.com/en-us/library/ms190217(v=sql.105).aspx

Pay particular attention to the diagram and the relationship with the FULL, DIFF and LOG backups.


The log file...

Do a Full backup....just to be sure (ideally, with no users in the DB).

Then
Set the recovery to SIMPLE

DBCC SHRINKFILE('MyDatabase_Log', 100)
(100 being 100MB)


Then set recovery mode back to FULL.

Do another Full backup.

Let the DIFFERENTIAL or LOG backups occur automatically from your decisions from the Books Online article.


T
0
Eprs_AdminSystem ArchitectAuthor Commented:
Hi first of all to the log and differential method.
Symantec write this to each SQL Backup job.
You have to create one full and two incremental jobs.
Now you say this is not needed, which is right now ?
0
Scott PletcherSenior DBACommented:
There is nothing wrong with having both backups, and in certain cases the differential backups may be needed even if you have log backups.

The log file will never automatically shrink itself.  You must explicitly shrink the log if it gets too big.

However, if the log backups were in place when the log(s) got that big, you probably need that log to remain that larger size.  

Either way, to insure best performance, as a one time thing:

1) shrink the log to the minimum size
2) re-allocate the log to the largest size you think you will need during normal processing.  Add 2GB "chunks" if you need more than 2GB total.  Also, reset the file growth to a specific reasonable amount (not a % growth).


USE db_name
CHECKPOINT

-- shrink the log as much as possible
DBCC SHRINKFILE( 'db_name_log' )

-- re-allocate the log bigger: for this example, to 6GB
ALTER DATABASE db_name
MODIFY FILE ( NAME = db_name_log, SIZE = 2GB, FILEGROWTH = 80MB )
ALTER DATABASE db_name
MODIFY FILE ( NAME = db_name_log, SIZE = 4GB )
ALTER DATABASE db_name
MODIFY FILE ( NAME = db_name_log, SIZE = 6GB )
0
Tony303Commented:
Scott,

I just want to get my mind straight, we are all students after all..

There is nothing wrong with having both backups, and in certain cases the differential backups may be needed even if you have log backups.

I wrote...
Basically, you don't need the log backup at the same time as the differential from a restore point of view.

If you do a Differential and a Log backup every 3 hours, I assuming at the same time. What is the benefit? From a restore point of view you'd restore your last Full then your latest Differential.....doesn't this nulify the need to restore translogs? And if you are doing a Differential, which takes 5 mins say, and the Log backup kicks in at the same time what will actually have all the data?

Sorry to be a pest.

Tony
0
Scott PletcherSenior DBACommented:
Differentials must be applied all-or-none.  So, a differential taken at, say, 3:00PM can only get you to 3:00PM -- not before and not after.

Say an error occurred at 3:37PM and you want to fully rebuild the database to just before that, i.e., 3:36PM.  You could restore the differential from 3:00PM and apply the relevant portions of the log backup from 6:00PM.  But note that you couldn't use the diff from 6PM, because that would take the database too far forward.

Yes, you could certainly all the log backups from the last full backup forward instead, but that might take considerably longer than just applying the last differential and only ~36 minutes worth of log activity.
0
Tony303Commented:
OK thanks,
0
Scott PletcherSenior DBACommented:
CORRECTION: I left out a key word and added one note::

Yes, you could certainly apply all the log backups instead, in order (required!), from the last full backup forward, but that might take considerably longer than just applying the last differential and only ~36 minutes worth of log activity.
0
Eprs_AdminSystem ArchitectAuthor Commented:
ok thanks for all the input.
One last question, what is better:

1. the backup of DB and Logs via SQL Management Studio

or

2. the backup via Symantec backup Exec.

Is it wise to use both together ?
0
Tony303Commented:
My preference has been to use SQL to do the backups, then the Symantec Backup to only backup the .bak and .trn files that the SQL process has created. IE, I don't let Symantec near SQL.

I am open to Scott's advice here though.

T
0
Scott PletcherSenior DBACommented:
I'm almost certain Symantec has the correct code to accurately call the SQL api's for making sure the backups taken by it are clean and proper.  If it does indeed have the "live" backup component, I'd let Symantec do it, because often 3rd party tools can do a much faster backup than native SQL.  For example, we have RedGate backup (etc.) tools here, which are vastly faster than native SQL.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eprs_AdminSystem ArchitectAuthor Commented:
hi Scott, is this also a command ?

USE db_name
CHECKPOINT
0
Eprs_AdminSystem ArchitectAuthor Commented:
please can you write all commands which I need in detail ?
0
Eprs_AdminSystem ArchitectAuthor Commented:
ok so far you helped me a lot.
I will close the ticket.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.