Problem with dbcc shrinkfile(datafile,targetsize)

Question

Post reply

Problem with dbcc shrinkfile(datafile,targetsize)

DBAsql-1046554

Ten Centuries

Points: 1088
More actions
November 9, 2010 at 2:42 am

#143338

My tempdb datafile stats is as follows.
current size is 32526 MB
space used is 76.69 MB
I had only one datafile tempdb.mdf
I issued dbcc shrinkfile(datafile,targetsize) to shrink the datafile. It neither returns any error nor it release the space.
Size of tempdb.mdf is 31.7 GB even after shrinking the datafile.
Can anyone help on this?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Feeg SSCrazy Points: 2766 More actions · Answer 1

why do you want to shrink the tempdb?

is the drive it is currently on running out of space? if this is the case move it to a larger drive ...

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

Adiga One Orange Chip Points: 27284 More actions · Answer 2

Check if any open transactions are there on tempdb. Once those transactions are complete the size will return to normalcy.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

DBAsql-1046554 Ten Centuries Points: 1088 More actions · Answer 3

This is in production environment. The file system is about to fill. Hence i am planning to shrink it.

Is there any other way than moving to a larger files system?

Thank You.

steveb. SSC-Forever Points: 46733 More actions · Answer 4

have a read of

http://support.microsoft.com/kb/307487#6"> http://support.microsoft.com/kb/307487#6

to shrink the tempDb without restarting the server you need to have no activity occuring on it.

DBAsql-1046554 Ten Centuries Points: 1088 More actions · Answer 5

This is in production environment. The file system is about to fill. Hence i am planning to shrink it.

Is there any other way than moving to a larger files system?

Thank You.

Adiga One Orange Chip Points: 27284 More actions · Answer 6

There are two options.

1) To add a new data file to tempdb on a drive which has enough space.

2) Kill the SPID which is currently active on it.

Since this server is production option 2 is not recommended and the SPID takes time to rollback depending on the work that it had done already.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

steveb. (11/9/2010)
have a read of
http://support.microsoft.com/kb/307487#6"> http://support.microsoft.com/kb/307487#6
to shrink the tempDb without restarting the server you need to have no activity occuring on it.

Did you read the article given by steve.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

DBAsql-1046554 Ten Centuries Points: 1088 More actions · Answer 8

I think i can wait until the current transaction completes.

Thank you all for your response.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 9

DBAsql-1046554 (11/9/2010)
I think i can wait until the current transaction completes.
Thank you all for your response.

Did you killed the transaction .

What is the status its shows?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

DBAsql-1046554 Ten Centuries Points: 1088 More actions · Answer 10

Since it is running in production environment i haven't killed the SPID.

Planning to wait until it completes.

Thank You.

DBAsql-1046554 Ten Centuries Points: 1088 More actions · Answer 11

There was no active transaction in the db. Even then space has not released and remains same.

Any suggestions?

Adiga One Orange Chip Points: 27284 More actions · Answer 12

Does these queries give you any output? If not, try shrinking tempdb now.

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

go

select * from sys.dm_tran_locks where resource_database_id=2

go

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

DBAsql-1046554 Ten Centuries Points: 1088 More actions · Answer 13

First query returns 1 row where as second does not return any.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 14

DBAsql-1046554 (11/9/2010)
There was no active transaction in the db. Even then space has not released and remains same.
Any suggestions?

Read this link

http://www.sqlservercentral.com/Forums/Topic615488-5-1.aspx

1.Never ever shrink the tempdb.

2.Try to change the path which have huge space.

3.Restart the sql server

Note: Downtime needed.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/