Database has invalid owner

I get this error when I try to view the database diagram. This is strange because it was about two weeks ago that I created and edited the database diagram. What is wrong with my login now as compared to when I did those edits?

The error suggests to use the ALTER AUTHORIZATION to change to a valid login. I am using windows authentication. If my login (the database ownere) is not the valid login then who is?

And what are the database diagram support objects that it suggests I install. I never heard of them, and where are they? Why didn't I need to install anything special when I worked on the diagrams two weeks ago?

Error message:

TITLE: Microsoft SQL Server Management Studio Express

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

BUTTONS:
OK

Does anyone have any suggestions?

dbuchanan

Right click the database in question and select "properties". Select the "Files" from 'Select a Page' on the left side.

On the right side browse or add the owner that you want.

If things dont work then try using the sa owner. I also faced the same problem but when I sed the sa login then it worked wonders.

Xavier

sql

Database has invalid owner

I get this error when I try to view the database diagram. This is strange because it was about two weeks ago that I created and edited the database diagram. What is wrong with my login now as compared to when I did those edits?

The error suggests to use the ALTER AUTHORIZATION to change to a valid login. I am using windows authentication. If my login (the database ownere) is not the valid login then who is?

And what are the database diagram support objects that it suggests I install. I never heard of them, and where are they? Why didn't I need to install anything special when I worked on the diagrams two weeks ago?

Error message:

TITLE: Microsoft SQL Server Management Studio Express

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

BUTTONS:
OK

Does anyone have any suggestions?

dbuchanan

Right click the database in question and select "properties". Select the "Files" from 'Select a Page' on the left side.

On the right side browse or add the owner that you want.

If things dont work then try using the sa owner. I also faced the same problem but when I sed the sa login then it worked wonders.

Xavier

Database getting slower for every BLOB object

Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:

>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
>

Database getting slower for every BLOB object

Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:

>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:

Database getting slower for every BLOB object

Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
> >Hi,
> >
> >I have a table in my database storing BLOBS (images), and I have
> >noticed something strange. For every record that goes into the table,
> >the next insert takes longer to perfrom. It used to be that inserting
> >a small blob used to take about .25 of a second, but now its upto a
> >second.
> >
> >I create a test database (copy of my live) and created a test
> >application that inserted the same images over and over again and this
> >is my results:
> >
> >
> >(starting from 1750 images):
> >
> >Inserting next 250 images = 267 seconds
> >Inserting next 250 images = 284 seconds
> >Inserting next 250 images = 296 seconds
> >Inserting next 250 images = 307 seconds
> >Inserting next 250 images = 319 seconds
> >and so on.
> >
> >Is this typical of SQL Server (2005)? On Oracle, after the first 250
> >items were added, it kind of settles down and the insert time repains
> >fairly constant, no matter how many images I insert.

Database Defaults

I have a strange (well I think its strange) where I have create a field
(CreateDate) with a default of (getdate()); however, on the server where this
table is built, it is not populating the CreateDate, and as a result I am
getting errors, has anyone ever seen an issue like this were the field
defaults aren't getting populated?Does the column allow nulls?
Is the insert statement inserting a blank string in that column, or
something else that shouldn't be inserted?
Are you receiving any errors?
can we see the insert script?
Simon Worth
Scott wrote:
> I have a strange (well I think its strange) where I have create a field
> (CreateDate) with a default of (getdate()); however, on the server where this
> table is built, it is not populating the CreateDate, and as a result I am
> getting errors, has anyone ever seen an issue like this were the field
> defaults aren't getting populated?|||"Simon Worth" wrote:
The field does NOT allow nuls, and I tested with a very simple script:
Insert into Codes
(Code,Description)
values ('0001','Test Code')
the error message that I received was that the field will not accept nulls
the actual verbage was "Cannot insert the value NULL into column
'CreateDate',table test_app.dbo.codes, column does not allow NULLs. Update
Fails."
I use this all the time, and I am sure alot of others as well, this is the
first time I have ever run into anything like this
> Does the column allow nulls?
> Is the insert statement inserting a blank string in that column, or
> something else that shouldn't be inserted?
> Are you receiving any errors?
> can we see the insert script?
> Simon Worth|||Are you certain that the default constraint is still on the column? Try
verifying existence with sp_help 'Codes'.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B5F635A5-6379-41C9-A849-BC10B57865A7@.microsoft.com...
>
> "Simon Worth" wrote:
> The field does NOT allow nuls, and I tested with a very simple script:
> Insert into Codes
> (Code,Description)
> values ('0001','Test Code')
> the error message that I received was that the field will not accept nulls
> the actual verbage was "Cannot insert the value NULL into column
> 'CreateDate',table test_app.dbo.codes, column does not allow NULLs.
> Update
> Fails."
> I use this all the time, and I am sure alot of others as well, this is the
> first time I have ever run into anything like this
>
>> Does the column allow nulls?
>> Is the insert statement inserting a blank string in that column, or
>> something else that shouldn't be inserted?
>> Are you receiving any errors?
>> can we see the insert script?
>> Simon Worth
>

Database Defaults

I have a strange (well I think its strange) where I have create a field
(CreateDate) with a default of (getdate()); however, on the server where this
table is built, it is not populating the CreateDate, and as a result I am
getting errors, has anyone ever seen an issue like this were the field
defaults aren't getting populated?
Does the column allow nulls?
Is the insert statement inserting a blank string in that column, or
something else that shouldn't be inserted?
Are you receiving any errors?
can we see the insert script?
Simon Worth
Scott wrote:
> I have a strange (well I think its strange) where I have create a field
> (CreateDate) with a default of (getdate()); however, on the server where this
> table is built, it is not populating the CreateDate, and as a result I am
> getting errors, has anyone ever seen an issue like this were the field
> defaults aren't getting populated?
|||"Simon Worth" wrote:
The field does NOT allow nuls, and I tested with a very simple script:
Insert into Codes
(Code,Description)
values ('0001','Test Code')
the error message that I received was that the field will not accept nulls
the actual verbage was "Cannot insert the value NULL into column
'CreateDate',table test_app.dbo.codes, column does not allow NULLs. Update
Fails."
I use this all the time, and I am sure alot of others as well, this is the
first time I have ever run into anything like this

> Does the column allow nulls?
> Is the insert statement inserting a blank string in that column, or
> something else that shouldn't be inserted?
> Are you receiving any errors?
> can we see the insert script?
> Simon Worth
|||Are you certain that the default constraint is still on the column? Try
verifying existence with sp_help 'Codes'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B5F635A5-6379-41C9-A849-BC10B57865A7@.microsoft.com...
>
> "Simon Worth" wrote:
> The field does NOT allow nuls, and I tested with a very simple script:
> Insert into Codes
> (Code,Description)
> values ('0001','Test Code')
> the error message that I received was that the field will not accept nulls
> the actual verbage was "Cannot insert the value NULL into column
> 'CreateDate',table test_app.dbo.codes, column does not allow NULLs.
> Update
> Fails."
> I use this all the time, and I am sure alot of others as well, this is the
> first time I have ever run into anything like this
>
>

Database Defaults

I have a strange (well I think its strange) where I have create a field
(CreateDate) with a default of (getdate()); however, on the server where thi
s
table is built, it is not populating the CreateDate, and as a result I am
getting errors, has anyone ever seen an issue like this were the field
defaults aren't getting populated?Does the column allow nulls?
Is the insert statement inserting a blank string in that column, or
something else that shouldn't be inserted?
Are you receiving any errors?
can we see the insert script?
Simon Worth
Scott wrote:
> I have a strange (well I think its strange) where I have create a field
> (CreateDate) with a default of (getdate()); however, on the server where t
his
> table is built, it is not populating the CreateDate, and as a result I am
> getting errors, has anyone ever seen an issue like this were the field
> defaults aren't getting populated?|||"Simon Worth" wrote:
The field does NOT allow nuls, and I tested with a very simple script:
Insert into Codes
(Code,Description)
values ('0001','Test Code')
the error message that I received was that the field will not accept nulls
the actual verbage was "Cannot insert the value NULL into column
'CreateDate',table test_app.dbo.codes, column does not allow NULLs. Update
Fails."
I use this all the time, and I am sure alot of others as well, this is the
first time I have ever run into anything like this

