Sorry if this is in an FAQ somewhere, but I can't seem to find an answer.

I have SQL Server 2008 "Developer Ed." (one instance) installed on a
Win7 64bit machine. By default, it appears to place it's internal cache files
(ie *.cache) in the:

"C:\Users\{Sqladmin}\AppData\Local\temp\*"

directory. The instance itself is installed on a different local drive, and I have
another local scratch drive, as well as a 2 Terabyte Raid10 stripped & mirrored
RAID for the filegroups.

I tend to leave the instance running constantly, and thus, the directory fills
up and has almost crashed the system once or twice. I would like to alter
SQL Server to re-direct these cache files to a directory on the scratch volume,
where there is much more space, and at least the system won't be in danger
of crashing.

Is there any way to alter the drive/directory designation for these cache files?
I've searched through all the help and docs and cannot seem to find how to
do it, if at all possible... I'd be willing to re-install SQL Server if necessary.
It's only a developer ed.

Thanks so much in advance.

(zmpmag3-msql (AT) yahoo (DOT) com) writes:
You mean that in this directory there are a bunch of *.cache files?

I don't know what files this might be, but I'm fairly sure that they do not
come from the engine; I've never heard of such files.

Possibly they are generated by SQL Server Management Studio for
Intellisense, but I don't really see why.

Have you looked inside of these files to see what's in them?
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mon, 24 Jan 2011 08:48:59 +0000 (UTC), Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote:

Yes. They come named as follows:

"machine-name_instance.SQL-Server-name_{49268954-4FD4-4EB6-AA04-CD59D9BB5714}_#.cache"

where: "machine-name" is the server's Windows computer name;
"instance.SQL-Server-name" is the MS SQL Server 2008 server and instance name;
"#" is some unique 1-2 digit identifier.

In one case, the end # identifier is followed by "_QuerryActive.cache". The code between the
braces does change for some of the files but the one I quote above is common to most of them.
If you like, I can send you a JPEG screen print of the directory listing to your email address. It seems
the file with the lowest identifer (usually "_4") is the one that grows exponentially and causes the system
to crash. In the past, I have been able to delete the files directly without any ill effects on the engine.
However, they re-generate themselves automatically and keep growing. I suppose I could run a script
that deletes them periodically, but that's not the correct approach.

Not running Intellisense. I am, however, running SQL Server Agent. I have, temporarily, stopped
SQL Server Agent and we will see if that results in a halt to the file growth.

A mix of binary code and text based log information. A lot of "D O U B L E S P A C E D" text like that.
Again, I can send you a sample attachement to your e-mail if you like.

Thanks much.
____________________________________________
Regards,

Arnold

mag3 (zmpmag3-atnis (AT) yahoo (DOT) com) writes:
Does not ring a bell. For this reason I posted question to our internal
MVP forum. The only suggestion this far is that it may be related to
FILESTREAM, but he wasn't sure. Do you use FILESTREAM?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Tue, 25 Jan 2011 00:02:22 +0100, Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote:

Yes, but I have no active instances of it at present.

This definitely seems to be "SQL Server Agent" related. When I turn SQL Server Agent off,
the .cache files stop accumulating data. When I start it again, they start accumulating again.
I also tested this by leaving SSMS on/off as well, and that has no affect at all on the results.
It is the "_4.cache" file that seems to accumulate the most data. I will send you JPEG screens
of my SSMS home page (with SQL Server Agent tree items expanded), as well as the screen
of the */temp directory where these .cache files are accumulating. I'll send these via e-mail.
I would send you a copy of the "_4.cache" file but it's too big now, and contains too much P.I.I. :-)


Thanks much. I just want to be able to specify a different directory in which these .cache files
can accumulate.

(zmpmag3-msql (AT) yahoo (DOT) com) writes:
Maybe you should scrutinize what Agents job you have enabled?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 25 Jan 2011 08:27:27 +0000 (UTC), Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote:

Only those agents that were the "defaults" upon installation are running.
And I don't necessarily want to stop them. I just want to relocate these
cache files off my C: drive to keep it from filling and crashing the system.

Is there anyway to do this?

Thanks again for looking into it.

(zmpmag3-msql (AT) yahoo (DOT) com) writes:
If you have Agent jobs running, they have been added by someone. There are
no jobs installed by default when you install SQL Server. What jobs do you
have there?

It will probably work if you change the TEMP and TMP environment variables
for the SQL Service account. The easiest way is probably to log into Windows
as the service account for Agent, and then use Control Panel. You probably
have to restart SQL Server Agent.

Then again, the best for you is probably to stop these jobs from running.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 25 Jan 2011 13:09:48 +0000 (UTC), Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote:

This seems to have been successful. The files are now re-directed to where I specified.

I will review them accordingly. Thanks for your assistance.

(zmpmag3-msql (AT) yahoo (DOT) com) writes:
Looking at the screen shots you sent me, I realise that you are running
the Mangement Data Warehouse. It is probably not very interesting to run
on a development server.

When you configure the Data Collector, you are asked to specify a directory
for the cache files, and says that if you leave it empty it will use the
TEMP directory for the service account. I set up MDW and started the jobs,
and sure enough those .cache files started to turn up.

I guess that you once set up the MDW to see what it was, and then forgot
all about it. Go the Management node in Object Explorer, and select Data
Collector.

I have not been able to figure how you remove all the configuration,
but at least you can select "Stop Data Collection" from the context menu.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx