Tr log is full how can we reduce it

Question

Tr log is full how can we reduce it

mw_sql_developer

SSCoach

Points: 19445
More actions
January 26, 2016 at 7:16 am

#307290

Please see attached error message.
I got 2 questions.
1.) Can we reduce the ( truncate ) the TR-log.
2.) Is there anyway ( any command ) that will prevent stuff being written to the transaction log when we some user executes an update statement. Some of these updates are of not any value ( not for production use )
( I mean for research reasons I do create tables that I later delete. I don't want any of my updates to result in populating the TR-log file )

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Have you followed the advice given in the error message? What is the recovery mode for your database, and what backups do you do?

John

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

If the log is full, that means a couple things.

First, it means SQL Server isn't able to mark VLFs within the log as reusable (that's truncation). As indicated in the error message, you'll want to find out why by checking the value of the log_reuse_wait_desc column in sys.databases for the database in question.

A couple common causes for not being able to truncate the log are 1) the DB is set to use the full recovery model but log backups are not being taken, and 2) a long-running single transaction is keeping the VLFs active (there are many others, of course, but those are two of the most common).

The second thing this indicates is that the log file probably was unable to autogrow, whether because the disk was full, autogrowth is disabled, or it's reached its max size.

I'd check sys.databases first, to see what's preventing log reuse. Once you've figured out the reason, you can take appropriate action.

I'd highly recommend taking a read of this as well, so you're better equipped to handle this in the future: http://www.sqlservercentral.com/articles/books/94938/.

Cheers!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

Reply to John:

I am not a DBA. I am a SQL developer. I am hoping someone will pass me the commands and I can check/test/execute them.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 4

mw112009 (1/26/2016)
Reply to John:
I am not a DBA. I am a SQL developer. I am hoping someone will pass me the commands and I can check/test/execute them.

This is kind of a DBA question, what needs to happen is to verify that your scheduled log backups are running properly, if they're not that either needs to be fixed or the DB should be set to simple recovery mode and hopefully you have DB backups running.

Either way a transaction log backup needs to be run and after that you should be able to shrink the log file.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

Zzartin:

verify that your scheduled log backups are running properly,

How do I do that ? CAn you guide me

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 6

2.) Is there anyway ( any command ) that will prevent stuff being written to the transaction log when we some user executes an update statement. Some of these updates are of not any value ( not for production use )

No.

Right now, as suggested, check the log_reuse_wait_desc to see what the issue is:

select name, log_reuse_wait_desc from sys.databases

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

If you're not a DBA, then I strongly recommend you get one involved. If you don't, you could end up compromising availability (as you've already seen) or, worse still, losing data. At the very least, search for articles on managing transaction logs and read through one or two of them.

SELECT

log_reuse_wait_desc

,recovery_model_desc

FROM sys.databases

WHERE name = 'TierMedTest 2016'

John

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 8

mw112009 (1/26/2016)
Zzartin:
verify that your scheduled log backups are running properly,
How do I do that ? CAn you guide me

In management studio go to SQL Server Agent -> jobs on the server in question and see what's scheduled. But if you don't have a dedicated DBA you likely just volunteered yourself for the job 🙂

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

I'm not sure he's doing backups at all. That's most likely to be what the problem is, and the reason I recommended getting a DBA or reading up on transaction log management.

John

Ken McKelvey SSCoach Points: 18843 More actions · Answer 10

As backups are not always done from the SQL Agent, your can also check for backups by looking at MSDB.

Try playing with the following script for FULL and LOG backups.

Ideally you want to see at least some backups being written to a file.

SELECT S.backup_start_date, S.backup_finish_date, M.physical_device_name

FROM msdb.dbo.backupset S

JOIN msdb.dbo.backupmediafamily M

ON S.media_set_id = M.media_set_id

WHERE S.[type] = 'D' -- Full Backup (I = Diff, L = Log)

AND S.database_name = 'YourDB'

-- 7 is the snapshot done by tape backup using VSS agent

--AND M.device_type = 2

ORDER BY S.backup_finish_date DESC;

It is also worth looking at the SQL Logs where the source is Backup.

The logs can be viewed from Management Studio under Management|SQL Server Logs.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 11

Reply to John:

See attached, I ran the sql you sent.

Also in my original post I have question#2.

That is:

IS there a way for me to do an update without SQL sever inflating the tr-log. I mean is there a command that I can issue which tells SQL server not to bother writing to the TR log. After all I am not in a PROD environment

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 12

Yes you can change the recovery mode of the DB to simple, that won't clear the existing log file so you would still need to do a transaction log back up and the shrink the log file but it will stop data from being written to the log file.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 13

Reply to ZZArtin

So does the recovery mode "SIMPLE" ( which is at the moment ) mean that nothing is written to the TR log ?

Bill Talada SSChampion Points: 11954 More actions · Answer 14

Sql Server will always write to the log for the current transactions, otherwise it would not be a sql database. This is the foundation of a transactional database implementing ACID properties of recoverability.

--If you have open transactions, the log will not clear.

SELECT * FROM master.dbo.sysprocesses where open_tran > 0

Sometimes it takes two or three backups to truncate logs. It depends on weird things I can't remember right now.