questions regarding full transaction log

Question

questions regarding full transaction log

Curly Bear

SSC Enthusiast

Points: 182
More actions
April 20, 2004 at 8:21 pm

#84172

Hello, All:
   I am currently maintaining a database on SQL server. One table in the database is very huge. The record count is about 450000. Sometimes, after I do queries or imports tables in, it says that the transaction log is full. Why is that? Should I remove the log files that automatically saved under the folder of SQL Server??
   I also have another question. A few days ago, I stopped my SQL Server. However, I was trying to re-connect it, but didn't succeed. Does the login account to connect SQL server need to be the same as the one set on MSSQLSERVER under "Service" in Control Panel???
   Please help! Thank you so much!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

r@st@f@R|@n Mr or Mrs. 500 Points: 551 More actions · Answer 1

if ur not using incremental backup for ur db then i would suggest u to change the recovery model to 'simple'. and make sure the autogrow property is checked. then stop n start the services.

if sql say tran log is full, it might be a harddisk runnin out of space...just double check on that....

hope this helps

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Transaction Log:

Do you backup your logs? (BACKUP LOG dbname) If not, you need to start doing that or as the previous poster said, change your Recovery Mode to SIMPLE (in Enterprise Manager - right click on the database, select Properties, go to the Options tab).

You can start the services by right clicking on the MyComputer icon, select Manage, expand until you find Services. Click on that and in the right pane find:

SQLServerAgent and MSSQLServer services. If you have instances other than default, the instance name will be part of the service name. Start both of those services (MSSQLService first).

You dont' need to be logged in with the same account as the service, but you do need to be an admin on SQLServer.

-SQLBill

Curly Bear SSC Enthusiast Points: 182 More actions · Answer 3

Got it! It is my harddish running out of the space. The transaction log is way too big. Will it deleted and repleaced with new transaction data after certain period of time?

Thank you for your help! I really appreciate it!!!!

r@st@f@R|@n Mr or Mrs. 500 Points: 551 More actions · Answer 4

go to the database property and under the Options tab,

change the recovery model to 'Simple' and click on the

auto shrink check box.

for ur info, just take note the space allocated under the Transaction Log tab

and then stop and restart the SQL.

then check back the Space Allocated under the Transaction Log tab and see if it shrinked after SQL starts!!!

let us know if this helped.....

Curly Bear SSC Enthusiast Points: 182 More actions · Answer 5

Thanks for the information!

The space allocated for transaction log is the same. I guess the log will be shrinked from now on, but the old log remains the same.