SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Transaction Log Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 04/14/2008 :  03:08:19  Show Profile  Reply with Quote
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
7174 Posts

Posted - 04/14/2008 :  10:32:50  Show Profile  Reply with Quote
check out there are so many posts regarding this.
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 04/14/2008 :  12:18:23  Show Profile  Reply with Quote
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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/14/2008 :  15:25:43  Show Profile  Reply with Quote
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
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/27/2008 :  10:46:59  Show Profile  Reply with 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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/27/2008 :  10:54:05  Show Profile  Reply with Quote
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??????
Go to Top of Page

monty
Posting Yak Master

130 Posts

Posted - 05/27/2008 :  12:40:38  Show Profile  Reply with Quote
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
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 05/27/2008 :  14:51:04  Show Profile  Reply with Quote
I think he means it frees up space inside the log file, rather than on the disk. At least I hope he does.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/27/2008 :  15:02:17  Show Profile  Reply with Quote

Then why you need Full backup for that.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 05/27/2008 :  15:17:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 05/27/2008 :  15:21:50  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 05/27/2008 :  15:30:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
No one has answered the question correctly so far. If you don't know the answer, just say "I don't know". Don't BS your way through it. I already know what the answer is, so your BS response isn't helping you.

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
Go to Top of Page

private1010
Starting Member

15 Posts

Posted - 05/27/2008 :  23:18:48  Show Profile  Reply with Quote
Hi Tara
Can u please let us know the answer.

Edited by - private1010 on 05/27/2008 23:19:18
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/28/2008 :  07:21:37  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 05/28/2008 :  12:41:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000