0

I have limited access to this server eg I cannot SELECT * FROM sys.dm_tran_session_transactions for example

I, perhaps foolishly, ran this query to insert some test data...

begin transaction

update aqt_tblquote 
set intVisits = ABS(CHECKSUM(NewId())) % 10

select top 100 intVisits FROM aqt_tblquote

rollback transaction

This sat about for a while and then informed me the log was full. From my experience this is not good news!

I was hoping that given it is in a ROLLBACK that it would just return the table to its previous state and clear the log.

However if I select from sys.databases I get log_reuse_wait_desc = 'ACTIVE_TRANSACTION'

I've closed Management Studio but that didn't do it. How can I kill it? Or do I need to contact the virtually unobtainable DBAs? (I work in a large organisation where there are many layers of bureaucracy to cut through).

El Ronnoco
  • 163
  • 1
  • 6
  • Instead of killing it could you check if you have not put restriction on log file. Does disk on which log file resides is full ? if you kill even rollback would get stuck. – Shanky Sep 12 '14 at 10:09
  • I can't do anything physically with the server - they are over the network in another city.... – El Ronnoco Sep 12 '14 at 10:18
  • I guess you can run query use db_name go select physical_name,max_size,is_percent_growth from sys.database_files if max size is -1 it is unlimited growth – Shanky Sep 12 '14 at 10:39
  • @Shanky Two rows - max_size 172800 and max_size 7168. MDF and LDF respectively. is_percent_growth = 0 for both – El Ronnoco Sep 12 '14 at 11:01
  • You have set a max size for log file as well as data file and due to your update operation it has reached its limit you need to increase the max size value to allow operation to complete. Hopefully this would sort out things – Shanky Sep 12 '14 at 11:11
  • @Shanky - Is there not a way I can just terminate the transaction? Or can that only be done by someone with more privileges? – El Ronnoco Sep 12 '14 at 12:53
  • You can kill command with Kill spid. But I would 'not advise' that or you can first run Kill SPID STATUSONLY to check how much time it would take to rollback(this would not kill SPID but would tell you stats). Main problem here is unless you extend log file size limit even killing would not help. SO please do it – Shanky Sep 12 '14 at 13:08
  • , you need not worry.there is concept called log space reservation.so even if your transaction ends with log file full.so rollback will happen always. – TheGameiswar Sep 12 '14 at 15:33
  • @TheGameiswar Well looking at my query (which is contained in a Begin/Rollback) - Why would I end up with a full logfile and no rollback? – El Ronnoco Sep 16 '14 at 09:26
  • Note that highest voted answer on the duplicate correctly points out "If you are in SQL Server Management Studio you can simply click on the stop button and 99% of the time the query will simply stop" – James Jenkins Nov 09 '18 at 14:49

0 Answers0