July 2, 2014 at 11:26 pm
Hi,
I am not sure this is the correct forum for this, but it is 2008 backup related. Sort of.
I got an interesting email from a friend on the IT contracting side of things. It seems that his boss was having an issue with a piece of software that had SQL Server 2008 Express as its data store. This software was running fine for years but just recently the entire system started to freeze up solid for several minutes. This would occur several times a day.
So the boss called the support company and they said that it was time to upgrade to SQL Server 2012 (or 2014) standard edition. That would solve the problem. And BTW it will cost 5K for the upgrade.
Being a frugal businessman the boss asked my friend for advice on the correct sizing of the new instance just so that he could contain the costs of the upgrade a bit. As my friend is naturally suspicious about db related things he asked for my help.
I was granted guest access only onto the system so there was no way that I could run any of my favourite system views to get a handle on why this little instance was slowing down. I did have file system access and was also able to read the windows event logs. I took a look into the data directory where all the db related files are kept and I found that the transaction log for the application db was 400 gigs in size! And while I was gazing at the size of that log file I saw it jump up in size by 40 gigs. This was not good.
Looking at the windows event logs I saw several logs for autogrowth warnings taking longer than X milliseconds. The log messages were reporting a 2 minute autogrowth delay 2 weeks ago. Now it was over 5 minutes.
Since I had guest access to the sql instance I was able to determine that the db in question was set to the full recovery model and that the transaction log backups had never been taken!
I reported this and recommendations to fix the issue to my friend, his boss and the support company. The support company replied that this was not the cause of the slowdown and that they still recommended the upgrade.
Eventually the support company gave in and 'fixed' the issue by setting the recovery model to SIMPLE, but they still strongly recommended the upgrade.
The next day I got a phone call saying that the sql instance is slowing down again and that the support company wants to fix the issue for good by upgrading the sql version. Checking into it I found a single message in the sql logs:
"SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file". And the file in question was the same old 400 gb transaction log file, now only being 768 K in size.
My solution to this new issue would be to throw a couple of SSDs into the server and put the application db trans logs on one of the drives as the likelihood of the software vendor updating their db code to be more efficient is next to nil.
Hopefully the ride for this business owner is done.
OK... rant over.
Thanks,
j.a.c.
July 3, 2014 at 7:05 am
And... what's the question? Ha!
That would have made a great blog post.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2014 at 4:18 pm
j.a.c (7/2/2014)
It seems that his boss was having an issue with a piece of software that had SQL Server 2008 [font="Arial Black"]Express[/font] as its data store....
{snip}
...
I took a look into the data directory where all the db related files are kept and I found that the transaction log for the application db was [font="Arial Black"]400 gigs [/font]in size! And while I was gazing at the size of that log file I saw it jump up in size by [font="Arial Black"]40 gigs[/font]. This was not good.
I have to ask... how can that be? 2008 [font="Arial Black"]Express [/font]has a maximum database size of 10G (last I heard, could be wrong).
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2014 at 4:25 pm
I believe that the limitation is for data files and not log files.
j.a.c.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply