Posted by Greg D. Moore \(Strider\) on 03/11/07 04:10

"JA" <jarmour@kc.rr.com> wrote in message
news:45f37f6d$0$28180$4c368faf@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 <dbname> to 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 <dbname> with 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

[Back to original message]