I am a database Engineer for a company that has relatively large and complex
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>|||bluefish wrote:
> I am a database Engineer for a company that has relatively large and complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity? If
> so how do you deal with the heightened complexity of back end updates when
> necessary?
The purpose of referential integrity is to implement business rules and
ensure that your database accurately and consistently models the real
world. The fact that your engineers have to ask this question suggests
that either they don't understand your business or that they don't care
about data integrity.
> On the other hand if you did not, how do we enforce accuracy?
Better ask the people who designed your systems without any
integrity...
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 21, 2012
Referential Integrity practices for complex Database
I am a database Engineer for a company that has relatively large and complex
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>sql
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>sql
Referential integrity different databases
I would like to apply referential integrity between two
tables that are not part of the same database.
Is this possible ?You can't do it with foreign key constraints. They are not allowed across
databases.
You can do it with triggers or by only allowing inserts, deletes, and
updates through stored procedures and enforcing the integrity in the
triggers or stored procs. Note that if you do this, there are still some
problematic situations. For example, the triggers and/or stored procs can't
prevent you from restoring one of the databases from last night's backup and
thus breaking the integrity.
Tom
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:61E4E107-5E5C-4D40-9575-D5B8E17D9F04@.microsoft.com...
>I would like to apply referential integrity between two
> tables that are not part of the same database.
> Is this possible ?
>sql
tables that are not part of the same database.
Is this possible ?You can't do it with foreign key constraints. They are not allowed across
databases.
You can do it with triggers or by only allowing inserts, deletes, and
updates through stored procedures and enforcing the integrity in the
triggers or stored procs. Note that if you do this, there are still some
problematic situations. For example, the triggers and/or stored procs can't
prevent you from restoring one of the databases from last night's backup and
thus breaking the integrity.
Tom
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:61E4E107-5E5C-4D40-9575-D5B8E17D9F04@.microsoft.com...
>I would like to apply referential integrity between two
> tables that are not part of the same database.
> Is this possible ?
>sql
Referential integrity different databases
I would like to apply referential integrity between two
tables that are not part of the same database.
Is this possible ?
You can't do it with foreign key constraints. They are not allowed across
databases.
You can do it with triggers or by only allowing inserts, deletes, and
updates through stored procedures and enforcing the integrity in the
triggers or stored procs. Note that if you do this, there are still some
problematic situations. For example, the triggers and/or stored procs can't
prevent you from restoring one of the databases from last night's backup and
thus breaking the integrity.
Tom
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:61E4E107-5E5C-4D40-9575-D5B8E17D9F04@.microsoft.com...
>I would like to apply referential integrity between two
> tables that are not part of the same database.
> Is this possible ?
>
tables that are not part of the same database.
Is this possible ?
You can't do it with foreign key constraints. They are not allowed across
databases.
You can do it with triggers or by only allowing inserts, deletes, and
updates through stored procedures and enforcing the integrity in the
triggers or stored procs. Note that if you do this, there are still some
problematic situations. For example, the triggers and/or stored procs can't
prevent you from restoring one of the databases from last night's backup and
thus breaking the integrity.
Tom
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:61E4E107-5E5C-4D40-9575-D5B8E17D9F04@.microsoft.com...
>I would like to apply referential integrity between two
> tables that are not part of the same database.
> Is this possible ?
>
Referential constraint between two tables in two databases
Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.
i think not possible
Thank u
Baba
Please remember to click "Mark as Answer" on this post if it helped you.
|||You should be able to set up the reference as a linked server (http://www.databasejournal.com/features/mssql/article.php/3085211,http://msdn2.microsoft.com/en-us/library/ms188279.aspx -- this is a sql server 2005 article, but earlier versions support linked servers as well)
I doubt that you can enforce the constraint declaritively, but I am quite sure you can do it using a trigger. Let us know if you need help with the triggers.
|||And what can be used when databases are sitted on the same server?
|||Then it's even easier because you don't need the linked server, you can simply reference the table on the other database by indluding the DB name, eg,
database2.dbo.sometable
Or (and this is the way I do it), create a view in database1 pointing to the table in database2
use database1
go
create view TableXyz as
select * from databse2.dbo.TableXyz
go
grant insert, update, select, delete on TableXyz to ...
go
Then, from within database1, all references to TableXyz really point to database2.dbo.TableXyz
I think the declaritive RI may work in this scenario but I'm not positive, but in any case a trigger will work
sqlTuesday, March 20, 2012
Referencing other databases in SQL Statements
Hello,
I have an access database and an SQL database and using data transformation services, i want to update the access database using the SQL data.
Can anyone tell me the syntax for referencing the access database?
Is it something like: [TABLENAME].dbo.FIELDNAME ?
Just to clarify, i have
Microsoft Access Database
Table 1 (UnitHistory)
SQL Database
Table 1 (UnitHistory)
How do i reference these seperately? I want to update the microsoft access database based on the SQL database data.
Eventually i'm trying to update an access database using the data held on my SQL server. Is DTS the best way for me to acomplish this or should i use another method?
Thanks guys1. look up in Sql book online for the syntax to create a linked server to Access.
2. do something like this to update Access:
Update A
set UnitHistory = S.UnitHistory
from [Access_linkedserver]...[Tb_name] A, [Sql_Tb_Name] S
where A.pkid=S.pkid
-- and
I have an access database and an SQL database and using data transformation services, i want to update the access database using the SQL data.
Can anyone tell me the syntax for referencing the access database?
Is it something like: [TABLENAME].dbo.FIELDNAME ?
Just to clarify, i have
Microsoft Access Database
Table 1 (UnitHistory)
SQL Database
Table 1 (UnitHistory)
How do i reference these seperately? I want to update the microsoft access database based on the SQL database data.
Eventually i'm trying to update an access database using the data held on my SQL server. Is DTS the best way for me to acomplish this or should i use another method?
Thanks guys1. look up in Sql book online for the syntax to create a linked server to Access.
2. do something like this to update Access:
Update A
set UnitHistory = S.UnitHistory
from [Access_linkedserver]...[Tb_name] A, [Sql_Tb_Name] S
where A.pkid=S.pkid
-- and
Referencing another Database in a query
I'm in query analyzer and I have 2 databases on this same server that I want
to reference in a query. How do I go about doing that? In other words, I
want to create an insert query which takes records from one database and
inserts them into a table in another database.
Thanks,
BrianAssuming that you have appropriate rights in both databases, use three
part naming:
INSERT INTO db1.owner.tablename (Column)
SELECT COLUMN
FROM db2.owner.tablename
HTH,
Stu|||Thanks - I thought I tried that, but I didn't include the 'owner'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1145403151.928575.228920@.i39g2000cwa.googlegroups.com...
> Assuming that you have appropriate rights in both databases, use three
> part naming:
> INSERT INTO db1.owner.tablename (Column)
> SELECT COLUMN
> FROM db2.owner.tablename
> HTH,
> Stu
>|||Do you have access to both databases?
In SQL each object has a four part identifier which enables cross-database
and cross-server access, provided that the user privileges also allow it.
More information under "Using Identifiers as Object Names" in Books Online.
ML
http://milambda.blogspot.com/|||Here:
http://msdn2.microsoft.com/en-us/library/ms187879.aspx
ML
http://milambda.blogspot.com/
to reference in a query. How do I go about doing that? In other words, I
want to create an insert query which takes records from one database and
inserts them into a table in another database.
Thanks,
BrianAssuming that you have appropriate rights in both databases, use three
part naming:
INSERT INTO db1.owner.tablename (Column)
SELECT COLUMN
FROM db2.owner.tablename
HTH,
Stu|||Thanks - I thought I tried that, but I didn't include the 'owner'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1145403151.928575.228920@.i39g2000cwa.googlegroups.com...
> Assuming that you have appropriate rights in both databases, use three
> part naming:
> INSERT INTO db1.owner.tablename (Column)
> SELECT COLUMN
> FROM db2.owner.tablename
> HTH,
> Stu
>|||Do you have access to both databases?
In SQL each object has a four part identifier which enables cross-database
and cross-server access, provided that the user privileges also allow it.
More information under "Using Identifiers as Object Names" in Books Online.
ML
http://milambda.blogspot.com/|||Here:
http://msdn2.microsoft.com/en-us/library/ms187879.aspx
ML
http://milambda.blogspot.com/
Friday, March 9, 2012
Reference 2 Databases in SQL Statement
I need to join a table with another table in a different database. Is this possible?
Assume Table1 is in DB1 and Table2 is in DB2.
Thanks!
Brian
Simple, just prefrace the table name with the database name.
e.g. SELECT * FROM database1..table1 JOIN database2..table2 ON ...
|||Brian:
It should simply be something like:
Code Snippet
select <columnList>
from Db1.schema1.Table1
join Db2.schema2.Table2
...
|||search for "linked servers"
Reference 2 Databases in SQL Statement
I need to join a table with another table in a different database. Is this possible?
Assume Table1 is in DB1 and Table2 is in DB2.
Thanks!
Brian
Simple, just prefrace the table name with the database name.
e.g. SELECT * FROM database1..table1 JOIN database2..table2 ON ...
|||Brian:
It should simply be something like:
Code Snippet
select <columnList>
from Db1.schema1.Table1
join Db2.schema2.Table2
...
|||search for "linked servers"
Wednesday, March 7, 2012
Re-Extenting
I need to move (actually copy) some 2000 databases over
to a faster disk array. How can I do it so that the
databases get re-extented if you know what I mean? If I
do a regular Windows Explorer type copy, it will get
unfragmented on the o/s level, right? But how can I get
any unneeded extents out of there?
Yes the Windows copy of the file ( from sp_detach_db) will do new disk
allocation and (if the contiguous disk space is available), you'll get
pretty allocations.
To remove unused disk space
DBCC shrinkdb
or
DBCC shrinkfile
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:2dbe601c46a86$5defb910$a501280a@.phx.gbl...
> I need to move (actually copy) some 2000 databases over
> to a faster disk array. How can I do it so that the
> databases get re-extented if you know what I mean? If I
> do a regular Windows Explorer type copy, it will get
> unfragmented on the o/s level, right? But how can I get
> any unneeded extents out of there?
to a faster disk array. How can I do it so that the
databases get re-extented if you know what I mean? If I
do a regular Windows Explorer type copy, it will get
unfragmented on the o/s level, right? But how can I get
any unneeded extents out of there?
Yes the Windows copy of the file ( from sp_detach_db) will do new disk
allocation and (if the contiguous disk space is available), you'll get
pretty allocations.
To remove unused disk space
DBCC shrinkdb
or
DBCC shrinkfile
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:2dbe601c46a86$5defb910$a501280a@.phx.gbl...
> I need to move (actually copy) some 2000 databases over
> to a faster disk array. How can I do it so that the
> databases get re-extented if you know what I mean? If I
> do a regular Windows Explorer type copy, it will get
> unfragmented on the o/s level, right? But how can I get
> any unneeded extents out of there?
Re-Extenting
I need to move (actually copy) some 2000 databases over
to a faster disk array. How can I do it so that the
databases get re-extented if you know what I mean? If I
do a regular Windows Explorer type copy, it will get
unfragmented on the o/s level, right? But how can I get
any unneeded extents out of there?Yes the Windows copy of the file ( from sp_detach_db) will do new disk
allocation and (if the contiguous disk space is available), you'll get
pretty allocations.
To remove unused disk space
DBCC shrinkdb
or
DBCC shrinkfile
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:2dbe601c46a86$5defb910$a501280a@.phx
.gbl...
> I need to move (actually copy) some 2000 databases over
> to a faster disk array. How can I do it so that the
> databases get re-extented if you know what I mean? If I
> do a regular Windows Explorer type copy, it will get
> unfragmented on the o/s level, right? But how can I get
> any unneeded extents out of there?
to a faster disk array. How can I do it so that the
databases get re-extented if you know what I mean? If I
do a regular Windows Explorer type copy, it will get
unfragmented on the o/s level, right? But how can I get
any unneeded extents out of there?Yes the Windows copy of the file ( from sp_detach_db) will do new disk
allocation and (if the contiguous disk space is available), you'll get
pretty allocations.
To remove unused disk space
DBCC shrinkdb
or
DBCC shrinkfile
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:2dbe601c46a86$5defb910$a501280a@.phx
.gbl...
> I need to move (actually copy) some 2000 databases over
> to a faster disk array. How can I do it so that the
> databases get re-extented if you know what I mean? If I
> do a regular Windows Explorer type copy, it will get
> unfragmented on the o/s level, right? But how can I get
> any unneeded extents out of there?
Re-Extenting
I need to move (actually copy) some 2000 databases over
to a faster disk array. How can I do it so that the
databases get re-extented if you know what I mean? If I
do a regular Windows Explorer type copy, it will get
unfragmented on the o/s level, right? But how can I get
any unneeded extents out of there?Yes the Windows copy of the file ( from sp_detach_db) will do new disk
allocation and (if the contiguous disk space is available), you'll get
pretty allocations.
To remove unused disk space
DBCC shrinkdb
or
DBCC shrinkfile
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:2dbe601c46a86$5defb910$a501280a@.phx.gbl...
> I need to move (actually copy) some 2000 databases over
> to a faster disk array. How can I do it so that the
> databases get re-extented if you know what I mean? If I
> do a regular Windows Explorer type copy, it will get
> unfragmented on the o/s level, right? But how can I get
> any unneeded extents out of there?
to a faster disk array. How can I do it so that the
databases get re-extented if you know what I mean? If I
do a regular Windows Explorer type copy, it will get
unfragmented on the o/s level, right? But how can I get
any unneeded extents out of there?Yes the Windows copy of the file ( from sp_detach_db) will do new disk
allocation and (if the contiguous disk space is available), you'll get
pretty allocations.
To remove unused disk space
DBCC shrinkdb
or
DBCC shrinkfile
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:2dbe601c46a86$5defb910$a501280a@.phx.gbl...
> I need to move (actually copy) some 2000 databases over
> to a faster disk array. How can I do it so that the
> databases get re-extented if you know what I mean? If I
> do a regular Windows Explorer type copy, it will get
> unfragmented on the o/s level, right? But how can I get
> any unneeded extents out of there?
Redundant Databases
I am trying to architect a system that will will have one or more machines
clustered together. The machines must share a common site database between
them. I assume that one machine at any one time should manage the site
database. If the machine that manages the site database fails, then another
system online, it doesn't matter which, should take over the site manager's
duties. It is acceptable if the first machine online would assume the site
manager's duties initally. The machines are essentually peer to peer, but
someone must manage the common database. Is SQL Server Replication the best
way to do this? Is there other, simpler or better ways to do this?
Database Mirroring or clustering are ideal for this. If you have an
intelligent client which will do automatic failover to the active node Log
Shipping or Clustering will also work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dbaechtel" <dbaechtel@.nospam.com> wrote in message
news:F754791D-082E-49C9-A8F6-7A205E9B4083@.microsoft.com...
>I am trying to architect a system that will will have one or more machines
> clustered together. The machines must share a common site database between
> them. I assume that one machine at any one time should manage the site
> database. If the machine that manages the site database fails, then
> another
> system online, it doesn't matter which, should take over the site
> manager's
> duties. It is acceptable if the first machine online would assume the site
> manager's duties initally. The machines are essentually peer to peer, but
> someone must manage the common database. Is SQL Server Replication the
> best
> way to do this? Is there other, simpler or better ways to do this?
|||If you are looking for pure hardware redundancy, clustering is a better
choice. If you want to make the data redundant as well, then you need to go
with an additional technology. In SQL Server 2000 you could use either log
shipping or replication. In SQL Server 2005, you can add database mirroring
to that list.
"dbaechtel" <dbaechtel@.nospam.com> wrote in message
news:F754791D-082E-49C9-A8F6-7A205E9B4083@.microsoft.com...
>I am trying to architect a system that will will have one or more machines
> clustered together. The machines must share a common site database between
> them. I assume that one machine at any one time should manage the site
> database. If the machine that manages the site database fails, then
> another
> system online, it doesn't matter which, should take over the site
> manager's
> duties. It is acceptable if the first machine online would assume the site
> manager's duties initally. The machines are essentually peer to peer, but
> someone must manage the common database. Is SQL Server Replication the
> best
> way to do this? Is there other, simpler or better ways to do this?
clustered together. The machines must share a common site database between
them. I assume that one machine at any one time should manage the site
database. If the machine that manages the site database fails, then another
system online, it doesn't matter which, should take over the site manager's
duties. It is acceptable if the first machine online would assume the site
manager's duties initally. The machines are essentually peer to peer, but
someone must manage the common database. Is SQL Server Replication the best
way to do this? Is there other, simpler or better ways to do this?
Database Mirroring or clustering are ideal for this. If you have an
intelligent client which will do automatic failover to the active node Log
Shipping or Clustering will also work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dbaechtel" <dbaechtel@.nospam.com> wrote in message
news:F754791D-082E-49C9-A8F6-7A205E9B4083@.microsoft.com...
>I am trying to architect a system that will will have one or more machines
> clustered together. The machines must share a common site database between
> them. I assume that one machine at any one time should manage the site
> database. If the machine that manages the site database fails, then
> another
> system online, it doesn't matter which, should take over the site
> manager's
> duties. It is acceptable if the first machine online would assume the site
> manager's duties initally. The machines are essentually peer to peer, but
> someone must manage the common database. Is SQL Server Replication the
> best
> way to do this? Is there other, simpler or better ways to do this?
|||If you are looking for pure hardware redundancy, clustering is a better
choice. If you want to make the data redundant as well, then you need to go
with an additional technology. In SQL Server 2000 you could use either log
shipping or replication. In SQL Server 2005, you can add database mirroring
to that list.
"dbaechtel" <dbaechtel@.nospam.com> wrote in message
news:F754791D-082E-49C9-A8F6-7A205E9B4083@.microsoft.com...
>I am trying to architect a system that will will have one or more machines
> clustered together. The machines must share a common site database between
> them. I assume that one machine at any one time should manage the site
> database. If the machine that manages the site database fails, then
> another
> system online, it doesn't matter which, should take over the site
> manager's
> duties. It is acceptable if the first machine online would assume the site
> manager's duties initally. The machines are essentually peer to peer, but
> someone must manage the common database. Is SQL Server Replication the
> best
> way to do this? Is there other, simpler or better ways to do this?
Monday, February 20, 2012
Reduce File Size
Everyone,
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.
It will probably be faster to backup the database and zip the file. But your developers need then
20GB to restore the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB each). I need to have
> copies of these available for my developers. They don't need the whole 20GB so what I do is delete
> all the records from the DB except for, say, one month worth of records. Then I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me having to buy a faster
> server?
> Thank you.
>
|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>
|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE command, and pray that the
page with the highest address isn't too high up in the database file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.
It will probably be faster to backup the database and zip the file. But your developers need then
20GB to restore the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB each). I need to have
> copies of these available for my developers. They don't need the whole 20GB so what I do is delete
> all the records from the DB except for, say, one month worth of records. Then I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me having to buy a faster
> server?
> Thank you.
>
|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>
|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE command, and pray that the
page with the highest address isn't too high up in the database file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>
Reduce File Size
Everyone,
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.It will probably be faster to backup the database and zip the file. But your
developers need then
20GB to restore the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx
.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB ea
ch). I need to have
> copies of these available for my developers. They don't need the whole 20G
B so what I do is delete
> all the records from the DB except for, say, one month worth of records. T
hen I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it
takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me
having to buy a faster
> server?
> Thank you.
>|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE com
mand, and pray that the
page with the highest address isn't too high up in the database file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx
.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.It will probably be faster to backup the database and zip the file. But your
developers need then
20GB to restore the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx
.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB ea
ch). I need to have
> copies of these available for my developers. They don't need the whole 20G
B so what I do is delete
> all the records from the DB except for, say, one month worth of records. T
hen I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it
takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me
having to buy a faster
> server?
> Thank you.
>|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE com
mand, and pray that the
page with the highest address isn't too high up in the database file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx
.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>
Reduce File Size
Everyone,
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.It will probably be faster to backup the database and zip the file. But your developers need then
20GB to restore the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB each). I need to have
> copies of these available for my developers. They don't need the whole 20GB so what I do is delete
> all the records from the DB except for, say, one month worth of records. Then I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me having to buy a faster
> server?
> Thank you.
>|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>> Everyone,
>> Here is what I am looking for. I have production databases (around 20GB
>> each). I need to have copies of these available for my developers. They
>> don't need the whole 20GB so what I do is delete all the records from the
>> DB except for, say, one month worth of records. Then I use the shrink
>> command to reduce the file size to few hundred MB. It works fine except
>> it takes forever on my lab system to shrink the DB. Is there any faster
>> method to do this without me having to buy a faster server?
>> Thank you.
>|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE command, and pray that the
page with the highest address isn't too high up in the database file...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> It will probably be faster to backup the database and zip the file. But your developers need then
>> 20GB to restore the database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
>> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>> Everyone,
>> Here is what I am looking for. I have production databases (around 20GB each). I need to have
>> copies of these available for my developers. They don't need the whole 20GB so what I do is
>> delete all the records from the DB except for, say, one month worth of records. Then I use the
>> shrink command to reduce the file size to few hundred MB. It works fine except it takes forever
>> on my lab system to shrink the DB. Is there any faster method to do this without me having to
>> buy a faster server?
>> Thank you.
>>
>
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.It will probably be faster to backup the database and zip the file. But your developers need then
20GB to restore the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB each). I need to have
> copies of these available for my developers. They don't need the whole 20GB so what I do is delete
> all the records from the DB except for, say, one month worth of records. Then I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me having to buy a faster
> server?
> Thank you.
>|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>> Everyone,
>> Here is what I am looking for. I have production databases (around 20GB
>> each). I need to have copies of these available for my developers. They
>> don't need the whole 20GB so what I do is delete all the records from the
>> DB except for, say, one month worth of records. Then I use the shrink
>> command to reduce the file size to few hundred MB. It works fine except
>> it takes forever on my lab system to shrink the DB. Is there any faster
>> method to do this without me having to buy a faster server?
>> Thank you.
>|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE command, and pray that the
page with the highest address isn't too high up in the database file...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> It will probably be faster to backup the database and zip the file. But your developers need then
>> 20GB to restore the database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
>> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>> Everyone,
>> Here is what I am looking for. I have production databases (around 20GB each). I need to have
>> copies of these available for my developers. They don't need the whole 20GB so what I do is
>> delete all the records from the DB except for, say, one month worth of records. Then I use the
>> shrink command to reduce the file size to few hundred MB. It works fine except it takes forever
>> on my lab system to shrink the DB. Is there any faster method to do this without me having to
>> buy a faster server?
>> Thank you.
>>
>
reduce databse size
Hi, my database size has grown out of control and I need help with the
following issues. (I am very new to databases)
following issues. (I am very new to databases)
I am storing financial tick data in one of the tables and after two months
the database has grown to 30GB. I do not need a permanent record of this
tick data after it has been processed and tried to remove all rows from this
table (delete from Tickdata), however sql does not take kindly to removing
millions of rows and the operation seams to time out. The only solution I
could come up with was to delete the table.
Secondly, after managing to clear out these tables I have noticed that the
database size is still 30GB, despite 29GB being available. Is there any way
to reduce the size of the database from 30GB. I tried the shrink database
option but it does not do anything. Any ideas?
Thanks.Have you tried a TRUNCATE Tickdata?
Look at dbcc shrinkfile in the Books online in order to make the db smaller.
"Fred" <Fred@.hotmail.com> wrote in message
news:4178493c$1@.duster.adelaide.on.net...
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from
this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.
> Secondly, after managing to clear out these tables I have noticed that the
> database size is still 30GB, despite 29GB being available. Is there any
way
> to reduce the size of the database from 30GB. I tried the shrink database
> option but it does not do anything. Any ideas?
> Thanks.|||"Fred" <Fred@.hotmail.com> wrote in message news:<4178493c$1@.duster.adelaide.on.net>...
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.
> Secondly, after managing to clear out these tables I have noticed that the
> database size is still 30GB, despite 29GB being available. Is there any way
> to reduce the size of the database from 30GB. I tried the shrink database
> option but it does not do anything. Any ideas?
> Thanks.
Here's what I use:
backup log SQLData with TRUNCATE_ONLY
dbcc shrinkfile (2,20,TRUNCATEONLY)
The first paramater in shrinkfile is the file number. To get a list
of filenumbers, issue the following query:
select * from sysfiles
The 2nd parameter is the number of pages. If you specify a number
less than the database uses, it will use the number of pages that it
requires.
"SQLData" is the name of the database. Replace that with your
database.
I do these to help manage log size. You may need to toy around with
these to get exactly what you're looking for.|||On Fri, 22 Oct 2004, Fred wrote:
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.
The reason it times out is most likely the logging of each delete. Most
operations in SQL Server are logged (I'm not qualified to give a complete
explanation of logging, but in short as it pertains to this problem it has
to do with being able to to restore the database to a previous point and
also it is much slower than a logged operation, plus log size can grow
very large depending on your setup) The truncate table command (see BOL)
is much like a delete from without any where criteria, except that in most
cases this is a non logged operation and will thus complete much much
faster (this non logged behavior however depends on some database options
- the recovery mode in SQL 2000, some other option I can't remember in SQL
7.0. See BOL). Depending on your backup solution non logged operations may
or may not be appropriate.
Alternately, you can only delete a smaller subset of rows at once and
iterate until everything you want deleted has been deleted, which is
probably less efficient overall but may help with any timeout / GUI
responsivness issues you are having. I took this approach once for a quick
and dirty solution to a similar problem.
Dave|||Metal Dave (metal@.spam.spam) writes:
> The truncate table command (see BOL) is much like a delete from without
> any where criteria, except that in most cases this is a non logged
> operation and will thus complete much much faster
TRUNCATE TABLE is logged, however minimally. When you delete rows, each
row is logged. With TRUNCATE TABLE, only the page deallocation is logged.
Note that TRUNCATE TABLE is not permitted on tables referenced by foreign
keys.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 26 Oct 2004, Erland Sommarskog wrote:
> Metal Dave (metal@.spam.spam) writes:
> > The truncate table command (see BOL) is much like a delete from without
> > any where criteria, except that in most cases this is a non logged
> > operation and will thus complete much much faster
> TRUNCATE TABLE is logged, however minimally. When you delete rows, each
> row is logged. With TRUNCATE TABLE, only the page deallocation is logged.
> Note that TRUNCATE TABLE is not permitted on tables referenced by foreign
> keys.
Oops, sorry for oversimplification. I was just trying to stick with "ask a
question, answer a question" much like the "take a penny, leave a penny"
at your local 7-11. Thanks for clarifying. The OP may find it useful
anyway though, as it's still much faster and advanced restore didn't not
sound like a priority to him/her.
Dave
Subscribe to:
Posts (Atom)