Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

database log file size 900 GB Expand / Collapse
Author
Message
Posted Saturday, December 15, 2012 11:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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.

Post #1396969
Posted Saturday, December 15, 2012 11:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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.
Post #1396970
Posted Sunday, December 16, 2012 3:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1396977
Posted Tuesday, December 18, 2012 10:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:23 PM
Points: 557, Visits: 1,637
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.
Post #1397896
Posted Tuesday, December 18, 2012 1:20 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 611, Visits: 516
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.

.
Post #1398003
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse