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 12»»

How do I delete excess transaction logs. Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2008 11:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131

I have 2 live database.
One of the database mdf size is 8 GB but its ldf size 76 GB.
As ldf is acquiring as much as space of hard disk.
My disk size 100 GB.
How do I remove this. How I can remove this excess logs.

Can any one suggest me..


Regards
Jafar
Post #438245
Posted Wednesday, January 2, 2008 11:48 PM


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 @ 1:53 PM
Points: 42,771, Visits: 35,870
Is your database in full recovery mode?
Do you have transaction log backups occuring regularly?
Do you need to be able to resore this database to the moment of failure if something happens to it?



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 #438250
Posted Wednesday, January 2, 2008 11:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131
Yes My database is in full recovery mode.
Transaction log backup is going on.
Yes I want to recover it from the point of failure..
Post #438254
Posted Thursday, January 3, 2008 12:06 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 @ 1:53 PM
Points: 42,771, Visits: 35,870
Check that the tran log backups are running. How often do they run?

Run this query for the DB with the problem. Please post what it returns.

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name = ...



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 #438259
Posted Thursday, January 3, 2008 11:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131
Query u have given is not working...

and backup of transaction log is going on twice in a day..
anything else u want.
Post #438742
Posted Thursday, January 3, 2008 11:20 PM


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 @ 1:53 PM
Points: 42,771, Visits: 35,870
mjafar (1/3/2008)
Query u have given is not working...


In what way is it not working?
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'myDBName'
(replace mydbname with the real name of that DB)

You are using SQL 2005? (I assume so, since this is posted in the SQL 2005 forum)


and backup of transaction log is going on twice in a day..
anything else u want.


You might consider making the log backups more frequent. If you're doing a lot of transactions, twice a day is not much.



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 #438743
Posted Thursday, January 3, 2008 11:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131
Yes sir,
I m not using SQL server 2005.

And about transaction log backup, No not huge transaction is going on it.
So i think it's not necessary to take frequent tranx backup.

Current mdf size is 8 GB & ldf is 61 GB.
How I can optimize my space.?

Post #438750
Posted Thursday, January 3, 2008 11:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
How old is your db and from when are you taking log backups.

Are you sure that no huge transactions are going on.
Have any triggers or jobs that modify data ? Since these 2 happen automatically you may missed these (just a guess).



"Keep Trying"
Post #438752
Posted Thursday, January 3, 2008 11:38 PM


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 @ 1:53 PM
Points: 42,771, Visits: 35,870
mjafar (1/3/2008)
Yes sir,
I m not using SQL server 2005.


The please post in the SQL 2000 forum so people don't waste their time and yours giving suggestions that don't work on the version you're using.



And about transaction log backup, No not huge transaction is going on it.
So i think it's not necessary to take frequent tranx backup.

Current mdf size is 8 GB & ldf is 61 GB.
How I can optimize my space.?



The log must have grown to that size because it needed that much space for transactions. You can shrink it, but the chances are it will grow again.

Check just before one of your log backups and see how much of the log is in use (taskpad in Enterprise manager)



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 #438754
Posted Friday, January 4, 2008 12:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 9, 2009 2:22 AM
Points: 57, Visits: 131
Ok Sir sorry for posting data in other forum.

My database is 2 years old.
But still no one has taken any type of backup of that database.
So its size became huge.

Now I have started taking backup.
But log size has become very huge...

Regards
Majid Jafar
Post #438760
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse