sql problem adding index column to table with data

Question

I am a NOVICE SQL ADMIN... I am trying to add a column 'ID' as Identity Primary Key Index to a Table with 212K rows Tickers Table = (date, open, high, low, close, volume, tickers).

When I issue this statement...

ALTER TABLE TICKERS ADD ID INT IDENTITY NOT NULL

I Get this message...

The transaction log for database 'TRADE' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

When I do this... select * from sys.dm_tran_active_transactions

I get this.. (which I think is the problem, cause their are 0 Open Transactions, just these Active Transactions..)

155 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

159 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

161 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

163 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

167 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

171 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

175 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

179 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

183 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

185 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

188 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

190 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

194 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

198 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

202 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

206 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

208 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

210 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

212 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0

17261 SELECT 2009-11-15 18:00:57.020 2 NULL 2 0 0 0 0 0

Does anyone have any advice for me on what to do ??? So that I can add the index to my table and then I can get better performance... My next question is do I have to modify my SQL queries to add the SELECT 'ID' column to get the index performance increase in the SELECT and UPDATE Queries???

Answer 1

It tries to create a copy of your table to facilitate a transaction.

Check a disk space where your transactional log is located. See if you need to flush a transactional log (according to your back up policy).