What version of SQL are you running?
~Charlie
_____
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Charlie Wilson
Sent: Wednesday, March 25, 2009 8:03 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] SQL TEMPDB
As a former SQL DBA let me shed some light on this situation.
To move tempdb there is a process you need to go through.
* Determine the logical file names of the tempdb database and their current
location on the disk.
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
Change the location of each file by using ALTER DATABASE
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO
* Stop and restart the instance of SQL Server.
* Verify the file change.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
* Delete the tempdb.mdf and templog.ldf files from the original location.
As far as shrinking goes there are ways to do this with out restarting the
server
http://support. <http://support.microsoft.com/kb/307487>
microsoft.com/kb/307487
Please refer to this doc. You could go as far as to create a maintenance
plan to run these commands.
~Charlie
_____
From: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
[mailto:vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com] On
Behalf Of
Vic Drecchio
Sent: Tuesday, March 24, 2009 5:01 PM
To: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
Subject: [Vantage] SQL TEMPDB
We had an issue today with our SQL TEMPDB going from 11GB to 64GB in the
matter of 2 hours. Later I found it was most likely due to a malformed
BAQ query a user was running. Since our TEMPDB is on the C: drive, it
came close to crashing our production server. I will be moving it to a
larger physical disk tonight.
Any SQL gurus out there? If so, is there any implication to restricting
the size of the TEMPDB? By default on most systems it's set to
"unrestricted" growth and the only way to "empty" the TEMPDB is via a
SQL restart.
Out of curiosity, what are your TEMPDB sizes?
Do me a favor and CC me directly (vic.drecchio@
<mailto:vic.drecchio%40timco.aero> timco.aero) so I get the
reply quicker than Yahoo's 3 hour propagation delay.
Thank you very much!
Vic
Vic Drecchio
ERP Administrator
TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@ <mailto:vic.drecchio%40timco.aero> timco.aero
Mobile: 704.530.3092
Office: 336.668.4410 x3159
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
~Charlie
_____
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Charlie Wilson
Sent: Wednesday, March 25, 2009 8:03 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] SQL TEMPDB
As a former SQL DBA let me shed some light on this situation.
To move tempdb there is a process you need to go through.
* Determine the logical file names of the tempdb database and their current
location on the disk.
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
Change the location of each file by using ALTER DATABASE
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO
* Stop and restart the instance of SQL Server.
* Verify the file change.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
* Delete the tempdb.mdf and templog.ldf files from the original location.
As far as shrinking goes there are ways to do this with out restarting the
server
http://support. <http://support.microsoft.com/kb/307487>
microsoft.com/kb/307487
Please refer to this doc. You could go as far as to create a maintenance
plan to run these commands.
~Charlie
_____
From: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
[mailto:vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com] On
Behalf Of
Vic Drecchio
Sent: Tuesday, March 24, 2009 5:01 PM
To: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
Subject: [Vantage] SQL TEMPDB
We had an issue today with our SQL TEMPDB going from 11GB to 64GB in the
matter of 2 hours. Later I found it was most likely due to a malformed
BAQ query a user was running. Since our TEMPDB is on the C: drive, it
came close to crashing our production server. I will be moving it to a
larger physical disk tonight.
Any SQL gurus out there? If so, is there any implication to restricting
the size of the TEMPDB? By default on most systems it's set to
"unrestricted" growth and the only way to "empty" the TEMPDB is via a
SQL restart.
Out of curiosity, what are your TEMPDB sizes?
Do me a favor and CC me directly (vic.drecchio@
<mailto:vic.drecchio%40timco.aero> timco.aero) so I get the
reply quicker than Yahoo's 3 hour propagation delay.
Thank you very much!
Vic
Vic Drecchio
ERP Administrator
TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@ <mailto:vic.drecchio%40timco.aero> timco.aero
Mobile: 704.530.3092
Office: 336.668.4410 x3159
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]