December 15, 2012 at 11:08 pm
Hi I know this might be silly question here. but its intresting after in spite my trials the file does not shrink.
The server in Test server so no issues I can try out anything on log except loosing the data files.
DB Size is 1.4 TB. CDC Enabled and is in Simple recovery mode.
Log file size is 900 GB
I did the below activity.
--> since the cdc capture job is running and not able to consume the updates since 8 days because it ran out of disk space. I created a new log file on another mount point. waited for 5-6 hours no improvement.
--> took full backup and dropped the database
--> restored it with out CDC.
--> Still shrink log file is not working.
We do not need logs. we only need the data to be intact.
Please suggest what to do in this case.
Meanwhile I will try doing tran backup and see if I will be able to shrink the file after that.
December 15, 2012 at 11:14 pm
I have changed the DB to FULL recovery mode temporarily untill this log space is released back to the disk
Tran backup is not working since there it is not finding the most recent FULL backup.
I have started the full backup and then will do transactional log backup.
Further will shrink the file. Fingers crossed.
December 16, 2012 at 3:22 am
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
I have changed the DB to FULL recovery mode temporarily untill this log space is released back to the disk
Waste of time.
In simple recovery model, the log is marked reusable when a chackpoint occurs, if nothing else needs the log records. In full recovery, a log backup is needed in addition. Hence, by switching to full recovery all you've done is add more conditions that have to be satisfied before the log can be reused. Switch back to simple.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2012 at 10:01 am
I know there's no hard & fast rule what the log file size should be relative to the DB file size, but a log file 60% of the data file size seems to me to be normal.
December 18, 2012 at 1:20 pm
I will say we have a 1.9 TB database and we are in full recovery mode. We run transaction log backups hourly. we run with a 400GB drive for our log. Not sure that is helpful, but just for size comparison.
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply