SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL server logs backup / reduction


SQL server logs backup / reduction

Author
Message
Ice007
Ice007
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 22
Intro:
I have sql server installed on c drive but the logs (LDF) is stacking up and I am getting out of space.

Specs:
Let my Db name be Analytics and the file Is analytics.ldf
Current settings are: recover - full , autogrowth enable but restricted by 1mb.

Analytic.mdf is being used daily by me , so can I resolve this issue.


Question:
1)so what is best practice to make a backup of that log and data , and also reduce the space they are using.
2)if Taking offline is a possible solution , need instructions to get it safely back online.
3)Will my data get effected if I create new similar name files and delete previous one after backing them up?

solution:
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228013 Visits: 46339
Please read through this - Managing Transaction Logs and http://www.sqlservercentral.com/articles/Transaction+Log/72488/

3)Will my data get effected if I create new similar name files and delete previous one after backing them up?


Other than the fact that it'll result in the database being marked recovery_pending and you needing to restore a full backup to get it accessible again, no.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


DBA From The Cold
DBA From The Cold
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3453 Visits: 1757
Hello,

As your database is in the full recovery mode, the transaction log will not be truncated unless you perform a transaction log backup. When you backup the log, the log is truncated (bear in mind that this does not mean that the file will be shrunk).

The correct backup strategy really does depend on the individual database, how often is it updated? How much data can you afford to lose in the event of corruption?

Andrew
Ice007
Ice007
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 22
My data gets updated daily once.
I need more space in disk and historical data changes is not necessary because i can use backup to create them back again ( correct me if I am wrong)
DBA From The Cold
DBA From The Cold
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3453 Visits: 1757
If your database get updated once a day I would backup your database and log before the load, adjust the log to a size that is suitable (preferably avoiding autogrowth) and monitor whilst the data is running in.

If your transaction log is expanding during the load you can back it up during to manage it OR add more disk space.

Andrew
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228013 Visits: 46339
aayushmail007 (4/22/2013)
My data gets updated daily once.
I need more space in disk and historical data changes is not necessary because i can use backup to create them back again ( correct me if I am wrong)


If you do what you are proposing, you will leave your entire database unusable and will need to restore from backup (which will recreate the huge files) access the database again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Ice007
Ice007
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 22
so in my case , I just need data to be restored by months but main emphasis it get space on my drive.
so which backup should I be using and instruction on how to do it be very helpful.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148230 Visits: 19444
My thoughts:

If you are updating this once and day, and it's your database, important, but not affecting others, here's what I'd do.

Change the database to simple mode. With updates once a day, no real reason to recover to a point in time (outside of the backup). If that's no the case, don't do this, but if you only care about recovering in a disaster to the time of the backup, this works. However before you do this, I'd run a log backup before my load/change, then load/change and run another log backup. This second one gives you an idea of how big your log should be.

Run a full backup before each load. You could do it after, but if the load fails or there's an issue, I'd prefer to have a recent backup and not assume that yesterday's backup is there. Copy this off the disk to another location immediately.

Now you should be able to shrink the log file to roughly what you found in step 1 above, with some pad. I'd only shrink the log here, not the data file. Use DBCC SHRINKFILE.

Ultimately you need space for the data, so you'll need that much disk space. I'd also recommend you read Gail's article so you understand what you're changing here.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228013 Visits: 46339


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Ice007
Ice007
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 22
Thanks Steve and Gila.
if this is organization data what would you prefer ?

My first requirement will be to have space in drive
2nd recover when needed
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search