July 26, 2010 at 12:58 pm
I reindexed my 114 gig DB for the first time in a while and it created a 69 gig log file. There has been a discussion in my group as to how large a log file should be. We take log backups every 15 minutes. Should I shrink the log file or leave it.
July 26, 2010 at 1:04 pm
Shrink it back to an appropriate size. You can determine an appropriate size based on usage and typical log backup size. Add 5-10GB to the typical usage and set the log file to that size.
A reindex will force some log growth. You could mitigate that through a targeted approach when rebuilding indexes (certain tables at certain times or only if the index is x% fragmented).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 26, 2010 at 1:08 pm
so I should shrink the log file and then let it grow for a while to determine it's normal size and then add 5-10 gigs.
July 26, 2010 at 1:17 pm
russell.young (7/26/2010)
so I should shrink the log file and then let it grow for a while to determine it's normal size and then add 5-10 gigs.
No. You determine what that size should be prior to shrinking. You should have an idea of what the log size would normally be based on your backups - add 5 to 10 GB to that size.
With that number in hand, then go back to your log file and shrink it.
Example.
Normal usage indicates that your log is 5GB (backups of the tlog are 5gb). Your first number would be 5GB. Add 10Gb to the first number and now you have 15GB. Shrink the log file from 69Gb to 15Gb. This will get you a workable log file size.
This does not take into account any fragmentation of your VLF's (see articles by Kimberly Trip in my sig). If you have a lot of VLFs in your log file then the process would change somewhat.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 26, 2010 at 1:39 pm
yes I do have a lot of VLF's(2618). So i assume I would shrink it as small as I could and then grow it to the 15 gig's
July 26, 2010 at 1:45 pm
Yes - shrink as small as you can. Then grow it out to 15GB (following the example) but grow it in chunks of about 4gb-5gb.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 1:08 am
Hi,
I think its difficult to shrink the 69 gig log file. Better way if you don't want the log backup then
you can use this query
Backup log databasename with truncate_only
otherwise if you have downtime. you can detach the database. Then you can delete the ldf file
and now attach the database. The ldf file is newly created.
Thanks & Regrds
Balaji.G
July 27, 2010 at 1:35 am
I don't agree with the statement that shrinking a large log file will take much time and you should use backup log <databasename> with no_log.
Log file has a different structure it is not like a data file. It does not contain data rather it contains log enteries in a sequential order and this file can be accessed sequentially only.
As this file is used for recovery options, it is not recommend to shrink or backup log <databasename> with no_log or truncate_only because first it will truncate all the transaction which has not been backed up which is dangerous speciallly for a critical production database and secondly because it will reset the log sequence chain so no further log backup can be taken unless you take full\differential backup of the same database to generate a new backup lsn.
Because rebuilding \ reindexing \ defrag all these operations are fully logged operations hence your log file is tend to grow .
If you do these rebuilding operations occassianaly and you have some disk space crunch on the drive where your log file is lying then you can think of shrinking it but again after taking appropraite log backup..
You can shrink the log file as much as it has free space you can check the used and free space using this command.
Dbcc sqlperf('logspace')
This command will show you all databases log space used space.
Then you can shrink your log file if it has some free space in the log file.
If you do these rebuiling operations regularily then i would recommend you not to shrink your log file\files because it will not help you infact allocating further disk space to a log file as and when required for rebuilding operation would be costlier operation as far as your rebuilding operation is concerned.
Sachin Sharma
July 27, 2010 at 1:55 am
balaji.ganga (7/27/2010)
Backup log databasename with truncate_only
Which breaks the log chain and leave you unable to take log backups or do point in time recovery until another full/diff backup is taken. That command is deprecated and there's no good reason to use it.
otherwise if you have downtime. you can detach the database. Then you can delete the ldf file
and now attach the database. The ldf file is newly created.
No, no, no, no!
The log is not an optional file that you can just delete and not have consequences. SQL cannot always recreate the log file (only if the DB is shut down cleanly). There is a chance that, if you do what you've written, the database will fail to reattach.
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
July 27, 2010 at 4:15 am
Take Gail's Advise ..its worth
July 27, 2010 at 11:49 am
I did a successions of shrinks and backups and got the log file to a very small size which got ride of the unused VLF's. I am now in a process of growing it back to its normal size plust some additions space.
July 31, 2010 at 8:09 am
Hi, This is going to put me in the mad house.
I'm running SQL 2000. I need to run reindex on certain tables from time to time to help performance.
Well, SQL is a failure in my situation.
If I reindex, my log file growes to almost 2X my DB size. My mdf is ~16GB, and after dbreindex, only on 5 tables, my ldf grows to 25GB filling my Log partition!
Ok, so now I need to use SHRINK to get the log size down.
Do you know that Shrink SEVERLY re-framents the files, undoing what REINDEX did?????
How the heck is this supposed to be managed???? It's catch 22.
How do I overcome this?
My log backups (every hour) can be over 1GB, but typically are 100MB or less.
Help...
MP
July 31, 2010 at 8:11 am
Please post new questions in a new thread. Thanks.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply