Performance Issue MSSQL 2005

[Former Member]

Hello Expert,

I have a quality system running on MS SQL Server 2005 & Netwever 7.02. I am experiencing a very poor performance recently & it was working fine couple of week ago.

I had some investigation and can see that the drive that hold the transaction log is left with space only 10 MB. it is also set to increase by 10%.

Will my system performance will temporary improve if I shrink the Transaction log file? What is the step involved? How much time will it take to shrink log of size around 57 GB?

Please help me if there is some other way to address the issue. Please let me know for any information required.

Regards

Suri

[Former Member · Accepted solution]

Hi Surymani Yadav,

It looks like that you database has "FULL" recovery model, you don't backup Transaction Log file.

You should schedule Transaction Log backup job and after several backup events you can shrink down the Transactin Log. It will improve performance.

[Former Member]

Thank you all for your suggestion. We have some major activities lined up where we will refer the suggestions

Now,We have taken the backup & shrunk the transaction log file. Now the log drive has more than 30 GB of free space. & i can see that the performance has improved significantly.

Regards

Suri

[luisdarui]

Hi Suri,

What metrics did you use before and after the transaction log shrink to compare the performance?

-> Are you looking at ST03N transaction, the DB times?

-> Are you using the DBAcockpit transaction -> Performance -> I/O Performance?

Can you share numbers before and after?

I'm asking because so far I have no cases where just shrinking a transaction log file will improve the database performance.

Best Regards,

Luis Darui

SAP Support

[Sriram2009]

Greeting Suri

1. You Sql Transaction log space is very less, so that you SAP system have performance issue.

Stop the SAP Instance

2. From MS Sql MMC or any backup software, start the full DB backup & then execute the MS Sql truncate log backup to clear the transaction log. Approximately 2 to 3 hrs to clear the 57 GB

Refer the Microsoft link - Back Up a Transaction Log (SQL Server)

3. Everyday once the Daily full DB backup completed then you can start the truncate log backup to clear the transaction log.

4. Star the SAP system check the free space on Log & DB using the transaction code ST04

Regards

SS

[luisdarui]

Hi Surymani,

The answer is: no, your system performance won't be improved if you shrink your log file. You have probably other bottlenecks. For this I suggest to check out some resources:

-> I/O specific performance issues:

-> Tune your SQL Server database:

-> SAP Knowledge Base Article:

1744217 - MSSQL: Improving the database performance

The fact that the DISK that hosts your transaction log is left with only 10MB may indicates that:

• your transaction log is getting filled too much faster;
• you may not be backing up your transaction log with regular frequency;
• maybe your application design is causing this: a custom code might be causing this.

If you backup your transaction log with high frequency, you shouldn't worry about this because it is most likely that your transaction log won't get full.

Incorrect transaction log file sizing combined with low frequency transaction log backup may lead to get -9002 errors or get your disk full.

How often do you take your transaction log backups? 30 min? 1 hour? 6 hours?