Can tempdb be presized?

SQL Server 2000 & 2005
When restarting a server, can you specify the size you want tempdb to be? I
have no choice but to place in on the same drive as the other .mdf files and
would like to avoid it being created in pieces.Yes. Simply create the files you want and expand them manually to the
desired size. When the server restarts, it will retain the specified size.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Jay" <nospam@.nospam.org> wrote in message
news:%23KdcLIjIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000 & 2005
> When restarting a server, can you specify the size you want tempdb to be?
> I have no choice but to place in on the same drive as the other .mdf files
> and would like to avoid it being created in pieces.
>|||I take it it will only do this if you have sized it manually, but will
shrink the tempdb if it had auto-grown?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:uqrJURjIIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Yes. Simply create the files you want and expand them manually to the
> desired size. When the server restarts, it will retain the specified
> size.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23KdcLIjIIHA.1168@.TK2MSFTNGP02.phx.gbl...
>|||That's right.

>I take it it will only do this if you have sized it manually, but will
>shrink the tempdb if it had auto-grown?|||Makes sense.
Thank you Aaron.
Thank you Geoff.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:uWnkLvjIIHA.4956@.TK2MSFTNGP06.phx.gbl...
> That's right.
>
>

Can tempdb be presized?

SQL Server 2000 & 2005
When restarting a server, can you specify the size you want tempdb to be? I
have no choice but to place in on the same drive as the other .mdf files and
would like to avoid it being created in pieces.
Yes. Simply create the files you want and expand them manually to the
desired size. When the server restarts, it will retain the specified size.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Jay" <nospam@.nospam.org> wrote in message
news:%23KdcLIjIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000 & 2005
> When restarting a server, can you specify the size you want tempdb to be?
> I have no choice but to place in on the same drive as the other .mdf files
> and would like to avoid it being created in pieces.
>
|||I take it it will only do this if you have sized it manually, but will
shrink the tempdb if it had auto-grown?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:uqrJURjIIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Yes. Simply create the files you want and expand them manually to the
> desired size. When the server restarts, it will retain the specified
> size.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23KdcLIjIIHA.1168@.TK2MSFTNGP02.phx.gbl...
>
|||That's right.

>I take it it will only do this if you have sized it manually, but will
>shrink the tempdb if it had auto-grown?
|||Makes sense.
Thank you Aaron.
Thank you Geoff.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWnkLvjIIHA.4956@.TK2MSFTNGP06.phx.gbl...
> That's right.
>
>

Can tempdb be presized?

SQL Server 2000 & 2005
When restarting a server, can you specify the size you want tempdb to be? I
have no choice but to place in on the same drive as the other .mdf files and
would like to avoid it being created in pieces.Yes. Simply create the files you want and expand them manually to the
desired size. When the server restarts, it will retain the specified size.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Jay" <nospam@.nospam.org> wrote in message
news:%23KdcLIjIIHA.1168@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2000 & 2005
> When restarting a server, can you specify the size you want tempdb to be?
> I have no choice but to place in on the same drive as the other .mdf files
> and would like to avoid it being created in pieces.
>|||I take it it will only do this if you have sized it manually, but will
shrink the tempdb if it had auto-grown?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:uqrJURjIIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Yes. Simply create the files you want and expand them manually to the
> desired size. When the server restarts, it will retain the specified
> size.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23KdcLIjIIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2000 & 2005
>> When restarting a server, can you specify the size you want tempdb to be?
>> I have no choice but to place in on the same drive as the other .mdf
>> files and would like to avoid it being created in pieces.
>|||That's right.
>I take it it will only do this if you have sized it manually, but will
>shrink the tempdb if it had auto-grown?|||Makes sense.
Thank you Aaron.
Thank you Geoff.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWnkLvjIIHA.4956@.TK2MSFTNGP06.phx.gbl...
> That's right.
>>I take it it will only do this if you have sized it manually, but will
>>shrink the tempdb if it had auto-grown?
>

Can table ownership be changed?

When I create tables in SQL I can specify dbo as the owner using the
syntax below but when using the upsize wizard in Access 2000 I own
everything. Is there a way that the system administrator can change
ownership after the fact?

CREATE TABLE dbo.mytable (c1 int not null)

Thank You,
Randy K
wawork@.hotmail.comOn 18 May 2004 12:16:39 -0700, Randy wrote:

>When I create tables in SQL I can specify dbo as the owner using the
>syntax below but when using the upsize wizard in Access 2000 I own
>everything. Is there a way that the system administrator can change
>ownership after the fact?
>CREATE TABLE dbo.mytable (c1 int not null)
>
>Thank You,
> Randy K
>wawork@.hotmail.com

Hi Randy,

sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Can one confirm this behaviour ?

Say i have a database restored with its data files on say D and E.
When I do a restore again with EM and this time specify the data file paths
to be M and N but at the same time check on the box that states " Force
Restore over existing database", does the database actually try restoring to
the previous D and E and ignore the M and N paths that i specified . Using
SQL 2000
Thanks
Specifying the path will make EM add MOVE options to the RESTORE command. In this case, SQL Server
will not try to access the original path, as specified in the backup header. "Force restore" is only
needed if the database name that you try to restore to already exists and doesn't have the same file
layout as the one you are trying to restore (REPLACE option to the RESTORE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:uutzcrqAFHA.936@.TK2MSFTNGP12.phx.gbl...
> Say i have a database restored with its data files on say D and E.
> When I do a restore again with EM and this time specify the data file paths
> to be M and N but at the same time check on the box that states " Force
> Restore over existing database", does the database actually try restoring to
> the previous D and E and ignore the M and N paths that i specified . Using
> SQL 2000
> Thanks
>

Can one confirm this behaviour ?

Say i have a database restored with its data files on say D and E.
When I do a restore again with EM and this time specify the data file paths
to be M and N but at the same time check on the box that states " Force
Restore over existing database", does the database actually try restoring to
the previous D and E and ignore the M and N paths that i specified . Using
SQL 2000
ThanksSpecifying the path will make EM add MOVE options to the RESTORE command. In
this case, SQL Server
will not try to access the original path, as specified in the backup header.
"Force restore" is only
needed if the database name that you try to restore to already exists and do
esn't have the same file
layout as the one you are trying to restore (REPLACE option to the RESTORE c
ommand).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:uutzcrqAFHA.936@.TK2MSFTNGP12.phx.gbl.
.
> Say i have a database restored with its data files on say D and E.
> When I do a restore again with EM and this time specify the data file path
s
> to be M and N but at the same time check on the box that states " Force
> Restore over existing database", does the database actually try restoring
to
> the previous D and E and ignore the M and N paths that i specified . Using
> SQL 2000
> Thanks
>

Can one confirm this behaviour ?

Say i have a database restored with its data files on say D and E.
When I do a restore again with EM and this time specify the data file paths
to be M and N but at the same time check on the box that states " Force
Restore over existing database", does the database actually try restoring to
the previous D and E and ignore the M and N paths that i specified . Using
SQL 2000
ThanksSpecifying the path will make EM add MOVE options to the RESTORE command. In this case, SQL Server
will not try to access the original path, as specified in the backup header. "Force restore" is only
needed if the database name that you try to restore to already exists and doesn't have the same file
layout as the one you are trying to restore (REPLACE option to the RESTORE command).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:uutzcrqAFHA.936@.TK2MSFTNGP12.phx.gbl...
> Say i have a database restored with its data files on say D and E.
> When I do a restore again with EM and this time specify the data file paths
> to be M and N but at the same time check on the box that states " Force
> Restore over existing database", does the database actually try restoring to
> the previous D and E and ignore the M and N paths that i specified . Using
> SQL 2000
> Thanks
>

Can not specify file name for new database in sql 2005

Hi,
We installed Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 2). In management
studio, when I created a new database, I was able to change logical name,
initial size, path, etc., but the problem is with the File Name field. I
could not enter file name for either data or log. I clicked in it, the
background color changed to light blue with no cursor.
Anybody know why?
Thanks in advance,
BingNever mind. Looks like the File Name field doesn't expect any user input.
It defaults to whatever that's entered in the Database Name field. But in
sql 2000, database name doesn't have to be the same with the physical
database disk file name. Wonder why 2005 handles that differently?
Bing
"bing" wrote:
> Hi,
> We installed Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
> 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
> Edition on Windows NT 5.2 (Build 3790: Service Pack 2). In management
> studio, when I created a new database, I was able to change logical name,
> initial size, path, etc., but the problem is with the File Name field. I
> could not enter file name for either data or log. I clicked in it, the
> background color changed to light blue with no cursor.
> Anybody know why?
> Thanks in advance,
> Bing

Can not specify file name for new database in sql 2005

Hi,
We installed Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 2). In management
studio, when I created a new database, I was able to change logical name,
initial size, path, etc., but the problem is with the File Name field. I
could not enter file name for either data or log. I clicked in it, the
background color changed to light blue with no cursor.
Anybody know why?
Thanks in advance,
Bing
Never mind. Looks like the File Name field doesn't expect any user input.
It defaults to whatever that's entered in the Database Name field. But in
sql 2000, database name doesn't have to be the same with the physical
database disk file name. Wonder why 2005 handles that differently?
Bing
"bing" wrote:

> Hi,
> We installed Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
> 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
> Edition on Windows NT 5.2 (Build 3790: Service Pack 2). In management
> studio, when I created a new database, I was able to change logical name,
> initial size, path, etc., but the problem is with the File Name field. I
> could not enter file name for either data or log. I clicked in it, the
> background color changed to light blue with no cursor.
> Anybody know why?
> Thanks in advance,
> Bing

Can not specify file name for new database in sql 2005

Hi,
We installed Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 2). In management
studio, when I created a new database, I was able to change logical name,
initial size, path, etc., but the problem is with the File Name field. I
could not enter file name for either data or log. I clicked in it, the
background color changed to light blue with no cursor.
Anybody know why?
Thanks in advance,
BingNever mind. Looks like the File Name field doesn't expect any user input.
It defaults to whatever that's entered in the Database Name field. But in
sql 2000, database name doesn't have to be the same with the physical
database disk file name. Wonder why 2005 handles that differently?
Bing
"bing" wrote:

> Hi,
> We installed Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23
> 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard
> Edition on Windows NT 5.2 (Build 3790: Service Pack 2). In management
> studio, when I created a new database, I was able to change logical name,
> initial size, path, etc., but the problem is with the File Name field. I
> could not enter file name for either data or log. I clicked in it, the
> background color changed to light blue with no cursor.
> Anybody know why?
> Thanks in advance,
> Bing