1

Working on SQL Server 2014.

I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been performing any transactions on the database for over a week. I asked the DBA if anyone else is using the database and they did not think so. The DBA mentioned that the automated index rebuild jobs could be filling up the log.

My question is this, can automated index rebuild jobs that run hourly fill up the log file? My assumption here is that the index would not be fragmented and so there would not be any work to rebuild the index. Therefor, the log file should not fill up. Is this assumption false? Am I missing something with how SQL Server index rebuilds work and how that might impact the log file?

Update

We are using Ola's solution: Click Here

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'\\DirectoryPath\',
@MirrorDirectory = N'\\DirectoryPath\',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 48,
@MirrorCleanupTime=24,
@CheckSum = 'Y',
@LogToTable = 'Y',
@Compress = 'Y'
  • 2
    My question is this, can automated index rebuild jobs that run hourly fill up the log file? --> Yes. Did index rebuilds happen in the database or not? If you are using Ola's solution then check the dbo.commandlog table if @LogtoTable = 'Y' (Or add it to the command). Is the database in the full recovery model and are you taking log backups? And running hourly index rebuilds seems a bit much to me, I would really look into scheduling this less often or even not doing it – Randi Vertongen May 22 at 16:58
  • @RandiVertongen Yes, we are using Ola's solution. Yes, we take log backups hourly. Note: the log backup job is failing due to an infrastructure issue. This has allowed me to catch the fact that the log file is filling up - unexpectedly I would say. – J Weezy May 22 at 17:00
  • Alright, if you have the @logtotable param set you should see the indexes that where rebuild / reorganized in that timeframe when querying the dbo.commandlog table – Randi Vertongen May 22 at 17:02
  • 1
    A more advanced approach is reading the contents of the log file. with the undocumented fn_dblog function. USE DbWithBigLog SELECT * FROM fn_dblog(NULL, NULL) and trying to see what kind of operations are in there. If nothing is happening the log should not be full. It is hard to say what exactly without more information. – Randi Vertongen May 22 at 17:59
  • 1
    @RandiVertongen I was unaware of this, thank you. In running it, the output looks like machine language, so I do not know how to read it. This is an expert concept I will need to learn. As luck would have it, the infrastructure issue has been resolved and the log file has been mostly cleared by the log backup. – J Weezy May 22 at 18:27
2

My question is this, can automated index rebuild jobs that run hourly fill up the log file?

Yes. An index rebuild typically rewrites all of the data contained in the index. I don't know of any exceptions, but there might be some. From the documentation:

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

You also asked:

My assumption here is that the index would not be fragmented and so there would not be any work to rebuild the index. Therefor, the log file should not fill up. Is this assumption false?

Fragmentation does not matter because a new copy of the data is created. You may be thinking of the REORGANIZE command. The amount of work performed by that command depends on fragmentation of the data, but it's done in small transactions.

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.