Log file growing

1

I am running my SQL agent Nightly Server Maintenance job. One of the step of this job is to perform a step called Integrity Check - User DBs. This job has been executing successfully for over 3 years. Starting this Saturday, when the integrity check step was executing.

Here is the code:

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'ChrismaFED',
@LogToTable = 'Y'

I run this every night.
Any suggestions is appricated.

Thanks
Mahmood

2

Just a couple of obvious questions that aren't meant as an insult but have to check...

  1. Have you ever done full backups on your database? The log file backup won't do a thing until you do.
  2. Does your nightly maintenance actually include backups?
  3. Does your nightly maintenance include index maintenance and you're using REORGANIZE?
  4. Do you have "Always On" running or anything else that uses the log file in such a manner?
3

Hi Jeff, thank you for getting back to me.

  1. Yes
  2. Yes
  3. Yes
  4. No. It is a SQL Server 2008 SP3. We are in a process of upgrading to 2019.

thanks
Mahmood

4

You should stop using REORGANIZE ( #3). That'll make the log file grow in a crazy manner and it sets up your indexes that you need a Fill Factor on for perpetual fragmentation.

As for it possibly being the stored procedure that you fun, We'll have to see that stored procedure to see if there might be something that we know about.