SQL Server Transaction Log problem....

[bharatm]

Hi,

I m having a DB on the web server with a hosting company..
The DB had been working fine and all of a sudden i get the Transaction Log is full error and
i checked the size of the Trans Log and its 17 MB that is the max size allocated by the hosting
guys.

I checked the size using the following methods.
sp_helpdb and here it says the size is 17408KB
and when using the command DBCC SQLPERF(LOGSPACE) it says
Logsize (Mb): 16.992 and Log Space Used (%): 12.40 .. why are the two stmnts showing
different sizes .. i.e in DBCC it says only around 12% has been used..

i am having a SP which is generating a Temp table within it writing some data to it and then
deleting the Temp table again. I dont have any Commit Trans and Rollback Trans stmnts in this
SP. Is the problem occuring bcos of this SP ? Do i need the commit / rollback trans stmnts..

The hosting company has not given me rights to run the DBCC SHRINKFILE and DBCC SHRINKDATABASE stmtns so i am unable to truncate the log.. is there any other way to truncate
the log..?

The DB is in simple recovery mode...
Thanx a lot for the help...


Regards

bTM

[Wen Lie]

hmm...
Is the DB options for Auto-shrink set to ON (checked) ?

have you run below T-SQL (and will the hosting allow you to do this) ? :

dump transaction database_name with no_log