August 7, 2003 at 2:05 pm
Hi all,
Is there any way to check active transaction in Transaction log file.
I am taking transaction log backup and assuming that it gets truncating automatically but somehow it's not getting truncating may be because of Active transaction in transaction log file.
Thanks in advance
August 7, 2003 at 2:17 pm
Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely.
Why do you think the log was not truncated after backup?
August 8, 2003 at 10:41 am
Thanks Allen,
I checked the transaction log backup and it's working fine but when I see size of the log file in Enterprise Manager it's still same even after the backup. It should reduce the size of the log file. that is why I was thinking that there might be some open transaction.
August 8, 2003 at 1:44 pm
Try to use "DBCC Log(DB_Name)" to see what is in the log.
August 8, 2003 at 3:10 pm
I use dbcc log(dbname) and get following result. I don't know what these values represent for.
0003a9f0:000000a4:0001LOP_BEGIN_XACT LCX_NULL 0000:0a983b29
0003a9f0:000000a4:0002LOP_DELETE_ROWS LCX_CLUSTERED 0000:0a983b29
0003a9f0:000000a4:0003LOP_INSERT_ROWS LCX_CLUSTERED 0000:0a983b29
0003a9f0:000000a4:0004LOP_COMMIT_XACT LCX_NULL 0000:0a983b29
0003a9f0:000000a7:0001LOP_BEGIN_XACT LCX_NULL 0000:0a983b2a
0003a9f0:000000a7:0002LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:0a983b2a
0003a9f0:000000a7:0003LOP_SET_BITS LCX_PFS 0000:00000000
0003a9f0:000000a7:0004LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:0a983b2a
0003a9f0:000000a7:0005LOP_SET_BITS LCX_PFS 0000:00000000
0003a9f0:000000a7:0006LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:0a983b2a
0003a9f0:000000a7:0007LOP_MODIFY_HEADER LCX_PFS 0000:00000000
0003a9f0:000000a7:0008LOP_SET_BITS LCX_PFS 0000:00000000
0003a9f0:000000a7:0009LOP_DELETE_ROWS LCX_CLUSTERED 0000:0a983b2a
0003a9f0:000000a7:000aLOP_INSERT_ROWS LCX_CLUSTERED 0000:0a983b2a
0003a9f0:000000a7:000bLOP_DELETE_ROWS LCX_CLUSTERED 0000:0a983b2a
0003a9f0:000000a7:000cLOP_INSERT_ROWS LCX_CLUSTERED 0000:0a983b2a
0003a9f0:000000a7:000dLOP_DELTA_SYSIND LCX_CLUSTERED 0000:0a983b2a
0003a9f0:000000a7:000eLOP_DELTA_SYSIND LCX_CLUSTERED 0000:0a983b2a
0003a9f0:000000a7:000fLOP_COMMIT_XACT LCX_NULL 0000:0a983b2a
0003a9f0:000000ad:0001LOP_BEGIN_XACT LCX_NULL 0000:0a983b2b
0003a9f0:000000ad:0002LOP_DELETE_ROWS LCX_CLUSTERED 0000:0a983b2b
0003a9f0:000000ad:0003LOP_INSERT_ROWS LCX_CLUSTERED 0000:0a983b2b
0003a9f0:000000ad:0004LOP_COMMIT_XACT LCX_NULL 0000:0a983b2b
0003a9f0:000000b0:0001LOP_BEGIN_XACT LCX_NULL 0000:0a983b2c
0003a9f0:000000b0:0002LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:0a983b2c
0003a9f0:000000b0:0003LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:0a983b2c
August 8, 2003 at 3:28 pm
when I check the transaction log file on the folder I see reduction in size but when I see transaction log in the enterprise manager, there is no reduction in the size.
I am doing bulk-logged backup using Database Maintenance plan.
Do I need to use Full backup for truncating transaction log file.
August 8, 2003 at 10:07 pm
Run sp_spaceused @updateusage = true and go back to EM, you will see the changes.
August 11, 2003 at 11:27 am
I used sp_spaceused stored procudure and got following result. I check the transaction log file on EM and it did not reduce the size.
eXpress739.38 MB15.55 MB
182544 KB169440 KB3224 KB9880 KB
My transaction log file is still growing very fast. the size of the database file is 194 MB and Transaction log file is 546.
August 13, 2003 at 5:00 pm
Hi, balbirsinghsodhi
After truncating log, you need to shrinkdatabase, then you can see the reduction in size.
Use DBCC SHRINKFILE('log file name')
November 25, 2007 at 4:38 am
Truncating the log file (using backup log) truncates the logical log. That means there is no reduction in the physical log file size. To reduce the physical log file size, we need to shrink the log file after truncating it.
December 7, 2007 at 6:19 am
Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file and marks as inactive the virtual logs that do not hold any part of the logical log.
A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.
To know more about this please visit the following link
http://www.akadia.com/services/sqlsrv_programming.html#Example%20Shrinking%20the%20Transaction%20Log
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply