Transaction Logs

[JA]

Hi, I know just about nothing about SQL Server. I am getting this error:

The log file for database 'my_database' is full. Back up the transaction log
for the database to free up some log space.

I can't access the transaction logs to back them up. I am told that my ISP's
tech support should have it set up to shrink those logs automatically every
so often. Is that true?

Why are they needed? Up till Monday, the logs are just of our getting SQL
Server set up, so couldn't the logs just be deleted? Most of my site can't
run with this problem.

I'd appreciate any enlightenment anyone can give me!

Thanks, Jill

[Greg D. Moore \(Strider\)]

"JA" <ja*****@kc.rr.comwrote in message
news:45***********************@roadrunner.com...

Hi, I know just about nothing about SQL Server. I am getting this error:

The log file for database 'my_database' is full. Back up the transaction
log for the database to free up some log space.

I can't access the transaction logs to back them up. I am told that my
ISP's tech support should have it set up to shrink those logs
automatically every so often. Is that true?

I have no idea what your ISP's tech support should do.

BUT... what access DO you have?

Sounds like you're in a bit of trouble.

Do you have access to run ad-hoc queries? (Query Analyzer, OSQL, etc.)

If so, try

BACKUP LOG <dbnameto DISK='D:\db_trn_2007031111.LOG'

(that will back up the log for that database to a file called
'D:\db_trn_2007031111.LOG'

You can name it something else and put it any SQL server has access to. If
the drive is NOT local, try a UNC share.)

If for some reason that does not back up your logs (you don't have write
permissions, etc.)

You can try,

BACKUP LOG <dbnamewith truncate_only

That will truncate the transaction log to the last pending transaction.

HOWEVER, it will completely invalidate your backup chain and you should do
a full backup as soon as you can afterwards.

If you need more help tonight, email me at the address below I'll be awake
for a bit.

>
Why are they needed? Up till Monday, the logs are just of our getting SQL
Server set up, so couldn't the logs just be deleted? Most of my site can't
run with this problem.

I'd appreciate any enlightenment anyone can give me!

Thanks, Jill

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com