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?
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'
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.