August 9, 2013 at 1:54 am
HI all
in my production server for one database mdf file size is 300gb land log file size is also showing as 300gb.
here the database recovery model is simple.
in simple recovery model automatically log truncation will happen. in that case it can not use the 300gb.
but in my server it is happening.
how can i solve this issue can u please help any one.
August 9, 2013 at 2:06 am
So are you saying the log file is 300Gb and full being caused by an open transaction on the server use DBCC OPENTRAN to work out if the log is held by an open transaction, you sometimes get issues with replication holding logs and preventing them shrinking.
Or is it that a transaction has increased the log file to 300Gb and you now need to shrink it back down in which case something like
DBCC SHRINKFILE (AdventureWorks_Log, 1);
should do the trick. Can we have a few more details to define the issue ?
August 9, 2013 at 2:56 am
There is nothing about simple recovery model that means it won't use the full size of the log or grow the log. Simple recovery only means that log truncation occurs on checkpoint, not log backup.
Take a read through these:
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
August 10, 2013 at 12:58 am
Tanks for your reply.
here there is no open transactions. but I have checked the size in disk it is showing as 300gb. also we are not configured replication.
August 10, 2013 at 1:52 am
Did you read the articles?
Is the log growing or is the file constant at 300GB? If it's growing, what's the reason given?
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
August 10, 2013 at 8:56 am
the file constant is 300gb is there any way to decrease it.
August 10, 2013 at 10:16 am
That's normal, the file shouldn't reduce in size automatically. That suggests that log truncation is happening regularly and there's nothing wrong.
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
August 10, 2013 at 11:12 am
Thank you very much Monster
August 10, 2013 at 4:07 pm
Since log and data files are the same size, one get the suspicioun that maybe someone set the size to the same for both files by mistage. Before you go ahead and shrink it, I suggest that you monitor log-space use for a while. Create this table somewhere, for instance in tempdb:
CREATE TABLE logspace (now datetime2(0) NOT NULL DEFAULT sysdatetime(),
DB sysname NOT NULL,
logsizeMB float NOT NULL,
logused float NOT NULL,
status int NOT NULL)
Then set up a job that you run every minute for a week. The text of the SQL job is:
INSERT tempdb..logspace (DB, logsizeMB, logused, status)
EXEC('DBCC SQLPERF(LOGSPACE)')
When the week is over, you can get the max log utilisation as:
SELECT MAX(logused * logsizeMB / 100)
FROM logspace
WHERE DB = 'yourDB'
Then you can shrink the log file to double this size. For instance, says that the query above returns 2000, then you do:
DBCC SHRINKFILE(<name_of_logfile>, 4000)
If you find that the max log utilisation is over 150 GB, you should not shrink the file.
Note that if you restart the server while the monitoring is running, you will have start over if you put the table in tempb.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply