Today I have tried to execute an update operation on 50M+ rows, something like:
UPDATE [table] SET [Column] += 1;
after 30min of waiting I have seen that the log has started to filling itself, after another 30min the entire transaction rolled-back due to the log being full. DB was in the simple recovery mode but this is meaningless as this was done as a single transaction. SQL Server on it's own couldn't predict that it will use the entire log size (lets say that it was locked size for this matter), but my question to you my dear experienced DBA's - could you predict that size? Is there any way to calculate such an estimation?
UPDATE:
Yes, I understand the process, I know I can batch, I know how to make it performing well also. My real question is how big the log size is going to be? How would you calculate update on a simple table like:
CREATE TABLE [TABL] ([ID] int identity(1,1) PRIMARY KEY CLUSTERED, [X] int not null);
INSERT INTO [TABL] VALUES(1)
GO 1000
UPDATE [TABL]
SET [X] = 2;
On my test box, the update part of the operation was 6 pages large = 48KB. When I repeated the same for 10k rows I received 144 pages of transaction log. For 100k rows it was 1314 pages, for 1M it was 13970 pages. This shows that we can concider this as linear function (on the we have extra pages which has to be there anyway no matter did we update anything or not --> 2-3 pages).
Going back to the begeining I know that I could run a single batch on 5% of my total operation, check the page chenge content (as blogged by Paul Randal):
DECLARE @Extent_ID INT;
DECLARE @Size_Total BIGINT = 0;
DECLARE @File_ID INT;
DECLARE @File_Size_Pages INT;
DECLARE @Log_Page_ID INT;
DECLARE @Log_Total BIGINT = 0;
DECLARE @Log_Total_Changed BIGINT;
DECLARE @DBCC_PAGE_String_Log VARCHAR (200);
DECLARE [files] CURSOR FOR
SELECT [file_id], [size]
FROM master.sys.master_files
WHERE [type_desc] = N'ROWS'
AND [state_desc] = N'ONLINE'
AND [database_id] = DB_ID(@DB_Name);
OPEN files;
FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Extent_ID = 0;
SET @Size_Total += @File_Size_Pages / 8; --| Convert size in pages to to extents
WHILE (@Extent_ID < @File_Size_Pages)
BEGIN
SELECT @Log_Page_ID = @Extent_ID + 7;
SELECT @DBCC_PAGE_String_Log = 'DBCC PAGE ([' + @DB_Name + '], ' + CAST (@File_ID AS VARCHAR) + ', ' + CAST (@Log_Page_ID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';
TRUNCATE TABLE [msdb].[dbo].[DBCC_Page_Check];
INSERT INTO [msdb].[dbo].[DBCC_Page_Check] EXEC (@DBCC_PAGE_String_Log);
SELECT @Log_Total_Changed = SUM ([msdb].[dbo].[Extent_Change_Checks] ([Field]))
FROM [msdb].[dbo].[DBCC_Page_Check]
WHERE [VALUE] = ' MIN_LOGGED'
AND [ParentObject] LIKE 'ML_MAP%';
SET @Log_Total += @Log_Total_Changed;
SET @Extent_ID += 511232;
END
----------------------------------------------------------------------------------------------------------------------------
FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;
END;
DROP TABLE [msdb].[dbo].[DBCC_Page_Check];
CLOSE [files];
DEALLOCATE [files];
but this still requires to make a change (even with rollback) before we could estimate anything at all. So again - is there anyone who knows how to estimate it better than the way above - up front?