how to shrink logfile ?

Question

Post reply

how to shrink logfile ?

ravi@sql

SSCrazy

Points: 2121
More actions
December 3, 2012 at 5:39 am

#271460

Hi All,
While running one sp am getting this below error
'The transaction log for database 'testdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases'
i tried to dbshrink using dbcc command still no use . i'll get small space but i.e not sufficient for my query or sp so i'll get the same error again.
so i wanted to clear log file completely or i wanted to move it to any other path .
Please provide quick solution for my problem.
Thanks,
Ravi@sql

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

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

Arun Yadav Ten Centuries Points: 1289 More actions · Answer 1

What is the recovery Model of the database?

What is the size of t-log and growth options?

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

Sounds like you need to do some transaction log management.

What is the output of this query

select recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'testdb'

And some reading material

Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/

Why is my transaction log full - http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

Stairway to Transaction Log Management - http://www.sqlservercentral.com/stairway/73776/

Accidental DBA Guide - Chapter 8 - http://www.sqlservercentral.com/articles/books/76296/

ravi@sql SSCrazy Points: 2121 More actions · Answer 3

recovery_model_desclog_reuse_wait_desc

SIMPLE NOTHING

Perry Whittle SSC Guru Points: 233821 More actions · Answer 4

if your database is using the full or bulk logged recovery model take a transaction log backup using the following

BACKUP LOG [MYDB] TO DISK = 'some drive\some path\mydb.trn'

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 233821 More actions · Answer 5

ravi@sql (12/3/2012)
recovery_model_desclog_reuse_wait_desc
SIMPLE NOTHING

What size is the transaction log?

Does it have any growth configured?

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Arun Yadav Ten Centuries Points: 1289 More actions · Answer 6

Space in t-log is made reusable in SIMPLE recovery model as soon as the transaction is Committed/ written to the database. Have you restricted your t-log growth? If you don't have enough space in the drive hosting t-logs consider using smaller transactions and frequent COMMITs.

ravi@sql SSCrazy Points: 2121 More actions · Answer 7

ravi@sql

SSCrazy

Points: 2121

December 3, 2012 at 5:54 am

#1564828

ya maxsize is given for autogrowth

ravi@sql SSCrazy Points: 2121 More actions · Answer 8

ok cant i point my log file path any other drive ? if yes how can i do that ? pls share the query ?

Thanks

Ravi@sql

Arun Yadav Ten Centuries Points: 1289 More actions · Answer 9

You can try the below options:

Increase the size of 'MAX GROWTH' (If ample space is available in the drive hosting t-log)

If not, you can try this

USE MASTER

GO

DBCC FREESESSIONCACHE WITH NO_INFOMSGS

GO

DBCC FREESYSTEMCACHE 'ALL'

GO

USE (YOURDBNAME)

GO

DBCC SHRINKFILE (N'LOGFILENAME', 0, TRUNCATEONLY)

GO

DBCC SHRINKFILE (N'LOGFILENAME' , 1024)

GO

Perry Whittle SSC Guru Points: 233821 More actions · Answer 10

ravi@sql (12/3/2012)
ya maxsize is given for autogrowth

What are the size details for the log currently?

Do you have any long running transactions?

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

"Ya can't make an omelette without breaking just a few eggs" 😉

ravi@sql SSCrazy Points: 2121 More actions · Answer 11

ravi@sql

SSCrazy

Points: 2121

December 3, 2012 at 6:09 am

#1564835

Thank you all .

Regards,

Ravi@sql

Arun Yadav Ten Centuries Points: 1289 More actions · Answer 12

ravi@sql (12/3/2012)
ok cant i point my log file path any other drive ? if yes how can i do that ? pls share the query ?
Thanks
Ravi@sql

If you want to move the existing t-log file to different location then, follow the below steps:

1. Run ALTER DATABASE command and change location of ldf file

2. Take database offline

3. Move the physical file from old location to new location.

3. Bring database online

You can also try to add second log file to your database

USE [master]

GO

ALTER DATABASE [DB_NAME] ADD LOG FILE ( NAME = N'second_log_file', FILENAME = N'D:\LOG_FILES\second_log_file.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

sanket kokane Hall of Fame Points: 3465 More actions · Answer 13

arunyadav007 (12/3/2012)
USE MASTER
GO
DBCC FREESESSIONCACHE WITH NO_INFOMSGS
GO
DBCC FREESYSTEMCACHE 'ALL'
GO

and how does it will help to manage the log space ?

you are just adding trouble to existing issues

-----------------------------------------------------------------------------
संकेत कोकणे

sanket kokane Hall of Fame Points: 3465 More actions · Answer 14

@OP ,please do check Gail's post for managing log file

http://www.sqlservercentral.com/articles/Administration/64582/

-----------------------------------------------------------------------------
संकेत कोकणे