SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Minimize Transaction Log Size During Monster Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jun0
Starting Member

United Kingdom
32 Posts

Posted - 01/14/2013 :  09:19:33  Show Profile  Reply with Quote
Hi,

Im running an ALTER TABLE statement to change the data type of a column in a table that has hundreds of millions of rows.

I tried to run it twice already but the transaction log exceeded the size of the drive it was on (at the time there was 40GB free of a 214GB).

So I shrunk the transaction log, basically cleared it, so that there was 212GB free of the 214GB drive. However as the ALTER TABLE statement is running, the log is still increasing at a pretty rapid pace, I keep having to shrink it with a statement I'm running. I actually put the DB into the simple recovery model before I did this as I thought that this would mean that the log wasn't used at all, at least nothing would be stored in it, so it wouldn't be a problem.

So, I want to know why its still filling up? can i stiop it? or is it just filling up whilst the query runs and the transactions wouldn't be saved to the file once the query finished?

I still have the problem that it is probably going to fill up from this statement I'm running again, whats my best option to sovle this??

Thank you

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/14/2013 :  10:03:27  Show Profile  Reply with Quote
Simple recovery model does not mean that there will be no logging. It will still write to the log file, but will reclaim the log space at the end of the transaction. SQL Server has no choice but to do it that way because it needs to have the ability to rollback the transaction if need be.

If you cancel the statement, that will take time as well - because SQL Server will have to rollback the changes it already did.

How much time is required for the alter table statement depends on the type of altering you are doing. For example, if you are altering a VARCHAR(100) column to a VARCHAR(200) column, it should be almost instantaneous even for large tables, because SQL Server knows that there will be no data loss. On the otherhand, if you were changinging from VARCHAR(200) to VARCHAR(100), SQL Server will need to examine every row to see if there is data loss. Statements that require dropping and recreating the table would be even more expensive.

An alternative might be to add a column with the new data type, copy the data from the existing column to the new column (in small chunks), drop the existing column and rename the new column to the old name. If you have FK's that would complicate this.

So what is your alter statement doing - what is the current data type and what are you changing it to?

One other thing you said that concerns me: "However as the ALTER TABLE statement is running, the log is still increasing at a pretty rapid pace, I keep having to shrink it with a statement I'm running." I didn't quite get what you meant by this, but if you are trying to shrink the log file as the alter statement is running, that is a bad idea. In general, shrinking the log file under a normal workload is a bad idea; if the log file grew because of one-time operations (such as the one you are doing here) it is ok, but don't try to shrink it while the transaction is still open! An interesting link here: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 01/14/2013 :  11:00:31  Show Profile  Reply with Quote
My ALTER TABLE statment is changing the data type of a column from varchar(100) to int.

How can I tell how much disk space this will take up with the log file if it ever completes? is there a way i can run the statement so that it gives a % of how far off completion it is? or anything similar to that...

thanks
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/14/2013 :  11:11:52  Show Profile  Reply with Quote
Create table with new name with same columns and alter datatype for which you want to change.
Disable Constraints on old table
Copy data from old table to new table and make sure no of rows are same
rename old table to other name for backup and new to original name
enable constraints for it
You will need space for it.Do this in maintenance window.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/14/2013 :  11:13:17  Show Profile  Reply with Quote
I don't know of any documented functions or API's to monitor the progress of an open transaction.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 01/14/2013 :  11:24:03  Show Profile  Visit jackv's Homepage  Reply with Quote
One option you have is to : a) add another transaction log file to the database on another drive - this may give you the extra space you need .In effect , the first file will fill up and continue onto the next log file. Once the one-off query finishes - you can drop the extra log file

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 01/15/2013 :  07:19:05  Show Profile  Reply with Quote
The best solution would be creating another table with desired datatypes and use import/export wizard to have the data in new table. Drop the old table and rename the new table to that of original table. (make sure you have the required data before you drop old table)

Adding log files from another drives would also works but you never know how much log is it going to take.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/15/2013 :  08:05:59  Show Profile  Reply with Quote
Both Sodeep and Srimani are recommending recreating the entire table.

Wouldn't it be easier to recreate only the column as I had described in my post on 01/14/2013 : 10:03:27? Recreating only the column would require less disk space and other resources, would require fewer constraints to be dropped and recreated etc.

The only disadvantage that I can see to doing it the way that I was proposing is that the ordinal number of the column would change. But that should be transparent to the user (unless they are relying on the ordinal position in some queries, which if you ask me, deserves punishment).

I must be missing something - is there an advantage to recreating and repopulating the entire table instead of the just the column?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 01/15/2013 :  08:16:33  Show Profile  Visit jackv's Homepage  Reply with Quote
jun0 - have you tried adding another log file on a separate drive ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/15/2013 :  23:39:49  Show Profile  Reply with Quote
quote:
Originally posted by James K

Both Sodeep and Srimani are recommending recreating the entire table.

Wouldn't it be easier to recreate only the column as I had described in my post on 01/14/2013 : 10:03:27? Recreating only the column would require less disk space and other resources, would require fewer constraints to be dropped and recreated etc.

The only disadvantage that I can see to doing it the way that I was proposing is that the ordinal number of the column would change. But that should be transparent to the user (unless they are relying on the ordinal position in some queries, which if you ask me, deserves punishment).

I must be missing something - is there an advantage to recreating and repopulating the entire table instead of the just the column?



That would be the nice solution but i think as you mentioned the ordinal position would matter and they would have check where it breaks or work on whole set of queries due to this.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 01/16/2013 :  06:32:17  Show Profile  Reply with Quote
Thanks sodeep. I thought there was something else that I was overlooking; thanks for clearing that up.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000