Databases updating simultaneously

Hi,

I have managed to create a second copy of my "live" database, for
software testing purposes.

Inspecting the properties of the new database, everything seems in
order. The logical file name is the same, which I believe is fine, and
the physical database (and log file name) is different.

However, despite the fact that there is no application currently
accessing the "testing" copy, both databases are seemingly being
updated simultanously. I can tell this from the physical file sizes on
the server, which are identical, and growing at the same rate.

Does anyone have any suggestions why this might be happening - and how
I can stop it?

Thanks in anticipation!

PhilRS200Phil (philsowden@.dataservicesltd.co.uk) writes:

Quote:

Originally Posted by

I have managed to create a second copy of my "live" database, for
software testing purposes.
>
Inspecting the properties of the new database, everything seems in
order. The logical file name is the same, which I believe is fine, and
the physical database (and log file name) is different.
>
However, despite the fact that there is no application currently
accessing the "testing" copy, both databases are seemingly being
updated simultanously. I can tell this from the physical file sizes on
the server, which are identical, and growing at the same rate.
>
Does anyone have any suggestions why this might be happening - and how
I can stop it?

It sounds funny to me that you can see the files grow. Autogrow events
on live databases should be rare events and not happen frequently. You
create them with a reasonable initial size, and then you preferrably
increase then while you have a maintenance window. Autogrow during
production should be avoided, as it could cause the database to be
inaccessible while autogrow is in progress.

To tell why your databases grow in parallel would require more knowledge
about your server. Here we are left to wild guesses. Maybe you set up
replication between the databases?

sp_who can tell you if there are any processes in the database at all.
You can use SQL Server Profiler to see if there is any action in the
server.

Which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

database: mssql

I am conducting a database design and I managed to reach the 3rd normal form. My question is, if i change my primary key, for example '0001' to '0002', is it possible to propagate the change/update to the other linked table.
if you require more info, please let me know. Thank you in advance.If I understand correctly then yes -
If you have 2 tables:
Customer - With Field CustomerId
And Order with Field OrderId and CustomerId
And you want to change the CustomerId Field then you would do the following:
SET IDENTITY_INSERT [dbo].[Customer] ON
GO
ALTER TABLE [dbo].[Order] NOCHECK CONSTRAINT ALL
GO
-- Add a column to hold the old ID
ALTER TABLE CUSTOMER WITH CHECK ADD OldID INT
GO
UPDATE CUSTOMER SET OldID = CustomerId
GO
-- Add Code to Change CustomerId Here
UPDATE O
Set O.CustomerId = C.CustomerId
From Order O
INNER JOIN Customer C
ON O.CustomerId = C.OldId
GO
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT ALL
GO
|||

You can use declarative referential integrity and cascade updates popagate the changes. Sample code below.

Regards

set nocount on
go

use tempdb
go

create table parent(p int constraint p_pk primary key)
go

create table child(
c int constraint c_pk primary key
, p int constraint c_fk foreign key references parent(p) on update cascade
)
go

insert into parent values (1)
go
insert into child values (1,1)
go

update parent set p = 2 where p = 1
go

select * from child
go

drop table child
go
drop table parent
go

|||Hi,
In addition to that. If your using sql2k you could just create a diagram and link the tables that you want in Enterprise Manager...
cheers,
Paul June A. Domag

Database with empty name

I somehow managed to get a database object with an empty name into one of my instances of SQL Server. I can't delete it or otherwise work with it (I've tried renaming it so I could delete it) without getting an error message because of the empty name. I've tried these things (as well as a "Drop Database" query with no name, which I didn't expect to work and it didn't) from both the SQL 2000 and 2005 environments. Has anyone come across this before? I don't suppose this database is hurting anything but I'd still like to get rid of it. The objects in it make it appear to be a copy of the Master database - it also doesn't show up where I would expect it to in the Data folder for this instance.

Thanks,

Dave

Dave,

I never come across this problem. Whenever I have to drop or delete a database, I relatively get that done with ease. In case the database name is empty, which is very weird case, I will suggest you to try deleting using Enterprise manager. It would have been more easy to address your issue if you could specify what error message did you get when you tried renaming or using "Drop Database" query. But I am pretty sure you can delete your database using Enterprise Manager. However, you have to make sure before deleting your database that the database is not currently running. Most easy way to do that is to stop the Sql Server Service Manager from your task bar.

I hope it works.

Ujjwal Kaji

|||

Thanks for the reply. I tried deleting it in Enterprise Manager and get the same Error Message:

Error 21776: [SQL-DMO]The name '' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

Thanks for any insight into this.

-Dave

|||

Hi,

before messing with the system tables, try to do a:

1. DROP DATABASE []

2. sp_renamedb '','MyDatabasetoDelete'

or (posted by Tom Moreau)

3. (can vary to SQL server 2k5, because sysdatabases is sys.databases now)

sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases
set
name = 'MyDB'
where
name = ''
go
sp_configure 'allow', 1
go
reconfigure with override
go

Stop and start SQL Server.

Check after each steps if the database is till existing, sometime SQl Server doesn′t know how to handle return code for this strange situation and gives back a weird error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the reply. I guess somehow it was a system database because your 3rd suggestion worked perfectly. Very strange...

Thanks for your help.

Dave

|||Thank you for the post, option 3 solved my empty database name as well.

Database with empty name

I somehow managed to get a database object with an empty name into one of my instances of SQL Server. I can't delete it or otherwise work with it (I've tried renaming it so I could delete it) without getting an error message because of the empty name. I've tried these things (as well as a "Drop Database" query with no name, which I didn't expect to work and it didn't) from both the SQL 2000 and 2005 environments. Has anyone come across this before? I don't suppose this database is hurting anything but I'd still like to get rid of it. The objects in it make it appear to be a copy of the Master database - it also doesn't show up where I would expect it to in the Data folder for this instance.

Thanks,

Dave

Dave,

I never come across this problem. Whenever I have to drop or delete a database, I relatively get that done with ease. In case the database name is empty, which is very weird case, I will suggest you to try deleting using Enterprise manager. It would have been more easy to address your issue if you could specify what error message did you get when you tried renaming or using "Drop Database" query. But I am pretty sure you can delete your database using Enterprise Manager. However, you have to make sure before deleting your database that the database is not currently running. Most easy way to do that is to stop the Sql Server Service Manager from your task bar.

I hope it works.

Ujjwal Kaji

|||

Thanks for the reply. I tried deleting it in Enterprise Manager and get the same Error Message:

Error 21776: [SQL-DMO]The name '' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

Thanks for any insight into this.

-Dave

|||

Hi,

before messing with the system tables, try to do a:

1. DROP DATABASE []

2. sp_renamedb '','MyDatabasetoDelete'

or (posted by Tom Moreau)

3. (can vary to SQL server 2k5, because sysdatabases is sys.databases now)

sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases
set
name = 'MyDB'
where
name = ''
go
sp_configure 'allow', 1
go
reconfigure with override
go

Stop and start SQL Server.

Check after each steps if the database is till existing, sometime SQl Server doesn′t know how to handle return code for this strange situation and gives back a weird error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the reply. I guess somehow it was a system database because your 3rd suggestion worked perfectly. Very strange...

Thanks for your help.

Dave

|||Thank you for the post, option 3 solved my empty database name as well.

Database trigger to run managed C# code

Hi there,

Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code.

How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?

The following article gave me some good insight on how to integrate the .NET CLR with SQL Server 2005.

http://msdn2.microsoft.com/en-us/library/ms345136.aspx

I can now create triggers to run managed C# code. However, the CLR is limited because I cannot reference the System.Web.HttpApplicationState object or anything to do with HTTP requests; so I can't update my Application collection.

However, the CLR does let me access System.Web.Services. So I should be able to make a web service call to update the variables. More to come on this....

|||

I solved my issue. Seems like I was going down the wrong path. The SqlCacheDependency class is what I needed. I can set up a cache dependency on a SQL table so that when values in that table change the associated cache value will be invalid.

Here's a good article on SqlCacheDependency ->http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html?page=last

This increases performance significantly! Awesome!