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

SQL server logs backup / reduction Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 12:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:08 AM
Points: 12, 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:




Post #1445111
Posted Monday, April 22, 2013 12:56 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:44 PM
Points: 42,454, Visits: 35,509
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 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 #1445119
Posted Monday, April 22, 2013 12:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 1,138, Visits: 809
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
Post #1445121
Posted Monday, April 22, 2013 1:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:08 AM
Points: 12, 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)
Post #1445128
Posted Monday, April 22, 2013 1:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 1,138, Visits: 809
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
Post #1445133
Posted Monday, April 22, 2013 1:17 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:44 PM
Points: 42,454, Visits: 35,509
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 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 #1445135
Posted Monday, April 22, 2013 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:08 AM
Points: 12, 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.
Post #1445137
Posted Monday, April 22, 2013 1:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:30 AM
Points: 33,062, Visits: 15,177
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
Post #1445148
Posted Monday, April 22, 2013 1:49 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:44 PM
Points: 42,454, Visits: 35,509



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 #1445150
Posted Monday, April 22, 2013 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:08 AM
Points: 12, 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
Post #1445151
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse