March 3, 2014 at 8:41 am
Hi!,
Need Help,
I have SQL 2008 R2, Database XYZ in simple recovery mode, the database log is growing very face, no maintenance in place, the log size grows about 1GB everyday,
Regards,
March 3, 2014 at 8:47 am
bubby (3/3/2014)
Hi!,Need Help,
I have SQL 2008 R2, Database XYZ in simple recovery mode, the database log is growing very face, no maintenance in place, the log size grows about 1GB everyday,
Regards,
What is the Free space in the log file ? Do you know what causes this growth ..
--
SQLBuddy
March 3, 2014 at 8:48 am
Take a read through this, identify why it's growing. Post back if you need further help once you've identified the root cause
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 3, 2014 at 8:49 am
How can I find it, space is at auto growth 10%
March 3, 2014 at 8:55 am
bubby (3/3/2014)
How can I find it, space is at auto growth 10%
Rt Click on DB --> Tasks --> Shrink --> Files --> Change File Type to Log , Check the Free space and cancel the window. Don't Click OK.
Read the article by Gail for better understanding ..
March 3, 2014 at 8:57 am
GilaMonster (3/3/2014)
Take a read through this, identify why it's growing. Post back if you need further help once you've identified the root causehttp://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Read through Gail's article. We can't see your environment, and this will help you understand what may be going on.
March 3, 2014 at 9:00 am
Currently Allocated 5939.19 MB
Available Free Space 156.59 (2%)
March 3, 2014 at 9:14 am
It is showing
XYZ Simple Replication,
Can I get a syntax to change it to CHECKPOINT, as I am not using replication
March 3, 2014 at 9:24 am
You need to remove replication from this database. If you don't have replication running, the log can't clear.
March 3, 2014 at 9:25 am
bubby (3/3/2014)
Currently Allocated 5939.19 MBAvailable Free Space 156.59 (2%)
bubby (3/3/2014)
It is showingXYZ Simple Replication,
Can I get a syntax to change it to CHECKPOINT, as I am not using replication
I'm not quite sure what this is saying. You either have an open transaction preventing the transaction log from freeing up, replication and/or CDC enabled but the sql agent jobs are stopped or disabled.
Issuing a checkpoint wont work if the log entries are marked for replication.
March 3, 2014 at 9:26 am
so replication is not the reason for the log growth, that what you mean correct
March 3, 2014 at 9:29 am
Yes, replication may be the rest for log growth. If replication was set up, and you have publications in this database, and replication was just let go, with subscribers turned off, the replication setup is preventing the log from clearing in simple mode.
You must remove replication from this database to allow checkpoints to clear the log.
March 3, 2014 at 9:32 am
How to do that? I have no replication set up, I do not see any Local Publications,
March 3, 2014 at 9:38 am
bubby (3/3/2014)
How to do that? I have no replication set up, I do not see any Local Publications,
If you don't have replication, run this query and check the result ..
SELECT [log_reuse_wait_desc]
FROM [master].[sys].[databases]
--WHERE [name] = N'DB_Name';
Also how big are you expecting the log file to be .. Is 5 GB too big for your environment ?
You need to pro actively size the log file for the future growth too so as to prevent log file fragmentation.
--
SQLBuddy
March 3, 2014 at 9:43 am
As Mystery mentioned, do you have CDC (Change Data Capture) enabled? If so, it could be your culprit as it used replication behind the scenes).
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply