| Author |
Topic |
|
Sharky
Starting Member
23 Posts |
 Posted - 2005-10-26 : 02:59:41
|
| Hi Everyonetempdb on one of our SQL servers has a number of 20GB files.When we run this:DBCC SHRINKFILE (<filename>, 1000)on one of the tempdb files, we get the following error:Server: Msg 8946, Level 16, State 1, Line 1Table error: Allocation page (6:35) has invalid IAM_PAGE page header values. Type is 1. Check type, object ID and page ID on the page.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Now - the shrink still happens and the file is 1GB after, if we increase the file size back to 20GB this also works fine.An idea that might fix the problem is to stop SQL server, delete the files and start SQL server again to recreate the tempdb files, but we want to know why we get the error, any ideas would be much appreciated.Leigh |
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2005-10-26 : 03:23:42
|
Hi,Tempdb it self get to its orginal size after you restart service you not need to Read BOL for following chapter :1). Expanding a Database2). Optimizing tempdb Performancequote:
tempdb tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.By default, tempdb autogrows as needed while SQL Server is running. Unlike other databases, however, it is reset to its initial size each time the database engine is started. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.
HTH quote:
-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com |
 |
|
|
Sharky
Starting Member
23 Posts |
Posted - 2005-10-26 : 03:42:21
|
| Thanks for the reply. I know that tempdb will revert to it's structure that you have defined depending on the files that you have specified. If one or more of these files are missing when SQL server starts, SQL Server will recreate them to the original spec that you specified when you set up tempdb. However - we want to know why we are getting the error, any ideas?ThanksLeigh |
|
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2005-10-26 : 04:17:10
|
| Hi,Refer to read following [url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2116[/url][url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9624[/url][url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3774[/url]HTH-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com |
|
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2005-10-26 : 04:20:47
|
quote:
Originally posted by activecrypt
Hi,Here is a solution / raeson WHY you are getting this error[url]http://support.microsoft.com/kb/278363/EN-US/[/url]Refer to read following [url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2116[/url][url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9624[/url][url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3774[/url]HTH-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com
[url][/url]-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com |
|
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2005-10-26 : 04:22:01
|
| Hi,one more question whats your sql server version and whats sp version ?!-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com |
|
|
|
Sharky
Starting Member
23 Posts |
Posted - 2005-10-26 : 04:28:23
|
| Thanks very much - I found that KB article as well, but we're running SQL 2000 SP 3a (I neglected to mention that in my original post) and that applies to SQL 7. Funny thing is that the article specifies that one gets the error when you run DBCC CHECKDB, however when I do - no errors! wierd... Thanks for the reading HTH, going to give it an eyeball. |
|
|
|
Sharky
Starting Member
23 Posts |
Posted - 2005-10-26 : 08:30:26
|
| Resolved, but i'm still not sure what caused the error.We stopped SQL server service, deleted the tempdb files, restarted SQL service, error no longer appears. Thanks very much for your help HTH. |
|
|
|
|
DBCC SHRINKFILE on a tempdb file