Reduce Log Database

Question

Post reply

Reduce Log Database

angelreynosog

Mr or Mrs. 500

Points: 545
More actions
June 27, 2016 at 8:01 am

#400327

Dear good day ,
I have a database of Dynamics NAV , this overgrown in the week do a FULL, every day at 3 A.M. , a differential every day at 1 P.M . , . The database weighs approximately 4GB, this is because the Log , is very huge. I wanted to make a TRUNCTING the Log, but they said it is not recomentable . What we should do is make a backup so they do not grow . Within the FULL I do, I correct Index, etc. As I can do to make the log , can be reduced and may have more space without losing transaction if it go to waste . HELP!!!!!! .
I plan maintenance that are running automatically , so you want is to have all standardized.
Thank you very much!
Best regards,

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What recovery model are you in?

How often do you back up the transaction log?

What's the maximum allowable data loss for this DB, in the case of a disaster?

Please take a read through http://www.sqlservercentral.com/articles/Administration/64582/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

angelreynosog (6/27/2016)
Dear good day ,
I have a database of Dynamics NAV , this overgrown in the week do a FULL, every day at 3 A.M. , a differential every day at 1 P.M . , . The database weighs approximately 4GB, this is because the Log , is very huge. I wanted to make a TRUNCTING the Log, but they said it is not recomentable . What we should do is make a backup so they do not grow . Within the FULL I do, I correct Index, etc. As I can do to make the log , can be reduced and may have more space without losing transaction if it go to waste . HELP!!!!!! .
I plan maintenance that are running automatically , so you want is to have all standardized.
Thank you very much!
Best regards,

Assuming that you're in either the FULL or BULK LOGGED recovery model because the log file has continued to grow...

Neither Full nor Differential backups do anything with the log file. You have to backup the log file if you want it to truncate correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

angelreynosog Mr or Mrs. 500 Points: 545 More actions · Answer 3

Hello Mr. Gail Shaw,

There is no defined structure as such , this is why I'm looking for guidance , how could. Currently , it is being done as I said , Backup Full at 3 A.M. , Differential Backup at 1 P.M., and the Log , you will be every 15 minutes ago, but as I had said it in the log that is the problem .

Thank you so much!

Y.B. SSChampion Points: 11642 More actions · Answer 4

Check the properties of your database and find it's Recovery Model.

Log files are automatically truncated in Simple recovery model. Otherwise if the recovery model is set to FULL or Bulk-Logged you need to do Transaction Log backups in order to truncate the log file. A big misconception is that Full backups clear the log file...it does not.

EDIT: I should note that there is usually a follow up question that normally goes along the lines of "I did a backup of my log file but it's still the same size...". You should note that truncating isn't the same thing as shrinking. During truncation you will free non-active VLF's inside your log file that can be reused. This in theory will prevent it from growing any larger if transactions and log backups are consistent.

SELECT quote FROM brain WHERE original = 1
0 rows returned

jasona.work SSC Guru Points: 50082 More actions · Answer 5

angelreynosog (6/27/2016)
Hello Mr. Gail Shaw,
There is no defined structure as such , this is why I'm looking for guidance , how could. Currently , it is being done as I said , Backup Full at 3 A.M. , Differential Backup at 1 P.M., and the Log , you will be every 15 minutes ago, but as I had said it in the log that is the problem .
Thank you so much!

A couple things:

Keep in mind, the Transaction Log will grow to be able to contain all the transactions it needs to. If you've got a long-running query, that will prevent your 15 minute log backups from marking the space as available for re-use. Thus, the log grows. Now, on the other hand, if you have lots and lots of short little queries that run in that 15 minute time-span, then potentially more frequent log backups might help keep the size of the log down.

The Full and Differential backups, as Jeff M pointed out, will not affect the Transaction Log. If you had a DB in Full Recovery, and all you ever did were full and differential backups, the TLog would just keep growing until it either filled the disk or hit whatever size limit was set.

So, let's take this back to basics.

What exactly are you trying to accomplish?

Reduce the size on disk of the Transaction Log? If so, why? It will quite likely only grow to that size again.

Jason A.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

angelreynosog (6/27/2016)
There is no defined structure as such , this is why I'm looking for guidance , how could. Currently , it is being done as I said , Backup Full at 3 A.M. , Differential Backup at 1 P.M., and the Log , you will be every 15 minutes ago, but as I had said it in the log that is the problem .

Let's try again.

What recovery model are you in?

How often are you backing up the transaction log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

angelreynosog Mr or Mrs. 500 Points: 545 More actions · Answer 7

Y.B. (6/27/2016)
Check the properties of your database and find it's Recovery Model.
Log files are automatically truncated in Simple recovery model. Otherwise if the recovery model is set to FULL or Bulk-Logged you need to do Transaction Log backups in order to truncate the log file. A big misconception is that Full backups clear the log file...it does not.
EDIT: I should note that there is usually a follow up question that normally goes along the lines of "I did a backup of my log file but it's still the same size...". You should note that truncating isn't the same thing as shrinking. During truncation you will free non-active VLF's inside your log file that can be reused. This in theory will prevent it from growing any larger if transactions and log backups are consistent.

Good day Mr. Jason A.

I have a FULL recovery model , observing in SQL Server , so I want to get at is to reduce the size of the Log, the following issues , I think the Log, should not be so huge, and added to this is that the disc no longer available

angelreynosog Mr or Mrs. 500 Points: 545 More actions · Answer 8

GilaMonster (6/27/2016)
angelreynosog (6/27/2016)
There is no defined structure as such , this is why I'm looking for guidance , how could. Currently , it is being done as I said , Backup Full at 3 A.M. , Differential Backup at 1 P.M., and the Log , you will be every 15 minutes ago, but as I had said it in the log that is the problem .
Let's try again.
What recovery model are you in?
How often are you backing up the transaction log?

Good day Mr. Gail Shaw,

What recovery model are you in ?

Full

How often are you backing up the transaction log ?

Every 15 minutes the Log backups

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

If you run these, what's the result for the database you have the problem with?

DBCC SQLPERF(LogSpace)

SELECT name, log_reuse_wait_desc from sys.databases

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

angelreynosog Mr or Mrs. 500 Points: 545 More actions · Answer 10

GilaMonster (6/27/2016)
If you run these, what's the result for the database you have the problem with?
DBCC SQLPERF(LogSpace)
SELECT name, log_reuse_wait_desc from sys.databases

Good day Mr. Gail Shaw,

This is the result , I introduce myself

Database Log Size(MB) Log Space User(%) Status

YAD-NEW-CATALOG 3717.805 2.707766 0

YARDNAVISION 2566.43 1.480124 0

angelreynosog Mr or Mrs. 500 Points: 545 More actions · Answer 11

GilaMonster (6/27/2016)
If you run these, what's the result for the database you have the problem with?
DBCC SQLPERF(LogSpace)
SELECT name, log_reuse_wait_desc from sys.databases

Good day Mr. Gail Shaw,

This is the result , I introduce myself

Database Name Log Size(MB) Log Space Used (%) Status

YARD-NEW-CATALOG 3,717.805 2.707766 0

YARDNAVSION 2,566.43 1.480124 0

Greetings and thanks for your help

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

And the second query?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

angelreynosog Mr or Mrs. 500 Points: 545 More actions · Answer 13

angelreynosog

Mr or Mrs. 500

Points: 545

June 28, 2016 at 1:23 pm

#1887423

It is the same query , not wanting to send it twice

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

angelreynosog (6/28/2016)
It is the same query , not wanting to send it twice

Run this:

SELECT name, log_reuse_wait_desc from sys.databases