Showing posts with label default. Show all posts
Showing posts with label default. Show all posts

Wednesday, March 21, 2012

Database Service Won't Start

We have multiple instances at Windows 2000 Advanced Server. One default
database is Enterprise Edition and another two instances are Developer
Editions. They have been running fine until recently.
We can start ANY ONE instance from the EM, only ONE. None of any other
services can be started anymore. Not even SQL Agent. We have to start
rest of other database services and sql agent from command line.
When we try to connect the SQL Server or Agent from the EM, we got
"Error 1053 - (The service did not respond to the start or control
request in a timely fashion) occured while performing this service
operation on the MSSQL service."
Thought maybe some corrpution to currnet databases, DLL etc. Installed
another new instance on the server, got the same error message.
Verified Microsoft KB article 322140, does not seem apply to us.
Any opinion is appreciated.Hi,
What you could do is first backup all the databases currently running on your
server.
1) Please never install Enterprise Edition and Developer Edition on the Same
Drive.
2) Install both of them on separate Drives.
3) Setup the Client and Server Tools for only the Enterprise Edition.
4) And when installing the Developer Editions, install only the Server and
not the client tools.
I faced a similar problem when I installed SQL Server 2000 and SQL server
2005 express on the same drive.
So when I installed it on separate drives, no problems faced yet :)
Hope this helps :)
--
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1|||Thanks for your response.
But it works fine before, until 2 weeks ago. Now I can start them from
command line.
Thanks.
DB2DOTNETCZAR via SQLMonster.com wrote:
> Hi,
> What you could do is first backup all the databases currently running on your
> server.
> 1) Please never install Enterprise Edition and Developer Edition on the Same
> Drive.
> 2) Install both of them on separate Drives.
> 3) Setup the Client and Server Tools for only the Enterprise Edition.
> 4) And when installing the Developer Editions, install only the Server and
> not the client tools.
> I faced a similar problem when I installed SQL Server 2000 and SQL server
> 2005 express on the same drive.
> So when I installed it on separate drives, no problems faced yet :)
> Hope this helps :)
> --
> Best Regards
> Anil Mahadev
> http://anilm001.myfreewebs.net/index2.php
> http://www.db2india.org
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1

Friday, February 24, 2012

Database Recovery Model Default value

Hi
I've been using SQL Server 2000 for quite some time. Every new database I
used to add, it would set the Database Recovery option to "Simple". Now
after shifting to SQL Server 2005, this option is being set to "Full" by
default for every new database. Can someone tell me where is this property
inherited from for every new database and can be changed so that each new
database may get default value of Recovery option to "Simple"
Thanks in advance
Usman
It is inherited from the recovery mode of you "model" database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Usman" <usman@.advcomm.net> wrote in message news:u9mhHeJVGHA.5364@.tk2msftngp13.phx.gbl...
> Hi
> I've been using SQL Server 2000 for quite some time. Every new database I
> used to add, it would set the Database Recovery option to "Simple". Now
> after shifting to SQL Server 2005, this option is being set to "Full" by
> default for every new database. Can someone tell me where is this property
> inherited from for every new database and can be changed so that each new
> database may get default value of Recovery option to "Simple"
> Thanks in advance
> Usman
>

Database Recovery Model Default value

Hi
I've been using SQL Server 2000 for quite some time. Every new database I
used to add, it would set the Database Recovery option to "Simple". Now
after shifting to SQL Server 2005, this option is being set to "Full" by
default for every new database. Can someone tell me where is this property
inherited from for every new database and can be changed so that each new
database may get default value of Recovery option to "Simple"
Thanks in advance
UsmanIt is inherited from the recovery mode of you "model" database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Usman" <usman@.advcomm.net> wrote in message news:u9mhHeJVGHA.5364@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
> Hi
> I've been using SQL Server 2000 for quite some time. Every new database I
> used to add, it would set the Database Recovery option to "Simple". Now
> after shifting to SQL Server 2005, this option is being set to "Full" by
> default for every new database. Can someone tell me where is this property
> inherited from for every new database and can be changed so that each new
> database may get default value of Recovery option to "Simple"
> Thanks in advance
> Usman
>[/vbcol]

Database Recovery Model Default value

Hi
I've been using SQL Server 2000 for quite some time. Every new database I
used to add, it would set the Database Recovery option to "Simple". Now
after shifting to SQL Server 2005, this option is being set to "Full" by
default for every new database. Can someone tell me where is this property
inherited from for every new database and can be changed so that each new
database may get default value of Recovery option to "Simple"
Thanks in advance
UsmanIt is inherited from the recovery mode of you "model" database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Usman" <usman@.advcomm.net> wrote in message news:u9mhHeJVGHA.5364@.tk2msftngp13.phx.gbl...
> Hi
> I've been using SQL Server 2000 for quite some time. Every new database I
> used to add, it would set the Database Recovery option to "Simple". Now
> after shifting to SQL Server 2005, this option is being set to "Full" by
> default for every new database. Can someone tell me where is this property
> inherited from for every new database and can be changed so that each new
> database may get default value of Recovery option to "Simple"
> Thanks in advance
> Usman
>

Tuesday, February 14, 2012

Database ownership

Hi
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.

Thanks Paul.

I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.

Thanks again

However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.

What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.

Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

Can you move all the user objects to a holding db and try your tests again?

Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table

This is what I'm trying to achieve with the NT group .

When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).

I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.

I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .

Thanks again for your help Paul

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
ChrisFor tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
Chris
For tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
ChrisFor tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>