VM / SQL Help please

[Davek0974]

HI all,

I seem to have an impending problem with one of my vm's, its running 2003sbs and SQL server database, its also our DC, DNS and AD controller.

The problem manifests itself as this - Every night we end the day on our invoicing system, this is a very large SQL transaction and generally runs without a hitch.

Previously, we might come in to find an error has occurred and it failed to end the day. Reboot the server and all was well again, this could be once in three-four months. Lately this has been happening more frequently, twice in the last month.

The errors are generally "Multiple step OLE error..." etc, and again, rebooting the particular VM image repairs it and we can end the day.

The increase in occurrence is worrying me now, nothing has changed on the server, no updates, the software is the same.

I am not sure how to diagnose network / database / SQL / VM / disk etc. so any help would be appreciated.

I am also not sure if this is time to create a new AD/DNS/DC/SQL VM with updated software now, obviously this would be expensive due to licensing & software etc so is not a great idea at present. The good thing is that it can be easily created on an isolated VM etc.

Any thoughts???

Dave

 

[uk_biz]

Hi

What is the server performance like at the time you receive errors?

Could be a memory leak but without taking a look, can't really say.

Do you have scheduled reboots in place?

 

[Paul_Rosser]

Sounds like an ActiveX issue but as uk_biz says it's impossible to diagnose properly without actually seeing the machine.

You mention an invoicing system, was this something you wrote ? or was it provided by a 3rd party ?

If you check the event log is there anything in there ?

 

[garyk]

You say its a *very* large transaction. Is it creating temp tables are you running 1 or more stored procs?

Reason for asking is it could either be the transaction log or the tempdb trying to expand in size and perhaps the VM preventing it from doing so. Only really test is to install an instance on a real box and run the scheduled job on that.

Dont know much about VM but know plenty about MSSQL.

Gary

 

[Paul_Rosser]

Forgot to ask, are you using a full version of SQL or SQL express ?

 

[Davek0974]

Hi

What is the server performance like at the time you receive errors?

Could be a memory leak but without taking a look, can't really say.

Do you have scheduled reboots in place?

Its unattended at that time but there are no errors relating to that that i can see.

Sounds like an ActiveX issue but as uk_biz says it's impossible to diagnose properly without actually seeing the machine.

You mention an invoicing system, was this something you wrote ? or was it provided by a 3rd party ?

If you check the event log is there anything in there ?

The software was written by me over many years, this part of the code remains unchanged for at least five years now. There is no ActiveX used, its basic VB/OLEDB stuff.

You say its a *very* large transaction. Is it creating temp tables are you running 1 or more stored procs?

Reason for asking is it could either be the transaction log or the tempdb trying to expand in size and perhaps the VM preventing it from doing so. Only really test is to install an instance on a real box and run the scheduled job on that.

Dont know much about VM but know plenty about MSSQL.

Gary

No stored procedures used, i will check the enterprise manager for info.

Forgot to ask, are you using a full version of SQL or SQL express ?

Its the full version of SQL7 so quite old now, 2004/5 i think.

I will look into the tempdb area and see if there is anything i can gain there. It used to do it in the physical world but only rarely, once or twice a year at most. Seems much worse in the virtual world.

 

[Paul_Rosser]

Sql 7! Ouch! Not really supported by VM as it's over 10 years old now.

I've managed to get sql 2000 working correctly with VM, but was a mission. Never tried sql 7, could you move sql onto it's own vm to see if the issue continues ?

Just trying to narrow it down as if it's running on the same vm as other services one of those could be causing the issue.

 

[garyk]

No stored procedures used, i will check the enterprise manager for info.

What about cursors/temp tables though? How often do you backup? The backup truncates the transaction log. You can also truncate it using a dbcc command via query analyser (just google it).

Gary

 

[MartCactus]

The multi step oLedb error I have seen many times... It's cryptic in so much as it doesn't really point to the cause of the problem.

In my experience it's always been due to a timeout as the query has become too complex to execute. That is why it did run fine but as db gets bigger there is more data to process so it take longer.

So remedies would entail one or more of following...

1) optimise queries to run more efficiently
2) other optimisation... Typically adding appropriate indexes
3) break query into chunks
4) increase timeout period to give query longer to run before error is declared
5) more horsepower for server

I'd approach them in this order. Whether you get far with 1 and 2 will depend on how well optimised db is at present.

 

[Davek0974]

Sql 7! Ouch! Not really supported by VM as it's over 10 years old now.

I've managed to get sql 2000 working correctly with VM, but was a mission. Never tried sql 7, could you move sql onto it's own vm to see if the issue continues ?

Just trying to narrow it down as if it's running on the same vm as other services one of those could be causing the issue.

Yes it is a bit old, hadn't thought about compatibility in the virtual world. I could move the SQL onto another VM as it would certainly reduce the workload on the DC/AD/DNS VM, but what O/S should we get? Also what version of SQL? I would need to be able to import our data as-it-is from old to new DB.

What about cursors/temp tables though? How often do you backup? The backup truncates the transaction log. You can also truncate it using a dbcc command via query analyser (just google it).

Gary

It is backed and optimised every night including index rebuilding but this all happens after the end-of-day routine.

The multi step oLedb error I have seen many times... It's cryptic in so much as it doesn't really point to the cause of the problem.

In my experience it's always been due to a timeout as the query has become too complex to execute. That is why it did run fine but as db gets bigger there is more data to process so it take longer.

So remedies would entail one or more of following...

1) optimise queries to run more efficiently
2) other optimisation... Typically adding appropriate indexes
3) break query into chunks
4) increase timeout period to give query longer to run before error is declared
5) more horsepower for server

I'd approach them in this order. Whether you get far with 1 and 2 will depend on how well optimised db is at present.

I have looked into the queries but as it all makes sense to me, i cant see how to make it better. All indexes are optimised precisely for the queries in use. I can't break it into chunks as it must be atomic - if any stage fails it must all roll back. I could increase the querytimeout period easily.

The only way to get more horsepower is to install an new VM with [insert option] operating system on it, and it could have a few CPU's and as much spare ram as i have.

Disk space is a problem here though as we are (allowing for snapshots) almost at capacity now because i P-to-V'd the 2003sbs unit with fixed size disks and it has a lot of them.

 

[garyk]

I have looked into the queries but as it all makes sense to me, i cant see how to make it better. All indexes are optimised precisely for the queries in use. I can't break it into chunks as it must be atomic - if any stage fails it must all roll back. I could increase the querytimeout period easily.

There's always room for improvement

You might want to upgrade to 2005, that comes with database tuning advisor which can actually suggest recommendations to any sql/tsql you are running.

Next try using an execution plan to see if there are any bottlenecks in the queries.

Google 'materialised views' I have used this many times to speed up queries. If you do go to 2005 this introduced common table expressions which can (but not always) improve performance.

Gary

 

[Paul_Rosser]

Yes it is a bit old, hadn't thought about compatibility in the virtual world. I could move the SQL onto another VM as it would certainly reduce the workload on the DC/AD/DNS VM, but what O/S should we get? Also what version of SQL? I would need to be able to import our data as-it-is from old to new DB.

What versions of SQL are you licenced for ?

 

[leemason]

As previously mentioned this could be down to a memory leak of some sort with something (possibly SQL itself) running on the server. Can you add more memory to the VM? I assume it's running on a 32-bit version of Windows so max memory would be 4GB.

 

[Davek0974]

What versions of SQL are you licenced for ?

SQL 7 only AFIK

As previously mentioned this could be down to a memory leak of some sort with something (possibly SQL itself) running on the server. Can you add more memory to the VM? I assume it's running on a 32-bit version of Windows so max memory would be 4GB.

Yes 32bit already at 4gb.

It used to run fine and as i said, the only thing that has changed is that it is now virtual, this was done some months ago now but it seems to be getting worse. Surely if it was a memory leak then it would always have done it??

I guess we do need to update it BUT i need to be really careful here as I certainly do not want to be re-coding my applications. Is there a SQL flavour that is VM savvy and will be easy to swap from old to new?? We use OLEDB ADO to communicate.

What OS would we require JUST to run a SQL database server???

Dave

 

[garyk]

I guess we do need to update it BUT i need to be really careful here as I certainly do not want to be re-coding my applications. Is there a SQL flavour that is VM savvy and will be easy to swap from old to new?? We use OLEDB ADO to communicate.

What OS would we require JUST to run a SQL database server???

Dave

You *could* run mysql on linux. The issue you are going to have (regardless of platform) is how much you have leveraged SQL server, transactsql, stored procs, triggers etc You'll find moving to another vendor quite painful unless you are simply issuing ANSI standard SQL but even then there are datatype issues.

You know you can get express (free) versions of SQL 2005,2008 and 2012?

Gary

 

[Davek0974]

You *could* run mysql on linux. The issue you are going to have (regardless of platform) is how much you have leveraged SQL server, transactsql, stored procs, triggers etc You'll find moving to another vendor quite painful unless you are simply issuing ANSI standard SQL but even then there are datatype issues.

You know you can get express (free) versions of SQL 2005,2008 and 2012?

Gary

Looks like I might be lucky...

I have used no triggers, no stored procedures, it's purely a large storage container for data. I use VB6 for my apps along with OLEDB ADO to connect, I also use a lot of reports written in Crystal Reports 10 and also MS Access.

Anything that will slot in painlessly to that scenario should be good.

 

[Paul_Rosser]

SQL 7 only AFIK

What OS would we require JUST to run a SQL database server???

Dave

You can run the express versions of 2005/2008 on Windows 7 or 2003/2008/2012 server. Express 2012 will only run on Windows 7 or server 2008/2012.

As they are free you can download and have a play to see if any of them will actually work correctly.

Surely if it was a memory leak then it would always have done it??

That could be an issue with VM.

 

[Davek0974]

I have a 2008r2 ISO so that will run for test purposes i guess plus one of the sql's mentioned.

As usual I am concerned about the costs involved in the dreaded licensing, we are still recovering from the license fees involved in setting up our rdp server, always seems to be the worst part.

Is Linux a viable alternative?

 

[Paul_Rosser]

Linux running with MySQL may work for you, however will depend on how your program works and if it will be happy.

You can down a LAMP (linux, apache, mysql, php) pre configured vmware server image for free to give it a go.

 

[leemason]

How did you virtualise the physical machine? Did you use a P2V utility or did you install the VM from scratch and simply migrate the data and code?

 

[MartCactus]

It used to run fine and as i said, the only thing that has changed is that it is now virtual,

Are you sure that is the only thing that changed?

What tends to happen over time is that the database gets larger. More data means more work, means more chance of a timeout or run out of memory.

As others suggest moving to later versions of SQL Server is a definite good move - MS has spent those years tweaking the code to be ever more efficient.

I wouldn't even consider MySQL as an option - its unlikely to offer better performance than SQL Server (we tend to encounter clients moving the other way). Oracle is probably the main choice for those wishing to move up from SQL Server, but that is serious money.

 

[Davek0974]

How did you virtualise the physical machine? Did you use a P2V utility or did you install the VM from scratch and simply migrate the data and code?

It was p2v'd as it is such a complex setup plus the fact that we do not have the 2003sbs disk anymore it was the only choice. We had to go through reactivation and re-licensing it after but it seems to be ok.

Are you sure that is the only thing that changed?

What tends to happen over time is that the database gets larger. More data means more work, means more chance of a timeout or run out of memory.

As others suggest moving to later versions of SQL Server is a definite good move - MS has spent those years tweaking the code to be ever more efficient.

I wouldn't even consider MySQL as an option - its unlikely to offer better performance than SQL Server (we tend to encounter clients moving the other way). Oracle is probably the main choice for those wishing to move up from SQL Server, but that is serious money.

yes of course it gets larger. What happens is that throughout the day, invoices are entered and added to a table that only holds data for that day, in the evening the end of day runs which reads that data one customer at a time, splits it up into various other tables, updates the stock, creates day logs, sales books etc and then saves the lot. Obviously this must all be atomic and I really can't see a way of easily splitting it up.

There is also the fact that it used to run well so there must be other forces at play. We re-index every night,the tempdb expands at will, there is plenty of disk space too.

If I knew for sure an update would fix it then of we would go for it but I have a feeling it's not that easy.

 

[garyk]

Just looked at your site Dave.

Tell you what let me have that little beauty of a steam engine of yours and I'll come onsite for a couple of days and sort it all out, deal?

Gary

 

[Davek0974]

Just looked at your site Dave.

Tell you what let me have that little beauty of a steam engine of yours and I'll come onsite for a couple of days and sort it all out, deal?

Gary

Lol

Unfortunately she has been sold now, anyway if I only get a couple of days onsite for it then you are definitely charging way too much