Truncating the SQL Server 2008 Log file..

Question

Viewing 6 posts - 1 through 5 (of 5 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

your database is probably in full recovery mode but you are not backing up your transaction log.

Switch to simple recovery mode

run a checkpoint in the database

then attempt to shrink the log to 1GB

switch back to full recovery mode

take a full backup of the database

set up and run regular log backups

and read this managing transaction logs by gail shaw[/url]

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

Shodhan Old Hand Points: 361 More actions · Answer 2

Hi,

I had once faced a situation where the transaction log was full. Below are some steps you can refer to;

Solution 1:

=======

DBCC SQLPERF(LOGSPACE)

BACKUP LOG Comapny WITH TRUNCATE_ONLY

DBCC SHRINKFILE (Company_log, 500)

DBCC SQLPERF(LOGSPACE)

Solution 2:

======

Change the recovery model from full recovery to simple

Solution 3:

=======

USE DatabaseName

GO

DBCC SHRINKFILE( TransactionLogName, 1) -- freeing some space if it there

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY -- backup the log before shrinking

DBCC SHRINKFILE( TransactionLogName, 1) -- shrink the file

GO

Regards,

Shodhan

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

Shodhan,

There are couple of issues with the solutions mentioned by you.

1) BACKUP LOG WITH TRUNCATE_ONLY removes the log entries from log file. So point-in-time restore is not possible. (btw this is deprecated)

2) ALTER DATABASE SET RECOVERY SIMPLE also removes the log entries from log file. So point-in-time restore is not possible.

3) Full database backup must be taken if the above statements are execute as they break the log chain.

Regards,

saadi92 Grasshopper Points: 15 More actions · Answer 4

Thanx all...

Problem Solved...

I really appreciate all of ur concern...

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

Suresh B. (3/13/2012)
3) Full database backup must be taken if the above statements are execute as they break the log chain.

A differential backup is sufficient to restart the log chain 😉

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

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