March 2, 2014 at 2:05 pm
Database is in simple recovery model..How do I shrink the log file? Log file growth out of space while loading huge chunk of records in one of the table? log_reuse_wait_desc says REPLICATION.
March 2, 2014 at 2:52 pm
I don't believe there's much you can do until the process(es) completes. Your log file is being written to and it won't allow the space in the file to be zeroed out (marked as active so it can be reused) until that process/transaction has completed. Perhaps you can kill the insert process?
Out of curiosity (just throwing it out there) do you even have replication set up? Or are you using CDC (change data capture) ? The reason I ask this is because CDC uses replication in the background and that might be why you are seeing this description show up in the log_reuse_wait_desc. Hard to give much more suggestions without knowing more information.
So please provide some details...what process is loading the "huge chunk of records" into the table? Are you using a SELECT INTO, INSERT INTO, bcp, BULK IMPORT? Etc? Each method would have a different impact on your transaction log...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 2:55 pm
Its a IBM third party tool CDC that replicates data from ERP to SQL server. I tried to one of the method listed in SSC.com. Detach, and reattach without the log file. But I can't even detach the db. It seems like there are 7 active connections and I'm getting same error message that transaction log is full.
March 2, 2014 at 3:04 pm
Can you create a "temporary" log file for the database on another drive to get around the log file issue?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 3:10 pm
How? I tried to issue
ALTER DATABASE MyDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Its giving me following error message.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 9002, Level 17, State 6, Line 1
The transaction log for database MyDb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
March 2, 2014 at 4:08 pm
Where "X" is the new drive?
USE [master]
GO
ALTER DATABASE [YOURDB] ADD FILE ( NAME = N'tmpLog', FILENAME = N'X:\TempLog.ldf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 4:15 pm
Can I do this when db is online?
March 2, 2014 at 5:38 pm
SQL_Surfer (3/2/2014)
Can I do this when db is online?
Yes.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2014 at 7:52 pm
Curious to know how this panned out for you...did adding the extra log file help?
Wanted to mention another point...since you executed ALTER DATABASE MyDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
I think you should be thankful that this did not work for you. Had it worked you may not have been the first to make a connection to the database, and if that happened you could have been in a world of panic (as you wouldn't have been able to connect to the database at all).
Be careful setting SINGLE_USER, there are steps you would want to take to make sure you got the first connection...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 9:25 pm
After waiting for a while, I was able to detach the db, rename the log file and then attach it back without the log file. This took care of it. Thanks for all of your help.
March 3, 2014 at 4:48 am
SQL_Surfer (3/2/2014)
After waiting for a while, I was able to detach the db, rename the log file and then attach it back without the log file.
Excellent way of destroying a database. Deleting a log file is somewhat akin to russian roulette with your database. DO NOT delete log files, they're not optional.
Have you investigated what data integrity issues deleting the log file caused?
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
March 3, 2014 at 7:04 am
What would be the ideal way to do this then? Haven't encountered data integrity yet. But there could be.
March 3, 2014 at 7:12 am
http://www.sqlservercentral.com/articles/Administration/64582/
http://www.sqlservercentral.com/articles/Transaction+Logs/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
March 4, 2014 at 5:10 am
This should do it also.
DBCC SHRINKFILE (N'<DATABASE_LOG_FILE_NAME>' , 0, TRUNCATEONLY)
March 4, 2014 at 5:18 am
psamyn (3/4/2014)
This should do it also.DBCC SHRINKFILE (N'<DATABASE_LOG_FILE_NAME>' , 0, TRUNCATEONLY)
TruncateOnly has no meaning when applied to a log file shrink, it's an option for data files, and shrinking the log to 0 is a bad idea, it's just going to have to grow again. If shrinking, the log should be shrunk to a sensible size based on regular operations.
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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply