transaction log full

Question

transaction log full

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

striker-baba SSCrazy Points: 2170 More actions · Answer 1

Actually , I first tried to shrink the log and data file using dbcc shrinkfile command.

But after using the command both the data and log file sizes got increased.

I understand that data file had lot of indexes and so the shrinkfile command doesnt work properly for data file. But I dont know why the log file sizee got increased??

I need the solution for both the scenarios I mean shrinking log and data files.

I think I can back up the log so that log gets truncated.... but how abt data file?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Apparently you're not to keen on reading full articles which would give you all the info you need to do this job so here's a "safe path"...

Ask for a maintenance window where the db is not used at all, or as little as possible.

Switch to simple recovery.

Immediatly take a full backup (you may even take one before switching to be reallllllly safe).

Run shrinkfile (make sure to keep a reasonable amount of space)... don't go from 10 GB to 10 mb. Maybe a ballpark figure could be 10% of the size of the datafiles. That works for my environements but it may not be suited for yours.

Switch back to full.

Take another full backup.

Add a maintenance step to take t-log backups.

And you should be good to go and never get that error again.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

striker-baba (4/25/2010)
Actually , I first tried to shrink the log and data file using dbcc shrinkfile command.
But after using the command both the data and log file sizes got increased.
I understand that data file had lot of indexes and so the shrinkfile command doesnt work properly for data file. But I dont know why the log file sizee got increased??
I need the solution for both the scenarios I mean shrinking log and data files.
I think I can back up the log so that log gets truncated.... but how abt data file?

Log files increase because they log the database activity.

How frequent are your transaction log backups ?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

i would suggest, rather playing with log/data files, spend some time to understand the log architecture how they grow how they work ; when/why they behave good or bad ?

Fixing issue by hook or crook will not help you/your organization, as this problem can again knock your door.So spend time to read article (suggested above by Gail ).

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

striker-baba (4/23/2010)
My Sr.dba says we can truncate the log no backup is required.
.
.
.
what do you suggest.?

I'd suggest that you stop trying to do a DBA's job. Seriously... the DBA should be taking care of this, not you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bernard black SSC-Addicted Points: 463 More actions · Answer 6

I can't imagine your DBA won't help. Its amazing how some people interact with each other (or don't)

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

bernard black (4/27/2010)
I can't imagine your DBA won't help. Its amazing how some people interact with each other (or don't)

OP's DBA was "helping", but not sure that it was good advice:

"... My Sr.dba says we can truncate the log no backup is required ..."