Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjsteury2
Starting Member
14 Posts |
 Posted - 2009-11-15 : 19:00:38
|
| As I mentioned in previous post I am trying to create an index on a table with existing data... but now I have another issue.. it says that The transaction log for database 'TEST' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databasesWhen I use this commmand select * from sys.dm_tran_active_transactionsThere are 20 transactions:155 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0159 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0161 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0163 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0167 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0171 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0175 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0179 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0183 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0185 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0188 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0190 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0194 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0198 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0202 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0206 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0208 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0210 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 0212 worktable 2009-11-15 16:48:47.593 2 NULL 2 0 0 0 0 017261 SELECT 2009-11-15 18:00:57.020 2 NULL 2 0 0 0 0 0HOW DO I KILL THESE TRANSACTIONS AND OR OVDECOME THIS ISSUE IF THIS IS THE REASON I CAN NOT ADD THE ID COLUMN IDENTITY NOT NULL TO MY TABLE???Craig |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-15 : 22:19:24
|
| are u out of disk space? what is log file restricted to? |
 |
|
|
cjsteury2
Starting Member
14 Posts |
Posted - 2009-11-16 : 07:29:13
|
quote:
Originally posted by russell
are u out of disk space? what is log file restricted to?
1. No I am not out of diskspace. I have 200 GB available and have used hardly any.2. I don't know how to answer that second question... restricted to? To my knowledge there are no 'restrictions'. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 08:53:54
|
| in the object explorer, right click the database, then click properties, then click files. look to see what log file is restricted to.also what recovery model is db in? |
|
|
|
cjsteury2
Starting Member
14 Posts |
Posted - 2009-11-16 : 17:18:56
|
quote:
Originally posted by russell
in the object explorer, right click the database, then click properties, then click files. look to see what log file is restricted to.also what recovery model is db in?
Russell, It is restricted to 66mb is it 52 mb it says it has never been backed up. I have tried Full backup recently but it apparently did not back Log, before that I think it was simple, but it's FULL now for recover model.I can't really do anything with tables and their is 1 open transaction.When I do this...SELECT [name], recovery_model_desc, log_reuse_wait_desc FROM sys.databases;I get this...master SIMPLE NOTHINGtempdb SIMPLE NOTHINGmodel FULL NOTHINGmsdb SIMPLE NOTHINGTIMBER FULL NOTHINGVTRADE FULL LOG_BACKUP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
active transactions