| Author |
Topic |
|
jun0
Starting Member
32 Posts |
 Posted - 2013-01-14 : 09:19:33
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-14 : 10:03:27
|
| 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/ |
 |
|
|
jun0
Starting Member
32 Posts |
Posted - 2013-01-14 : 11:00:31
|
| 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 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-14 : 11:11:52
|
| Create table with new name with same columns and alter datatype for which you want to change.Disable Constraints on old tableCopy data from old table to new table and make sure no of rows are samerename old table to other name for backup and new to original nameenable constraints for itYou will need space for it.Do this in maintenance window. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-14 : 11:13:17
|
| I don't know of any documented functions or API's to monitor the progress of an open transaction. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-01-14 : 11:24:03
|
| 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 fileJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-01-15 : 07:19:05
|
| 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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-15 : 08:05:59
|
| 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? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-01-15 : 08:16:33
|
| jun0 - have you tried adding another log file on a separate drive ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-15 : 23:39:49
|
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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-16 : 06:32:17
|
| Thanks sodeep. I thought there was something else that I was overlooking; thanks for clearing that up. |
|
|
|
|
Minimize Transaction Log Size During Monster Query