Urgent! Help! How to dump the transaction log?

God!   I allocate not enough space for temp database, now it is full, I do not know the exact use of system procedure and so on, but I need to solve the problem soon. I need to know how to dump transaction log. Can you write the statement for me?   THANK YOU!
happyeverydayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amitpagarwalCommented:
dump transaction DATABASE_NAME with no_log
0
namasi_navaretnamCommented:
0
mansoor_a_khanCommented:
NO_LOG is not a good option to use, it may corrupt ur database.
I would suggest.

dump transaction DATABASE_NAME with truncate_only
checkpoint

Cheers,
MAK

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rooksmithCommented:
I second that !  Dump transaction with no_log is the same as a dump transaction with truncate_only, with the exception that "no log record is written to the log" - In other words - it truncates the inactive portion of your log and does not write the log records to a file. In short it will invalidate your subsequent dump transaction commands.

So if the author of the message simply wants to back up the log, he should be doing something like this:

Time 1: dump database dbname to "dbdumpfilename"
Time 2: dump transaction dbname to "transfile1"
Time3: dump transaction dbname to "transfile2"

etc...

Recovery is the same list with the word load instead of dump..

Good luck and RTFM!   ;-)
0
rooksmithCommented:
Whoops - my appologies. I should always read the entire question before posting and answer!

Your question was about Tempdb which is a different breed of cat.With Tempdb the thing to realize is that it gets created (ie, the model database is literally copied to the tempdb) from scratch each time you restart the server. So go ahead and use the "dump tran with truncate_only" command to clear out the log. If that doesnt work use "dump tran with no_log". IF all else fails reboot... In fact if you have the time reboot first.

Another option is to alter the tempdb and add more space. This is often the only solution if this keeps happening over and over again. I would caution against altering it on the master device. Master gets a lot of I/O and so does the tempdb. I would create a new device fragment on a "fast disk" somehwere (note- you might be using RAID - go for Raid level 0, or "striped only", higher throughput and no redundancy for the tempdb)....

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.