database log file size 900 GB

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » database log file size 900 GB

database log file size 900 GB

Rate Topic

Display Mode

Topic Options

Author

Message

Mac1986

Posted Saturday, December 15, 2012 11:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 180, Visits: 513

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

Mac1986

Posted Saturday, December 15, 2012 11:14 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 180, Visits: 513

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

GilaMonster

Posted Sunday, December 16, 2012 3:22 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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

dan-572483

Posted Tuesday, December 18, 2012 10:01 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 332, Visits: 958

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

Bill (fluffydeadangel)

Posted Tuesday, December 18, 2012 1:20 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 70, Visits: 221

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 »

Permissions