Question For Nightman Shrinking MSSQL 2000 and 2005 Databases
Posted on 2006-11-28
I have several databases that are in the 7 + GB range. The log files will grow to over 7 + GB. I'm trying to develop a VB6 application shrink these databases.
These databases are in MSSQL 2000 and MSSQL 2005
The NO_LOG and TRUNCATE_ONLY options of the BACKUP LOG statement will be removed in a future version of SQL Server. These options break the log chain, because they remove the inactive part of the log without making a backup copy of it and truncate the log by discarding all but the active log. Until the next full or differential database backup, the database is not protected from media failure. Therefore, we strongly recommend that you avoid using either of these options in new development work, and that you plan to modify applications that currently use it
Backup the DATA file with
EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -VrfyBackup -BkUpMedia DISK -BkUpDB "c:\junk_1" -BkExt "BAK"'
Backup the LOG file with
EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -BkUpMedia DISK -BkUpLog "c:\junk_1" -BkExt "TRN"'
Then do a
BACKUP LOG cax_Development WITH TRUNCATE_ONLY
Does the 5 limit the size of the log file or just shrink it to 5 meg if possible?
Does this break the log chain or could you restore the database and then restore the log file
or how wound you recommend shrinking the log file.