Truncate Log

Question

Post reply

Truncate Log

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
October 3, 2005 at 11:18 am

#110998

I have Hr_database
Hr_database_data.mdf 1235MB
Hr_database_log.LDF 25623502MB
The LDF is growing how to truncate this log file, any scripts??

Thanks NITa

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

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

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

Adam Wilbur

SSCertifiable

Points: 7001

October 3, 2005 at 11:28 am

#594652

checkpoint

A.J.
DBA with an attitude

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 2

Thanks but I am a new DBA, how don't know what you mean by checkpoint. I want to truncate the log file I mean the LDF file....how to write the complete script.

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 3

Lookup "Backup Log" in Books Online and you should be able to figure it out from there.

A.J.
DBA with an attitude

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 4

Thanks for Inspiring me

I did

Backup log Hr_database with Truncate_only

But when I see the size still remain same???

David S. SSC Veteran Points: 228 More actions · Answer 5

There is a diffference between spaced used and space allocated. When you truncate the trans log, you are mainly reducing the amound of space used on the transaction log.

To reduce the actual allocated space, look for: DBCC SHRINKFILE

Be sure to understand the implications that this commands has.

The truth is just a point of view accepted by majority

David Stohlmann

gcp777 Ten Centuries Points: 1059 More actions · Answer 6

That must be a typo? Please tell me you don't really have a 25 TERABYTE log file for a 1 GB database?

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 7

In order to reduce your LDF, you must back it up. Running checkpoint will write data from the ldf to disk. SQL Server itself issues checkpoints at predefined intervals. However to progress your issue, and implement good practice to your environment, create a maintenance plan to back up your ldf on a regular basis. Also decide on a full and differential backup strategy. You never mentioned what version of SQL you have. ( I assume the DB is in FULL Recovery ). I have in the past experienced issues reducing LDF's for SQL 7. Don't want to get into logical logs files with an LDF, but it can help to understand how these are structured. Anyway try the following.

Backup the database. Backup the tansaction log. If log file still not shrunk run dbcc shrinkfile. If still not shrunk try to clarify the structure of your ldf by using Enterprise, right click on the database, all tasks, shrink database, files, select ldf from drop down menu and see how much space is available. DBCC SQLPERF (logspace) will also return this info.

If you don't make any progress, let me know and I'll try to help further.

Rgds, Derek

barsuk SSChampion Points: 12280 More actions · Answer 8

something like that:

Backup LOG Hr_database WITH NO_LOG

GO

DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY)

GO

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 9

Thanks for you valueble help.

MS_SQL ver 2000

DB Name: HR_database

HR_database_data.mdf size is 403072 KB

HR_database_log.LDF size is 40125675 KB

I took Complete backup then I ran

Backup LOG HR_database WITH NO_LOG

GO

dbcc checkdb

BUT Still the size remains same????

shahgols SSCrazy Eights Points: 9874 More actions · Answer 10

You didn't run the dbcc shrinkfile, run that and your file will shrink.

Question for the experts here, is dbcc shrinkfile('log_file', truncateonly) the same as the option "Compress pages and then truncate free space from the file" in EM > Shrink File? And if yes, does running this option also remove the inactive transactions (and so requiring an immediate full backup)?

C-262216 SSC-Addicted Points: 430 More actions · Answer 11

I have found this to be useful:

dump tran <database> with no_log

C-262216 SSC-Addicted Points: 430 More actions · Answer 12

I should mention that you should backup the database prior to running the code above.

FdoArens SSC Enthusiast Points: 196 More actions · Answer 13

Is your database recovery mode in Simple or Full? check the database properties using EM. If simple, changed to Full, otherwise the truncate might not work. When using the script

Backup LOG Hr_database WITH NO_LOG

GO

DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY)

GO

set the size you want the log to be:

Backup LOG Hr_database WITH NO_LOG

GO

DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY, 20)

GO

Which will take the log to 20 MB or any size you want to have, just as a reminder the log should be at least 25% of your MDF file.

There is another way to recreate the log, not very used but you can just detach the database, rename the LDF file and then reattach, because the DB is not going to find the old log, the system creates a new one. Make sure that you backup your database first (full backup) to have a plan B. The question ypou have to ask is: do I really need the log as is? or can I get rid of the old log (remane it will keep the old log handy in case you need to go back to that one)? In any event, PLEASE BACKUP first!! Good luck

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 14

It worked through shrink via EM,

When I did

DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY)

GO

Could not locate file 'Quanta_repl_log' in sysfiles.

Thanks

Nita