when I run the function fn_dblog to get some info from the SQL Server transaction log - I am after what is deleting something sometimes only - I have set up a server side trace for this now.

I thought I could see something related to my mysterious deleting from the transaction log, but they are backed up every 15 min...

anyway, while looking at the transaction log using the query below, I see this:

select * from fn_dblog (null, null)
where spid is not null
order by [begin time] desc

enter image description here

is it because the inserted rows did not fit in a page of the clustered index, and therefore had to be created somewhere else allocating a new page for them?

could this be an indication that the FILLFACTOR is too high?


I'd say that just reading the transaction log won't give you a good idea if the fillfactor is too high. Keep in mind that Page Splits are not always a bad thing, and they are necessary.

Your fill factor is set when the index is created and rebuilt, so I'd look at how often your indexes need to be rebuilt if you use Ola's maintenance solution it will track that for you. Use tools like Ola's Command Log table and judgement based on the size of the index, size of the table, type of key values to decide on your fill factor. You can usually set your fill factor to 100 and then re-evaluate if there are a lot of index rebuild commands in Command Log.

New pages created during Page Splits like in your example will not allocate empty space based on your fill factor, they will simply fill the pages as needed for the new data.

In general, a good clustered index key will always be incrementing, so every page split will be simply to allocate a new page at the end of the index. This changes a bit with varchar columns, since they can cause page splits during an update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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