April 11, 2006 at 7:15 am
Fundamental question: At what point is the SQL Server 2000 LOG (.LDF) AUTOMATICALLY reduced in size?
1) After a nightly FULL backup of it's DB (.MDF)
2) After a scheduled HOURLY LOG Backup
3) Upon a manual (or scheduled) SHRINKFILE
4) Other??
April 11, 2006 at 7:21 am
2) After scheduled or manaul LOG Backup and Upon a manual (or scheduled) SHRINKFILE
April 11, 2006 at 12:42 pm
Trick question.
1) Full Backup - doesn't affect the log size
2) Log Backup - truncates the log by removing the inactive data, but does not change the size of the .ldf. This is the same for the command BACKUP LOG dbname WITH TRUNCATE_ONLY.
3) SHRINKFILE - returns the unused portion of the .ldf to the operating system, but is not AUTOMATIC. The command has to be run either by a job or by an authorized user.
-SQLBill
April 12, 2006 at 10:46 am
Trick answer:
4) Other --> alter database [database_name] set AUTO_SHRINK on
This shrinks both the data and log portions of the database whenever the SQL Server engine feels it has the time. I got caught by this when updating an MSDE application database to full SQL Server !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply