Unpinning the pinned tables

Hi,

Can anyone tell me whether the DROPCLEANSTATUS will unpin the pinned tables?Due to tye pinned table swe are facing space issues.
Plz respond as soon as possible

Regards,
SARAVANAN.G

Are you using SQL Server 2005? You cannot pin tables in it. DBCC PINTABLE runs but does not do anything.

In 2000 you need to run DBCC UNPINTABLE to unpin tables, I believe.

Hi Thomas,

Thanks for your reply.

Actually in SQL server 2005 also we could able to pin the tables.
Now only I got the script to check the pinned status of particular table.


IF OBJECTPROPERTY (object_id('table_name' ),'TableIsPinned') = 1 PRINT 'table_name is pinned'.

Once again I thank you for your concern.

Regards,
SARAVANAN.G

Hi,
Even AFAIK DBCC PINTABLE and DBCC UNPINTABLE has been discontinued from SQL 2005.

I tried this for a table called Role



dbcc unpintable(6,922486365)

SELECT OBJECTPROPERTY (object_id('Role' ),'TableIsPinned')



and this returned 0, i.e. the pintable had no effect.

Books Online states

"This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server."

Which I presume is correct.

Yes Thomas.You are right.

Regards,
SARAVANAN.G

Can you explain how this DBCC PINTABLE affects the inram space and letme know how to reduce the space took by that DBCC PINTABLE?
Whether DBCC UNPINTABLE will be effective?
I had used DBCC UNPINTABLE.But Some 'other' category memory was used instead of Inram memory.
So to free those space should we restart the server?

Regards,
SARAVANAN.G

dbcc pintable has no effect in SQL Server 2005. So that's it, you don't need to do anything. there is no other 'category' of memory other than RAM, as far as I know.

Do you have a performance problem? If your server is in an uncertain state, and you can restart it, I would, at least then you know where you are starting from.

Not sure why you continue asking? If you are using SQL Server 2005, the commands have no effect at all. If you include them in a script, they are ignored.

As Thomos mentioned many times and BOL clearly stats...it has been removed... why are you asking the same question again and again...<br /><br />Is your sql server reporting table as PINNED?<br /><br />Read the topic "DBCC PINTABLE & DBCC UNPINTABLE" for more details about PINTABLE...from the following article...<br /><br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms178015.aspx>http://msdn2.microsoft.com/en-us/library/ms178015.aspx</a><br /><br />I think it is better to lock this thread...<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br /><br />Mohammed U.

Hi Satya,

I am so sorry.We are using SQL server 2000.Thats why I am again asking about the DBCC PINTABLE and DBCC UNPUINTABLE commands.So Can you answer my last question?

Regards,
SARAVANAN.G

http://msdn2.microsoft.com/en-us/library/aa258284(SQL.80).aspx

http://www.rampant-books.com/t_hpsdba_89_pinning_tables_pintable.htm
http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-45-dba_Tbl_PinListedTables.htm


MohammedU.
Moderator
SQL-Server-Performance.com

Hi all,
Thank you verymuch for your replies.
I would like to explain the 'other' category which I mentioned in my previous posts(as enquired by Hemanth)
If we use DBCC MEMORYSTATUS,it will list a list buffer distribution and buffers.'other' is one among the buffer distribution.



Regards,
SARAVANAN.G

Hi Hemanth,
Thanks once again...

Regards,
SARAVANAN.G

Hi Satya,
This topic is for SQL server 2000 only.I am sorry for not mentioning the SQL server edition clearly while starting the discussion.

Regards,
SARAVANAN.G