Preventing Deadlock: grow

Showing posts with label grow. Show all posts

Friday, March 9, 2012

'PRIMARY' filegroup is full (yet another)

Hello,
I am getting this error on the database msdb when trying to update a DTS
package.
When I look at msdb, it is set to automatically grow and autoshrink is off.
I changed the auto-grow on the MDF file to 10% but that didn't help.
I then tried changing the space allocated - on the Data Files tab in the
Properties menu after right-clicking the database in Enterprise Manager. As
soon as I hit "OK", the size changes back to the original (too small) value
and nothing has changed. What else could be wrong, and why can't I change
the size'
Thanks
LesTry changing the size using ALTER DATABASE and see if you get any error message. Also see if you
have autoshrink turned on for the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> Hello,
> I am getting this error on the database msdb when trying to update a DTS
> package.
> When I look at msdb, it is set to automatically grow and autoshrink is off.
> I changed the auto-grow on the MDF file to 10% but that didn't help.
> I then tried changing the space allocated - on the Data Files tab in the
> Properties menu after right-clicking the database in Enterprise Manager. As
> soon as I hit "OK", the size changes back to the original (too small) value
> and nothing has changed. What else could be wrong, and why can't I change
> the size'
> Thanks
> Les|||With an 'is it plugged in question' (because all your factors were within
SS)--is there enough room on the disk?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Tibor Karaszi" wrote:
> Try changing the size using ALTER DATABASE and see if you get any error message. Also see if you
> have autoshrink turned on for the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
> news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> > Hello,
> >
> > I am getting this error on the database msdb when trying to update a DTS
> > package.
> >
> > When I look at msdb, it is set to automatically grow and autoshrink is off.
> > I changed the auto-grow on the MDF file to 10% but that didn't help.
> >
> > I then tried changing the space allocated - on the Data Files tab in the
> > Properties menu after right-clicking the database in Enterprise Manager. As
> > soon as I hit "OK", the size changes back to the original (too small) value
> > and nothing has changed. What else could be wrong, and why can't I change
> > the size'
> >
> > Thanks
> > Les
>|||Hello,
I used the ALTER DATABASE command and it increased the PHYSICAL size of the
file on disk. However it had no effect on the "Space Allocated (MS)" figure
and the database still refuses to expand. Autoshrink os OFF and always has
been.
I tried increasing the MAXSIZE - this also had no effect. This is extremely
frustrating - the server is basically ignoring my instructions. Could it be
a security issue? (although I am getting no warning or error messages). The
owner of the database is sa.
There is 50GB of space left on the disk - no problem there.
We are now stuck in a situation where we cannot modify our DTS packages.
Any help appreciated.
Les
"Tibor Karaszi" wrote:
> Try changing the size using ALTER DATABASE and see if you get any error message. Also see if you
> have autoshrink turned on for the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
> news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> > Hello,
> >
> > I am getting this error on the database msdb when trying to update a DTS
> > package.
> >
> > When I look at msdb, it is set to automatically grow and autoshrink is off.
> > I changed the auto-grow on the MDF file to 10% but that didn't help.
> >
> > I then tried changing the space allocated - on the Data Files tab in the
> > Properties menu after right-clicking the database in Enterprise Manager. As
> > soon as I hit "OK", the size changes back to the original (too small) value
> > and nothing has changed. What else could be wrong, and why can't I change
> > the size'
> >
> > Thanks
> > Les
>|||I seemed to have got this to work by creating a second file to the PRIMARY
filegroup for the msdb database. However I would still be interested in any
theories as to why I couldn't get the single file to work.
I should mention we are having software RAID problems on this server - I
don't think they are a factor because I am getting no related error messages.
Les
"Les Russell" wrote:
> Hello,
> I used the ALTER DATABASE command and it increased the PHYSICAL size of the
> file on disk. However it had no effect on the "Space Allocated (MS)" figure
> and the database still refuses to expand. Autoshrink os OFF and always has
> been.
> I tried increasing the MAXSIZE - this also had no effect. This is extremely
> frustrating - the server is basically ignoring my instructions. Could it be
> a security issue? (although I am getting no warning or error messages). The
> owner of the database is sa.
> There is 50GB of space left on the disk - no problem there.
> We are now stuck in a situation where we cannot modify our DTS packages.
> Any help appreciated.
> Les
> "Tibor Karaszi" wrote:
> > Try changing the size using ALTER DATABASE and see if you get any error message. Also see if you
> > have autoshrink turned on for the database.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
> > news:4D994768-4737-4CA9-9817-BFE42C083317@.microsoft.com...
> > > Hello,
> > >
> > > I am getting this error on the database msdb when trying to update a DTS
> > > package.
> > >
> > > When I look at msdb, it is set to automatically grow and autoshrink is off.
> > > I changed the auto-grow on the MDF file to 10% but that didn't help.
> > >
> > > I then tried changing the space allocated - on the Data Files tab in the
> > > Properties menu after right-clicking the database in Enterprise Manager. As
> > > soon as I hit "OK", the size changes back to the original (too small) value
> > > and nothing has changed. What else could be wrong, and why can't I change
> > > the size'
> > >
> > > Thanks
> > > Les
> >
> >

'primary file was full' claimed by job when there is plenty space to grow

Hi,
Working on sql server 2000.
I have a job that pulls data from one server into this
server's database. It failed last time claiming the
primary file of the db was full while there were over 24
gig room to grow and the job will only pull in less than 2
gig data.
Then, I run it again serveral hours later, and everything
went through fine.
What happened? Ever encountered this wierd problem
yourself?
Many thanks.
JJCheck out below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
Tibor Karaszi
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:07ab01c3a258$9cf1a610$a301280a@.phx.gbl...
> Hi,
> Working on sql server 2000.
> I have a job that pulls data from one server into this
> server's database. It failed last time claiming the
> primary file of the db was full while there were over 24
> gig room to grow and the job will only pull in less than 2
> gig data.
> Then, I run it again serveral hours later, and everything
> went through fine.
> What happened? Ever encountered this wierd problem
> yourself?
> Many thanks.
> JJ

Primary file is full

We set the database to : automatically grow file, unstricted file growth, grow by 10%.
As I understand it will automatically grow, why we recieved error message like primary file full.Not enough disk to grow another ten percent?

-PatP|||Thanks, But we do have enough space.|||How big is the datafile now? And is it sitting on a volume that is formatted with FAT32 or NTFS?|||We set the database to : automatically grow file, unstricted file growth, grow by 10%.
As I understand it will automatically grow, why we recieved error message like primary file full.

How about, like, the actual error message...|||Thanks,

What I want to do now is to set an alert that when the database file is 90 % full, not log file, then send an email.

I didn't see there is an alert like this in the sql event alert, do I need to define it, can I do it in EM?

Also wondering when the system extended the database, when it's full or when it has some percent left?

Thanks|||Search on the term "autogrow" in Books Online, and you should find this in an article titled "Physical Database Files and Filegroups":

SQL Server 2000 files can grow automatically from their originally specified size. When you define a file, you can specify a growth increment. Each time the file fills, it increases its size by the growth increment. If there are multiple files in a filegroup, they do not autogrow until all the files are full. Growth then occurs using a round-robin algorithm.

Primary file & tan Log grow crazy!

HI:
I have a Table about 3GB in size. Whenever I try to add a column or set
a primary key to this table from the Enterprise Manager, the operation
would take a long time and the MDF and LOG file will just keep growing
crazy (more then 3GB) that it finanlly eats up all my hard disk space
and I have to kill the process.
So, how many HD space does it need to modify a 3GB Table (eg. add a
column). Actually what is the ratio? Is there any better way to modify
a large Table?
I have also enable the Auto-Shrink properties of the Table but still no
use.
Much appreciate for any advise.JJ wrote:
> HI:
> I have a Table about 3GB in size. Whenever I try to add a column or
> set a primary key to this table from the Enterprise Manager, the
> operation would take a long time and the MDF and LOG file will just
> keep growing crazy (more then 3GB) that it finanlly eats up all my
> hard disk space and I have to kill the process.
> So, how many HD space does it need to modify a 3GB Table (eg. add a
> column). Actually what is the ratio? Is there any better way to modify
> a large Table?
> I have also enable the Auto-Shrink properties of the Table but still
> no use.
> Much appreciate for any advise.
You should always make these types of changes using good old ALTER TABLE
commands from your query tool and avoid using SQL EM for these tasks.
While SQL EM seems like an easy alternative, depending on the operation,
SQL EM may actually be creating a copy of the table with the changes and
manually migrating the data. To see what SQL EM is doing you can either
watch the activity in Profiler or script out the changes in SQL EM.
Setting a PK in a table should not cause a recreate of the table, but if
you create the PK as clustered, then SQL Server has to reorg the entire
3GB table, which could be quite time consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use change scripts for that, EMs doing something a useless stuff around
the scripts. If you make your changes in a DDL scripts you should be
able to execute that much easier, even because the addition of a
primary key has no need to recreate the table as it is in the case of
an identity column.
HTH, Jens Suessmeyer.

Friday, March 9, 2012

'PRIMARY' filegroup is full (yet another)

'PRIMARY' filegroup is full (yet another)

'PRIMARY' filegroup is full (yet another)

'primary file was full' claimed by job when there is plenty space to grow

Primary file is full

Primary file & tan Log grow crazy!

Preventing Deadlock

Blog Archive

About Me