March 18, 2008 at 8:46 am
Hello,
I have a question about an Alter statement that is causing the log to fill up.
Here is my alter statement.
ALTER TABLE ym_car_inspect_detail
ALTER COLUMN inspect_result varchar(20) NULL
I am increasing the length of the column from char(10) to varchar(20). There are close to 3 million rows in the table.
I would appreciate all your help.
Thanks
March 19, 2008 at 11:31 am
Here is an example of what SQL Server will do when I tried to change the data type of c2 from INT to BIGINT. Basically, SQL Server replicates the table to be modified. That is the reason why you have a huge log file. That is the reason why managing "huge" data differs from "small" data.
****************
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_t3
(
c1 int NULL,
c2 bigint NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.t3)
EXEC('INSERT INTO dbo.Tmp_t3 (c1, c2)
SELECT c1, CONVERT(bigint, c2) FROM dbo.t3 TABLOCKX')
GO
DROP TABLE dbo.t3
GO
EXECUTE sp_rename N'dbo.Tmp_t3', N't3', 'OBJECT'
GO
COMMIT
March 19, 2008 at 11:56 am
SQL ORACLE explained what it did.
If you alter columns like that you should keep in mind if you are changeing the space needed to store the data.
- char(x) space needed is x
- varchar(x) space needed is a length indicator + used x
- int space needed is 4 bytes
- bigint space needed is 8 bytes
- ...
(see bol on data types)
An alter from varchar(10) to varchar(5000) would only imply a catalog update. !
Should make sence now 😉
So always keep in mind how sqlserver stores your column data and what it may need to do to change the existing data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 19, 2008 at 3:48 pm
So I should use char(20) in the Alter statement?
March 19, 2008 at 3:53 pm
Yasir Masood (3/19/2008)
So I should use char(20) in the Alter statement?
No, it won't make any difference.
The point of the prior posters was that SQL Server cannot just change the column in place. It has to 1) make a whole new table 2) copy all of the data from the old table to the new table, 3) drop the old table, and finally 4) rename the new table to the old table's name.
All of that takes a lot of log space, no matter what the old and new data-types are.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2008 at 12:58 am
char is a fixed length datatype that takes all the length as defined in the clause, no matter if you actualy fill it up or not, so that would not make a change in behaviour in your case.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 21, 2008 at 11:06 pm
That explains why it is filling up the log file. If somebody could suggest a solution, I would appreciate it.
Thanks
March 22, 2008 at 12:19 am
You are doing in right way by executing Alter table command.
My recommendation would be to put your alter table command in SQL Server Agent T-SQL Task job. You should also change recovery model to simple to reduce the amount logging SQL Server does.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 22, 2008 at 3:47 am
putting the recovery model to simple will not prevent it to grow.
The alter is performed in a single transaction so it will need the log space
to cover the whole operation.
However, you may want to shrink the logfile(s) back to their normal size after your whole operation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 22, 2008 at 9:56 am
Yasir Masood (3/21/2008)
That explains why it is filling up the log file. If somebody could suggest a solution, I would appreciate it.Thanks
There is not much that you can do to get around this problem: you are just going to have to find the space to let it run.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2008 at 12:18 pm
That explains why it is filling up the log file. If somebody could suggest a solution, I would appreciate it.
You can limit the impact and keep the log file under control by adding a new column.
1. ALTER TABLE myTable ADD NewColumn VARCHAR(20)
2. UPDATE myTable SET NewColumn = OldColumn
3. ALTER TABLE DROP COLUMN OldColumn
4. sp_rename 'myTable.NewColumn', 'OldColumn', 'COLUMN'
For step 2, do the updates in batches.
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
UPDATE TOP (50000) myTable WITH(TABLOCKX)
SET NewColumn = OldColumn WHERE NewColumn IS NULL
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
END
March 22, 2008 at 2:06 pm
I don't know, Todd. My recollection is that your step #3 (drop the old column) still has the same problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2008 at 6:43 pm
My recollection is that your step #3 (drop the old column) still has the same problem.
ALTER TABLE ... DROP COLUMN ... is a metadata only operation since it doesn't have to modify any data in the table. Are you thinking of deleting the data in the table? Because that's something that would be logged for each row.
March 22, 2008 at 7:26 pm
Wheter it is DDL or DML is irrelevant. It is a transacted operation and thus has to be recoverable. Physically it is modifying every row in the table, so it appears that it should be filling up the log file.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2008 at 7:29 am
It's logged, but it is logged differently. "ALTER TABLE tablename DROP COLUMN columnname" logs only the removal of rows from some system base tables, which store metadata. It doesn't log changes for every row in the base table.1
It's like comparing "TRUNCATE TABLE vs. DELETE", which we know are logged operations but log two different things.2
This demonstration trys to illustrate that.
-----------------------------------------
-- Effects of ALTER TABLE ... DROP COLUMN
-----------------------------------------
IF OBJECT_ID('table1', 'U') IS NOT NULL
DROP TABLE Table1
CREATE TABLE table1 (col1 INT IDENTITY(1,1), col2 CHAR(10))
GO
INSERT INTO Table1(col2) VALUES('abcde01234')
GO 10
CHECKPOINT
SELECT Operation, AllocUnitName FROM fn_dblog(NULL,NULL) WHERE operation LIKE '%DELETE%'
ALTER TABLE Table1 DROP COLUMN col2
SELECT Operation, AllocUnitName FROM fn_dblog(NULL,NULL) WHERE operation LIKE '%DELETE%'
-----------------------------------------
-- Effects of DELETE
-----------------------------------------
IF OBJECT_ID('table1', 'U') IS NOT NULL
DROP TABLE Table1
CREATE TABLE table1 (col1 INT IDENTITY(1,1), col2 CHAR(10))
GO
INSERT INTO Table1(col2) VALUES('abcde01234')
GO 10
CHECKPOINT
SELECT Operation, AllocUnitName FROM fn_dblog(NULL,NULL) WHERE operation LIKE '%DELETE%'
DELETE FROM Table1 WHERE 1=1
SELECT Operation, AllocUnitName FROM fn_dblog(NULL,NULL) WHERE operation LIKE '%DELETE%'
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply