| Author |
 Topic  |
|
|
sent_sara
Constraint Violating Yak Guru
India
328 Posts |
 Posted - 04/14/2008 : 03:08:19
|
My Database size is around 1.5 GB incase my Transaction log is 15.0 gb it grows..to avoid this i use to run dbcc command:
dump tran fin_wh with no_log
dbcc shrinkdatabase(fin_wh ,5)
whether this is the correct way to do it? orelse anyother option to do implicitly.
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 04/14/2008 : 10:32:50
|
| check out there are so many posts regarding this. |
 |
|
|
tosscrosby
Aged Yak Warrior
USA
676 Posts |
Posted - 04/14/2008 : 12:18:23
|
Just a tip, in case you don't want to search. After backup tran fin_wh with no_log, do a full backup immediately afterwards. Reasons can be found as sodeep said, search this site. I point this out here so that you're protected in the event of disaster.
Terry |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/14/2008 : 15:25:43
|
quote:
Originally posted by sent_sara
My Database size is around 1.5 GB incase my Transaction log is 15.0 gb it grows..to avoid this i use to run dbcc command:
dump tran fin_wh with no_log
dbcc shrinkdatabase(fin_wh ,5)
whether this is the correct way to do it? orelse anyother option to do implicitly.
1. 15 GB log is farely big for a 1.5 GB DB. Unless it is higly transcational
2. dumpt tran ..may not be the correct option.
3. Take a log backup before shrinking.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
mdubey
Posting Yak Master
USA
133 Posts |
Posted - 05/27/2008 : 10:46:59
|
You can take a full and then Tlog backup, and then try to shrink the log file only.
Once you done with Tlog backup then automatically it will release the space from the tlog.
Manoj
MCP, MCTS |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 05/27/2008 : 10:54:05
|
quote:
Originally posted by mdubey
You can take a full and then Tlog backup, and then try to shrink the log file only.
Once you done with Tlog backup then automatically it will release the space from the tlog.
Manoj
MCP, MCTS
Doesn't make sense!!! How?????? |
|
|
|
monty
Posting Yak Master
130 Posts |
Posted - 05/27/2008 : 12:40:38
|
quote:
You can take a full and then Tlog backup, and then try to shrink the log file only.
Once you done with Tlog backup then automatically it will release the space from the tlog.
Manoj
MCP, MCTS
doesnt make sense
its me monty |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 05/27/2008 : 14:51:04
|
| I think he means it frees up space inside the log file, rather than on the disk. At least I hope he does. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 05/27/2008 : 15:02:17
|
Then why you need Full backup for that. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 05/27/2008 : 15:17:30
|
quote:
Originally posted by sodeep
Then why you need Full backup for that.
Because you've broken the transaction log chain.
If you care about the ability to a restore to a point in time, then you should perform a full backup after you've truncated the transaction log so that the chain has started again.
One of the questions that I ask to people interviewing for a DBA position is:
If you were to perform a BACKUP LOG WITH NO_LOG (TRUNCATE_ONLY), what is the next command that you should run so that you don't lose the ability to restore to a point in time?
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
Edited by - tkizer on 05/27/2008 15:19:13 |
|
|
|
tosscrosby
Aged Yak Warrior
USA
676 Posts |
Posted - 05/27/2008 : 15:21:50
|
It's amazing how many people don't know the answer to that question, until it's too late of course.
Terry |
Edited by - tosscrosby on 05/27/2008 15:23:11 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
private1010
Starting Member
15 Posts |
Posted - 05/27/2008 : 23:18:48
|
Hi Tara
Can u please let us know the answer. |
Edited by - private1010 on 05/27/2008 23:19:18 |
|
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 05/28/2008 : 07:21:37
|
if you are shrinking the log file all the time and worried about it growing...
A. change the recovery model to simple (ofcourse no point in time restoration)
OR
B. take more frequent log backups, otherwise investigate why the log file is growing to that size all the time, because logically it might need that much space, large transaction blocks etc.
HTH
--------------------
keeping it simple... |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 05/28/2008 : 12:41:02
|
quote:
Originally posted by private1010
Hi Tara
Can u please let us know the answer.
The answer was given above by tosscrosby, which is what started us on that conversation.
Answer: perform a full backup immediately after truncating the transaction log when you care about the ability to restore to a point in time so that the transaction log chain is started again. This is relevant for full and bulk-logged recovery models only since point in time recovery is not available for simple recovery model.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
| |
Topic |
|
Transaction Log Problem
