• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

Large transaction logs

Good morning all

Currently, i have 2 servers with transaction logs of 21GB (on a 34GB drive) and 41GB (on two drives - 21 and 20) . Both have unexpectedly jumped size and i now face the dilemma of 2 choices : installing an fresh set of drives or trying to prune them in some way. With limited disk space, though, they won't shrink any more and my question comes to this :

Can i truncate the log without damaging the actual database then shrink ? If so, how to, especially on the database with the two log files ? Mind you it has been storing transactions for the past 5 years and no-one sat down to fix it.
db_Log is on a RAID5 with 800mb free.
db_Log2 is on a single drive with 10GB free.
I do have the flexibility of 3 drives that i have sitting beside me at the moment

Any solutions ?

Cheers !
0
AlexD_Gr
Asked:
AlexD_Gr
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Can i truncate the log without damaging the actual database then shrink ?
yes.
however, the best would be to backup the transaction log though, and then shrink it (this will avoid data loss in case of crash).

to avoid such a increase, you should have regular transaction log backup in place on the database (possibly, you can use the disk you have beside you for exactly that...), this will keep it from growing.
0
 
AlexD_GrAuthor Commented:
The log itself is set to grow @ 150mb so as to avoid similar problems that the company had in the past as well as removing the default 7% that MS SQL has from start - 7% @ 21GB is deadly when you have small drives.

Backup - i.e. detach db and copy log.ldf over somewhere else ? (just to be 100% certain)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Backup - i.e. detach db and copy log.ldf over somewhere else ? (just to be 100% certain)

that is one possibilty, although you don't need to do it that way (as it requires db downtime)
0
 
AlexD_GrAuthor Commented:
server essentially isn't used after 6pm so it's all mine
0
 
AlexD_GrAuthor Commented:
Thanks angelIII - all's well !
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now