|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > error 1101 problem with tempdb
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
error 1101 problem with tempdb
|
|
| Joshua Worthington 2002-11-28, 4:23 am |
| Hi there,
I am getting the following message :
Could not allocate new page for database 'TEMPDB'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
When I run a query that truncates a table and re-populates it from 2 other
tables (nothing to complicated that I am aware of) This has worked for many
months until recently. I have looked at Tempdb, and it has no file growth
restrictions that I can see.
Has Anyone had this before and knows of a quick answer?
Regards
Josh
| |
| Uri Dimant 2002-11-28, 5:23 am |
| Joshua
Move the database to other location
Joshua Worthington <joshuaworthington@hotmail.com> wrote in message
news:OBmt1WslCHA.1216@tkmsftngp02...
> Hi there,
>
> I am getting the following message :
>
> Could not allocate new page for database 'TEMPDB'. There are no more pages
> available in filegroup DEFAULT. Space can be created by dropping objects,
> adding additional files, or allowing file growth.
>
> When I run a query that truncates a table and re-populates it from 2 other
> tables (nothing to complicated that I am aware of) This has worked for
many
> months until recently. I have looked at Tempdb, and it has no file growth
> restrictions that I can see.
>
> Has Anyone had this before and knows of a quick answer?
>
> Regards
>
> Josh
>
>
| |
| Joshua Worthington 2002-11-28, 6:23 am |
| How do you mean? move Tempdb to another server? can you be more specific.
These are the current properties:
Size: 307MB
Space available: 304.27MB
Database options: normal
Number of users 3
Thanks
Josh
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:e6PjzfslCHA.2840@tkmsftngp04...
> Joshua
> Move the database to other location
>
> Joshua Worthington <joshuaworthington@hotmail.com> wrote in message
> news:OBmt1WslCHA.1216@tkmsftngp02...
> > Hi there,
> >
> > I am getting the following message :
> >
> > Could not allocate new page for database 'TEMPDB'. There are no more
pages
> > available in filegroup DEFAULT. Space can be created by dropping
objects,
> > adding additional files, or allowing file growth.
> >
> > When I run a query that truncates a table and re-populates it from 2
other
> > tables (nothing to complicated that I am aware of) This has worked for
> many
> > months until recently. I have looked at Tempdb, and it has no file
growth
> > restrictions that I can see.
> >
> > Has Anyone had this before and knows of a quick answer?
> >
> > Regards
> >
> > Josh
> >
> >
>
>
| |
| Uri Dimant 2002-11-28, 6:23 am |
| Joshua,
What version odf sql server are you using?
For what it is worth, yes running out of space in tempdb when creating
an object can cause corruption to tempdb. The same with any database. The
problem is that the system tables can be out of synch after such a problem.
This is the 'corruption' that you experience.
You should prevent tempdb from ever running out of space. If it does,
you will likely need to shut down and restart the SQL Server service. (Not
necessarily the machine.) There are ways to fiddle with and clean our the
system tables, but they are not recommended.
If you run out of space while inserting rows into a temp table the
impact is not quite as severe.
Consider to move tempdb (special database) to other physical disk array
Joshua Worthington <joshuaworthington@hotmail.com> wrote in message
news:#ZokDNtlCHA.2800@tkmsftngp04...
> How do you mean? move Tempdb to another server? can you be more specific.
> These are the current properties:
>
> Size: 307MB
> Space available: 304.27MB
> Database options: normal
> Number of users 3
>
> Thanks
>
> Josh
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:e6PjzfslCHA.2840@tkmsftngp04...
> > Joshua
> > Move the database to other location
> >
> > Joshua Worthington <joshuaworthington@hotmail.com> wrote in message
> > news:OBmt1WslCHA.1216@tkmsftngp02...
> > > Hi there,
> > >
> > > I am getting the following message :
> > >
> > > Could not allocate new page for database 'TEMPDB'. There are no more
> pages
> > > available in filegroup DEFAULT. Space can be created by dropping
> objects,
> > > adding additional files, or allowing file growth.
> > >
> > > When I run a query that truncates a table and re-populates it from 2
> other
> > > tables (nothing to complicated that I am aware of) This has worked for
> > many
> > > months until recently. I have looked at Tempdb, and it has no file
> growth
> > > restrictions that I can see.
> > >
> > > Has Anyone had this before and knows of a quick answer?
> > >
> > > Regards
> > >
> > > Josh
> > >
> > >
> >
> >
>
>
| |
| Allan Mitchell 2002-11-28, 8:23 am |
| Have a look at your disk space. In particular look at the drivespace for
the drive on which SQL Server is installed
EXEC master..xp_fixeddrives (or Explorer of course)
--
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joshua Worthington" <joshuaworthington@hotmail.com> wrote in message
news:OBmt1WslCHA.1216@tkmsftngp02...
> Hi there,
>
> I am getting the following message :
>
> Could not allocate new page for database 'TEMPDB'. There are no more pages
> available in filegroup DEFAULT. Space can be created by dropping objects,
> adding additional files, or allowing file growth.
>
> When I run a query that truncates a table and re-populates it from 2 other
> tables (nothing to complicated that I am aware of) This has worked for
many
> months until recently. I have looked at Tempdb, and it has no file growth
> restrictions that I can see.
>
> Has Anyone had this before and knows of a quick answer?
>
> Regards
>
> Josh
>
>
| |
| Joshua Worthington 2002-11-28, 8:23 am |
| I had installed SQL server 7 with the default settings, which sets the
tempdb properties to grow by 10% when needed. The drive the database is
installed on has 56GB free space which should be ample (the main application
db is around this size), so I am concerned that it could have hit a size
limit.
Thanks for your help on this, I may restart the SQL Service soon depending
on how things go.
Regards
Josh
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:OHPjvXtlCHA.1244@tkmsftngp02...
> Joshua,
> What version odf sql server are you using?
>
> For what it is worth, yes running out of space in tempdb when creating
> an object can cause corruption to tempdb. The same with any database.
The
> problem is that the system tables can be out of synch after such a
problem.
> This is the 'corruption' that you experience.
> You should prevent tempdb from ever running out of space. If it does,
> you will likely need to shut down and restart the SQL Server service.
(Not
> necessarily the machine.) There are ways to fiddle with and clean our
the
> system tables, but they are not recommended.
> If you run out of space while inserting rows into a temp table the
> impact is not quite as severe.
> Consider to move tempdb (special database) to other physical disk array
>
>
> Joshua Worthington <joshuaworthington@hotmail.com> wrote in message
> news:#ZokDNtlCHA.2800@tkmsftngp04...
> > How do you mean? move Tempdb to another server? can you be more
specific.
> > These are the current properties:
> >
> > Size: 307MB
> > Space available: 304.27MB
> > Database options: normal
> > Number of users 3
> >
> > Thanks
> >
> > Josh
> >
> > "Uri Dimant" <urid@iscar.co.il> wrote in message
> > news:e6PjzfslCHA.2840@tkmsftngp04...
> > > Joshua
> > > Move the database to other location
> > >
> > > Joshua Worthington <joshuaworthington@hotmail.com> wrote in message
> > > news:OBmt1WslCHA.1216@tkmsftngp02...
> > > > Hi there,
> > > >
> > > > I am getting the following message :
> > > >
> > > > Could not allocate new page for database 'TEMPDB'. There are no more
> > pages
> > > > available in filegroup DEFAULT. Space can be created by dropping
> > objects,
> > > > adding additional files, or allowing file growth.
> > > >
> > > > When I run a query that truncates a table and re-populates it from 2
> > other
> > > > tables (nothing to complicated that I am aware of) This has worked
for[c
olor=darkred]
> > > many
> > > > months until recently. I have looked at Tempdb, and it has no file
> > growth
> > > > restrictions that I can see.
> > > >
> > > > Has Anyone had this before and knows of a quick answer?
> > > >
> > > > Regards
> > > >
> > > > Josh
> > > >
> > > >
> > >
> > >
> >
> >
>
>[/color]
|
|
|
|
|