I have sql server 2008 and on one of it's database I have table caching. Now this database should run on another server. So I wonder if we export this database and import it on the new server, do I have to reconfigure table caching? Any ideas?

How did you enable table caching? I am asking because I am not familiar with any setting in SQL 2008 that would allow you to specify that a table be cached. There was a DBCC PINTABLE in old versions of SQL, but that has been deprecated in SQL 2008. Is there an equivalent in SQL 2008? Or is it some other command/setting that you are using?

Visakh, that has been my impression too. If a table is pinned in the buffer pool and the table continues to grow, it can have BAD consequences. I recall reading that, for this and other reasons, even though the command exists in SQL 2008, it does nothing at all. SQL Server takes your pintable requests, and silently throws into the garbage bin and makes funny faces at you for having made that request.

In any case, raysefo might be talking about something else completely different.

Visakh, that has been my impression too. If a table is pinned in the buffer pool and the table continues to grow, it can have BAD consequences. I recall reading that, for this and other reasons, even though the command exists in SQL 2008, it does nothing at all. SQL Server takes your pintable requests, and silently throws into the garbage bin and makes funny faces at you for having made that request.

In any case, raysefo might be talking about something else completely different.

I tried out the walk through described on this page: http://msdn.microsoft.com/en-us/library/e3w8402y(v=vs.90).aspx When you do that, it creates a trigger on the table of interest. In my case, the table is called MyTest and a trigger on it for INSERT, UPDATE, and DELETE was created with the name MyTest_AspNet_SqlCacheNotification_Trigger. (Also a bunch of tables and stored procs).

So, then the question is whether if you backed up this database and installed on a new server, would the caching setup automatically port and be useable? I don't know the answer, but my sense is that it won't. I would have liked to test that as well, but time constraints.

While my response is absolutely of no use to you, I learned something new. So thank you for asking the question. If you do find the right answer, would you post back so all can learn?