> Does the column allow nulls?
> Is the insert statement inserting a blank string in that column, or
> something else that shouldn't be inserted?
> Are you receiving any errors?
> can we see the insert script?
> Simon Worth|||Are you certain that the default constraint is still on the column? Try
verifying existence with sp_help 'Codes'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B5F635A5-6379-41C9-A849-BC10B57865A7@.microsoft.com...
>
> "Simon Worth" wrote:
> The field does NOT allow nuls, and I tested with a very simple script:
> Insert into Codes
> (Code,Description)
> values ('0001','Test Code')
> the error message that I received was that the field will not accept nulls
> the actual verbage was "Cannot insert the value NULL into column
> 'CreateDate',table test_app.dbo.codes, column does not allow NULLs.
> Update
> Fails."
> I use this all the time, and I am sure alot of others as well, this is the
> first time I have ever run into anything like this
>
>
>

Database create error

I have a strange error occuring on one of my sql servers.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.

Database create error

I have a strange error occuring on one of my sql servers.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.

Database create error

I have a strange error occuring on one of my sql servers.
When I execute a CREATE DATABASE statement without
specifying any file locations, e.g. "CREATE DATABASE
fred", I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Device
activation error. The physical file name '\fred.mdf' may
be incorrect.
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
DATABASE failed. Some file names listed could not be
created. Check previous errors.
If I specify a location for the data file the but not the
log file it works ok. I set the default database
locations in the server properties and this then allowed
the original statement to work.
However, I would like to know where the error is coming
from. I assume that the default locations where
calculated based on either master of model, but where is
the file name error being generated?
Any pointers would be great.
Thanks,
Phil.Instead of putting '\fred.mdf' you will need to put (as an
example) C:\fred.mdf.
If you like to know more I would sugest you script a
database by going into EA and
1. Right Click on the DB -> All Tasks -> Generate SQL
Script.
2. Go the options tab and select script database
3. Press ok and choose a location to save the file.
After you have done this load the file in QA and have a
look at it - note - Do not run it.
Peter
>--Original Message--
>I have a strange error occuring on one of my sql servers.
>When I execute a CREATE DATABASE statement without
>specifying any file locations, e.g. "CREATE DATABASE
>fred", I get the following error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Device
>activation error. The physical file name '\fred.mdf' may
>be incorrect.
>[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
>DATABASE failed. Some file names listed could not be
>created. Check previous errors.
>If I specify a location for the data file the but not the
>log file it works ok. I set the default database
>locations in the server properties and this then allowed
>the original statement to work.
>However, I would like to know where the error is coming
>from. I assume that the default locations where
>calculated based on either master of model, but where is
>the file name error being generated?
>Any pointers would be great.
>Thanks,
>Phil.
>.
>|||> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
The default database file locations are stored in the registry and are not
related to the location of the master or model databases. When you set the
default locations using EM, this updates the registry entries that SQL
Server uses for CREATE DATABASE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil" <philip@.intex.co.uk> wrote in message
news:4f2901c48067$e34c6d00$a601280a@.phx.gbl...
> I have a strange error occuring on one of my sql servers.
> When I execute a CREATE DATABASE statement without
> specifying any file locations, e.g. "CREATE DATABASE
> fred", I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Device
> activation error. The physical file name '\fred.mdf' may
> be incorrect.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> DATABASE failed. Some file names listed could not be
> created. Check previous errors.
> If I specify a location for the data file the but not the
> log file it works ok. I set the default database
> locations in the server properties and this then allowed
> the original statement to work.
> However, I would like to know where the error is coming
> from. I assume that the default locations where
> calculated based on either master of model, but where is
> the file name error being generated?
> Any pointers would be great.
> Thanks,
> Phil.

Database could not be removed

We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now we
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> > We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> > we
> > are facing a strange case where when we tried to restore and overwrite a
> > database, it always say database is in use and cannot be overwritten. I
> > have
> > tried to detach and even delete it but everytime it just said the database
> > is
> > in use and refused to give up. I have checked and make sure no users and
> > connections are make to the said database. Eventually I have to resort to
> > restarting the SQL service to remove this database.
> > Is there a way to remove or overwrite a database without restarting the
> > SQL
> > service?
> >
>
>

Database could not be removed

We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now we
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?
Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>
|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:

> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>

Database could not be removed

We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now w
e
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database i
s
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:

> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you ar
e
> trying to remove. There will then be a spid, which can let you determine w
ho
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>

Database corruption problem?

We have a strange problem with an SQL SERVER 2000 database, it doesn't
seem like a corruption problem but I can't identify what's happening.
I'd appreciate any ideas.
On Wednesday we noticed that 85 records in a person table had been
added. These appear to be old versions of existing records. Most fields
are duplicates, a few are slightly different, some are null. For
example, Joe Smith now has 2 records instead of one, with different
phone numbers. The primary key is an autoincrement field and the new
records have normally incremented ID's. The user interface is a c# web
app using stored procedures. The 'last_update_date' field is blank on
the duplicate records and this is added by the stored procedure using
GetDate() so it doesn't appear that the application code or the SP
created the bad records.
On Thursday, a similar problem occurred on a different table but on a
larger scale - a 10,000 record table increased to 30,000, basically 3
copies of each record. This table also uses an autoincrement ID, this
time there is a gap of a couple of thousand berfore the new records
begin.
DBCC CheckDB finds no problems. A standard maintenance script (rebuild
indexes etc. ran without errors on the previous Sunday night. Nightly
full backups for the previous days are OK. Inside a firewall, only IT
folks have direct access, virus software running.
In the meantime, I've added some unique constraints on last/first names
in the person table to try to catch an error if it happens again. but
we are running out of ideas, it seems impossible but... any
suggestions appreciated.I would run a profiler over night and see if you can see anything unsual.
"GregC" wrote:

> We have a strange problem with an SQL SERVER 2000 database, it doesn't
> seem like a corruption problem but I can't identify what's happening.
> I'd appreciate any ideas.
> On Wednesday we noticed that 85 records in a person table had been
> added. These appear to be old versions of existing records. Most fields
> are duplicates, a few are slightly different, some are null. For
> example, Joe Smith now has 2 records instead of one, with different
> phone numbers. The primary key is an autoincrement field and the new
> records have normally incremented ID's. The user interface is a c# web
> app using stored procedures. The 'last_update_date' field is blank on
> the duplicate records and this is added by the stored procedure using
> GetDate() so it doesn't appear that the application code or the SP
> created the bad records.
> On Thursday, a similar problem occurred on a different table but on a
> larger scale - a 10,000 record table increased to 30,000, basically 3
> copies of each record. This table also uses an autoincrement ID, this
> time there is a gap of a couple of thousand berfore the new records
> begin.
> DBCC CheckDB finds no problems. A standard maintenance script (rebuild
> indexes etc. ran without errors on the previous Sunday night. Nightly
> full backups for the previous days are OK. Inside a firewall, only IT
> folks have direct access, virus software running.
> In the meantime, I've added some unique constraints on last/first names
> in the person table to try to catch an error if it happens again. but
> we are running out of ideas, it seems impossible but... any
> suggestions appreciated.
>|||Also, I would enable login auditing so you can see who is logging into the
instance. Then, I would grab a copy of Lumigent's Log Explorer and open up
the tranaction log to find the transactions that put the data into the
table. Many times they will be tagged with a network name or some other
form of a value to ID the workstation the command came from.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"SBAUER" <SBAUER@.discussions.microsoft.com> wrote in message
news:AAECCB2C-1143-4943-AF43-037BF273AF55@.microsoft.com...[vbcol=seagreen]
>I would run a profiler over night and see if you can see anything unsual.
>
> "GregC" wrote:
>

Database corruption problem?

We have a strange problem with an SQL SERVER 2000 database, it doesn't
seem like a corruption problem but I can't identify what's happening.
I'd appreciate any ideas.
On Wednesday we noticed that 85 records in a person table had been
added. These appear to be old versions of existing records. Most fields
are duplicates, a few are slightly different, some are null. For
example, Joe Smith now has 2 records instead of one, with different
phone numbers. The primary key is an autoincrement field and the new
records have normally incremented ID's. The user interface is a c# web
app using stored procedures. The 'last_update_date' field is blank on
the duplicate records and this is added by the stored procedure using
GetDate() so it doesn't appear that the application code or the SP
created the bad records.
On Thursday, a similar problem occurred on a different table but on a
larger scale - a 10,000 record table increased to 30,000, basically 3
copies of each record. This table also uses an autoincrement ID, this
time there is a gap of a couple of thousand berfore the new records
begin.
DBCC CheckDB finds no problems. A standard maintenance script (rebuild
indexes etc. ran without errors on the previous Sunday night. Nightly
full backups for the previous days are OK. Inside a firewall, only IT
folks have direct access, virus software running.
In the meantime, I've added some unique constraints on last/first names
in the person table to try to catch an error if it happens again. but
we are running out of ideas, it seems impossible but... any
suggestions appreciated.I would run a profiler over night and see if you can see anything unsual.
"GregC" wrote:
> We have a strange problem with an SQL SERVER 2000 database, it doesn't
> seem like a corruption problem but I can't identify what's happening.
> I'd appreciate any ideas.
> On Wednesday we noticed that 85 records in a person table had been
> added. These appear to be old versions of existing records. Most fields
> are duplicates, a few are slightly different, some are null. For
> example, Joe Smith now has 2 records instead of one, with different
> phone numbers. The primary key is an autoincrement field and the new
> records have normally incremented ID's. The user interface is a c# web
> app using stored procedures. The 'last_update_date' field is blank on
> the duplicate records and this is added by the stored procedure using
> GetDate() so it doesn't appear that the application code or the SP
> created the bad records.
> On Thursday, a similar problem occurred on a different table but on a
> larger scale - a 10,000 record table increased to 30,000, basically 3
> copies of each record. This table also uses an autoincrement ID, this
> time there is a gap of a couple of thousand berfore the new records
> begin.
> DBCC CheckDB finds no problems. A standard maintenance script (rebuild
> indexes etc. ran without errors on the previous Sunday night. Nightly
> full backups for the previous days are OK. Inside a firewall, only IT
> folks have direct access, virus software running.
> In the meantime, I've added some unique constraints on last/first names
> in the person table to try to catch an error if it happens again. but
> we are running out of ideas, it seems impossible but... any
> suggestions appreciated.
>|||Also, I would enable login auditing so you can see who is logging into the
instance. Then, I would grab a copy of Lumigent's Log Explorer and open up
the tranaction log to find the transactions that put the data into the
table. Many times they will be tagged with a network name or some other
form of a value to ID the workstation the command came from.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"SBAUER" <SBAUER@.discussions.microsoft.com> wrote in message
news:AAECCB2C-1143-4943-AF43-037BF273AF55@.microsoft.com...
>I would run a profiler over night and see if you can see anything unsual.
>
> "GregC" wrote:
>> We have a strange problem with an SQL SERVER 2000 database, it doesn't
>> seem like a corruption problem but I can't identify what's happening.
>> I'd appreciate any ideas.
>> On Wednesday we noticed that 85 records in a person table had been
>> added. These appear to be old versions of existing records. Most fields
>> are duplicates, a few are slightly different, some are null. For
>> example, Joe Smith now has 2 records instead of one, with different
>> phone numbers. The primary key is an autoincrement field and the new
>> records have normally incremented ID's. The user interface is a c# web
>> app using stored procedures. The 'last_update_date' field is blank on
>> the duplicate records and this is added by the stored procedure using
>> GetDate() so it doesn't appear that the application code or the SP
>> created the bad records.
>> On Thursday, a similar problem occurred on a different table but on a
>> larger scale - a 10,000 record table increased to 30,000, basically 3
>> copies of each record. This table also uses an autoincrement ID, this
>> time there is a gap of a couple of thousand berfore the new records
>> begin.
>> DBCC CheckDB finds no problems. A standard maintenance script (rebuild
>> indexes etc. ran without errors on the previous Sunday night. Nightly
>> full backups for the previous days are OK. Inside a firewall, only IT
>> folks have direct access, virus software running.
>> In the meantime, I've added some unique constraints on last/first names
>> in the person table to try to catch an error if it happens again. but
>> we are running out of ideas, it seems impossible but... any
>> suggestions appreciated.
>>