September 29, 2006 at 12:42 pm
I did a backup log with no_log on a database. But the space is not being released. What should I do?
Thank You!
Mark
September 29, 2006 at 1:36 pm
Backing up the log does not change the physical size of the log, it only removes inactive entries. In order to physically shrink the file, you must run DBCC Shrinkfile. See 'Shrinking the Transaction Log' in BOL for details.
September 29, 2006 at 2:13 pm
Sorry, I didn't make it clear. I did dbcc sqlperf(logspace) which still shows a 98% log space used after the truncate.
October 4, 2006 at 12:51 am
You may try:
BACKUP DATABASE WITH NO_TRUNCATE
October 4, 2006 at 2:59 am
First of all, please let me know if the database is in SIMPLE revovery mode or FULL or BULK-LOGGED. The truncation commands can remove valuable entries from the transaction log file if the database is NOT in SIMPLE recovery mode.
When you backup a transaction log file with NO_LOG, it will not actually backup anything; rather, it will truncate the inactive portion in the virtual log files (its different from the physical log file). After removing the inactive portion of the logs, the physical size of the log file will still be the same. To actually trim the physical log file, use DBCC SHRIKFILE(file-id) command while BEING in the same database whose log file you are truncating.
Do post here your queries, if any.
Pankaj Khanna
Database Administrator - SQL Server 2000
Keep hope to keep you intact...
October 18, 2006 at 7:36 am
By the way, how to check the database recovery mode ?
October 18, 2006 at 7:36 am
By the way, how to check the database recovery mode ?
October 18, 2006 at 3:51 pm
In Enterprise Manager, righ click the database and go to properties. In the Option tab, you can find the Recovery Model for that database
Pankaj Khanna
Database Administrator - SQL Server 2000
Keep hope to keep you intact...
October 20, 2006 at 2:55 pm
Mark,
I don't know if you ever got a good answer or solved your problem, but....
The issue is with the Active part of the log. Let's say your log file looks like this (A = active I = inactive)
IIIIIIAIII
When you truncate,shrink,NO_LOG the file, it can only remove the 3 I's at the end. It has to wait until a checkpoint is reached and the A portion moves.
This is from the BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space.
Notice the first part of that "Removes the inactive part of the log "
-SQLBill
October 23, 2006 at 5:40 am
I tried to copy the log file to other location and look at the log file to better understand the active and inactive concept. it gave me error for sharing violation since the file is in use.
October 23, 2006 at 5:18 pm
Look up Transaction Log in the BOL. There's a pretty good description of how the active/inactive stuff works.
-SQLBill
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply