Transaction log size up to 1.5 GB in 3 hours

Question

Post reply

Transaction log size up to 1.5 GB in 3 hours

Viewing 15 posts - 16 through 29 (of 29 total)

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

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 1

Nope

It seems something is not updating the column again so i ask all the experts again.

What would cause the value log_reuse_wait_desc to not update?

I am not running mirroring or replication just the basic SQL Server 2012 / 2008 enterprise edition DBs

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

hurricaneDBA (11/21/2016)
What would cause the value log_reuse_wait_desc to not update?

I already answered that.

The value shows what prevented one or more VLFs from being marked reusable last time log truncation was attempted.

It doesn't mean that no VLFs were marked reusable. It doesn't mean your log is full. It doesn't mean that the log backup isn't working.

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

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

The question here is why the log grows to 1.5GB.

How big is the database?

How big are your hourly log backups files for this database?

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

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 4

The hourly transaction logs vary in size from 4mb to now it's 12GB

The size went up to 12GB from 2gb in one night as indexing and crawling were enabled in SP

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

hurricaneDBA (11/21/2016)
The hourly transaction logs vary in size from 4mb to now it's 12GB

Well that's the answer to why your log is growing.

If your log backup is 12GB, that means there was 12GB (or a bit more) in the transaction log at the time of the backup, which means the log file would have needed to be 12 GB or larger at the time.

I suspect the increase in the size of the log is from increased activity, not something wrong.

Couple options:

* Back up the log more frequently, if the log uses 12GB in an hour, then backing up every 15 minutes would mean that the log backups are only around 3GB and the log should grow less

* Switch to simple recovery model if point in time recovery is not required, and a Sharepoint index database should be OK with data loss, since the documents can be re-crawled

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

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 6

I stand corrected

The transaction log backup sizes vary from

85K to 150MB

The biggest one was this morning at 6am at 11gb but after that the sizes vary from 4MB to 6MB now

So moral of the story is the LOG_BACKUP still means that the truncation is occurring and i need to run more frequent TL backups

Is it common for the value for log_reuse_wait_desc to change from LOG_BACKUP to NOTHING on every log transaction job?

Kal

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

hurricaneDBA (11/21/2016)
The biggest one was this morning at 6am at 11gb but after that the sizes vary from 4MB to 6MB now

I would suggest running them every half an hour all day, all night, and see how the log size stabilises.

The crawls and indexing is going to be running all the time, so not backing the log up for ~12 hours overnight means the log grows during that time.

Is it common for the value for log_reuse_wait_desc to change from LOG_BACKUP to NOTHING on every log transaction job?

The value for log_reuse is what prevented one or more VLFs from being marked reusable the last time that log truncation ran. It's not what is, at this instant, happening in the log. It's not updated by every single statement that touches the 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

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 8

Ok i will inform my management and see what i can do as they are now thinking of putting the recovery mode as simple

But i tried to run the following commands:

BACKUP LOG MyDB TO DISK = 'nul'

select name, log_reuse_wait_desc from sys.databases

MyDbLOG_BACKUP

Checkpoint

go

select name, log_reuse_wait_desc from sys.databases

MyDbLOG_BACKUP

So it didnt change and this is my test database with no transactions taking place

Kal

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 9

I figured out my issue

I was running checkpoint while using another database than the one in the script

So when running the scripts if i include

CHECKPOINT

GO

And the database name:

USE DB

The status might change; i will test this and get back to you

thanks

Kal

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

No, I'm not saying you need to include CHECKPOINT in your maintenance scripts, you don't. Checkpoints run on a regular interval, they're background processes.

I showed you that just to answer your question as to whether the log_reuse is expected to change and whether it not changing after a log backup is a bug (it's not)

And don't back up to Nul except as a test (it deletes the files)

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

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 11

Hi Gail

This is the script i am using and i did include checkpoint and now its showing up as NOTHING

Its ok to include it right?

Also the following script will actually delete the backup TLs and generate new ones

What do you think?

@ECHO OFF

cd L:

CD L:\Daily_TL_Job

del *.bak *.log *.trn

REM Get date in format YYYY-MM-DD (assumes the locale is the United States)

FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup

SET DBList=L:\Daily_TL_Job\SQLDBList.txt

SqlCmd -E -S SERVERNAME -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE Name NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

REM Backup each database, prepending the date to the filename

ECHO Starting the backups on %Date% at %Time% >> L:\Daily_TL_Job\trn_log.log

ECHO **************************************** >> L:\Daily_TL_Job\trn_log.log

FOR /F "tokens=*" %%I IN (%DBList%) DO (

ECHO Backing up database: %%I

SqlCmd -E -S SDOADP10 -Q "BACKUP LOG [%%I] TO Disk='L:\Daily_TL_Job\%%I.trn' " >> L:\Daily_TL_Job\trn_log.log

SqlCmd -E -S SDOADP10 -Q "USE [%%I]; CHECKPOINT" >> L:\Daily_TL_Job\trn_log.log

ECHO **************************************** >> L:\Daily_TL_Job\trn_log.log

ECHO.

)

ECHO **************************************** >> L:\Daily_TL_Job\backup_log.log

ECHO Backups completed on %Date% at %Time% >> L:\Daily_TL_Job\backup_log.log

ECHO **************************************** >> L:\Daily_TL_Job\trn_log.log

ECHO Backups completed on %Date% at %Time% >> L:\Daily_TL_Job\trn_log.log

REM Clean up the temp file

IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ENDLOCAL

Kal

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

hurricaneDBA (11/21/2016)
Hi Gail
This is the script i am using and i did include checkpoint and now its showing up as NOTHING
Its ok to include it right?

.....

GilaMonster (11/21/2016)
No, I'm not saying you need to include CHECKPOINT in your maintenance scripts, you don't. Checkpoints run on a regular interval, they're background processes.

And seriously, if you're going to delete the log files right after making them, you may as well be in simple recovery as you will NOT be able to restore from those log backups in a disaster. To restore a database to point in time with log backups, you need all the log backups since the last full. Your script deletes the full and the log backups, meaning you have no way of recovering that database to point in time,

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

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 13

Strongly suggest you switch from the Maintenance plans to Ola Hallengren's SQL Server Maintenance Solution[/url]

😎

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 14

Thanks guys I appreciate the help and keep up the great work

Have a good week

Kal

Ray Herring SSCertifiable Points: 5533 More actions · Answer 15

hurricaneDBA (11/20/2016)
Our RTO is 5 days but they are using EMC backup so the dbs have to stay in full backup mode.
The issue I have is sharepoint has enabled auditing, indexing and crawling plus we're using file streaming and RBS and it's all on one db server.
Because of RBS and FS storage issues are present hence keeping the transaction logs on the server is not an option so I can only truncate the logs every hour
Any ideas how to resolve this?
Kal

HI Kal, I think this is a big part of your problem. Is management depending on EMC backup for SQL database recovery? If so I strongly recommend that you push for a change to that policy. I have had many problems with "Enterprise Backup Solutions" that "seamlessly integrate" with the database engine.

I always use a SQL specific backup solution (MS SQL, Red Gate, Idera, etc.) and then have the storage team do file level backups of my SQL Backup Files.

If you can't get management to change the backup set up then you need to have them correct the EMC configuration. From you description and comments I guess that EMC is not properly truncating the Tlog after backup. I had a similar problem many years ago with a CA based solution.

Also, do you understand that in this context "truncate" just means "mark VLF as unused" or "mark VLF as available"? It does not mean actually shrinking the tlog file.

Good Luck,

Ray,