log file is bigger than data file!!

Question

Post reply

log file is bigger than data file!!

SQLRep

SSCrazy

Points: 2052
More actions
January 7, 2005 at 8:41 am

#109531

Hi all some how my log file is twice the size of my data file! what would be the reason for that! i tired dbcc shrink command but it is not getting to it;s normal size! how can i truncate to it;s original size!
Thanks a lot

Viewing 10 posts - 1 through 9 (of 9 total)

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

What is the recovery model set to?

Are you doing log backups which will remove committed transactions from the file?

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

SQLRep SSCrazy Points: 2052 More actions · Answer 2

the recovery mode is set to full, and i do log back up as well but still it stays the same all the time now!!

Thanks

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

This is the command I use on our data warehouse db. A monthly build process causes the log file to grow really large. This always works, but the recovery model is simple in this case.

DBCC SHRINKFILE (son_db_log, 1000)

I'm not sure if this is the same DBCC command you are using.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

SQLRep SSCrazy Points: 2052 More actions · Answer 4

yeah i tried that but it stays the same size so don't know what to do now! but please tell me what is the other way i can do that!

Thanks

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

When you run the DBCC command, does it give an error message or timeout? Did you do a log backup right before shrinking the log so that the data is removed first?

You didn't say how large the file actually was -- MB's? GB's? I remember once in this data warehouse I had to shrink the log down a gig at a time.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

SQLRep SSCrazy Points: 2052 More actions · Answer 6

hi it is 5 GB and the data file is 3 GB. Acually it doesn't give me any error when i run dbcc command or it doesn't timeout either!!

thanks

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 7

Try

BACKUP LOG <database name> WITH TRUNCATE_ONLY

then DBCC SHRINKFILE.

That should do the trick.

SQLRep SSCrazy Points: 2052 More actions · Answer 8

thanks every one but one thing needed to be done that needed to run sp_repldone so all the pending replicated trasaction flush so the log gets clear.

But thanks for the help and support, you guys are the great.

Thanks

Yoav S. SSC Eights! Points: 914 More actions · Answer 9

hey,

If you are doing log backups, it means you want your log to be saved. By using the command "backup log with truncate_only" you LOSE all your log information, and won't be able to use future log backups until you do a full DB backup.

So, if your Tlog increases again and you want to shrink it, run an acutal Tlog backup (if you have a Maintnance plan job ready) and then use the DBCC command.

Yoav.