I have a log table which is currently holding millions of records. I want to enable partitioning on that table, so what I did for now is:

  1. Created a partition function and a partition scheme.
  2. Created an empty table with the same structure on that partition scheme.
  3. Copied data from the current log table from this point in time (let's call it T1) going backwards to the new partitioned table.

The next steps would be to copy the last remaining records from T1 to Tnow and rename the two tables so that the application starts to write to the new partitioned table.

Of course the log table is frequently accessed, so my question is:

How can I make sure that I don't lose any data during this process? Can i make it so that the users don't notice anything or do I necessarily need to stop the application for this brief time? Or can I just block that table so that the application keeps running? If so, how?

  • 1
    Is the log table frequently queried or just frequently inserted?
    – EzLo
    Commented Jul 19, 2018 at 15:03
  • 3
    What's the ballpark size of the table, 1M rows, 1B rows, GBs, TBs? That'll help drive the right answer as well (since the less downtime you want, the more work this is going to take.)
    – Brent Ozar
    Commented Jul 19, 2018 at 15:08
  • 1
    @EzLo only inserts and selects are performed againts that table. @BrentOzar 110 millions of records, 32GBs in total (13 GBs of data and 19 GBs of indexes) Commented Jul 19, 2018 at 15:49

2 Answers 2


Consider creating a new table with the partitioning strategy you want, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some terrible execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the terrible execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.

The more transparent you want this to be for your end users, the more work (and testing) it's going to take. This especially holds true if you're using partitioning: very often folks believe it's going to make all their queries faster, and yet some of them end up much slower. Try to test as much of your workload on a development server with the partitioned tables if you can.

  • Hi Brent, big fan. This sounds like a really clever approach, i never thought about triggers. However, 2 things are unclear to me. First, how do you make people use the view? Shouldn't that require a change in the application code? Second, wouldn't be an after trigger enough, without creating an additional view? Than you for your time. Commented Jul 19, 2018 at 16:11
  • 3
    @MattiaNocerino sp_rename was how I handled this recently. 1) create new table 2) rename base table 3) create view with name of old base table Commented Jul 19, 2018 at 16:39
  • 1
    Yep, bingo. Peter's comment is probably the piece you're missing, Mattia.
    – Brent Ozar
    Commented Jul 19, 2018 at 17:14
  • 2
    Select the row limit, waitfor time, & logging flag from a settings table, per batch, in your job. We just ramped the # of rows per pass up until we saw blocking then backed down a bit. We logged time/row/loop & found the sweet spot. 50k/0sec at night, 4.5k/0.1sec in the day. 10bil rows took like 9 days. Never stopped the job! :-)
    – Mark
    Commented Jul 19, 2018 at 18:49

You may want to consider a smaller logging table and a larger archive table to do the partitioning you spoke of:

Here at the hospital where I work we had the similar issue about 15 years ago. People were trying access the audit log for reporting while the audit logging was being locked by those reports. Therefore, we separated the reporting from the logging by chunking off the log to another database for reporting purposes. To avoid gaps in service we set a timer to chunk off portions of the data to the new table--100000 possible records at a time. In your case, once you get the data to your new table, you can partition how you like.

Using this strategy your original logging table will always remain small and your archive table will grow and be the one you want to partition. For us, it was an immense benefit to separate the current logging from the historical information. I guess you'll need to weigh that benefit out for yourself. But at least, you'd still be able to write to the original table without affecting the logging that's going on--when you export the data to the archive database/table.

The users who do historical reporting, in your case, may notice because they've got a new table to go to. But logging will continue on the original table unhindered.

Here's the code, my apologies, it's a bit crusty--where newaudit is the logging database and audit is the archiving database. (Of course you'd want to test this in a dev environment before any production go-lives):

Create proc [dbo].[spExportAuditLog]

    set nocount on
    declare @errorvar int,@max int, @min int, @watermark int, @batch int, @beforecounter int, @aftercounter int, @AuditCounter int, @NewAuditCounter int, @errortext varchar(255)
    select @beforecounter=0
    select @aftercounter=0
    select @NewAuditCounter = count(*) from newaudit.dbo.auditlog
    print 'NewAudit AuditLog Count Before'
    print @NewAuditCounter
    print 'Audit AuditLog Count Before'
    select @AuditCounter=count(*) from [TARGETSQLSERVER].audit.dbo.auditlog
    print @AuditCounter

    select top 1 @max=ID from newaudit.dbo.auditlog where  datediff( minute, datestamp,getdate()) <= 1440 order by ID

    select @min=min(ID) from newaudit.dbo.auditlog
    Set XACT_ABORT on
    select @batch =100000
    select @watermark=@min
    while (@watermark <=@max)
            begin transaction
            insert into [TARGETSQLSERVER].audit.dbo.auditlog(ApplicationName, MessageType, Message, Comments, UserID, CustomFields, Datestamp, ServerName, MRN)
            select ApplicationName, MessageType, Message, Comments, UserID, CustomFields, Datestamp, ServerName, MRN
                         from newaudit.dbo.auditlog where ID between @watermark and @watermark+@batch and ID <= @max order by id
                         if @@ERROR <> 0 select @errorvar=@errorvar+1
            delete from newaudit.dbo.auditlog where ID between @watermark and @watermark+@batch and ID <= @max
            if @@ERROR <> 0 select @errorvar=@errorvar+1
            if @errorvar > 0 
                select @errortext= 'ROLLBACK!! Problem with error IDs between:' + CONVERT(varchar(50), @watermark) + ' and ' +CONVERT(varchar(50), @watermark+@batch)
                print @errorText
                select @errorvar=0
            else commit
            select @watermark = @watermark+@batch +1
            waitfor delay '000:00:10'

    select @NewAuditCounter = count(*) from newaudit.dbo.auditlog
    print 'NewAudit AuditLog  Count After'
    print @NewAuditCounter
    print 'Audit AuditLog Count After'
    select @AuditCounter=count(*) from [TARGETSQLSERVER].audit.dbo.auditlog
    print @AuditCounter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.