February 19, 2008 at 2:35 pm
Hi,
My transaction log file has grown to 25GB.
I did dbcc shrinkfile which did not help much.
Pls confirm if this is the right procedure.
1. Backup the database.
2. Backup log
I am not sure if i have to add an init / truncate command on step 2.
Pls advise asap.
Thanks
Murali
February 19, 2008 at 2:37 pm
try this command in tsql / query analyzer
backup log MyDatabaseName with truncate_only
although, if this was a tempdb then the story might be a different one..
let me know..
John Esraelo
Cheers,
John Esraelo
February 19, 2008 at 6:18 pm
If you must backup the log with truncate_only, make sure you immediately do a full database backup. Otherwise you risk losing any data since the last backup if the database becomes corrupted.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 19, 2008 at 8:32 pm
Muralidharan Venkatraman (2/19/2008)
Hi,My transaction log file has grown to 25GB.
I did dbcc shrinkfile which did not help much.
Pls confirm if this is the right procedure.
1. Backup the database.
2. Backup log
I am not sure if i have to add an init / truncate command on step 2.
Pls advise asap.
Thanks
Murali
You have a couple of issues that need to be addressed. First, the size of the log file needs to be reduced to a manageable size, then - you need to setup your maintenance plan to maintain the log file size.
Your database is in full recovery model. This means you need to schedule frequent log backups. These log backups are what you would use to recover the system to a point in time in the event of a disaster. For example, you would restore from the latest backup with norecovery, restore each log backup until you get to the point in time you are restoring to (using STOPAT and RECOVERY).
If you do not need this ability, change the database to simple recovery model which does not require any log backups to be run.
If all you do is shrink the log file, you will end up in the same situation again.
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 19, 2008 at 9:30 pm
You might have a large virtual log file as well, which could prevent shrinks.
Search for "forcibly shrink the transaction log" here. There's a script that can help, but be sure that you also set up regular log backups.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 20, 2008 at 1:57 am
Hi,
If you have time in the week ends or after making sure no transactions happens, you can try the following steps.
1. Detach the Database.
2. Rename the transaction log file.
3. Attach the Database.
SQL Server will create new transaction log file by itself with the minimum size.
February 20, 2008 at 2:03 am
But make sure you take the Full backup before trying the above and have one more after the above step.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
February 20, 2008 at 7:54 am
Thanks for the tip. I much appreciate.
What i did is,
1. Backup the database
2. Truncate the log file
But it was still showing up even after i refreshed.
I went ahead and did a dbcc shrink and then it shrinked both the database and log file.
Thanks
Murali
February 21, 2008 at 1:14 am
Take the backup of the database and log
Then Execute this Command
Backup Log DataBaseName
With no_Log
DBCC Shrinkdatabase( DataBaseName ,0)
Hope that It will Help you
February 21, 2008 at 2:49 am
If the log file doesn't shrink immediately on running the SHRINKFILE cmd it's probably because there are open transactions preventing the log file from being shrunk. You may have run the shrink a few times, backing up the log each time before you get the desired size.
Remember to carryout a full backup afterward
heres some related articles:
http://support.microsoft.com/kb/907511/en-us
February 25, 2008 at 4:13 am
Hi ,
Please run this on your DB, This will be most easy and efficient solution .
-----------------------------------
exec sp_dboption 'DB_NAME','trunc. log on chkpt.','ON'
exec sp_dboption 'DB_NAME','AUTOSHRINK','ON'
checkpoint
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (DB_NAME)
checkpoint
exec sp_spaceused
-----------------------------------
Regards
Faheem latif
Best Regards
Faheem latif
Senior Database Architect
Genie Technologies (Pvt.) Ltd.
March 17, 2008 at 1:23 am
Hi Faheem,
Is this procedure enough to truncate transaction log of a replicated database?
Do we need to configure the db options back to "off", after once we done with the transaction log truncation?
Kindly send your advice.
Thanks,
Balakrishnan.K
March 17, 2008 at 10:08 pm
Hi,
The solution which I mentioned ,I only test on normal DB's but I hope this will also works only replicated db's for more details please see latest articles for replication db's.
Thanks
Best Regards
Faheem latif
Senior Database Architect
Genie Technologies (Pvt.) Ltd.
March 18, 2008 at 1:29 am
It is not advisable to use truncate log command on the Tlog replicated database. As in Tlog replication logreader agent is reading data from LOG ONLY!!!.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 19, 2008 at 2:52 am
I am performing these steps (daily) which seem to work very well (as well as TLog backups during the day)
1) Backup Tlog
2) Set recovovery mode to simple
3) shrink database
4) dbcc reindex with drop_existing routines
4) update statistics
5) set recovery mode to full
6) full backup
This simple technique has solved our tlog space issue and I beleive it gives us a solid recoverable position.
Neil.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply