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 truncate a 99 GB transaction log need badly help

Hi,
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|UsedPages|Est imatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
Zorba
Hi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller
|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller
|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|Est imatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>
|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>

Can't truncate a 99 GB transaction log need badly help

Hi,
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|Use
dPages|EstimatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
ZorbaHi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|Use
dPages|EstimatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>

Can't truncate a 99 GB transaction log need badly help

Hi,
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
ZorbaHi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I am having hard time trying to truncate a 99 GB transaction log for a
>> database.
>> I already tried
>> CHECKPOINT
>> DBCC SHRINKFILE (emptydb_Log, 15000)
>> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
>> And I get this results:
>> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
>> 8 | 2 | 11601696| 63| 11601696| 56
>> From allocated 99 GB to this log in taskpad I can see that used space is
>> 13642.14 MB however none of the above work, even a full backup with
>> truncate log option selected.
>> Maintenance plan also can't do the job also.
>> Can someone explain to me how I can safely get rid of these free 76996 GB
>> from log file.
>>
>> I really appreciate your support, it is imperative to find a solution
>> ASAP as I run out of space.
>> Thank you,
>> Zorba
>>
>