| Author |
Topic |
|
kensai
Posting Yak Master
172 Posts |
 Posted - 2012-02-23 : 06:25:12
|
| We had a disk failure and I restored using full and differential backups (we didn't have up-to-date tlog backups after the last diff). We backup transaction logs frequently and even after that the log file is very big and I'd like to save some space while we're starting fresh. My question is, are there any dangers of shrinking or deleting the log file after a restore? I mean will it break backup sequence or something like that? If I delete the log file and continue to make backups on the new database, will the backups fully operational in the future or will this create any problems? Thanks. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-23 : 06:34:47
|
| the log file isn't a dumb application log file. It maintains the transactional integrity of your db. Don't delete it! The ldf files will continue to be big until you back them up using sql server backup.Are you using SQL Server backups? It doesn't sound like you are. You should do that if you aren't as *well* as the server backups. you are using server backups?Maybe read up on SIMPLE / BULK LOGGED and FULL recovery models for the database backups -- be default you will be on full recovery which means you can restore the db to a point of time as long as you have the backup chain.If I've got the wrong end of the stick - could you please describe your current backup strategy.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-23 : 07:59:32
|
| Deleting the log file is a good way to damage your database beyond recovery.Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2012-02-23 : 08:23:04
|
quote:
Originally posted by Transact CharlieThe ldf files will continue to be big until you back them up using sql server backup.Are you using SQL Server backups? It doesn't sound like you are. You should do that if you aren't as *well* as the server backups. you are using server backups?If I've got the wrong end of the stick - could you please describe your current backup strategy.
We are using SQL Server backups. The thing is, there wasn't any backups until recently (read: I started working) and by the time I started regular full, diff, tlog backups the log file was already enormous. It doesn't grow much anymore AFAIK, but since we're starting to use the newly restored db I figured maybe it's a chance to get rid of this behemoth.I'm making weekly full, daily diff and tlog backups every 3 hours. I restored up to last diff backup and there aren't any newer tlog backups and we're gonna continue to use the db from this point on. When we start backups again it will be from this last diff to the backup time.So can I delete/shrink the log according to this situation? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-23 : 08:33:42
|
| Please read the article I linked.--Gail ShawSQL Server MVP |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2012-02-23 : 09:18:12
|
quote:
Originally posted by GilaMonsterPlease read the article I linked.
I red it. It says:Detach the database, delete the log file, and reattach the database....A log can only be recreated if the database was shut down cleanly. That means no uncommitted transactions, sufficient time to perform a checkpoint and sufficient log space to perform a checkpoint.How about shutting down anything that uses the database, create a full + tlog backup, deattach, delete log and reattach? I don't know about the performing a checkpoint though. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-23 : 09:27:18
|
| erm. those are presented in the article of things NOT TO DO.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2012-02-23 : 09:49:10
|
quote:
Originally posted by Transact Charlieerm. those are presented in the article of things NOT TO DO.
I get it that it is listed under log mismanagement and not recommended but it states that it could be done if handled properly. Am I wrong? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-23 : 10:05:31
|
| No, the article does not state that it can be done if handled properly. It lists that in the section "Log mismanagement" and says very clearly "his one's particularly terrible as it can cause unexpected downtime and potentially could even result in the complete loss of the database.", so unless you're happy playing russian roulette with your database leave the log alone and read the sections of the article that tell you how the log should be managed.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-23 : 15:17:28
|
| If the log is huge because it was never managed before you got there and started taking backups, then a ONE TIME shrink is fine.How often are you taking tran log backups? |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2012-02-24 : 04:05:50
|
quote:
Originally posted by russell
If the log is huge because it was never managed before you got there and started taking backups, then a ONE TIME shrink is fine.How often are you taking tran log backups?
That's the exact situation, it's a one time shrink or delete. I'm taking tlog backups every 1 hour. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-24 : 04:08:35
|
| One-time shrink is fine. Deleting the log file is not.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-24 : 12:24:09
|
| ^ what she said.DO NOT delete the log. And be sure to read the link Gail gave you thoroughly. |
|
|
|
|
Deleting/shrinking log file after restoring backup