Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sureshot
Yak Posting Veteran
72 Posts |
 Posted - 2007-08-19 : 08:33:54
|
| I'm running full recovery mode and doing log shipping so changing to simple mode is not an option.I'm running BACKUP LOG right before and when I check it says my log is 99% free (on a 180GB log).When I do DBCC LOGINFO('dbname') right before and after I see a dozen entries and they are all over the file and not just at the starting offset areas. The BACKUP LOG doesn't clean out the file completely.Is there any explanation for this? Even though I'm doing this at off hours, is it possible that someone on the site in that split second is putting new entries in the log? Why are they spread out though? If they just put entries at the beginning I could shrink the file to a normal size still. |
|
|
anxcomp
Starting Member
41 Posts |
Posted - 2007-08-19 : 10:42:24
|
| I have similar problem, any suggestion? I resolve this by BACKUP LOG WITH NO_LOG, but it's NOT recommended by Microsoft :( And this mean you LOSE any information from LOG, you CAN'T restore from transact log backup!!! It's working but I know it is not good way do this.--Regards,anxcomp |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-19 : 17:03:00
|
| Just do full backup again. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-08-20 : 15:05:14
|
| DBCC shrinkfile will not shrink the log file, if there are any log records towards the end of the file. Check for open transactions with DBCC OPENTRAN. If there are none, backup the log as normal, and then run DBCC SHRINKFILE (logfileid, targetsize). The normal log backup should remove the entries at the end of the log, unless they are required for replication, in which case run the logreader agent. |
|
|
|
anxcomp
Starting Member
41 Posts |
Posted - 2007-08-20 : 16:16:43
|
| mcrowley wrote:Check for open transactions with DBCC OPENTRAN. If there are none, backup the log as normal, and then run DBCC SHRINKFILE (logfileid, targetsize)but what about this:dbcc opentrandbcc shrinkfile ('db_name_log', truncateonly)dbcc opentranoutput:No active open transactions.DBCC execution completed. If DBCC printed error messages, contact your system administrator.HERE -------> Cannot shrink log file 2 (db_name_log) because all logical log files are in use.(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.No active open transactions.DBCC execution completed. If DBCC printed error messages, contact your system administrator. I don't understand this?--Regards,anxcomp |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-20 : 22:22:41
|
| Tried shrink log in ssms? Works better than 'dbcc shrinkfile'. |
|
|
|
anxcomp
Starting Member
41 Posts |
Posted - 2007-08-21 : 03:50:40
|
| I can't from ssms, I have to do this from T-SQL script. Anny other suggestion, please--Regards,anxcomp |
|
|
|
|
|
|
|
|
DBCC SHRINKFILE won't shrink-yes I did backup log