Database server will not expand mdf or ndf files

**Craig Kelly** · Jul 23 '05, 07:58 AM

Re: Database server will not expand mdf or ndf files

"2centbob" wrote:
[color=blue]
> Has anyone had an issue with SQL Server not being able to expand
> against a RAID 5 file system? My current configuration is that the
> server is started and stopped using the local system account. I have
> only one database (besides the master, model,etc)on the server. What
> has happend to me several times is that the primary database in
> question try's to expand the main datafile for the database (.mdf). I
> setup the database to not expand automatically initially so that I can
> be sure that we have enough file system space. Becuase of problems with
> the application I decided to automatically expand. The other day the
> developers came to me indicating that the databse was full and needed
> to be expanded. Knowing that the database was in automatic expanding
> more I was surprise to hear this. I went into EM and attempted to
> expand first the log and it would not indicating that it there was an
> issue in attempting to do so. I have never heard of a database not
> being able to expand. I ran DBCC's, etc and it came up clean. I tried
> to back the database up to disk and it would not backup. I finally had
> to rename the datbase and rebuild it using DTS and scripts. I thought
> I had fixed it only to find out today that it (again) won't expand. I
> renamed the datbase and then tried taking an older backup file and
> restore it and it would not restore. This problem seems to be related
> to the file system but how I do not know.[/color]

<snip>

I don't know of issues specifically with RAID 5 (unless your RAID card has
gone bonkers), but here's a few guesses (mostly based on my trying to figure
out why the file system or something else would stop a file from expanding).

- Are you sure you have enough disk space? (I'm pretty that's not it and you
would have seen it, but better safe than sorry.) One place to look is
programs that might create huge temp files that eventually go away: we had a
server that ran multiple concurrent server processes. We had a heck of a
time figuring out why disk space seemingly came and went in huge chunks
until we realized that 3rd party code in our services was creating *huge*
temp files (because a few programmers didn't code for users requesting
reports with 4 million lines before control breaks :).

- Is your file system NTFS or FAT? Not being able to expand and then not
being able to backup or restore sounds fishy: could you be bumping into
FAT's file size limit? If I recall it's 4GB in FAT32 and 2GB in earlier FAT
versions.

- Are disk quotas enabled on the server? I've never even touched these in
Windows, so I have no idea where you would look... For that matter, does
your RAID hw/sw combo allow for any kind of quota?

- I'm pretty sure you already have, but in case you haven't, have you
checked the SQL Server logs and the OS event logs?

Good Luck,

Craig

**Bob Schmitz** · Jul 23 '05, 07:58 AM

Re: Database server will not expand mdf or ndf files

Thanks for your reply. In these cases its allways novce to have a
complete picture and that doesn't necessarly get conveyed sometimes.
So, a little more information is warrented. The application that uses
the database is a Java app sitting on a different server. The database
server has no application running on it. The application was written by
a vendor. Thier requirements require that the datbase owner have full
rights to the database, i.e., using sp_changedbowne r to that user. If I
did not use that approach then the application had problems upon
installation and therefore would not properly install. So, as I said I
changed it. Prior to this expereince the database was left to expand as
it needed and it did with no issues. The two circumstances that I
refered in my earlier email: the file system filled up and the database
could not expand. In addition, the server could not be reached and so
we had to shut it down hard. When it came back up we could not use the
database nor could we back it up. We were forced to rebuild the server:
OS and SQL Server. Later, a similar incident happened again and we were
forced (again) to rebuild. This last time, I had an additional 40 GB
added so we would not have a file system space problem again. I put the
database and log into a non-expansion mode so that when the application
would not accidently consume all of the disk space. However, the
database hit the high water mark on the datafile and could not expand.
I was notofied and of the problem and went to expand the file and it
would not expand again. No you most of the information.

At this point I am starting to think that as long the database has file
space to expand into and is not resitricted in any way the application
would probably work alright. However, because "sa" does not own the
database, the database owner probably needs "sa" rights. This is just
conjecture at this point. Funny thing, when this happend, the last
time, the "TaskPad" information came up with an error saying it could
not display the information and wanted to me to stop running the rest of
the script. I am concered that OS files are being walked on somehow.

Boy, I never had this expereince using Sybase and I have never seen
anyting like it in Oracle as well. But then again those were Unix
databases that I worked on, and not Windows.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Erland Sommarskog** · Jul 23 '05, 07:58 AM

Re: Database server will not expand mdf or ndf files

Bob Schmitz (bschmitz4@wi.r r.com) writes:[color=blue]
> Thanks for your reply. In these cases its allways novce to have a
> complete picture and that doesn't necessarly get conveyed sometimes.
> So, a little more information is warrented. The application that uses
> the database is a Java app sitting on a different server. The database
> server has no application running on it. The application was written by
> a vendor. Thier requirements require that the datbase owner have full
> rights to the database, i.e., using sp_changedbowne r to that user. If I
> did not use that approach then the application had problems upon
> installation and therefore would not properly install. So, as I said I
> changed it. Prior to this expereince the database was left to expand as
> it needed and it did with no issues. The two circumstances that I
> refered in my earlier email: the file system filled up and the database
> could not expand. In addition, the server could not be reached and so
> we had to shut it down hard. When it came back up we could not use the
> database nor could we back it up. We were forced to rebuild the server:
> OS and SQL Server. Later, a similar incident happened again and we were
> forced (again) to rebuild. This last time, I had an additional 40 GB
> added so we would not have a file system space problem again. I put the
> database and log into a non-expansion mode so that when the application
> would not accidently consume all of the disk space. However, the
> database hit the high water mark on the datafile and could not expand.
> I was notofied and of the problem and went to expand the file and it
> would not expand again. No you most of the information.[/color]

A lots of words, but, frankly, not very much information.

First of all, who owns the database does not matter. Auto-grow will
work anyway.

Since you seem to have difficulties to explain what is going on, I would
like you to run sp_helpdb on your database and post the output. That
will at least give some minimum of information for us to work from.

I would also like you do a DIR on the disks where the data and log files
reside, and post the bottom lines from that output.

In your previous message you said that you could not backup the database,
but you never explained why. Did you get an error message? Or how did
you conclude that the backup wasn't working?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**John Bell** · Jul 23 '05, 07:58 AM

Re: Database server will not expand mdf or ndf files

Hi

Even though you mention EM! Are you using MSDE?
Do you have disk quotas?
Are you using mount points?

It may help if you posted the version
http://www.aspfaq.com/show.asp?id=2160.

John

"Bob Schmitz" <bschmitz4@wi.r r.com> wrote in message
news:4204f360$1 _2@127.0.0.1...[color=blue]
> Thanks for your reply. In these cases its allways novce to have a
> complete picture and that doesn't necessarly get conveyed sometimes.
> So, a little more information is warrented. The application that uses
> the database is a Java app sitting on a different server. The database
> server has no application running on it. The application was written by
> a vendor. Thier requirements require that the datbase owner have full
> rights to the database, i.e., using sp_changedbowne r to that user. If I
> did not use that approach then the application had problems upon
> installation and therefore would not properly install. So, as I said I
> changed it. Prior to this expereince the database was left to expand as
> it needed and it did with no issues. The two circumstances that I
> refered in my earlier email: the file system filled up and the database
> could not expand. In addition, the server could not be reached and so
> we had to shut it down hard. When it came back up we could not use the
> database nor could we back it up. We were forced to rebuild the server:
> OS and SQL Server. Later, a similar incident happened again and we were
> forced (again) to rebuild. This last time, I had an additional 40 GB
> added so we would not have a file system space problem again. I put the
> database and log into a non-expansion mode so that when the application
> would not accidently consume all of the disk space. However, the
> database hit the high water mark on the datafile and could not expand.
> I was notofied and of the problem and went to expand the file and it
> would not expand again. No you most of the information.
>
> At this point I am starting to think that as long the database has file
> space to expand into and is not resitricted in any way the application
> would probably work alright. However, because "sa" does not own the
> database, the database owner probably needs "sa" rights. This is just
> conjecture at this point. Funny thing, when this happend, the last
> time, the "TaskPad" information came up with an error saying it could
> not display the information and wanted to me to stop running the rest of
> the script. I am concered that OS files are being walked on somehow.
>
> Boy, I never had this expereince using Sybase and I have never seen
> anyting like it in Oracle as well. But then again those were Unix
> databases that I worked on, and not Windows.
>
> Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

**Bob Schmitz** · Jul 23 '05, 07:59 AM

Re: Database server will not expand mdf or ndf files

Thats becuase this was a very difficult and weird situation. I knew
that I would not be able to explain it all and some would have
questions. sp_helpdb is not the problem becuase it shows the database.
There are no errors in the logs except when I try to backup or if i
tried to restore the database in question. When I ran a dir on the
filesystem the database files and there sizes show that they have
expanded but the databsae does no reflect this.

Now, what I have doen since then is to blow away the master, model,
msdb, and tempdb. I then ran the rebuild.exe program. That seems to
have fixed the problem as after I reattached the database I was able to
expand but log and data.

2centbob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Erland Sommarskog** · Jul 23 '05, 07:59 AM

Re: Database server will not expand mdf or ndf files

Bob Schmitz (bschmitz4@wi.r r.com) writes:[color=blue]
> Thats becuase this was a very difficult and weird situation. I knew
> that I would not be able to explain it all and some would have
> questions. sp_helpdb is not the problem becuase it shows the database.
> There are no errors in the logs except when I try to backup or if i
> tried to restore the database in question. When I ran a dir on the
> filesystem the database files and there sizes show that they have
> expanded but the databsae does no reflect this.
>
> Now, what I have doen since then is to blow away the master, model,
> msdb, and tempdb. I then ran the rebuild.exe program. That seems to
> have fixed the problem as after I reattached the database I was able to
> expand but log and data.[/color]

I strongly suspect that you put far more work into fix this that was
required.

However, since your choice is not to share the information I asked you
to, I'm afraid I can't help you with advice of what you should have done.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Bob Schmitz** · Jul 23 '05, 07:59 AM

Re: Database server will not expand mdf or ndf files

You can suspect all you want ... Unless you had been there working side
by side you don't know anything. Not only that, I resent your attitude
as though you know more than anyone else on this site. Please, in the
future, if you don’t have something say other than criticize someone,
please refrain from responding. I don't need it and suspect others
don't need it as well.

For others: The end users were screaming to have this system back and so
my time was limited in responding. THE ONLY THING THAT HAS WORKED HAS
BEEN TO REBUILD THE MASTER DATABASE. Be that as it may, it works now,
thanks to all that replied.

2centbob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Erland Sommarskog** · Jul 23 '05, 07:59 AM

Re: Database server will not expand mdf or ndf files

Bob Schmitz (bschmitz4@wi.r r.com) writes:[color=blue]
> You can suspect all you want ... Unless you had been there working side
> by side you don't know anything. Not only that, I resent your attitude
> as though you know more than anyone else on this site. Please, in the
> future, if you don't have something say other than criticize someone,
> please refrain from responding. I don't need it and suspect others
> don't need it as well.[/color]

You appeared to ask for help. And that's basically what I do here. Try
to help people. But often, I need more information about the case, so I
ask for that. It's true, that I have not been on your site, so I don't
know what happened. I have however been trying to find out, but you have
been very willing to give me the information that I have asked for. Of
course, you may do as you please. But you cannot really expect to get any
useful assistence that way.

And that is a piece of advice for the future when you have a need to
ask for help.
[color=blue]
> For others: The end users were screaming to have this system back and so
> my time was limited in responding.[/color]

It may be better in a situation like this to open a case with Microsoft
support. It's certainly more expensive than a free forum like this one.
Then again, if they can help to reduce downtime, you get the money back
that way. Of course, also the support engineers will ask you questions
about the configuration, error messages etc.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

Database server will not expand mdf or ndf files

Database server will not expand mdf or ndf files

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment