Hi I'm having a production database and its replicated report database. How to shrink the transaction log files in the production database as the log file size is increasing. I had tried DBCC SHRINKFILE and SHRINKDATABASE commands but it does not work for me. I can't detach and shrink and attach back as the db in replication. Please help me in this issue.
A:
Do you have a regular backup schedule in place?
If not, I suggest you read this excellent article: 8 Steps to better Transaction Log throughput
Mitch Wheat
2009-08-06 12:17:59
no i'm not having backup schedule.I need to remove the trancation log
2009-08-06 12:36:28
The reason you have a large transaction log is because you are not backing up your database regularly.
Mitch Wheat
2009-08-06 13:36:48
+1
A:
The database won't let you remove transaction data that isn't backed up. First you have to back up the transaction log, then you can shrink it.
Guffa
2009-08-06 12:31:50
If your DB is in full recovery mode you need to make sure you're doing a transaction log backup as well as a database backup.
KSimons
2010-05-13 18:10:07
A:
Shrink the logfile with dbcc shrinkfile
Then truncate the log file using
Backup databaseName with truncate_only
Then shrink the logfile again
sgmoore
2009-08-06 12:39:15
A:
I used Red-Gate's SQL Backup tool to take care of the backing up. Then I just use the management console to issue a shrink command on the log file (telling it to rearrange the pages before releasing unused space).
Works like a charm.
Jon Dewees
2010-05-13 17:52:28