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

Transaction Log file Deletion or movement? Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 2:22 AM
Points: 6, Visits: 133
Hello All,

I have a Query Please help me out about this:
Scenerio is, I have a database server which have a harddisk size is 70 GB c drives is occupied by OS. on which I have 2 databases DBA1 and DBA2 both database are live databases and configured in production environment.DBA1 and DBA2 are also configred with database mirroring without Automatic failover.
Now on my Primary server in C drive[Size=30GB] datafiles are configured which is .mdf format and in D drive[Size=30GB] have Logfiles are storing DBA1 logfile size is 12 Gb and DBA2 logfile size is 13GB and these logfiles are growing everyday 500MB.
Now how to manage these logfile?

Thanks
Post #1401827
Posted Wednesday, January 2, 2013 8:12 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 653, Visits: 3,841
This would be a good start.

http://www.sqlservercentral.com/articles/Administration/64582/
Post #1401867
Posted Wednesday, January 2, 2013 8:16 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 @ 7:53 AM
Points: 42,822, Visits: 35,952
Please read through this - Managing Transaction Logs


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 #1401869
Posted Wednesday, January 2, 2013 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 2:22 AM
Points: 6, Visits: 133
Hi,

Thanks for quick response I have read the document.But as I mentioned this is a production database and my log is growing every day 500mb and It will be full in next 8 days.And it will create aproblem after 8 days I want something like automate that after 15 days automatic my log will move to some other NAS drive location is that possible to live databases?


Thanks
Post #1401904
Posted Wednesday, January 2, 2013 11:29 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 @ 7:53 AM
Points: 42,822, Visits: 35,952
Read the article again.

If your log is continually growing it's because you're mis-managing it. Most likely full recovery model with no log backups. Read the article, make sure you have appropriate log management going on. A properly managed transaction log does not continually grow.

And no, you can't move it, you can't delete it. Not without potentially destroying your database.



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 #1401993
Posted Wednesday, January 2, 2013 10:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 22, 2014 11:33 PM
Points: 15, Visits: 131
To simplify the fix for you

What is the backup strategy you have implemented for these databases?

You need to have log backups at regular intervals inorder to manage your transaction log growth.

Please read the article referred above that makes you understand how transaction log files are managed
Post #1402173
Posted Wednesday, January 2, 2013 10:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
jitendra.te (1/2/2013)
I have a Query Please help me out about this:
Scenerio is, I have a database server which have a harddisk size is 70 GB c drives is occupied by OS. on which I have 2 databases DBA1 and DBA2 both database are live databases and configured in production environment.DBA1 and DBA2 are also configred with database mirroring without Automatic failover.
Now on my Primary server in C drive[Size=30GB] datafiles are configured which is .mdf format and in D drive[Size=30GB] have Logfiles are storing DBA1 logfile size is 12 Gb and DBA2 logfile size is 13GB and these logfiles are growing everyday 500MB.
i found this configuration a bit confusing , you are saying that you have single harddisk with size 70gb plus you are using the same in production ?
additionally you have mirrong setup too , what i am thinking here is apart from youre space issue you might be getting IO isssues too sooner or later.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1402175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse