Very Computer

My tempdb has the allocation errors

(Extent xxx in database ID 2 is marked allocated in the
GAM, but no SGAM or IAM has allocated it)

I moved tempdb to another drive and SQL created it from
the scratch, but it still has the same errors.

In order to fix the database, I need to put it into single-
user mode, but I cannot do this for tempdb.

What should I do?

Thanks,

-Stan

If you specified a new drive for tempdb and it actually has moved, then you have some problem.
You could always try to stop SQL Server, delete tempdb files and start again. You now know that
you get new fresh files. (Do a backup first, of course.)

If problems are re-introduced then you either have some bug in SQL Server (MS Support) or HW
problems.

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer

Stan,

Moving it should not be necessary since it is created from scratch eachtime
you restart sql server.  When do you get these errors?  Is tempdb
accessible?  Only thing I can think of off hand is to attempt to shrink
tempdb down to it's smallest size and see if that helps.  If not then I
would give MS PSS a call.

http://support.Microsoft.com/default.aspx?scid=fh;EN-US;sql    SQL Support
http://www.mssqlserver.com/faq/general-pss.asp   MS PSS

--

Andrew J. Kelly
SQL Server MVP

Quote:> My tempdb has the allocation errors

> (Extent xxx in database ID 2 is marked allocated in the
> GAM, but no SGAM or IAM has allocated it)

> I moved tempdb to another drive and SQL created it from
> the scratch, but it still has the same errors.

> In order to fix the database, I need to put it into single-
> user mode, but I cannot do this for tempdb.

> What should I do?

> Thanks,

> -Stan

Hi Stan.

The easy way is simply to stop / start SQL Server if you can afford to
because tempdb is rebuilt upon each restart of SQL Server.

Regards,
Greg Linwood

Quote:> My tempdb has the allocation errors

> (Extent xxx in database ID 2 is marked allocated in the
> GAM, but no SGAM or IAM has allocated it)

> I moved tempdb to another drive and SQL created it from
> the scratch, but it still has the same errors.

> In order to fix the database, I need to put it into single-
> user mode, but I cannot do this for tempdb.

> What should I do?

> Thanks,

> -Stan

Andrew,

I get these errors when I run DBCC. Specifically,
"Extent (1:473160) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it."

I shrunk it, but still have 3532 allocation errors....

Quote:>-----Original Message-----
>Stan,

>Moving it should not be necessary since it is created

from scratch eachtime

Quote:>you restart sql server.  When do you get these errors?  
Is tempdb
>accessible?  Only thing I can think of off hand is to
attempt to shrink
>tempdb down to it's smallest size and see if that helps.  
If not then I
>would give MS PSS a call.

>http://support.Microsoft.com/default.aspx?scid=fh;EN-

US;sql    SQL Support

If you stop and restart sql server and the error is still there I would call
MS PSS.  I have never seen a situation like this where a restart didn't fix
the problem with tempdb.

--

Andrew J. Kelly
SQL Server MVP