September 6, 2011 at 8:31 am
I know this has been asked before. And I did peruse the forums but couldn't reach any conclusion.
I have multiple tables with millions of records. Have received a request to add a TIMESTAMP column for auditing purposes. Since TIMESTAMP generation is controlled entirely by the database I am not able to think up of any "piecemeal" approach to solving my problem.
The [font="Courier New"]ALTER TABLE [TableName] ADD TimeStamp NOT NULL[/font] statement takes hours and then fails.
Any way out of this?
September 6, 2011 at 8:39 am
faling with what?
Log full?
Deadlock???
Tooth fairy out of cash because of economy??
September 6, 2011 at 8:45 am
I wish it was tooth fairy. Then I could blame someone else.
"Log File Full" is the correct answer to your question. It got full multiple times. Any way I can have the log file keep continuously purging itself?
Or any other suggestions of course.
Thanks much.
September 6, 2011 at 8:50 am
Timestamp datatype is deprecated.
September 6, 2011 at 8:53 am
Pagan DBA (9/6/2011)
I wish it was tooth fairy. Then I could blame someone else."Log File Full" is the correct answer to your question. It got full multiple times. Any way I can have the log file keep continuously purging itself?
Or any other suggestions of course.
Thanks much.
No because this is a single operation. It's all or nothing.
You could back it up just before to make as much room as possible but I assume you tried that.
Any way you could add another log file to a different drive while you perform this?
September 6, 2011 at 8:54 am
Cadavre (9/6/2011)
Timestamp datatype is deprecated.
Agreed but this is 2k5 and I don't see how it would solve the issue at hand ;-).
September 6, 2011 at 8:58 am
Ninja's_RGR'us (9/6/2011)
Cadavre (9/6/2011)
Timestamp datatype is deprecated.Agreed but this is 2k5 and I don't see how it would solve the issue at hand ;-).
It doesn't, just a warning that it's likely to be removed from a future version 🙂
Honestly, it sounds to me like the issue is due to massive amounts of operations on a database that has an insufficient log file.
September 6, 2011 at 9:01 am
Cadavre (9/6/2011)
Ninja's_RGR'us (9/6/2011)
Cadavre (9/6/2011)
Timestamp datatype is deprecated.Agreed but this is 2k5 and I don't see how it would solve the issue at hand ;-).
It doesn't, just a warning that it's likely to be removed from a future version 🙂
Honestly, it sounds to me like the issue is due to massive amounts of operations on a database that has an insufficient log file.
Well ya, that's what the OP said before you replied ;-).
September 6, 2011 at 9:11 am
What about adding the column with NULL? Would that work?
-- Gianluca Sartori
September 6, 2011 at 9:20 am
Gianluca Sartori (9/6/2011)
What about adding the column with NULL? Would that work?
How could it possibly work? the value is auto-generated & maintained by the server!
BEGIN TRAN
CREATE TABLE dbo.Boom (a int identity (1,1), b varchar(20))
INSERT INTO dbo.Boom (b) VALUES ( 'a')
INSERT INTO dbo.Boom (b) VALUES ( 'c')
INSERT INTO dbo.Boom (b) VALUES ( 'd')
INSERT INTO dbo.Boom (b) VALUES ( 'e')
SELECT * FROM dbo.Boom
ALTER TABLE dbo.Boom
ADD ts TIMESTAMP NULL
SELECT * FROM dbo.Boom
ROLLBACK
September 6, 2011 at 9:23 am
I know Timestamp is deprecated. We are using RowVersion.
Besides the point at this time, don't you think? The deprecation of the "syntax" has nothing to do with my problem. Unless you know something I don't. If so, please reply.
September 6, 2011 at 9:50 am
Pagan DBA (9/6/2011)
I know Timestamp is deprecated. We are using RowVersion.Besides the point at this time, don't you think? The deprecation of the "syntax" has nothing to do with my problem. Unless you know something I don't. If so, please reply.
*sigh*
Why's everyone so hostile today? It was just a friendly warning that using timestamp may come back to bite you a few years down the line.
Your log file is the problem as you know.
Can you perform the change outside of business hours? If so, create a new table with the new column included, then insert in batches, confirm that the two tables contain the same data, delete the old table and rename the new one.
--edit--
Yes, that is an ugly solution. But the alternative is to reduce your logging, which is worse imo.
September 6, 2011 at 9:54 am
Err, the entire point behind adding the column IS to have auditabel field. Not only does it not help to make it NULL, it should NOT be null.
Sorry, I am at a point I either don't understand your reply. If you could please elaborate?
September 6, 2011 at 9:59 am
Cadavre (9/6/2011)
Pagan DBA (9/6/2011)
Why's everyone so hostile today? It was just a friendly warning that using timestamp may come back to bite you a few years down the line.Your log file is the problem as you know.
Can you perform the change outside of business hours? If so, create a new table with the new column included, then insert in batches, confirm that the two tables contain the same data, delete the old table and rename the new one.
--edit--
Yes, that is an ugly solution. But the alternative is to reduce your logging, which is worse imo.
Okay. Ctrl+Alt+Delete. No one is being hostile. I should have clearly stated, "log file full" is my issue. And everyone knows that now. Everyone also knows - I hope - that restating the question does not help the person who asked the question in thefirst place 🙁
So now, OF COURSE I will run the script after hours in production. AGAIN, let's not state the obvious please. Let me be more specific. It takes 4 hours for me to run the ALTER script on ONE table. I have several tables. On top of that log gets full and the script aborts.
So I want to know if there is a way out of this or not? Maybe turn the logs off as long as no other changes are being made (after hours after all)? Or some other trick to have the log file purge itself? Anything?
September 6, 2011 at 10:01 am
Ninja's_RGR'us (9/6/2011)
Gianluca Sartori (9/6/2011)
What about adding the column with NULL? Would that work?How could it possibly work? the value is auto-generated & maintained by the server!
BEGIN TRANCREATE TABLE dbo.Boom (a int identity (1,1), b varchar(20))
INSERT INTO dbo.Boom (b) VALUES ( 'a')
INSERT INTO dbo.Boom (b) VALUES ( 'c')
INSERT INTO dbo.Boom (b) VALUES ( 'd')
INSERT INTO dbo.Boom (b) VALUES ( 'e')
SELECT * FROM dbo.Boom
ALTER TABLE dbo.Boom
ADD ts TIMESTAMP NULL
SELECT * FROM dbo.Boom
ROLLBACK
Ah! Thanks for the demo.
Learned something new.
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply