Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
I get error on altering table, but
- Thread starter babeo
- Start date
sybaseguru
Instructor
I assume this is not your production database
tHe message means that your database transaction log is full and you cannot perform the operation. If you have a DBO role you can dump the transaction log by doing
dump tran <DATABASE_NAME> with truncate_only
go
This may or may not free the transaction log. In that case try
dump tran <DATABASE_NAME> with no_log
go
This should free up the transaction log.
If you still have problem with your database do the following and send the list
use <DATABASE_NAME>
go
sp_helpdb <DATABASE_NAME>
go
select * from master..syslogshold
go
and send the output
Good luck
tHe message means that your database transaction log is full and you cannot perform the operation. If you have a DBO role you can dump the transaction log by doing
dump tran <DATABASE_NAME> with truncate_only
go
This may or may not free the transaction log. In that case try
dump tran <DATABASE_NAME> with no_log
go
This should free up the transaction log.
If you still have problem with your database do the following and send the list
use <DATABASE_NAME>
go
sp_helpdb <DATABASE_NAME>
go
select * from master..syslogshold
go
and send the output
Good luck
- Thread starter
- #3
Hi,
Here is what I did:
- checkpoint
- truncate the log using both methods, but it looks like it does not succeed.
error messages "DUMP TRANSACTION for database 'loms6' could not truncate the log. Either extend
the log using ALTER DATABASE ... LOG ON command or eliminate the oldest active
transaction in database 'loms6' shown in syslogshold table."
- Then I want to try to increase the logsegment for this database, but I am not sure the command, is it:
alter table loms6 log on data12=4096 or is it below
alter table loms6 log on log02=4096
(notes, the "data12" is reserved for data, and the "log02" is reserved for log - I suppose too)
- and here is the information:
name db_size owner dbid created status
------------ ------------- ------------------------ ------
loms6 13138.0 MB sa 19 Nov 29, 2002 no options set
(1 row affected)
name attribute_class attribute int_value char_value comments
----------- ------------
loms6 lock strategy page lock promotion NULL PCT = 100, LWM = 2000, HWM = 2000 NULL
device_fragments size usage created free kbytes
------------------------------ ------------- --------------data17 8192.0 MB data only Nov 29 2002 12:59PM 1834
data22 4096.0 MB data only Apr 11 2003 3:16PM 3389014
log01 226.0 MB log only Nov 29 2002 12:59PM not applicable
log05 624.0 MB log only Nov 29 2002 12:59PM not applicable
---------------------------------------------------------
log only free kbytes = 250678
(return status = 0)
1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid starttime name xloid
------ -------------------------------- -----------
19 0 260 4346137 0x00425119000d 0x000000000000 May 29 2003 9:56AM $ins 520
(1 row affected)
Thanks alot for your help
Here is what I did:
- checkpoint
- truncate the log using both methods, but it looks like it does not succeed.
error messages "DUMP TRANSACTION for database 'loms6' could not truncate the log. Either extend
the log using ALTER DATABASE ... LOG ON command or eliminate the oldest active
transaction in database 'loms6' shown in syslogshold table."
- Then I want to try to increase the logsegment for this database, but I am not sure the command, is it:
alter table loms6 log on data12=4096 or is it below
alter table loms6 log on log02=4096
(notes, the "data12" is reserved for data, and the "log02" is reserved for log - I suppose too)
- and here is the information:
name db_size owner dbid created status
------------ ------------- ------------------------ ------
loms6 13138.0 MB sa 19 Nov 29, 2002 no options set
(1 row affected)
name attribute_class attribute int_value char_value comments
----------- ------------
loms6 lock strategy page lock promotion NULL PCT = 100, LWM = 2000, HWM = 2000 NULL
device_fragments size usage created free kbytes
------------------------------ ------------- --------------data17 8192.0 MB data only Nov 29 2002 12:59PM 1834
data22 4096.0 MB data only Apr 11 2003 3:16PM 3389014
log01 226.0 MB log only Nov 29 2002 12:59PM not applicable
log05 624.0 MB log only Nov 29 2002 12:59PM not applicable
---------------------------------------------------------
log only free kbytes = 250678
(return status = 0)
1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid starttime name xloid
------ -------------------------------- -----------
19 0 260 4346137 0x00425119000d 0x000000000000 May 29 2003 9:56AM $ins 520
(1 row affected)
Thanks alot for your help
sybaseguru
Instructor
Ok you have along running transaction. Do a sp_who and send the output or just do
kill 260 ---- this is process holding the log
thta should free up your tran log. If it does not work I will tell you what.
Can upu also confirmn that this is a dev database cause you have not got any truncate option on the database?
kill 260 ---- this is process holding the log
thta should free up your tran log. If it does not work I will tell you what.
Can upu also confirmn that this is a dev database cause you have not got any truncate option on the database?
sybaseguru
Instructor
Once killed the job do "select * from syslogshold" to make sure that nothing is there and do a "dump tran loms6 with truncate_only"
- Thread starter
- #6
Hi,
I did increase the logsegment for the database and I try again to recreate the table, however I run into the same problem of out of log space again, I don't think I will keep increasing the log space, what can I do to stop this?
BTW, Due to this problem, I try to truncate the log again, and kill the process (I check and it is same spid number), but you are right, I don't have enough right to truncate the log nor to kill the process. However, according to the log message that the command to truncate with no_log is successul ?!
What should I do next to free up the space now? I run out of logdevice !
Thanks for your help.
I did increase the logsegment for the database and I try again to recreate the table, however I run into the same problem of out of log space again, I don't think I will keep increasing the log space, what can I do to stop this?
BTW, Due to this problem, I try to truncate the log again, and kill the process (I check and it is same spid number), but you are right, I don't have enough right to truncate the log nor to kill the process. However, according to the log message that the command to truncate with no_log is successul ?!
What should I do next to free up the space now? I run out of logdevice !
Thanks for your help.
sybaseguru
Instructor
Hang on you have a long running transaction (spid 260), if you cannot kill it (you need 'sa' rights) then you will not be able to get rid of it and do not increase the tran log. Who has "sa" access to your box? Ask him to kill process 260 (what message did you get when you did try the killcommand)?
- Thread starter
- #8
Ha ha,
Thanks alot (no wonder you call yourself sybaseguru !)
Yes, you are right again, this process is a long run transaction, so in fact, I kill it (no error), but the message keeps saying different thing, I probably killing some other ps then !! (Ah! that's the danger of using GUI instead of command line!)
Ok,
so, I would like to recreate the table, but so ... scare of running out of space again, how can I do?, by the way, due to the 1st problem of running out of space, I have the orginal table is auto renamed to dbo.oldtableRenamed_05292003165106000, I run the script to create the table and run into the same problem at the 2nd time, now, could I go back to the script of recreating the table and change the line of, :
"INSERT INTO dbo.newtable ( allfieldsxxxx ) SELECT * FROM dbo.oldtableRenamed_05292003192028000"
to (look at the oldtableRenamed)
"INSERT INTO dbo.newtable ( allfieldsxxxx ) SELECT * FROM dbo.oldtableRenamed_05292003165106000"
Thanks
Thanks alot (no wonder you call yourself sybaseguru !)
Yes, you are right again, this process is a long run transaction, so in fact, I kill it (no error), but the message keeps saying different thing, I probably killing some other ps then !! (Ah! that's the danger of using GUI instead of command line!)
Ok,
so, I would like to recreate the table, but so ... scare of running out of space again, how can I do?, by the way, due to the 1st problem of running out of space, I have the orginal table is auto renamed to dbo.oldtableRenamed_05292003165106000, I run the script to create the table and run into the same problem at the 2nd time, now, could I go back to the script of recreating the table and change the line of, :
"INSERT INTO dbo.newtable ( allfieldsxxxx ) SELECT * FROM dbo.oldtableRenamed_05292003192028000"
to (look at the oldtableRenamed)
"INSERT INTO dbo.newtable ( allfieldsxxxx ) SELECT * FROM dbo.oldtableRenamed_05292003165106000"
Thanks
sybaseguru
Instructor
open an sql sesion and repeat the sp_helpdb sp_who, select * again and send the output again
sybaseguru
Instructor
Also I think what is happening you are filling the transaction log with your insert, select statement. You need to change the database option differently so do
use master
go
sp_dboption loms6, 'select into', true
go
sp_dboption loms6, 'trunc.', true
go
use loms6
go
checkpoint
go
sp_helpdb loms6
go
Now you need to insert data into your new table without filling the transaction log. Use id column to do the insert in stages i.e
insert into ... select * from old_name where id <10000
or whatever and next time do the insert with id > 10000 and id < 20000
See how it goes
use master
go
sp_dboption loms6, 'select into', true
go
sp_dboption loms6, 'trunc.', true
go
use loms6
go
checkpoint
go
sp_helpdb loms6
go
Now you need to insert data into your new table without filling the transaction log. Use id column to do the insert in stages i.e
insert into ... select * from old_name where id <10000
or whatever and next time do the insert with id > 10000 and id < 20000
See how it goes
- Thread starter
- #11
sybaseguru
Instructor
do an inser select with explicit column name say like this
insert into
new_table
(
col1,
col2,
col3,
new_column
)
select
col1,
col2,
col3,
NULL -- this is the default for new column
from old_table
insert into
new_table
(
col1,
col2,
col3,
new_column
)
select
col1,
col2,
col3,
NULL -- this is the default for new column
from old_table
- Thread starter
- #14
- Status
Similar threads
- Locked
- Question