LOg files Growth - Out of Control

Question

Post reply

LOg files Growth - Out of Control

aby_dvd

Ten Centuries

Points: 1310
More actions
August 8, 2011 at 12:46 am

#242722

Log files for my database are growing fast and un-expectedly and i am left with no space available
in the 3 drives where the log files for the database have been kept .
I had tried taking the lock backup for the database with truncate option , but of no use .
i even tried shrinking of log files , but that too of no use .
now , i killed all the running process and I am going for shrinking of database .
P.S.- Error --
Date8/8/2011 1:31:16 AM
LogSQL Server (Current - 8/8/2011 1:31:00 AM)
Sourcespid384
Message
The transaction log for database 'UGP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Please suggest .
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

Have you checked for any bulk operations happening on the DB. Any recent deployments ?

Whats the auto growth setting for the log file for this DB?

Jayanth Kurup[/url]

aby_dvd Ten Centuries Points: 1310 More actions · Answer 2

Yes , There are some scheduled batch jobs runnings in the background which has resulted in large growth of transactions .

Also , when i take the log backup with truncate only , i dont see any reduction in the size of the t-log file .why?

Autogrowth of log files is set to 10 percent , restricted .

Thanks

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

The log file will grow as required and occupy as much space as needed. You need to check if the size its occupying is relevant within the scope of the batch operations that are happening.

If it is then you need to sizing the disks accordingly.

Btw shrinking the log files in not a good option if this is anything but a staging table.

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Why are you trying to shrink a file when the error is that it's full.

Full means that all space is used. To make space (ie to fix a full log error) you either need to reduce the space that's been used or grow the file. Shrink won't do the former and it's the opposite of the latter.

http://qa.sqlservercentral.com/articles/Transaction+Log/72488/

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

aby_dvd Ten Centuries Points: 1310 More actions · Answer 5

select log_reuse_wait_desc from sys.databases

Result :

log_reuse_wait_desc

------------------------------------------------------------

NOTHING

CHECKPOINT

LOG_BACKUP

(5 row(s) affected)

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

And which of those is for the database with the full 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

aby_dvd Ten Centuries Points: 1310 More actions · Answer 7

????

The query has been executed for that particular database only .

Please help if something can be done from my side.

Thanks

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

No, the sys.databases view is instance-wide, there's one row in it for each database on that SQL instance.

Try including a couple of the other columns from it (look in Books Online if you don't know what they are) and you'll see.

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

M&M SSC-Insane Points: 21699 More actions · Answer 9

abhishek_dwivedi03 (8/8/2011)
????
The query has been executed for that particular database only .
Please help if something can be done from my side.
Thanks

Use this,

select name,log_reuse_wait_desc from sys.databases where name ='your db name'

M&M

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 10

Have to add this: Why is my log full.[/url]

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

M&M SSC-Insane Points: 21699 More actions · Answer 11

Grant Fritchey (8/8/2011)
Have to add this: Why is my log full.[/url]

Good one Grant, thanks for sharing.

M&M

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 12

abhishek_dwivedi03 (8/8/2011)
when i take the log backup with truncate only , i dont see any reduction in the size of the t-log file .why?
Autogrowth of log files is set to 10 percent , restricted .
Thanks

Taking a log backup with truncate only, will not the reduce the size of your log file. Check this link.

My blog

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 13

If it isn't clear - the reason by your transaction logs are growing is because you are not performing regular transaction log backups.

Sounds like you got in the 'habit' of solving this problem by using BACKUP LOG ... WITH TRUNCATE_ONLY. That has been deprecated in SQL Server 2008 - which means it no longer works.

The problem with doing the above is that it breaks the log chain, preventing a point in time restore from a previous good backup.

If you do not need point in time recovery, then you would be better off switching the recovery model to simple - performing a one time shrink of the transaction log and then leaving it alone. However, that is not recommended for a production system and would put your organization at risk (which they already are, because of the lack of transaction log backups).

Please review the article in my signature - and the article Grant posted.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs