Review issues we are having with DB server

Question

These are the issues:

• The log file partition fills up and we have to manually shrink the LSUASC DB log file every 2-3 months. We have a table for logging all web requests, exceptions, and some other informational things. We have a job that is supposed to clean up old logs, but the query fails due to log file space, so we end up manually truncating the table every now and then. The table currently has 2322230 rows and was last truncated on 2023-12-19 09:30:21.380. Would we benefit from an increased size for the log file partition or do we need a different backup plan?
• The SQL server agent does not appear to be running so our SQL jobs are not running so we manually run them as needed. What can we do to get the SQL server agent up and running again?
• We have need to create a new DB but are unsure if the current server can handle another DB since all DB's on that server share the same log file partition. We do not anticipate this new DB to have anywhere near as many daily write operations as the LSUASC DB. Do you think our current SQL server can handle another DB that would not have a significant amount of write or delete operations?

Answer 1

For the first issue, it seems that the log file partition is filling up and causing problems with the cleanup job. It may be beneficial to increase the size of the log file partition to prevent this issue from happening in the future. As for the backup plan, it is recommended to take regular backups of the database to prevent data loss and ensure recoverability.

For the second issue, it appears that the SQL Server Agent is not running, which is causing SQL jobs to not run as scheduled. To get the SQL Server Agent up and running again, you can try restarting the service and checking the service account permissions.

For the third issue, it is unclear if the current SQL server can handle another database without more information on the server's specifications and current workload. However, it is generally recommended to have separate log file partitions for each database to prevent issues like the one described in the first issue.

---

References:

• SQL Server transaction log architecture and management guide
• Maintain and troubleshoot BizTalk Server databases

Answer 2

Hi @Vanitha Dodla (MINDTREE LIMITED),

For the first question:

1. You need to investigate why is the log growing. Go read Factors That Can Delay Log Truncation. Follow the steps in the article to identify why is the log growing.
2. If the reasons is anything else but 'LOG_BACKUP', post an update with the reason you discovered and we can take give further advise.
3. If the reason is LOG_BACKUP then we can proceed. You have a database in non-SIMPLE recovery mode which is not being backed up correctly. You need to answer a question: Why is the database not in SIMPLE recovery mode? This is a business decision question so we cannot possibly know the answer.
4. If you don't know the answer to above or if you realize that SIMPLE recovery mode is acceptable, then we can do the quick fix. Change the recovery model to SIMPLE then run DBCC SHRINKFILE to shrink the log.
5. If you need a non-SIMPLE recovery model then you need to set up a proper log backup plan and start taking log backups. Read Transaction Log Backups and Use the Maintenance Plan Wizard. See See How to shrink the SQL Server Log to understand why you need to take repeated log backups until the SHRINKFILE is effective, due to the circular nature of the log.

For the second one:

Please check that you have schedule the job successfully.

Schedule a Job

For the last one:

Please chek Multiple Database in One Server Problems.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.