March 21, 2008 at 6:26 am
Okay, I'm a newbie to SQL. I have a physical log file that is 39 gig. Is there anyway to get this smaller. Based on what I've read so far, it looks like doing a dbcc shrinklog, NO_LOG, or Truncate_No will not make the physical file smaller, correct? I'm backing up the log files and the database on a regular basis, however the physical file is not shrinking.
Little help please?
March 21, 2008 at 7:45 am
Seems to be a VERY popular subject lately! Check this other thread going on. It should help. Re-read Steve's comments in the first link after you've accomplished what you want to do.
http://qa.sqlservercentral.com/Forums/Topic470281-146-1.aspx
http://qa.sqlservercentral.com/Forums/Topic457658-357-1.aspx
-- You can't be late until you show up.
March 21, 2008 at 9:49 am
Backing up the log TRUNCATES the log (meaning - it will remove transactions that have been committed -and if replication is in play, have replicated - so that the space can be reused), but it does NOT make the file smaller.
The logic is - if the file has needed to grow this big, it will need to do so again, so why shrink and grow over and over.
Check for open transactions, check your replication status, read the threads above.And if possible - DON't shrink.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 21, 2008 at 10:22 am
If you had an exception, like a really unsuallly large load or update etc, that caused the log file to grow then perform the t-log backup and you can follow up with a shrink. If it is growing to the size it is based on normal activity, if possible leave it alone. If a third party tool created the log file at a larger size than desired, you can perform the following but the backups will be critical. I would only do this if I understood that it was created initially to large and that I do not have this volume, otherwise the cost of growing and shrinking the log file is to expensive(io etc).
-- backup the tranlog
BACKUP Log dbname TO disk = 'location\filename.trn'
-- put datbase in simple mode,shrink logfile, put bak in full mode
--
ALTER DATABASE dbname SET RECOVERY SIMPLE
--
-- can use statement like this to empty if needed
-- dbcc shrinkfile (logfilename,emptyfile)
--
dbcc shrinkfile (dbname,size in mb's)
ALTER DATABASE dbname SET RECOVERY FULL
-- Back up the database.
-- this backup is very critical so that you have a recovery point, without it you
-- could end up unrecoverable
--
BACKUP DATABASE dbname
TO disk ='location\filename.trn'
--
Good luck;
Mark
March 21, 2008 at 11:27 am
Actually, while I agree with your comments, especially about knowing what is causing the growth and the expense of growing (and shrinking), the tasks can be performed as simply as..
BACKUP Log dbname TO disk = 'location\filename.trn' with truncate_only
dbcc shrinkfile (logfilename,xxx) --xxx is the desired file size in Mb
BACKUP DATABASE dbname TO disk ='location\filename.trn'
Even if you don't issue a shrinkfile, ALWAYS do a full backup of the database immediately after you truncate the log.
-- You can't be late until you show up.
March 22, 2008 at 8:56 pm
As Tom mentioned, if you mess with the log (NO_TRUNCATE, NO_LOG, etc), always run a a backup.
You should always determine what caused growth and then decide if it's regular or a one-time event. You could shrink after a one-time event, but you don't want to regularly do this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 30, 2008 at 8:32 am
[font="Tahoma"]In addition to the above responses you may wish to refer this KB as well,
http://support.microsoft.com/kb/317375
http://support.microsoft.com/kb/873235[/font]
[font="Verdana"]- Deepak[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply