Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Can't use DAC in SQL2K5.

SQL2K5
No SP.
Im trying to test out Dedicated Administrator Connection for my first time
and getting an error message:
____________________________
Cannot connect to admin:Server\InstanceName.
Additional information:
DAC's are not supported. (ObjectExplorer)
_________________________
The Browser service is running.
Any ideas?
TIA, ChrisRHi ChrisR
Is it a SQL Express instance?
From BOL: To conserve resources, SQL Server 2005 Express Edition does not
listen on the DAC port unless started with a trace flag 7806.
Is it a remote instance?
DAC must be enabled for remote connections from the Configuration
Manager.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:OLSsHeBjGHA.4748@.TK2MSFTNGP04.phx.gbl...
> SQL2K5
> No SP.
> Im trying to test out Dedicated Administrator Connection for my first time
> and getting an error message:
>
> ____________________________
> Cannot connect to admin:Server\InstanceName.
> Additional information:
> DAC's are not supported. (ObjectExplorer)
> _________________________
> The Browser service is running.
> Any ideas?
> TIA, ChrisR
>|||No ma'am.
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Also, it is on my local workstation.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e0gkxtCjGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi ChrisR
> Is it a SQL Express instance?
> From BOL: To conserve resources, SQL Server 2005 Express Edition does
not
> listen on the DAC port unless started with a trace flag 7806.
> Is it a remote instance?
> DAC must be enabled for remote connections from the Configuration
> Manager.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:OLSsHeBjGHA.4748@.TK2MSFTNGP04.phx.gbl...
time[vbcol=seagreen]
>|||Ok, I duplicated this. You can't use DAC to connect to the Object Explorer,
only when opening a query window.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%231B2YEEjGHA.1264@.TK2MSFTNGP05.phx.gbl...
> No ma'am.
> Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
> Oct 14 2005 00:33:37
> Copyright (c) 1988-2005 Microsoft Corporation
> Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack
> 4)
> Also, it is on my local workstation.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e0gkxtCjGHA.4512@.TK2MSFTNGP04.phx.gbl...
> not
> time
>|||To continue, your initial connection can't be DAC so first open the Mgmt
Studio without DAC, and then open a query window with a DAC.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%231B2YEEjGHA.1264@.TK2MSFTNGP05.phx.gbl...
> No ma'am.
> Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
> Oct 14 2005 00:33:37
> Copyright (c) 1988-2005 Microsoft Corporation
> Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack
> 4)
> Also, it is on my local workstation.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e0gkxtCjGHA.4512@.TK2MSFTNGP04.phx.gbl...
> not
> time
>|||Perfect, thanks alot Kalen!
Have a great weekend!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uzgZg$EjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> To continue, your initial connection can't be DAC so first open the Mgmt
> Studio without DAC, and then open a query window with a DAC.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:%231B2YEEjGHA.1264@.TK2MSFTNGP05.phx.gbl...
Pack[vbcol=seagreen]
does[vbcol=seagreen]
>

Can't use DAC in SQL2K5.

SQL2K5
No SP.
Im trying to test out Dedicated Administrator Connection for my first time
and getting an error message:
____________________________
Cannot connect to admin:Server\InstanceName.
Additional information:
DAC's are not supported. (ObjectExplorer)
_________________________
The Browser service is running.
Any ideas?
TIA, ChrisRHi ChrisR
Is it a SQL Express instance?
From BOL: To conserve resources, SQL Server 2005 Express Edition does not
listen on the DAC port unless started with a trace flag 7806.
Is it a remote instance?
DAC must be enabled for remote connections from the Configuration
Manager.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:OLSsHeBjGHA.4748@.TK2MSFTNGP04.phx.gbl...
> SQL2K5
> No SP.
> Im trying to test out Dedicated Administrator Connection for my first time
> and getting an error message:
>
> ____________________________
> Cannot connect to admin:Server\InstanceName.
> Additional information:
> DAC's are not supported. (ObjectExplorer)
> _________________________
> The Browser service is running.
> Any ideas?
> TIA, ChrisR
>|||No ma'am.
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Also, it is on my local workstation.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e0gkxtCjGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi ChrisR
> Is it a SQL Express instance?
> From BOL: To conserve resources, SQL Server 2005 Express Edition does
not
> listen on the DAC port unless started with a trace flag 7806.
> Is it a remote instance?
> DAC must be enabled for remote connections from the Configuration
> Manager.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:OLSsHeBjGHA.4748@.TK2MSFTNGP04.phx.gbl...
> > SQL2K5
> > No SP.
> >
> > Im trying to test out Dedicated Administrator Connection for my first
time
> > and getting an error message:
> >
> >
> > ____________________________
> > Cannot connect to admin:Server\InstanceName.
> >
> > Additional information:
> > DAC's are not supported. (ObjectExplorer)
> > _________________________
> >
> > The Browser service is running.
> >
> > Any ideas?
> >
> > TIA, ChrisR
> >
> >
>|||Ok, I duplicated this. You can't use DAC to connect to the Object Explorer,
only when opening a query window.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%231B2YEEjGHA.1264@.TK2MSFTNGP05.phx.gbl...
> No ma'am.
> Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
> Oct 14 2005 00:33:37
> Copyright (c) 1988-2005 Microsoft Corporation
> Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack
> 4)
> Also, it is on my local workstation.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e0gkxtCjGHA.4512@.TK2MSFTNGP04.phx.gbl...
>> Hi ChrisR
>> Is it a SQL Express instance?
>> From BOL: To conserve resources, SQL Server 2005 Express Edition does
> not
>> listen on the DAC port unless started with a trace flag 7806.
>> Is it a remote instance?
>> DAC must be enabled for remote connections from the Configuration
>> Manager.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "ChrisR" <ChrisR@.noEmail.com> wrote in message
>> news:OLSsHeBjGHA.4748@.TK2MSFTNGP04.phx.gbl...
>> > SQL2K5
>> > No SP.
>> >
>> > Im trying to test out Dedicated Administrator Connection for my first
> time
>> > and getting an error message:
>> >
>> >
>> > ____________________________
>> > Cannot connect to admin:Server\InstanceName.
>> >
>> > Additional information:
>> > DAC's are not supported. (ObjectExplorer)
>> > _________________________
>> >
>> > The Browser service is running.
>> >
>> > Any ideas?
>> >
>> > TIA, ChrisR
>> >
>> >
>>
>|||To continue, your initial connection can't be DAC so first open the Mgmt
Studio without DAC, and then open a query window with a DAC.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%231B2YEEjGHA.1264@.TK2MSFTNGP05.phx.gbl...
> No ma'am.
> Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
> Oct 14 2005 00:33:37
> Copyright (c) 1988-2005 Microsoft Corporation
> Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack
> 4)
> Also, it is on my local workstation.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e0gkxtCjGHA.4512@.TK2MSFTNGP04.phx.gbl...
>> Hi ChrisR
>> Is it a SQL Express instance?
>> From BOL: To conserve resources, SQL Server 2005 Express Edition does
> not
>> listen on the DAC port unless started with a trace flag 7806.
>> Is it a remote instance?
>> DAC must be enabled for remote connections from the Configuration
>> Manager.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "ChrisR" <ChrisR@.noEmail.com> wrote in message
>> news:OLSsHeBjGHA.4748@.TK2MSFTNGP04.phx.gbl...
>> > SQL2K5
>> > No SP.
>> >
>> > Im trying to test out Dedicated Administrator Connection for my first
> time
>> > and getting an error message:
>> >
>> >
>> > ____________________________
>> > Cannot connect to admin:Server\InstanceName.
>> >
>> > Additional information:
>> > DAC's are not supported. (ObjectExplorer)
>> > _________________________
>> >
>> > The Browser service is running.
>> >
>> > Any ideas?
>> >
>> > TIA, ChrisR
>> >
>> >
>>
>|||Perfect, thanks alot Kalen!
Have a great weekend!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uzgZg$EjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> To continue, your initial connection can't be DAC so first open the Mgmt
> Studio without DAC, and then open a query window with a DAC.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:%231B2YEEjGHA.1264@.TK2MSFTNGP05.phx.gbl...
> > No ma'am.
> >
> > Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
> >
> > Oct 14 2005 00:33:37
> >
> > Copyright (c) 1988-2005 Microsoft Corporation
> >
> > Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service
Pack
> > 4)
> >
> > Also, it is on my local workstation.
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:e0gkxtCjGHA.4512@.TK2MSFTNGP04.phx.gbl...
> >> Hi ChrisR
> >>
> >> Is it a SQL Express instance?
> >> From BOL: To conserve resources, SQL Server 2005 Express Edition
does
> > not
> >> listen on the DAC port unless started with a trace flag 7806.
> >>
> >> Is it a remote instance?
> >> DAC must be enabled for remote connections from the Configuration
> >> Manager.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> >> news:OLSsHeBjGHA.4748@.TK2MSFTNGP04.phx.gbl...
> >> > SQL2K5
> >> > No SP.
> >> >
> >> > Im trying to test out Dedicated Administrator Connection for my first
> > time
> >> > and getting an error message:
> >> >
> >> >
> >> > ____________________________
> >> > Cannot connect to admin:Server\InstanceName.
> >> >
> >> > Additional information:
> >> > DAC's are not supported. (ObjectExplorer)
> >> > _________________________
> >> >
> >> > The Browser service is running.
> >> >
> >> > Any ideas?
> >> >
> >> > TIA, ChrisR
> >> >
> >> >
> >>
> >>
> >
> >
>