cannot shrink transaction log

I have not been able to successfully shrink my transaction log. I'm
receiving the infamous "the log file for database is full".
I have tried:
backup log database with truncate_only
go
dbcc shrinkfile (database _log,0)
go
I receive this message when I am importing a large excel spreadsheet.
The error occurs on line 7956. Again, this is large, so what can I do to
make sure this works. This import is daily.
Thanks!!
*** Sent via Developersdex http://www.examnotes.net ***Joey,
Sounds like you have two issues.
1. >I have not been able to successfully shrink my transaction log.
Are there any open transactions (can use DBCC OPENTRAN to determine and KILL
to end).
Also see:
http://groups.google.com/group/micr...9c56843a0f1d1db
2. Import issue - ensure adequate log space is available by increasing the
size of the log file as well as ensuring auto-growth is enabled. You can
use DBCC SQLPERF(LOGSPACE) to track the log usage. If the table has a high
number of indexes you may consider dropping the indexes prior to the import
then recreating them following the import. Minimally logged op with BCP or
BULK COPY is another option - but probably not waranted here.
HTH
Jerry
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||What version of SQL Server are you using? What service pack? I've seen
this problem before, but I think it went away when I installed SP3.
Have you tried shrinking the file using Enterprise Manager?
You might try adding a second log file on another disk, truncate and shrink
the first file, and then remove the second log file. I'm not sure if that
will work, but it's worth a try.
Another option would be to replace the log file: detach the database using
sp_detach_db, delete the log file and then reattach the database using
sp_attach_single_file_db. If the log file wasn't created on the correct
physical disk drive, detach the database using sp_detach_db, move the log
file onto the log disk drive, and then reattach the database using
sp_attach_db.
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:OtHjosX2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I have not been able to successfully shrink my transaction log. I'm
> receiving the infamous "the log file for database is full".
> I have tried:
> backup log database with truncate_only
> go
> dbcc shrinkfile (database _log,0)
> go
> I receive this message when I am importing a large excel spreadsheet.
> The error occurs on line 7956. Again, this is large, so what can I do to
> make sure this works. This import is daily.
> Thanks!!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***