Full Backup interrupts client application

We are a 911 center, with a vertical dispatch application. This software is client - server application. We were on SQL 2k for many years with no Backup problems. After upgrade to OS 2k8 and SQL 2k5 just shortly after a Full Backup starts there is an 'interruption' in the client application using that database. This never happened with SQL 2k8. What should I be looking for? How can I identify exactly what is causing the interruption? When the interrupt happens it completely stops the application. It is a 'delay' that can be 30+ seconds long. About 29+ seconds to long for a 911 dispatcher. Any help would be most appreciated. Thank you.

gunneykNew Member

22 Jul 2014 06:18 PM

Backups should not cause any interruptions in the normal operation of the database itself. However if the backup files are being written to the same drives the data or log files are on you may get an I/O bottleneck. I don't see why that would have changed between versions though so I would check to see if any changes to the way the backups are performed or where they are written to. Does the CPU spike at that time? I have to say it is a little scary to hear such an important app is using 9 year old unsupported versions of SQL Server. One other thing, not that there are any backup related issues that I know of but if you are that far behind in SQL Server versions at least make sure you have the latest service pack and CU for it. One other thing might be to check memory utilization. If you were used to running SQL2000 and Win2003 and upgraded but kept the same hardware you may be lacking memory. What are the hardware specs and how exactly is the backup being done?

Data and Logs are on the same Virtualized Drive Backups are on a different 'drive' of the VM server.

No CPU spike. I did not know that SQL 2005 was no longer supported. The 'application' is a "sealed" vertical app. SQL does nothing but manage One Instance with Seven databases. It would likely run "as designed" for 100 years ... as long as hardware supported the SQL. Ver 10.50.4000.0 2k8 R2 SP2 - 9GB Ram - 2 Procs - VM - "Merom" Generation

The VM Statistics show that the server is Barely Bumping this VM.

ross1ycomNew Member

23 Jul 2014 09:51 AM

I am sorry. It is SQL 2008 R2 ... not SQL 2005. Thinking of a completely different app.

gunneykNew Member

23 Jul 2014 12:53 PM

OK SQL2008R2 is better but unfortunately it too just went out of mainstream support. However the issue is probably related to the VM configurations. First off even though the backups may be going to a different logical drive than the data it is likely just a partition off the same physical array underneath, especially if these are virtualized drives and not pass thru. So any traffic on one will potentially impact the other since they are really the one and the same physically. But the memory is a real killer here. I don't know what else is using that VM but 9GB is not a lot for the OS, SQL Server and what ever else is on that VM. This would be especially true if it also hosts the app itself. Do you have a MAX Memory value set for the SQL Server instance? If not you should definitely set it so that you always leave memory for the OS and any other apps that may need it. Otherwise you will get sloshing between SQL Server and the OS / apps for available memory. Again I don't know what you have running on that VM other than Windows and SQL Server but I would set the MAX Memory to leave 2GB at least for the OS.

I should have asked if this was 64 bit SQL Server or not. Hopefully it is so it can use the additional memory. But that is another very likely cause of the issues. If everything was 32 bit before SQL Server and the other apps were essentially limited as to what they could use. If now it is a 64 bit OS and apps they will potentially use much more and are more likely to collide as I mention in the previous post.