October 18, 2013 at 4:41 am
Hi friends,
here i need some information regarding Log space.
Today i have faced one issue. for one my database log file is occupying 390GB space due to that less space in drive.
so checked in DBCC SQLPERF(LOGSPACE) here log space used is 2.8Gb
select
log_reuse_wait_desc from sys.databases
where name = 'Database' here Nothing is showing.here we can't do shink because this is my production database.
so can anyone suggest me on this how can i fix this issue
my database is in simple recover model
October 18, 2013 at 4:45 am
Monitor the log usage for a couple of weeks (especially after maintenance). See from that how large the log needs to be to support the app. Shrink the log to a little larger than that.
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
October 18, 2013 at 7:20 am
thanks for your reply.
How can i monitor the log file. we have 390gb of log file so if i shink we can do it up to 350gb or we can do it more.
October 18, 2013 at 8:18 am
The same command you specified in your initial post works fine. Monitor on a regular basis for a couple of weeks, see how big the log needs to be, then you know how large the log needs to be.
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
October 18, 2013 at 9:46 am
You are performing log backups, correct?
These help manage the size of the log.
October 18, 2013 at 10:12 am
Hi Steve
here we have a database with simple recovery model
October 18, 2013 at 10:20 am
In that case, follow Gail's advice. Record the peak log usage and shrink to that (leave a pad).
October 18, 2013 at 10:34 am
We can monitor but presently we dont have the space in drive we have only 3 gb left. i have to fix this immediately.
can you please advice how can i increase the space for temporarily.then we can monitor the log size.
October 18, 2013 at 10:37 am
You can add another log file to the filegroup.
http://technet.microsoft.com/en-us/library/ms189253.aspx#TsqlProcedure
October 19, 2013 at 7:27 am
thank you all for your valuable information.
here i have shrink the log now we got 30 gb space. here we have 360gb log now. can i reduce it up to 300gb.
i want to know if we reduce it how it will effect to our database.
October 19, 2013 at 2:00 pm
It doesn't affect the database, but if the space is needed, the log will grow if autogrow is enabled, and stop accepting transactions if it isn't.
The size of the log should be set based on the workload. Nothing else.
October 20, 2013 at 12:52 pm
New persopn (10/19/2013)
here i have shrink the log now we got 30 gb space. here we have 360gb log now. can i reduce it up to 300gb.
No way to answer that. We don't know your environment, we don't know how large the log needs to be. Maybe you can, maybe you can't, maybe it'll grow straight back, maybe it won't.
You need to do some investigations, see what it is that requires the most log space, how much it needs, whether or not it can be tuned.
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
October 20, 2013 at 9:24 pm
Thanks for u r information. i will monitor the log for few days and i will let you know how much it is occupying.
October 22, 2013 at 7:22 am
If your log has experienced lots of autogrowth you might want to check the number of vlfs. I've seen 3 cases in the wild where vlfs have been extremely high, mostly on 2008 r2 sp1 (sp2 has the fix).
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
October 22, 2013 at 7:29 am
How can i check whether how many virtual log files in log
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply