October 15, 2008 at 7:11 am
On the basis of articles and postings on this site, I recently turned AUTOSHRINK off in our production database (not sure why I turned it on in the first place, about three years ago, when we implemented...) and ran the DBCC INDEXDEFRAG script found on this site to clean up the indexes.
As expected, I ended up with a large log file, as large as the database itself actually, but I thought that it would go back to a "normal" size eventually, as it gets backed up?
I noticed that, probably not coincidentally, the initial size specified for the log file is now almost its current size.
Did INDEXDEFRAG cause this to happen and what is the recommended way to get the log file's initial size back down to a reasonable number?
What would happen if I just manually changed it in SQL Server Management Studio, under Database Properties and Files?
October 15, 2008 at 8:23 am
First of all, good for turning off shrink.
Second, the log file is allocated, it doesn't grow like a Word or Excel file. If you need that space for index rebuilds (which are logged) or for normal activity, leave it there. It doesn't do any good to reclaim that space from the OS today to have it grow again tomorrow.
A backup marks records inside the log file as backed up so they can be reused. It's not there to shrink the file. The space gets reused and the file should stay the same size to the OS.
October 15, 2008 at 8:42 am
I understand, thank you for your reply.
Just in case I do decide to reduce the size of the log file, though, would manually changing the Initial Size value in SQL Server Management Studio, under Database Properties and Files, be the way to do it? Would there be any possible "unforeseen" consequences to doing that?
October 20, 2008 at 7:22 am
Gilles,
you won't break anything doing it that way. SSMS will issue a corresponding dbcc command to shrink the logfile to the size you enter. If the logfile can't be shrunk becuase there are portions of the log that are not yet backed up, then the shrink will either not occur at all (unbacked up data at the "end" of the logfile), or the shrink will only be carried out as far as the last segment of unbacked up data in the logfile.
Check out this http://www.karaszi.com/SQLServer/info_dont_shrink.asp - Tibor Karaszi's Blog/Website has lots of interesting stuff, but this post talks about Logfiles and how to use them and especially why you should not shrink if possible.
Regards
GermanDBA
Regards,
WilliamD
October 20, 2008 at 7:40 am
There's only two good reasons to shrink a log file that I can think of:
1) Exceptional Growth: By exceptional I mean "large and unlikely to reoccur". It's OK to shrink it if you do not expect it to regrow to that size.
2) Severe Disk Space Issues: as a short-term fix in emergencies only. Depending on this will invariably hurt you even worse if you do not correct the space problems very soon thereafter.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 7:46 am
Got it, thanks for clearing that up for me!
October 21, 2008 at 10:35 am
For future reference (and for others who may do a web search and stumble across this thread), for your particular situation it would almost certainly have been better to drop and recreate all indexes to more efficiently (and quickly) remove what was probably severe fragmentation. INDEXDEFRAG can do an astounding amount of work swapping pages to get the indexes in order. Also, you should have increased the size of the database by some large percentage (50-100) to provide sufficient free space for the indexes to actually lay down in contiguously.
Note you can determine frag levels using sys.dm_db_index_physical_stats and use that to determine whether to reorg or rebuild (see BOL for a very simple script).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 21, 2008 at 10:54 am
We have a small database (~850MB), so speed and efficiency were not huge factors.
The INDEXDEFRAG script took less than half an hour to run and afterwards, I noticed that our database backup files were down to half the ~850MB mentioned above!
What is "BOL", where the recommended script resides?
Thanks
October 21, 2008 at 10:56 am
BOL = "Books Online"
SQL Server's built-in help & documentation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 11:00 am
Oh, duh, never mind...:blush:
October 21, 2008 at 11:10 am
Don't worry about it. It got me the first time I read it here too. :unsure:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply