LOG FILE - 90GB

I'm taking Backup of the LOG every 15 minutes, but which LOG has created up to 90GB.

While it is true every time you get a BK Full or LOG truncates the LOG, but this has grown a lot, as you can calculate how much is needed for transactions LOG and not grow as much, as in this case.

This is for several BD that you have.
enrique_aeoAsked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:
you may have to shrink the log file during a maintenance period. How large is individual log files, if its pretty big, you need to investigate what the issue is.
0
enrique_aeoAuthor Commented:
you may have to shrink the log file during a maintenance period. What do you mean, what should I do?

How large is individual log files, if its pretty big, you need to investigate what the issue is. Where do I begin?
0
Aneesh RetnakaranDatabase AdministratorCommented:
dbcc shrinkfile  is the statement to shrink the log file.

How many transaction are you processing / second ? do you have any background job that runs on sql server ? You said pretty big, are they more than 1GB ?  Page Splits can cause the log growth, here is an article that can help you
https://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> While it is true every time you get a BK Full or LOG truncates the LOG, but this has grown a lot

Log files can't be reused in certain scenarios when you have Active Log as listed below even if you have Log backups configured once every 15 minutes.
* Replication
* Long Running Transactions

To identify why the Log file is growing when it is growing, run the below command. Once that process is completed, you will be able to shrink your log file as Aneesh mentioned above..
Use ur_db_name
GO
select log_reuse_wait_desc
from sys.databases
where name = db_name()

Open in new window

0
enrique_aeoAuthor Commented:
in all the BD that I have executed the query it shows me the value "NOTHING"

whats the mean?
0
Mark WillsTopic AdvisorCommented:
It means there are no extraneous wait/log states on the log file. You want to see "NOTHING" is the best result of that query.

Read about it : https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017

Truncating a log file does not reduce allocated disk space, it means that the  entries within the transaction log can be flushed if no longer needed.

To make the log file smaller, you have to SHRINK it, but there are things that can stop it from shrinking.

The easiest way is to use SSMS right cleck on the Database Name (on left hand side in the Object explorer) go into Tasks => Shrink => Files => then use the drop down on file type and choose the transaction log. You should select the option to reorganise pages.

You can also do in T-SQL, but should do a full backup + Transaction log backup before and after issuing the following :
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Open in new window

The other option (during down time) and not really recommended
ALTER DATABASE <DatabaseName> SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(<TransactionLogName>, 1)
ALTER DATABASE <DatabaseName> SET RECOVERY FULL WITH NO_WAIT
GO

Open in new window

I have written an Article about the transaction log : https://www.experts-exchange.com/articles/657/Managing-the-Transaction-Log-for-the-Accidental-DBA.html

Ideally you need to plan ahead for optimal size of disk space to allocate to the various files a DB will use. Then monitor that against a benchmarked allocation and adjust or manage as needed.

Here is some commentary (from the bloke who wrote DBCC - Paul Randal) https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
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
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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.