Problems with Transaction Log

[collierd]

Hello

I am using SQL Server 2000

Simple question, maybe not so straight forward answer:

I have a transaction log that is 65Gb but no space in which to back it up
How do I get round this?

Thanks

Damian.

 

[plsh]

How big is your DB?

Have you tried shrinking the log file?

 

[collierd]

The DB is 16Gb
The transaction Log is 65Gb

I've basically let it get out of control!
I don't have 65Gb free to back this up to

I was going to back up trans, remove backup, backup DB, remove backup, backup trans again (this will now be much smaller), backup DB again

Then optimise

 

[plsh]

Is your transaction log set to automatically grow?

Did you try shrinking your transaction log?

 

[SQLBill]

Take a deep breath.

Run BACKUP LOG WITH TRUNCATE_ONLY.
Then immediately backup your database (FULL BACKUP) as you won't have the log files anymore.
Then run DBCC SHRINKDATABASE (dbname)

That should work.....oh, you can let out your breath. It's not that bad.

Refer to the BOL for more information on those commands.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[collierd]

Thanks

Will try that

 

[sneki]

If you don't need your tr.log (do backup if you have free disk space), just remove it:
1. detach database
2. delete transaction log
3. attach database

 

[SQLBill]

sneki's suggestion will work, but on step three you need to remember to use sp_attach_single_file_db.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[SQLSister]

And don't forget to start backing up the transaction log regularly from now on so it doesn't grow that large again.


Questions about posting. See faq183-874