Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004sql
Showing posts with label tempdb. Show all posts
Showing posts with label tempdb. Show all posts
Wednesday, March 28, 2012
Multi-table joins & tempdb growth / query performance
Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ? Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time?
Or does SQL do this automatically?
Thanks
Sean
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.c om...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ? Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time?
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ? Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time?
Or does SQL do this automatically?
Thanks
Sean
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.c om...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ? Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time?
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Multi-table joins & tempdb growth / query performance
Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Monday, March 12, 2012
multiple tempdb references in master sysaltfiles
Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
....
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
Rusty
I've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!
|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John
|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.
|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John
|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
....
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
Rusty
I've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!
|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John
|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.
|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John
|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.
multiple tempdb references in master sysaltfiles
Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to d
o
> with it!
> What command have you used to create this database? It seems to me that yo
u
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locatio
ns
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted
!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to d
o
> with it!
> What command have you used to create this database? It seems to me that yo
u
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locatio
ns
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted
!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.
multiple tempdb references in master sysaltfiles
Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
> > Hi,
> > We tried to create a new database on an application server (Win 2003
> > server/SQL Server 2003) and got the following error.
> > error 945...
> > ....
> > device activation error. The physical filename g:\mssqldata
> > \templog.ldf may be incorrect.
> > The problem is that tempdb is on f:\mssql as shown in the database
> > properties and with sp_dbhelp.
> > Poking around in master the real wierdness comes through. sysaltfiles
> > has 2 entries each for tempdb logs and data files. One of them is on
> > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > g: (actually the last two rows in the table).
> > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > the log and data files to the g: drive and one pair of rows points
> > them to the f: drive. The references to g: drive need to go away but
> > I've been googinling for a while now and haven't come up with much.
> > I tried running the alter database command normally used to move
> > tempdb and though it didn't fail, it didn't change anything.
> > Several months ago our software vendor moved tempdb from g: to f: to
> > try and speed it up a bit. Appearantly they messed it up and now have
> > written us off till WE fix it.
> > The entries in sysaltfiles were the only references to g: that turned
> > up (though we didn't look at every table and aren't even remotely sure
> > where other references might be located).
> > Any pointers on getting this corrected would be greatly apprecieated.
> > We thought about trying a reconfigure and restarting but I'm not real
> > hopeful. We also thought about just updating the wrong entries to
> > reflect the right locations but that smacks of kluge.
> > Tangential wierdness is that while trying to isolate the source of g:
> > \mssql in the error I found that in master.sysdevices the file
> > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > \tempdb.mdf.
> > I believe this is from the initial install then while configuring the
> > server it got moved to g: then to f:.
> > HELP!!!
> > Thanks in advance for any input!
> > Rusty
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> > "rnb...@.gmail.com" wrote:
> >
> > > Hi,
> >
> > > We tried to create a new database on an application server (Win 2003
> > > server/SQL Server 2003) and got the following error.
> >
> > > error 945...
> >
> > > ....
> > > device activation error. The physical filename g:\mssqldata
> > > \templog.ldf may be incorrect.
> >
> > > The problem is that tempdb is on f:\mssql as shown in the database
> > > properties and with sp_dbhelp.
> >
> > > Poking around in master the real wierdness comes through. sysaltfiles
> > > has 2 entries each for tempdb logs and data files. One of them is on
> > > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > > g: (actually the last two rows in the table).
> >
> > > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > > the log and data files to the g: drive and one pair of rows points
> > > them to the f: drive. The references to g: drive need to go away but
> > > I've been googinling for a while now and haven't come up with much.
> >
> > > I tried running the alter database command normally used to move
> > > tempdb and though it didn't fail, it didn't change anything.
> >
> > > Several months ago our software vendor moved tempdb from g: to f: to
> > > try and speed it up a bit. Appearantly they messed it up and now have
> > > written us off till WE fix it.
> >
> > > The entries in sysaltfiles were the only references to g: that turned
> > > up (though we didn't look at every table and aren't even remotely sure
> > > where other references might be located).
> >
> > > Any pointers on getting this corrected would be greatly apprecieated.
> > > We thought about trying a reconfigure and restarting but I'm not real
> > > hopeful. We also thought about just updating the wrong entries to
> > > reflect the right locations but that smacks of kluge.
> >
> > > Tangential wierdness is that while trying to isolate the source of g:
> > > \mssql in the error I found that in master.sysdevices the file
> > > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > > \tempdb.mdf.
> >
> > > I believe this is from the initial install then while configuring the
> > > server it got moved to g: then to f:.
> >
> > > HELP!!!
> >
> > > Thanks in advance for any input!
> > > Rusty
> >
> > When creating the database I would not expect tempdb to have anything to do
> > with it!
> >
> > What command have you used to create this database? It seems to me that you
> > have specified g:\mssqldata\templog.ldf as the log file instead of
> > g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> > have been correctly set.
> >
> > What happens is you just use the T-SQL
> >
> > CREATE DATABASE NewDatabase
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
> > Hi,
> > We tried to create a new database on an application server (Win 2003
> > server/SQL Server 2003) and got the following error.
> > error 945...
> > ....
> > device activation error. The physical filename g:\mssqldata
> > \templog.ldf may be incorrect.
> > The problem is that tempdb is on f:\mssql as shown in the database
> > properties and with sp_dbhelp.
> > Poking around in master the real wierdness comes through. sysaltfiles
> > has 2 entries each for tempdb logs and data files. One of them is on
> > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > g: (actually the last two rows in the table).
> > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > the log and data files to the g: drive and one pair of rows points
> > them to the f: drive. The references to g: drive need to go away but
> > I've been googinling for a while now and haven't come up with much.
> > I tried running the alter database command normally used to move
> > tempdb and though it didn't fail, it didn't change anything.
> > Several months ago our software vendor moved tempdb from g: to f: to
> > try and speed it up a bit. Appearantly they messed it up and now have
> > written us off till WE fix it.
> > The entries in sysaltfiles were the only references to g: that turned
> > up (though we didn't look at every table and aren't even remotely sure
> > where other references might be located).
> > Any pointers on getting this corrected would be greatly apprecieated.
> > We thought about trying a reconfigure and restarting but I'm not real
> > hopeful. We also thought about just updating the wrong entries to
> > reflect the right locations but that smacks of kluge.
> > Tangential wierdness is that while trying to isolate the source of g:
> > \mssql in the error I found that in master.sysdevices the file
> > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > \tempdb.mdf.
> > I believe this is from the initial install then while configuring the
> > server it got moved to g: then to f:.
> > HELP!!!
> > Thanks in advance for any input!
> > Rusty
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> > "rnb...@.gmail.com" wrote:
> >
> > > Hi,
> >
> > > We tried to create a new database on an application server (Win 2003
> > > server/SQL Server 2003) and got the following error.
> >
> > > error 945...
> >
> > > ....
> > > device activation error. The physical filename g:\mssqldata
> > > \templog.ldf may be incorrect.
> >
> > > The problem is that tempdb is on f:\mssql as shown in the database
> > > properties and with sp_dbhelp.
> >
> > > Poking around in master the real wierdness comes through. sysaltfiles
> > > has 2 entries each for tempdb logs and data files. One of them is on
> > > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > > g: (actually the last two rows in the table).
> >
> > > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > > the log and data files to the g: drive and one pair of rows points
> > > them to the f: drive. The references to g: drive need to go away but
> > > I've been googinling for a while now and haven't come up with much.
> >
> > > I tried running the alter database command normally used to move
> > > tempdb and though it didn't fail, it didn't change anything.
> >
> > > Several months ago our software vendor moved tempdb from g: to f: to
> > > try and speed it up a bit. Appearantly they messed it up and now have
> > > written us off till WE fix it.
> >
> > > The entries in sysaltfiles were the only references to g: that turned
> > > up (though we didn't look at every table and aren't even remotely sure
> > > where other references might be located).
> >
> > > Any pointers on getting this corrected would be greatly apprecieated.
> > > We thought about trying a reconfigure and restarting but I'm not real
> > > hopeful. We also thought about just updating the wrong entries to
> > > reflect the right locations but that smacks of kluge.
> >
> > > Tangential wierdness is that while trying to isolate the source of g:
> > > \mssql in the error I found that in master.sysdevices the file
> > > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > > \tempdb.mdf.
> >
> > > I believe this is from the initial install then while configuring the
> > > server it got moved to g: then to f:.
> >
> > > HELP!!!
> >
> > > Thanks in advance for any input!
> > > Rusty
> >
> > When creating the database I would not expect tempdb to have anything to do
> > with it!
> >
> > What command have you used to create this database? It seems to me that you
> > have specified g:\mssqldata\templog.ldf as the log file instead of
> > g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> > have been correctly set.
> >
> > What happens is you just use the T-SQL
> >
> > CREATE DATABASE NewDatabase
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.
Multiple tempdb files to no. of CPUs in SQL 2005
I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT
Multiple tempdb files to no. of CPUs in SQL 2005
I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT
Multiple tempdb files to no. of CPUs in SQL 2005
I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?
Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>
|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT
files as the number of CPUs.
Is it still the case for SQL 2005 ?
Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>
|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT
Multiple TempDB files for Multi_CPU
Hi all,
I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
have as many files for tempDB as I have CPU's - ie, eight.
I have a few questions about this:
1. I am assuming that these files are created in SSMS by right-clicking
tempdb and going to the Properties\Files tab.
--Should I add one tempdev_n and one templog_n for each CPU, or just the
tempdev file (the mdf)?
--Does it make sense to leave all of the new tempdev_n files in the
PRIMARY filegroup (same as Partition?), or should I create a new filegroup
for each tempdev_n instance?
2. Will performance actually have the potential to improve if all of these
tempdev_n files are on the same RAID 1 disc array? Does anyone actually
spread these files to dedicated discs per instance?
Thanks. These questions probably expose my experience level (rank amateur),
but that's why I am asking them. I appreciate any help.
JT
--
JohnHi
Increasing the number of data files will reduce the contention in SGAM and
PFS pages in tempdb. This is described in
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Also check out
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
This is more likely to occur if your application uses an excessive number of
temporary table or other features that require tempdb.
You do not have to increase the number of log files. You can use management
studio or use the ALTER DATABASE command to increase the number of files.
Also make sure that the initial size of the tempdb files are large enough for
normal use so you avoid it having to grow.
John
"JT" wrote:
> Hi all,
> I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
> have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
> array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
> have as many files for tempDB as I have CPU's - ie, eight.
> I have a few questions about this:
> 1. I am assuming that these files are created in SSMS by right-clicking
> tempdb and going to the Properties\Files tab.
> --Should I add one tempdev_n and one templog_n for each CPU, or just the
> tempdev file (the mdf)?
> --Does it make sense to leave all of the new tempdev_n files in the
> PRIMARY filegroup (same as Partition?), or should I create a new filegroup
> for each tempdev_n instance?
> 2. Will performance actually have the potential to improve if all of these
> tempdev_n files are on the same RAID 1 disc array? Does anyone actually
> spread these files to dedicated discs per instance?
> Thanks. These questions probably expose my experience level (rank amateur),
> but that's why I am asking them. I appreciate any help.
> JT
> --
> John|||Hello John,
I agree with john that it is necessary if you do have load that requres big
tempdb. Also, setting the database size to a large value is suggested.
For SQL Server 2005 it is recommended that you create tempdb files striped
across fast disks. Since you have raid1 disk, the performance improvement
may not as much as you expect though it should improve performance when the
problem is caused by tempdb. Generally, you may want to monitor the system
by using performon as described in the articles recommended by John and add
files when necessary. Also, you may want to configure trace flag 1118 to
eliminate mixed extent allocations. Please see the following article for
more details:
328551 FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
If you have further questions or concerns on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Peter and John. One follow-on question...
Given current resource restrictions, I have two FC disks to devote to the
tempdb for this SQL instance on the SAN. For reliability sake, I choose to
go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
array). Would it make sense to go with RAID 0? What would happen if one of
the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
Thanks.
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I agree with john that it is necessary if you do have load that requres big
> tempdb. Also, setting the database size to a large value is suggested.
> For SQL Server 2005 it is recommended that you create tempdb files striped
> across fast disks. Since you have raid1 disk, the performance improvement
> may not as much as you expect though it should improve performance when the
> problem is caused by tempdb. Generally, you may want to monitor the system
> by using performon as described in the articles recommended by John and add
> files when necessary. Also, you may want to configure trace flag 1118 to
> eliminate mixed extent allocations. Please see the following article for
> more details:
> 328551 FIX: Concurrency enhancements for the tempdb database
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> If you have further questions or concerns on the issue, please feel free to
> let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi
With Raid 0 you would have some down, while you either added a new disc or
reconfigured tempdb (I think SQL Server would not restart until you did!) .
The database should roll back any usage of tempdb when the system stopped so
your database should remain consistent but you would need to reapply the
transactions that were in progress.
John
"JT" wrote:
> Thank you Peter and John. One follow-on question...
> Given current resource restrictions, I have two FC disks to devote to the
> tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> array). Would it make sense to go with RAID 0? What would happen if one of
> the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> Thanks.
> --
> John
>
> "Peter Yang [MSFT]" wrote:
> > Hello John,
> >
> > I agree with john that it is necessary if you do have load that requres big
> > tempdb. Also, setting the database size to a large value is suggested.
> >
> > For SQL Server 2005 it is recommended that you create tempdb files striped
> > across fast disks. Since you have raid1 disk, the performance improvement
> > may not as much as you expect though it should improve performance when the
> > problem is caused by tempdb. Generally, you may want to monitor the system
> > by using performon as described in the articles recommended by John and add
> > files when necessary. Also, you may want to configure trace flag 1118 to
> > eliminate mixed extent allocations. Please see the following article for
> > more details:
> >
> > 328551 FIX: Concurrency enhancements for the tempdb database
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> >
> > If you have further questions or concerns on the issue, please feel free to
> > let's know. Thank you.
> >
> > Best Regards,
> >
> > Peter Yang
> > MCSE2000/2003, MCSA, MCDBA
> > Microsoft Online Community Support
> > ==================================================> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications
> > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hello John,
As John mentioned, raid 0 disk failure of tempdb shall not bring data
consistency issue. However, transactions related to tempdb will roll back
and you shall reapply these transactions.
Usually it is suggested that you use RAID 1 or RAID 1+0, not RAID 0 for
tempdb, unless you are prepared to lose SQL Server access should either
drive fail in a RAID 0 striped array.
Please see:
http://www.microsoft.com/sql/prodinfo/previousversions/scalabilityfaq.mspx
and the section on tempdb
http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx
917047 Microsoft SQL Server I/O subsystem requirements for the tempdb
database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;917047
If you have further questions, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"any usage of tempdb" does not read well! Try substituting "any uncommitted
transactions in progress"
John
"John Bell" wrote:
> Hi
> With Raid 0 you would have some down, while you either added a new disc or
> reconfigured tempdb (I think SQL Server would not restart until you did!) .
> The database should roll back any usage of tempdb when the system stopped so
> your database should remain consistent but you would need to reapply the
> transactions that were in progress.
> John
> "JT" wrote:
> > Thank you Peter and John. One follow-on question...
> > Given current resource restrictions, I have two FC disks to devote to the
> > tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> > go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> > array). Would it make sense to go with RAID 0? What would happen if one of
> > the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> >
> > Thanks.
> > --
> > John
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> > > Hello John,
> > >
> > > I agree with john that it is necessary if you do have load that requres big
> > > tempdb. Also, setting the database size to a large value is suggested.
> > >
> > > For SQL Server 2005 it is recommended that you create tempdb files striped
> > > across fast disks. Since you have raid1 disk, the performance improvement
> > > may not as much as you expect though it should improve performance when the
> > > problem is caused by tempdb. Generally, you may want to monitor the system
> > > by using performon as described in the articles recommended by John and add
> > > files when necessary. Also, you may want to configure trace flag 1118 to
> > > eliminate mixed extent allocations. Please see the following article for
> > > more details:
> > >
> > > 328551 FIX: Concurrency enhancements for the tempdb database
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> > >
> > > If you have further questions or concerns on the issue, please feel free to
> > > let's know. Thank you.
> > >
> > > Best Regards,
> > >
> > > Peter Yang
> > > MCSE2000/2003, MCSA, MCDBA
> > > Microsoft Online Community Support
> > > ==================================================> > > Get notification to my posts through email? Please refer to
> > > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > > ications
> > > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > > where an initial response from the community or a Microsoft Support
> > > Engineer within 1 business day is acceptable. Please note that each follow
> > > up response may take approximately 2 business days as the support
> > > professional working with you may need further investigation to reach the
> > > most efficient resolution. The offering is not appropriate for situations
> > > that require urgent, real-time or phone-based interactions or complex
> > > project analysis and dump analysis issues. Issues of this nature are best
> > > handled working with a dedicated Microsoft Support Engineer by contacting
> > > Microsoft Customer Support Services (CSS) at
> > > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > > ==================================================> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >|||Hello John,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
have as many files for tempDB as I have CPU's - ie, eight.
I have a few questions about this:
1. I am assuming that these files are created in SSMS by right-clicking
tempdb and going to the Properties\Files tab.
--Should I add one tempdev_n and one templog_n for each CPU, or just the
tempdev file (the mdf)?
--Does it make sense to leave all of the new tempdev_n files in the
PRIMARY filegroup (same as Partition?), or should I create a new filegroup
for each tempdev_n instance?
2. Will performance actually have the potential to improve if all of these
tempdev_n files are on the same RAID 1 disc array? Does anyone actually
spread these files to dedicated discs per instance?
Thanks. These questions probably expose my experience level (rank amateur),
but that's why I am asking them. I appreciate any help.
JT
--
JohnHi
Increasing the number of data files will reduce the contention in SGAM and
PFS pages in tempdb. This is described in
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Also check out
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
This is more likely to occur if your application uses an excessive number of
temporary table or other features that require tempdb.
You do not have to increase the number of log files. You can use management
studio or use the ALTER DATABASE command to increase the number of files.
Also make sure that the initial size of the tempdb files are large enough for
normal use so you avoid it having to grow.
John
"JT" wrote:
> Hi all,
> I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
> have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
> array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
> have as many files for tempDB as I have CPU's - ie, eight.
> I have a few questions about this:
> 1. I am assuming that these files are created in SSMS by right-clicking
> tempdb and going to the Properties\Files tab.
> --Should I add one tempdev_n and one templog_n for each CPU, or just the
> tempdev file (the mdf)?
> --Does it make sense to leave all of the new tempdev_n files in the
> PRIMARY filegroup (same as Partition?), or should I create a new filegroup
> for each tempdev_n instance?
> 2. Will performance actually have the potential to improve if all of these
> tempdev_n files are on the same RAID 1 disc array? Does anyone actually
> spread these files to dedicated discs per instance?
> Thanks. These questions probably expose my experience level (rank amateur),
> but that's why I am asking them. I appreciate any help.
> JT
> --
> John|||Hello John,
I agree with john that it is necessary if you do have load that requres big
tempdb. Also, setting the database size to a large value is suggested.
For SQL Server 2005 it is recommended that you create tempdb files striped
across fast disks. Since you have raid1 disk, the performance improvement
may not as much as you expect though it should improve performance when the
problem is caused by tempdb. Generally, you may want to monitor the system
by using performon as described in the articles recommended by John and add
files when necessary. Also, you may want to configure trace flag 1118 to
eliminate mixed extent allocations. Please see the following article for
more details:
328551 FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
If you have further questions or concerns on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Peter and John. One follow-on question...
Given current resource restrictions, I have two FC disks to devote to the
tempdb for this SQL instance on the SAN. For reliability sake, I choose to
go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
array). Would it make sense to go with RAID 0? What would happen if one of
the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
Thanks.
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I agree with john that it is necessary if you do have load that requres big
> tempdb. Also, setting the database size to a large value is suggested.
> For SQL Server 2005 it is recommended that you create tempdb files striped
> across fast disks. Since you have raid1 disk, the performance improvement
> may not as much as you expect though it should improve performance when the
> problem is caused by tempdb. Generally, you may want to monitor the system
> by using performon as described in the articles recommended by John and add
> files when necessary. Also, you may want to configure trace flag 1118 to
> eliminate mixed extent allocations. Please see the following article for
> more details:
> 328551 FIX: Concurrency enhancements for the tempdb database
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> If you have further questions or concerns on the issue, please feel free to
> let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi
With Raid 0 you would have some down, while you either added a new disc or
reconfigured tempdb (I think SQL Server would not restart until you did!) .
The database should roll back any usage of tempdb when the system stopped so
your database should remain consistent but you would need to reapply the
transactions that were in progress.
John
"JT" wrote:
> Thank you Peter and John. One follow-on question...
> Given current resource restrictions, I have two FC disks to devote to the
> tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> array). Would it make sense to go with RAID 0? What would happen if one of
> the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> Thanks.
> --
> John
>
> "Peter Yang [MSFT]" wrote:
> > Hello John,
> >
> > I agree with john that it is necessary if you do have load that requres big
> > tempdb. Also, setting the database size to a large value is suggested.
> >
> > For SQL Server 2005 it is recommended that you create tempdb files striped
> > across fast disks. Since you have raid1 disk, the performance improvement
> > may not as much as you expect though it should improve performance when the
> > problem is caused by tempdb. Generally, you may want to monitor the system
> > by using performon as described in the articles recommended by John and add
> > files when necessary. Also, you may want to configure trace flag 1118 to
> > eliminate mixed extent allocations. Please see the following article for
> > more details:
> >
> > 328551 FIX: Concurrency enhancements for the tempdb database
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> >
> > If you have further questions or concerns on the issue, please feel free to
> > let's know. Thank you.
> >
> > Best Regards,
> >
> > Peter Yang
> > MCSE2000/2003, MCSA, MCDBA
> > Microsoft Online Community Support
> > ==================================================> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications
> > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hello John,
As John mentioned, raid 0 disk failure of tempdb shall not bring data
consistency issue. However, transactions related to tempdb will roll back
and you shall reapply these transactions.
Usually it is suggested that you use RAID 1 or RAID 1+0, not RAID 0 for
tempdb, unless you are prepared to lose SQL Server access should either
drive fail in a RAID 0 striped array.
Please see:
http://www.microsoft.com/sql/prodinfo/previousversions/scalabilityfaq.mspx
and the section on tempdb
http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx
917047 Microsoft SQL Server I/O subsystem requirements for the tempdb
database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;917047
If you have further questions, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"any usage of tempdb" does not read well! Try substituting "any uncommitted
transactions in progress"
John
"John Bell" wrote:
> Hi
> With Raid 0 you would have some down, while you either added a new disc or
> reconfigured tempdb (I think SQL Server would not restart until you did!) .
> The database should roll back any usage of tempdb when the system stopped so
> your database should remain consistent but you would need to reapply the
> transactions that were in progress.
> John
> "JT" wrote:
> > Thank you Peter and John. One follow-on question...
> > Given current resource restrictions, I have two FC disks to devote to the
> > tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> > go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> > array). Would it make sense to go with RAID 0? What would happen if one of
> > the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> >
> > Thanks.
> > --
> > John
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> > > Hello John,
> > >
> > > I agree with john that it is necessary if you do have load that requres big
> > > tempdb. Also, setting the database size to a large value is suggested.
> > >
> > > For SQL Server 2005 it is recommended that you create tempdb files striped
> > > across fast disks. Since you have raid1 disk, the performance improvement
> > > may not as much as you expect though it should improve performance when the
> > > problem is caused by tempdb. Generally, you may want to monitor the system
> > > by using performon as described in the articles recommended by John and add
> > > files when necessary. Also, you may want to configure trace flag 1118 to
> > > eliminate mixed extent allocations. Please see the following article for
> > > more details:
> > >
> > > 328551 FIX: Concurrency enhancements for the tempdb database
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> > >
> > > If you have further questions or concerns on the issue, please feel free to
> > > let's know. Thank you.
> > >
> > > Best Regards,
> > >
> > > Peter Yang
> > > MCSE2000/2003, MCSA, MCDBA
> > > Microsoft Online Community Support
> > > ==================================================> > > Get notification to my posts through email? Please refer to
> > > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > > ications
> > > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > > where an initial response from the community or a Microsoft Support
> > > Engineer within 1 business day is acceptable. Please note that each follow
> > > up response may take approximately 2 business days as the support
> > > professional working with you may need further investigation to reach the
> > > most efficient resolution. The offering is not appropriate for situations
> > > that require urgent, real-time or phone-based interactions or complex
> > > project analysis and dump analysis issues. Issues of this nature are best
> > > handled working with a dedicated Microsoft Support Engineer by contacting
> > > Microsoft Customer Support Services (CSS) at
> > > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > > ==================================================> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >|||Hello John,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
multiple tempdb files and large operation in tempdb.
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advanceIt will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advanceIt will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
multiple tempdb files and large operation in tempdb.
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
It will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
It will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
multiple tempdb filegroup
If I want to try the multiple tempdb file trick (have number of files equal
to number of processors), is it better if they are each a separate filegroup,
or is it better (or required!) if they are all members of a single filegroup?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
Josh
If you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromance
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:
> If I want to try the multiple tempdb file trick (have number of files equal
> to number of processors), is it better if they are each a separate filegroup,
> or is it better (or required!) if they are all members of a single filegroup?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm is
> to try to use the largest file first, it will quickly become not the largest
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh
to number of processors), is it better if they are each a separate filegroup,
or is it better (or required!) if they are all members of a single filegroup?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
Josh
If you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromance
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:
> If I want to try the multiple tempdb file trick (have number of files equal
> to number of processors), is it better if they are each a separate filegroup,
> or is it better (or required!) if they are all members of a single filegroup?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm is
> to try to use the largest file first, it will quickly become not the largest
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh
multiple tempdb filegroup
If I want to try the multiple tempdb file trick (have number of files equal
to number of processors), is it better if they are each a separate filegroup
,
or is it better (or required!) if they are all members of a single filegroup
?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
JoshIf you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromanc
e
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_she...r-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:
> If I want to try the multiple tempdb file trick (have number of files equa
l
> to number of processors), is it better if they are each a separate filegro
up,
> or is it better (or required!) if they are all members of a single filegro
up?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm i
s
> to try to use the largest file first, it will quickly become not the large
st
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh
to number of processors), is it better if they are each a separate filegroup
,
or is it better (or required!) if they are all members of a single filegroup
?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
JoshIf you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromanc
e
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_she...r-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:
> If I want to try the multiple tempdb file trick (have number of files equa
l
> to number of processors), is it better if they are each a separate filegro
up,
> or is it better (or required!) if they are all members of a single filegro
up?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm i
s
> to try to use the largest file first, it will quickly become not the large
st
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh
multiple tempdb filegroup
If I want to try the multiple tempdb file trick (have number of files equal
to number of processors), is it better if they are each a separate filegroup,
or is it better (or required!) if they are all members of a single filegroup?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
JoshIf you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromance
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:
> If I want to try the multiple tempdb file trick (have number of files equal
> to number of processors), is it better if they are each a separate filegroup,
> or is it better (or required!) if they are all members of a single filegroup?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm is
> to try to use the largest file first, it will quickly become not the largest
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh
to number of processors), is it better if they are each a separate filegroup,
or is it better (or required!) if they are all members of a single filegroup?
Also, while the kb article recommends using fixed-size and equal-sized
files, it seems to me that is should not really matter, if the algorithm is
to try to use the largest file first, it will quickly become not the largest
and we will get pretty much the same round-robin as the much more limited
configurations would see.
Anyone like to comment on the performance improvements, if any, likely?
This is mostly sequential batch processing, but with fair millions of
records, and a lot more use of group-by, mostly on current 2 * dualcore
systems, and doesn't actually have a lot of tempdb contention that I've
noticed, though we'd like to see better
performance in any case.
Thanks.
JoshIf you do use multiple filegroups, you would be using a very unusually and
uncommon tempdb configuration. My suggestion is, forget about multiple
filegroups. What matters is the number of data files.
I have not done any performance tests myself on the impact of the number of
the tempdb data files, but I've done tests on the performance impact of the
number of data files in a user database, and there is significant perfromance
impact if there is significant allocation activity.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Since tempdb by its very nature is allocation intensive, I would not
question the impact of the recommend configuration on the tempdb performance
(and therefore the SQL Server performance).
Linchi
"JRStern" wrote:
> If I want to try the multiple tempdb file trick (have number of files equal
> to number of processors), is it better if they are each a separate filegroup,
> or is it better (or required!) if they are all members of a single filegroup?
> Also, while the kb article recommends using fixed-size and equal-sized
> files, it seems to me that is should not really matter, if the algorithm is
> to try to use the largest file first, it will quickly become not the largest
> and we will get pretty much the same round-robin as the much more limited
> configurations would see.
>
> Anyone like to comment on the performance improvements, if any, likely?
> This is mostly sequential batch processing, but with fair millions of
> records, and a lot more use of group-by, mostly on current 2 * dualcore
> systems, and doesn't actually have a lot of tempdb contention that I've
> noticed, though we'd like to see better
> performance in any case.
> Thanks.
> Josh
Multiple Tempdb
Hi,
Is it possible to have multiple tempdb on 1 sql server. If
yes please pass on the info.
Thanks in advance.No. But you can relocate TempDB or span it across multiple files on multiple
devices if necessary. This may help with performance or storage management
issues if that is your goal.
Use ALTER DATABASE to add / modify files in TempDB, then restart the server.
--
David Portas
SQL Server MVP
--|||Hi,
What about in Yukon server.
>--Original Message--
>Hi,
>Is it possible to have multiple tempdb on 1 sql server.
If
>yes please pass on the info.
>Thanks in advance.
>.
>|||Not that I'm aware of. Could you explain just what functionality you are
looking for. What could you do with multiple TempDBs that you can't do with
one? How would you want multiple DBs to be used by the server?
--
David Portas
SQL Server MVP
--|||It is possible in Sybase 12.5.1.That's why i wanna know if
it is possible in MS-SQL
>--Original Message--
>Not that I'm aware of. Could you explain just what
functionality you are
>looking for. What could you do with multiple TempDBs that
you can't do with
>one? How would you want multiple DBs to be used by the
server?
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do
with
> one? How would you want multiple DBs to be used by the server?
I think there has been a pretty common request to allow each database to
have its own tempdb. However I don't believe it got any further than the
request stage for SQL Server 2005.
A|||You could install another instance of SQL Server. You would lose though in
available memory as it is likely you would set a maximum for each instance
which would be less than the single instance would have access to.
"Moh" <anonymous@.discussions.microsoft.com> wrote in message
news:408401c4a009$e215b600$a301280a@.phx.gbl...
> Hi,
> Is it possible to have multiple tempdb on 1 sql server. If
> yes please pass on the info.
> Thanks in advance.|||Hi,
Better option will be create the TEMPDB database files which can be spanned
across multiple disk controllers. This will reduce the Disk I/O.
I have written this under the impression that perfomance is the real bottle
neck.
If the performance is the real bottle neck you could:-
1. reduce Order by clause for select statement returns huge results
2. reduce joins and group by
3. reduce usage of temp table
Thanks
Hari
MCDBA
"David Portas" wrote:
> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do with
> one? How would you want multiple DBs to be used by the server?
> --
> David Portas
> SQL Server MVP
> --
>
>
Is it possible to have multiple tempdb on 1 sql server. If
yes please pass on the info.
Thanks in advance.No. But you can relocate TempDB or span it across multiple files on multiple
devices if necessary. This may help with performance or storage management
issues if that is your goal.
Use ALTER DATABASE to add / modify files in TempDB, then restart the server.
--
David Portas
SQL Server MVP
--|||Hi,
What about in Yukon server.
>--Original Message--
>Hi,
>Is it possible to have multiple tempdb on 1 sql server.
If
>yes please pass on the info.
>Thanks in advance.
>.
>|||Not that I'm aware of. Could you explain just what functionality you are
looking for. What could you do with multiple TempDBs that you can't do with
one? How would you want multiple DBs to be used by the server?
--
David Portas
SQL Server MVP
--|||It is possible in Sybase 12.5.1.That's why i wanna know if
it is possible in MS-SQL
>--Original Message--
>Not that I'm aware of. Could you explain just what
functionality you are
>looking for. What could you do with multiple TempDBs that
you can't do with
>one? How would you want multiple DBs to be used by the
server?
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do
with
> one? How would you want multiple DBs to be used by the server?
I think there has been a pretty common request to allow each database to
have its own tempdb. However I don't believe it got any further than the
request stage for SQL Server 2005.
A|||You could install another instance of SQL Server. You would lose though in
available memory as it is likely you would set a maximum for each instance
which would be less than the single instance would have access to.
"Moh" <anonymous@.discussions.microsoft.com> wrote in message
news:408401c4a009$e215b600$a301280a@.phx.gbl...
> Hi,
> Is it possible to have multiple tempdb on 1 sql server. If
> yes please pass on the info.
> Thanks in advance.|||Hi,
Better option will be create the TEMPDB database files which can be spanned
across multiple disk controllers. This will reduce the Disk I/O.
I have written this under the impression that perfomance is the real bottle
neck.
If the performance is the real bottle neck you could:-
1. reduce Order by clause for select statement returns huge results
2. reduce joins and group by
3. reduce usage of temp table
Thanks
Hari
MCDBA
"David Portas" wrote:
> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do with
> one? How would you want multiple DBs to be used by the server?
> --
> David Portas
> SQL Server MVP
> --
>
>
Multiple Tempdb
Hi,
Is it possible to have multiple tempdb on 1 sql server. If
yes please pass on the info.
Thanks in advance.
No. But you can relocate TempDB or span it across multiple files on multiple
devices if necessary. This may help with performance or storage management
issues if that is your goal.
Use ALTER DATABASE to add / modify files in TempDB, then restart the server.
David Portas
SQL Server MVP
|||You could install another instance of SQL Server. You would lose though in
available memory as it is likely you would set a maximum for each instance
which would be less than the single instance would have access to.
"Moh" <anonymous@.discussions.microsoft.com> wrote in message
news:408401c4a009$e215b600$a301280a@.phx.gbl...
> Hi,
> Is it possible to have multiple tempdb on 1 sql server. If
> yes please pass on the info.
> Thanks in advance.
Is it possible to have multiple tempdb on 1 sql server. If
yes please pass on the info.
Thanks in advance.
No. But you can relocate TempDB or span it across multiple files on multiple
devices if necessary. This may help with performance or storage management
issues if that is your goal.
Use ALTER DATABASE to add / modify files in TempDB, then restart the server.
David Portas
SQL Server MVP
|||You could install another instance of SQL Server. You would lose though in
available memory as it is likely you would set a maximum for each instance
which would be less than the single instance would have access to.
"Moh" <anonymous@.discussions.microsoft.com> wrote in message
news:408401c4a009$e215b600$a301280a@.phx.gbl...
> Hi,
> Is it possible to have multiple tempdb on 1 sql server. If
> yes please pass on the info.
> Thanks in advance.
Multiple Tempdb
Hi,
What about in Yukon server.
>--Original Message--
>Hi,
>Is it possible to have multiple tempdb on 1 sql server.
If
>yes please pass on the info.
>Thanks in advance.
>.
>
Not that I'm aware of. Could you explain just what functionality you are
looking for. What could you do with multiple TempDBs that you can't do with
one? How would you want multiple DBs to be used by the server?
David Portas
SQL Server MVP
|||It is possible in Sybase 12.5.1.That's why i wanna know if
it is possible in MS-SQL
>--Original Message--
>Not that I'm aware of. Could you explain just what
functionality you are
>looking for. What could you do with multiple TempDBs that
you can't do with
>one? How would you want multiple DBs to be used by the
server?
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do
with
> one? How would you want multiple DBs to be used by the server?
I think there has been a pretty common request to allow each database to
have its own tempdb. However I don't believe it got any further than the
request stage for SQL Server 2005.
A
|||Hi,
Better option will be create the TEMPDB database files which can be spanned
across multiple disk controllers. This will reduce the Disk I/O.
I have written this under the impression that perfomance is the real bottle
neck.
If the performance is the real bottle neck you could:-
1. reduce Order by clause for select statement returns huge results
2. reduce joins and group by
3. reduce usage of temp table
Thanks
Hari
MCDBA
"David Portas" wrote:
> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do with
> one? How would you want multiple DBs to be used by the server?
> --
> David Portas
> SQL Server MVP
> --
>
>
What about in Yukon server.
>--Original Message--
>Hi,
>Is it possible to have multiple tempdb on 1 sql server.
If
>yes please pass on the info.
>Thanks in advance.
>.
>
Not that I'm aware of. Could you explain just what functionality you are
looking for. What could you do with multiple TempDBs that you can't do with
one? How would you want multiple DBs to be used by the server?
David Portas
SQL Server MVP
|||It is possible in Sybase 12.5.1.That's why i wanna know if
it is possible in MS-SQL
>--Original Message--
>Not that I'm aware of. Could you explain just what
functionality you are
>looking for. What could you do with multiple TempDBs that
you can't do with
>one? How would you want multiple DBs to be used by the
server?
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do
with
> one? How would you want multiple DBs to be used by the server?
I think there has been a pretty common request to allow each database to
have its own tempdb. However I don't believe it got any further than the
request stage for SQL Server 2005.
A
|||Hi,
Better option will be create the TEMPDB database files which can be spanned
across multiple disk controllers. This will reduce the Disk I/O.
I have written this under the impression that perfomance is the real bottle
neck.
If the performance is the real bottle neck you could:-
1. reduce Order by clause for select statement returns huge results
2. reduce joins and group by
3. reduce usage of temp table
Thanks
Hari
MCDBA
"David Portas" wrote:
> Not that I'm aware of. Could you explain just what functionality you are
> looking for. What could you do with multiple TempDBs that you can't do with
> one? How would you want multiple DBs to be used by the server?
> --
> David Portas
> SQL Server MVP
> --
>
>
Subscribe to:
Posts (Atom)