May 6, 2011 at 3:42 am
I am using MSSQL Server 2008 R2. size of logFile is now 45GB. I used following queries to solve the problem.My DB is in live environment.
ALter database DBName
set Recovery Simple
GO
DBCC Shrinkfile('logFile',1)
GO
Alter Database DBName
set Recovery Full
GO
But it Does not work. Then I tried another moethod
Change single User mode, detache the DB move the move the log file to other location and the reattach the DB in single mode but still I got same log file Size.
I would be very thankful to you. My another Question is that what is the ideal size for Log File. My data file size is now 7.5 GB.
Thaks in advance.
Azhar
May 6, 2011 at 4:40 am
It looks like you are not taking log back regularly. Create a maintenance plan to take log backUP regularly.
45GB log file is NOT ideal for 7.5 data file.
I have slightly modified your script. Added checkpoint before shinkfile.
ALter database DBName
set Recovery Simple
GO
CHECKPOINT
GO
DBCC Shrinkfile('logFile',1)
GO
Alter Database DBName
set Recovery Full
GO
If it does not work, check whether you have any open transactions.
May 6, 2011 at 5:01 am
Please read through this - Managing Transaction Logs[/url]
and maybe 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
May 6, 2011 at 5:04 am
You should not be shrinking your files as a regular part of your operations. If you are, that's indication of problems somewhere. It does sound like you're in Full Recovery, but not taking log backups or not taking regular enough log backups. Here's a blog post[/url] I wrote on this topic.
Also, the log writes things in a (mostly) serial fashion. So if you go in to try to shrink it when it's writing stuff out at the end of the log, you may not be able to shrink it at that time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2011 at 5:07 am
In our production databases, transaction log backups are usually every 15 or 20 minutes.
From Gail's article above"
" If the database is in full or bulk-logged recovery model then log backups must be done. Without log backups the log entries will never be discarded from the log and the log file will grow without bound. Since one of the main reasons for having a database in full or bulk-logged recovery model is to allow the database to be restored without data loss, it’s important to have an unbroken log chain to allow a restore to the point of failure, if necessary."
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply