July 24, 2008 at 10:52 am
Hi guys,
I have a situation going on, i am trying to shrink my log file which is increased very much and increasing slowly everyday, so we decided to shrink the log file,
first i have used the dbcc commands on the other server before applying to our prod server.This commands work fine in other servers but have not worked on my prod.
i used this commands when i executed in prod after testing it in other servers.
1-->DBCC SHRINKFILE(transactionlogname)
it didnt work, instead it increased the log file size
then i used the below commands and then again ran the dbcc, nothing changed instead everytime the logfile size is increasing.
2-->USE databasename
GO
DBCC SHRINKFILE(transactionlogname)
BACKUP LOG transactionlogname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(transactionlogname)
Again i did it by
3--> taking a full backup and a t-log backup and then ran the dbcc shrinkfile command
but same thing happened, it didnt changed a bit instead increased the log file,
can anyone tell me the exact process to shrink a log file, what did i did wrong above,
Please suggest me asap, i dont want the log file to grow until my disk becomes full
FYI- we are using sql server2005, and this database is a subcriber.
Thanks
July 24, 2008 at 10:55 am
-Take a full backup
-Change database to Simple logging
-Shirnk Log
-Change database back to full logging
Be careful though as Log files grow to a size that they need to, at more than likely will grow back to the same size.
July 24, 2008 at 11:12 am
What recovery mode are you using?
Do you have regular transaction log backups?
Is the database published for replication
What does the following query return for the offending database?
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases
Oh, and maybe this will help a bit - Recovery model and transaction logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2008 at 11:13 am
this database is a subcriber, will it affect the replication. if we change it to simple recovery mode.
July 24, 2008 at 11:16 am
yeah this database is a subscriber in full recovery mode, and we dont do any t-log backups for this.
July 24, 2008 at 11:22 am
ALIF (7/24/2008)
this database is a subcriber, will it affect the replication. if we change it to simple recovery mode.
No. Just be sure you're happy with the recovery implications
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2008 at 11:27 am
so how do you suggest to shrink this log file.
July 24, 2008 at 11:45 am
Just because your database is a subscriber is no reason NOT to have proper maintenance plans in place. If you're not doing anything with your transaction logs, then the logs are unable to release space for transactions which are no longer needed. Performing transaction log backups will tell SQL Server that at least SOME of the transactions need not be retained - they're on backup instead.
That's a deliberate oversimplification to solely address your question. The devil, as always, is in the details. If it's a pure subscriber, what kind of subscription may guide you in how to set up your log backups. You don't necessarily have to retain the backups, but you must take them.
July 24, 2008 at 11:53 am
Try to get the DB in SIMPLE Recovery Mode and then
use DatabaseName
DBCC SHRINKFILE(transactionlogname,10).This will Shrink your Log to 10MB in size etc...
Try this also in FULL Mode.
Maninder
www.dbanation.com
July 24, 2008 at 12:02 pm
Mani,
just one question with you, doing the dbcc will do but will it affect the replication going on.
July 24, 2008 at 3:42 pm
As its a part of replication, some times the transactions which is already replicated doesn't get clear from transaction log.
In that case it would get clear by::
sp_repldone
It would not harm as it clears only replicated transactions from the log.
you can give it a try and then take log backup and shrink the log file, might be it would serve your purpose.
Cheers,
Deepak
July 25, 2008 at 12:17 am
DKG (7/24/2008)
As its a part of replication, some times the transactions which is already replicated doesn't get clear from transaction log.In that case it would get clear by::
sp_repldone
True, for the publisher of a replication setup. Not for the subscribers.
sp_repldone can break replication. It tells the log reader that all transactions have been copied to the distributor and hence on the the subscribers. If that is not the case thn you can end up with a subscriber that is issing certain data
It's another 'quick fix' that can break stuff badly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2008 at 8:20 am
When, then, is an appropriate use of sp_repldone?
July 25, 2008 at 9:56 am
It's used by the replication log reader, to mark that it has read and replicated a set of commands. You can use it if there's problems with replication, but that's a specific usage for a specific purpose.
As Books online says:
If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. sp_repldone should only be used for troubleshooting replication as directed by an experienced replication support professional.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2010 at 4:49 pm
To anyone still browsing this forum. A way around shrinking a log file which wont shrink is by creating a new one and deleting the old one through moving the database files. I will use the adventureworks database as an example
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME = 'D:\SQLData\AdventureWorks_Data.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME = 'D:\SQLData\AdventureWorks_Log.ldf');
To complete the move, set the database offline:
ALTER DATABASE AdventureWorks SET OFFLINE
and then move the .mdf file to the new location, then bring the database back online:
ALTER DATABASE database_name SET ONLINE
A new log file will be created
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply