| Author |
Topic |
|
kid_on_the_block
Posting Yak Master
172 Posts |
 Posted - 2005-10-18 : 11:10:56
|
| Hi I have a DB whose size has grown a bit large , comparedto the HDD that i have , I want to shrink it ...But then before i do so I will have to truncate the log file. I dont want the backup ofthe file , just want to truncate it & shrink it ....Just read abt this :- trunc. log on chkpt. - When true, a checkpoint truncates the inactive part of the log when the database is in log truncate mode. This is the only option you can set for the master database Can i set the dboption trun.logo on chktp true for this user db ....??????? or is it useful for master DB onlyPlease HELP.... |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-10-18 : 11:24:14
|
| You can only set this option true for Master database and not for user defined database.RegardsSachin |
 |
|
|
kid_on_the_block
Posting Yak Master
172 Posts |
Posted - 2005-10-18 : 11:36:04
|
| But i just typed sp_dboption 'MyDatabase','trunc. log on chkpt','True'& the command got executed successfully......Can someone PLZ EXPLAIN ...... |
|
|
|
kid_on_the_block
Posting Yak Master
172 Posts |
Posted - 2005-10-18 : 18:51:55
|
| I want to knw , how to truncate the Log file without taking a backup of it . I mean all i want to do it shrink a log file, but before that i need to truncate it ..... I am not interested in taking its backup .... How to do that ....PLAIN TRUNCATE THANKS....I even wanna knw .....what is the use of the CHECKPOINT statement... is that required to be executed...... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-18 : 21:00:32
|
| backup log dbname with truncate_only--------------------keeping it simple... |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2005-10-19 : 01:37:06
|
quote:
Originally posted by jen
backup log dbname with truncate_only--------------------keeping it simple...
after that u might need to executedbcc shrinkfile @filenameHe is a fool for five minutes who asks , but who does not ask remains a fool for life!http://www.sqljunkies.com/weblog/sqldudehttp://harshals.blogspot.com |
|
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2005-10-25 : 01:23:23
|
Hi,i will not suggest to use truncate_only , suggested option is dbcc shrinkfile, dbcc shrinkdb .And read CHECKPOINT and Checkpoints and the Active Portion of the Log under BOLBOL says for truncate_only quote:
NO_LOG | TRUNCATE_ONLYRemoves the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE
HTH-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-25 : 03:24:25
|
| shrinkdb and shrinkfile will only shrink the file upto it's free spaceif db in full recovery mode, the entries are still there even if they're already committed/inactivetruncate will remove "committed" or "inactive" entries in the log file thus freeing up space and shrinking it will physically decrease the file size to that point if the db is in simple recovery mode, otherwise, truncating will not be needed--------------------keeping it simple... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-25 : 06:36:28
|
| You can set a user database to truncate on checkpoint. he commecnt in bol just means that you can't set the master database to anything else.You will need to set it back to keep the log and take a full backup afterwards if you are taking log backups. If you aren't then you should or leave it to truncate.This will leave free space in the log but you will have to shrinnk the file to recover the space.seehttp://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
LOG file.....