Transaction Log Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Transaction Log Problem

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-14 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-14 : 10:32:50

check out there are so many posts regarding this.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-14 : 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
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-14 : 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

133 Posts

Posted - 2008-05-27 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-27 : 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 - 2008-05-27 : 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 - 2008-05-27 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-27 : 15:02:17

Then why you need Full backup for that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 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

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-27 : 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 15:30:12

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

private1010
Starting Member

15 Posts

Posted - 2008-05-27 : 23:18:48

Hi Tara
Can u please let us know the answer.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-05-28 : 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

38200 Posts

Posted - 2008-05-28 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources