June 27, 2013 at 6:59 am
HI,
I'm trying to backup a LIVE db so I can restore it on a conversion db for testing.
I ran into a problem that the transaction log is 114G. I did a bit of research and found this
ALTER DATABASE [dbname] set recovery simple
GO
CHECKPOINT
GO
DBCC SHRINKFILE (logicalname,1)
GO
ALTER DATABASE [dbname] set recovery full
GO
tested with a test db and works but just wondering what implications it may have...
Thanks
Joe
June 27, 2013 at 7:12 am
Joe
Does your transaction log need to be that big? In other words, does it ever approach 100% full? If it doesn't, it's probably safe for you to shrink the log. You don't need to set the recovery mode to simple - just make sure you do the shrink immediately after a transaction log backup. Also, don't shrink to 1MB - choose a file size that reflects how the database is used.
I would advise you to download this eBook [/url]and read through it. It's best to have an understanding of this sort of stuff before you do anything you might regret later.
John
June 27, 2013 at 7:18 am
to reclaim the space using shrink command,following command should return 'NOTHING'.
else the log won't be truncated and file won't shrink
select log_reuse_wait_desc from sys.databases where name='Your_db_name'
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
June 27, 2013 at 7:37 am
I know I have some reading to do,
but what if it returns REPLICATION ?
June 27, 2013 at 7:46 am
It means you have articles marked for replication which is keeping portions of your transaction log from being reused...and also is the reason the log file grew to such a large size.
Do you have replication setup for that database? You need to figure out why your data isn't making it to the subscriber. Open replication monitor and see if there are any errors.
June 27, 2013 at 7:52 am
This could be the answer to your problem, as well. Depending on how your replication is configured, and depending on what testing you need to do, you could either use the existing subscriber database for your testing, or set up a new one and test on that.
John
June 27, 2013 at 8:37 am
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply