April 28, 2010 at 4:42 am
Hi guys,
i have an urgent issue with both of my production sql clusters and i can't seem to find any answers for this anywhere
on thursday last week we began receiving error messages from 2 of our production clusters (hosted for us at a hosting centre) at the time i thought it was odd that both began behaving badly and therefore requested a list of all works done by the hosting company recently - none had been performed.
on further investigation i found that the data held about the database files was in some cases incorrect - for example
tempdb had sysfiles and sysaltfiles records that indicated the mdf file was 310 Mb (megabytes) set at 10% auto extend.
but the physical file size was 25GB (yes gigabytes)
what seemed to be happening was that sql was trying to expend the file by 31mb but was receiving 2.5 Gb and didn't know it. hence it kept requesting more and more - but a 2.5Gb file expand was killing the server (it's OLTP)
i ran a quick query and found lots of these occurences on both servers - ironically only of the data drive of each server (both of which clusters us the same SAN)
i shrank the tempdb file and all seemed well, the file sizes matched - but just to test i shrank it back to 1Mb... as soon as it started auto expanding it was allocating 8 times the size (approx) that is should have - and within minutes the file sizes were out of sync again.
anyone ever hit this problem before ? i'm not a san expert, but it seems that 2 clusters doing the same thing on the same drive ..... coincidence - i think not..!!!
MVDBA
April 28, 2010 at 6:42 am
Have you run consistency checks on the databases? I'd be quite concerned about that. Also, you could try DBCC UPDATEUSAGE. I don't think it'll help, but it's the first thing that came to mind. Consitency is still my bigger fear.
"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
April 28, 2010 at 7:19 am
if we shut the sql server down and re-start then tempdb is re-created - but the issue still occurs on every expand
it also applies to log files as well as data files
, so updateusage and check db are fairly reducndant to me....
MVDBA
April 28, 2010 at 7:22 am
Yeah, I figured updateusage was a total waste of time, but it was niggling at my head, so wrote it down.
If consistency is good.... it's the OS or the SAN or a combination of both. I'm not sure. I haven't run into this one. Sorry.
"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
April 28, 2010 at 7:29 am
Have you tried changing the growth to a set amount, say 30MB and seeing what that does? Then change it back to a %?
April 28, 2010 at 7:48 am
run dbcc on master.
Check to make sure that nothing else is trying to access the database files process monitor from sysinternals will tell you 100% who is accessing the files.
Check with the SAN guys that there isn't any issues with the cache or cache coherency modules if it is a multi-head SAN where the heads are redundant.
Make sure there isn't something like disk keeper, double take, polyserve or some other tool that copies database files while they are live.
If you backup one of the effected databases, other than tempdb, and restore it to a different server does the problem follow it?
April 28, 2010 at 8:08 am
update on the situation
after analysing the pattern of behaviour further it looks like the following
the file size is being incremented, but sql is not updating it's internal records.
so when tempdb becomes 90% full it asks for 10% more - you get 10% but sql does not update the sysfiles entry.... hmmm
as a result you continued requests for more space - ntfs obliges, but sql does not think it has
only happens on auto expand and we receive no error
MVDBA
April 28, 2010 at 8:10 am
I'd seriously think about calling MS support on this one.
April 28, 2010 at 8:13 am
Wesley Brown (4/28/2010)
I'd seriously think about calling MS support on this one.
Concur. That sounds like a serious problem.
"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
April 28, 2010 at 8:19 am
And let us know what you find out!
April 28, 2010 at 8:24 am
further update
if you right click on any database except tempdb
then choose properties
then choose file
then the column "initial size" will tell you the current database size
but with tempdb it tells you the "orginal size"
i can replicate this on several sql servers on different service packs
can anyone else confirm they have this behaviour?
MVDBA
April 28, 2010 at 8:28 am
Nope. I'm not seeing that.
"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
April 28, 2010 at 8:30 am
agreed ... a rdbms engine unable to properly manage space is a scary thing to face.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 28, 2010 at 9:03 am
i can see this on every dev machine in our office as well.... how odd.
i think sql 2008 must be using the figure in "initial size" to recreate tempdb back at it's smallest form in order to speed up startup where sql recreates tempdb........
the general screen shows the real size of the data, however the file screen show the "initial size"
in older versions of sql the file size shown was "as is" however it's shuffled around a bit - looks like it might be user error on our part here .....
but only on tempdb - on all other databases i'm finding initial size=current size
even worse documentation for sysaltfiles table shows "size=number of pages of the database" - this does not seem to apply to tempdb
MVDBA
April 28, 2010 at 9:08 am
if you set the size of tempdb it will default to that size on restart. It doesn't report the sizes properly in 2008 in some parts of the GUI you may actually see negative numbers. You can't shrink it smaller than the default startup size ether.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply