Is it better to drop temporary tables at the end of a stored procedure?
I have heard somewhere that there can be stored proc recompilation issues which affect performance when you explicitly drop temp tables?
Will SQL Server "Clean Up" once the stored proc is out of scope or should I "do it myself".
Which method has known performance benefits?
CheersHi,
If resources arent an issue on your sqlserver you dont need to drop temps yourself. sqlserver will drop them when the sp is done.
Be careful though when working with temps, more than five updates to a temp table could force sqlserver to recomple. Any Set statements in the sp will also force sqlserver to recompile(SET NOCOUNT ON for example).
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Thursday, March 29, 2012
Dropping tempe tables
My query creates a new temp table every time a report is run. I need to check
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.
Dropping tables from a article
Just a quick question , when you have a article is their any way to drop a table from a article.
Don't know if it important but I'm using merge replication.Ok....what's an article?|||Originally posted by Brett Kaiser
Ok....what's an article?
Sorry I suppose that should have been described as how do I drop a article from a publication. When the article is a table, or is a table a group of articles. Not hundered percent on the Publishing Metaphor.
A article is part of a publication that is part use as part of replication.
Any suggestions?|||Sorry...haven't done replication, but looking up in bol, it looks like the correct terminology...
Did you check out BOL?
To delete an article
Note Deleting articles from publications that have subscriptions is not allowed. To delete an article, you must first delete all subscriptions to the publication.
At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, right-click the publication, and then click Properties.
Click the Articles tab, select an article to delete, and then clear the check box next to the article to delete.
1988-2000 Microsoft Corporation. All Rights Reserved.|||I don't think there is. What I usually do is drop and recreate the article if I want to make a change.|||Originally posted by joejcheng
I don't think there is. What I usually do is drop and recreate the article if I want to make a change.
Brill, Just wanted to be sure. Thankssql
Don't know if it important but I'm using merge replication.Ok....what's an article?|||Originally posted by Brett Kaiser
Ok....what's an article?
Sorry I suppose that should have been described as how do I drop a article from a publication. When the article is a table, or is a table a group of articles. Not hundered percent on the Publishing Metaphor.
A article is part of a publication that is part use as part of replication.
Any suggestions?|||Sorry...haven't done replication, but looking up in bol, it looks like the correct terminology...
Did you check out BOL?
To delete an article
Note Deleting articles from publications that have subscriptions is not allowed. To delete an article, you must first delete all subscriptions to the publication.
At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, right-click the publication, and then click Properties.
Click the Articles tab, select an article to delete, and then clear the check box next to the article to delete.
1988-2000 Microsoft Corporation. All Rights Reserved.|||I don't think there is. What I usually do is drop and recreate the article if I want to make a change.|||Originally posted by joejcheng
I don't think there is. What I usually do is drop and recreate the article if I want to make a change.
Brill, Just wanted to be sure. Thankssql
dropping table from publication
I have setup transactional replication and its going fine since some time in
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?
Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?
|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>
|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
|||Have you seen this article?
http://support.microsoft.com/default...b;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
left over after replication was removed?
Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?
|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>
|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
|||Have you seen this article?
http://support.microsoft.com/default...b;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>|||Have you seen this article?
http://support.microsoft.com/defaul...kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>|||Have you seen this article?
http://support.microsoft.com/defaul...kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> > What is the best way to drop the 100's of %conlict% tables from a database
> > left over after replication was removed?
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>|||Have you seen this article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> > What is the best way to drop the 100's of %conlict% tables from a database
> > left over after replication was removed?
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>|||Have you seen this article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>
Dropping Indexes
I'm doing some bulk inserting into various tables, and I'm dropping all the
indexes first, and rebuilding them after the data load. One of the tables
has several nonclustered indexes + a clustered index. My question is
simple: does the order I drop the indexes make a difference in the speed
with which they are dropped? My thought is that the clustered index should
be dropped last. Any suggestions?
ThanksFrom the Books Online topic "Dropping Indexes"
Dropping a clustered index can take time because in addition to dropping the
clustered index, all nonclustered indexes on the table must be rebuilt to
replace the clustered index keys with row pointers to the heap. When you
drop all indexes on a table, drop the nonclustered indexes first and the
clustered index last. That way, no indexes have to be rebuilt.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Mike C#" <xyz@.xyz.com> wrote in message
news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> I'm doing some bulk inserting into various tables, and I'm dropping all
> the indexes first, and rebuilding them after the data load. One of the
> tables has several nonclustered indexes + a clustered index. My question
> is simple: does the order I drop the indexes make a difference in the
> speed with which they are dropped? My thought is that the clustered index
> should be dropped last. Any suggestions?
> Thanks
>|||That's what I figured, just wanted to make sure. Thanks Gail.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:Odo3RpbhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> From the Books Online topic "Dropping Indexes"
> Dropping a clustered index can take time because in addition to dropping
> the clustered index, all nonclustered indexes on the table must be rebuilt
> to replace the clustered index keys with row pointers to the heap. When
> you drop all indexes on a table, drop the nonclustered indexes first and
> the clustered index last. That way, no indexes have to be rebuilt.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
>sql
indexes first, and rebuilding them after the data load. One of the tables
has several nonclustered indexes + a clustered index. My question is
simple: does the order I drop the indexes make a difference in the speed
with which they are dropped? My thought is that the clustered index should
be dropped last. Any suggestions?
ThanksFrom the Books Online topic "Dropping Indexes"
Dropping a clustered index can take time because in addition to dropping the
clustered index, all nonclustered indexes on the table must be rebuilt to
replace the clustered index keys with row pointers to the heap. When you
drop all indexes on a table, drop the nonclustered indexes first and the
clustered index last. That way, no indexes have to be rebuilt.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Mike C#" <xyz@.xyz.com> wrote in message
news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> I'm doing some bulk inserting into various tables, and I'm dropping all
> the indexes first, and rebuilding them after the data load. One of the
> tables has several nonclustered indexes + a clustered index. My question
> is simple: does the order I drop the indexes make a difference in the
> speed with which they are dropped? My thought is that the clustered index
> should be dropped last. Any suggestions?
> Thanks
>|||That's what I figured, just wanted to make sure. Thanks Gail.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:Odo3RpbhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> From the Books Online topic "Dropping Indexes"
> Dropping a clustered index can take time because in addition to dropping
> the clustered index, all nonclustered indexes on the table must be rebuilt
> to replace the clustered index keys with row pointers to the heap. When
> you drop all indexes on a table, drop the nonclustered indexes first and
> the clustered index last. That way, no indexes have to be rebuilt.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
>sql
DSN or DSNless connection?
I'm starting to move my VB6 application from Access to SQL Server. In the
beginning I'm going to use Access database with tables linked to SQL Server
tables. Then form by form are going to be moved to direct SQL Server
connection. When all of them are moved I delete Access. I have very weak
experience with SQL Server, so I even do not know advantages and
disadvantages of DSN and DSN less connections. I know that I have to learn
everything and I'm going to do that. But for the beginning could somebody
give me some suggestions regarding connection. What is better ? DSN or DSN
less?
Thank you
Vladhttp://groups.google.com/groups?q=dsn+vs+dsn-less
-oj
http://www.rac4sql.net
"Vlad" <vovan.c@.verizon.net> wrote in message
news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
> I'm starting to move my VB6 application from Access to SQL Server. In the
> beginning I'm going to use Access database with tables linked to SQL
Server
> tables. Then form by form are going to be moved to direct SQL Server
> connection. When all of them are moved I delete Access. I have very weak
> experience with SQL Server, so I even do not know advantages and
> disadvantages of DSN and DSN less connections. I know that I have to learn
> everything and I'm going to do that. But for the beginning could somebody
> give me some suggestions regarding connection. What is better ? DSN or DSN
> less?
> Thank you
> Vlad
>|||Thank you very much
Vlad
"oj" <nospam_ojngo@.home.com> wrote in message
news:OYlwjdlAEHA.2448@.TK2MSFTNGP12.phx.gbl...
> http://groups.google.com/groups?q=dsn+vs+dsn-less
> --
> -oj
> http://www.rac4sql.net
>
> "Vlad" <vovan.c@.verizon.net> wrote in message
> news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
the
> Server
learn
somebody
DSN
>
beginning I'm going to use Access database with tables linked to SQL Server
tables. Then form by form are going to be moved to direct SQL Server
connection. When all of them are moved I delete Access. I have very weak
experience with SQL Server, so I even do not know advantages and
disadvantages of DSN and DSN less connections. I know that I have to learn
everything and I'm going to do that. But for the beginning could somebody
give me some suggestions regarding connection. What is better ? DSN or DSN
less?
Thank you
Vladhttp://groups.google.com/groups?q=dsn+vs+dsn-less
-oj
http://www.rac4sql.net
"Vlad" <vovan.c@.verizon.net> wrote in message
news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
> I'm starting to move my VB6 application from Access to SQL Server. In the
> beginning I'm going to use Access database with tables linked to SQL
Server
> tables. Then form by form are going to be moved to direct SQL Server
> connection. When all of them are moved I delete Access. I have very weak
> experience with SQL Server, so I even do not know advantages and
> disadvantages of DSN and DSN less connections. I know that I have to learn
> everything and I'm going to do that. But for the beginning could somebody
> give me some suggestions regarding connection. What is better ? DSN or DSN
> less?
> Thank you
> Vlad
>|||Thank you very much
Vlad
"oj" <nospam_ojngo@.home.com> wrote in message
news:OYlwjdlAEHA.2448@.TK2MSFTNGP12.phx.gbl...
> http://groups.google.com/groups?q=dsn+vs+dsn-less
> --
> -oj
> http://www.rac4sql.net
>
> "Vlad" <vovan.c@.verizon.net> wrote in message
> news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
the
> Server
learn
somebody
DSN
>
Tuesday, March 27, 2012
dropping corrupt tables?
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?
Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?
|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?
Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?
|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?
dropping corrupt tables?
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=101248
8
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even mor
e
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?sql
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=101248
8
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even mor
e
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?sql
dropping corrupt tables?
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?
dropping articles in a running merge replication
hi all, i need to drop some tables from a merge replication, i have a
publisher that is also the distrubuitor, and 2 subscribers, i have 2 tables
that i no longer need to replicate, my articles are set to on name conflits
leave the current table unchanged. Whats the best method of dropping the
tables i dont need, if i have to drop the subscriptions, can my clients at
the subscribers continue to use there system ?. Down time is a serious issue
to my clients lol..
Thanks Andrew
can't be done. You will have to drop the subscribers and do a nosync. Any
work your subscribers are doing will be lost.
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:u8yAO4HKFHA.724@.TK2MSFTNGP10.phx.gbl...
> hi all, i need to drop some tables from a merge replication, i have a
> publisher that is also the distrubuitor, and 2 subscribers, i have 2
tables
> that i no longer need to replicate, my articles are set to on name
conflits
> leave the current table unchanged. Whats the best method of dropping the
> tables i dont need, if i have to drop the subscriptions, can my clients at
> the subscribers continue to use there system ?. Down time is a serious
issue
> to my clients lol..
> Thanks Andrew
>
sql
publisher that is also the distrubuitor, and 2 subscribers, i have 2 tables
that i no longer need to replicate, my articles are set to on name conflits
leave the current table unchanged. Whats the best method of dropping the
tables i dont need, if i have to drop the subscriptions, can my clients at
the subscribers continue to use there system ?. Down time is a serious issue
to my clients lol..
Thanks Andrew
can't be done. You will have to drop the subscribers and do a nosync. Any
work your subscribers are doing will be lost.
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:u8yAO4HKFHA.724@.TK2MSFTNGP10.phx.gbl...
> hi all, i need to drop some tables from a merge replication, i have a
> publisher that is also the distrubuitor, and 2 subscribers, i have 2
tables
> that i no longer need to replicate, my articles are set to on name
conflits
> leave the current table unchanged. Whats the best method of dropping the
> tables i dont need, if i have to drop the subscriptions, can my clients at
> the subscribers continue to use there system ?. Down time is a serious
issue
> to my clients lol..
> Thanks Andrew
>
sql
Dropping and Recreating Tables
We have a job that runs every 30 minutes dropping and recreating a table with
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?
Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?
|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>
>
|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?
Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?
|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>
>
|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
Dropping and Recreating Tables
We have a job that runs every 30 minutes dropping and recreating a table wit
h
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/o
r
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will thi
s
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>sql
h
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/o
r
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will thi
s
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>sql
Dropping and Recreating Tables
We have a job that runs every 30 minutes dropping and recreating a table with
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> > We have a job that runs every 30 minutes dropping and recreating a table
> > with
> > updated information. Besides the fact that I am trying to get rid of this
> > needless process, the question I have is:
> >
> > When this job is running and attempting to drop and recreate the table
> > will
> > it not be able to complete if someone has a connection to the database
> > and/or
> > is running a report off the table that SQL Server is attempting to drop
> > and
> > recreate? If there is a lock on this table from someone using it, is there
> > a
> > way I can stop this from happening?
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>> Connections to the database itself will not matter however all locks
>> involving the table would need to be released before the DROP command
>> executes. In the event that someone is using the table, the drop command
>> will wait until the locks are released or your connection times out
>> (which
>> ever comes first).
>> --
>> --Brian
>> (Please reply to the newsgroups only.)
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>> > We have a job that runs every 30 minutes dropping and recreating a
>> > table
>> > with
>> > updated information. Besides the fact that I am trying to get rid of
>> > this
>> > needless process, the question I have is:
>> >
>> > When this job is running and attempting to drop and recreate the table
>> > will
>> > it not be able to complete if someone has a connection to the database
>> > and/or
>> > is running a report off the table that SQL Server is attempting to drop
>> > and
>> > recreate? If there is a lock on this table from someone using it, is
>> > there
>> > a
>> > way I can stop this from happening?
>>
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> > We have a job that runs every 30 minutes dropping and recreating a table
> > with
> > updated information. Besides the fact that I am trying to get rid of this
> > needless process, the question I have is:
> >
> > When this job is running and attempting to drop and recreate the table
> > will
> > it not be able to complete if someone has a connection to the database
> > and/or
> > is running a report off the table that SQL Server is attempting to drop
> > and
> > recreate? If there is a lock on this table from someone using it, is there
> > a
> > way I can stop this from happening?
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>> Connections to the database itself will not matter however all locks
>> involving the table would need to be released before the DROP command
>> executes. In the event that someone is using the table, the drop command
>> will wait until the locks are released or your connection times out
>> (which
>> ever comes first).
>> --
>> --Brian
>> (Please reply to the newsgroups only.)
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>> > We have a job that runs every 30 minutes dropping and recreating a
>> > table
>> > with
>> > updated information. Besides the fact that I am trying to get rid of
>> > this
>> > needless process, the question I have is:
>> >
>> > When this job is running and attempting to drop and recreate the table
>> > will
>> > it not be able to complete if someone has a connection to the database
>> > and/or
>> > is running a report off the table that SQL Server is attempting to drop
>> > and
>> > recreate? If there is a lock on this table from someone using it, is
>> > there
>> > a
>> > way I can stop this from happening?
>>
Dropping an old foreign key restraint?
This forum looked like the only one where I may get an answer to this question.
I built a database with a set of tables in Sql Server 2005 sp2.
I later had to refactor the data which led to removing one table, renaming another table, and moving some data around.
Now when I attempt to write my test code and execute my initialization logic (which clears data out of the tables) I get a foreign key restraint error when trying to delete one of my records. However the FK restraint is an old one that refers to a table that no longer exists. I have looked at all of my tables in the current design and the constraint referenced in the exception does not show up on any of the tables.
How things changed: I had DesktopItems tie to a DataEntry table with a foreign key constraint. I now have DesktopItems point to WorkItem ( the orgininal table renamed ) and it is on the delete of items from DesktopItems that I get the old FK constraint. The really odd part about this is if I go into the table and manually delete the record, I do NOT get the constraint error!!!
In the database that I'm getting the error, I implemented the changes by deleting all tables and SPROCS and then executing scripts to rebuild the new design.
Is there a system SPROC that I can execute to show and delete this item?
Dear Michael,
Try this code:
IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'[FK_DesktopItems_TO_DataEntry]'))
IF OBJECT_ID (N'[DesktopItems]') IS NOT NULL
EXEC ('ALTER TABLE [DesktopItems] DROP CONSTRAINT FK_DesktopItems_TO_DataEntry')
HTH,
Suprotim Agarwal
Dropping an old foreign key restraint?
This forum looked like the only one where I may get an answer to this question.
I built a database with a set of tables in Sql Server 2005 sp2.
I later had to refactor the data which led to removing one table, renaming another table, and moving some data around.
Now when I attempt to write my test code and execute my initialization logic (which clears data out of the tables) I get a foreign key restraint error when trying to delete one of my records. However the FK restraint is an old one that refers to a table that no longer exists. I have looked at all of my tables in the current design and the constraint referenced in the exception does not show up on any of the tables.
How things changed: I had DesktopItems tie to a DataEntry table with a foreign key constraint. I now have DesktopItems point to WorkItem ( the orgininal table renamed ) and it is on the delete of items from DesktopItems that I get the old FK constraint. The really odd part about this is if I go into the table and manually delete the record, I do NOT get the constraint error!!!
In the database that I'm getting the error, I implemented the changes by deleting all tables and SPROCS and then executing scripts to rebuild the new design.
Is there a system SPROC that I can execute to show and delete this item?
Dear Michael,
Try this code:
IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'[FK_DesktopItems_TO_DataEntry]'))
IF OBJECT_ID (N'[DesktopItems]') IS NOT NULL
EXEC ('ALTER TABLE [DesktopItems] DROP CONSTRAINT FK_DesktopItems_TO_DataEntry')
HTH,
Suprotim Agarwal
Dropping all Data but keeping constraints and Structure Intact!
Hi All -
I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
FK, and other relationships on them. I would like to drop all data in all
tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
relationships cascading, and indexes and such. I want to keep intact all
that is not data. So basically truncate or delete all data and leave all
table structures with all their constraints etc ..
I wonder if such a script exists? that would be a great help.
Thank You In Advance.
Wise -
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200704/1On Apr 11, 7:20 pm, "wiseteufel via SQLMonster.com" <u4067@.uwe> wrote:
> Hi All -
> I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
> FK, and other relationships on them. I would like to drop all data in all
> tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
> relationships cascading, and indexes and such. I want to keep intact all
> that is not data. So basically truncate or delete all data and leave all
> table structures with all their constraints etc ..
> I wonder if such a script exists? that would be a great help.
> Thank You In Advance.
> Wise -
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200704/1
You can script out the entire database ( with PK,FK, INDEX ,
Constraint options) and create a new database from the script .
I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
FK, and other relationships on them. I would like to drop all data in all
tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
relationships cascading, and indexes and such. I want to keep intact all
that is not data. So basically truncate or delete all data and leave all
table structures with all their constraints etc ..
I wonder if such a script exists? that would be a great help.
Thank You In Advance.
Wise -
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200704/1On Apr 11, 7:20 pm, "wiseteufel via SQLMonster.com" <u4067@.uwe> wrote:
> Hi All -
> I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
> FK, and other relationships on them. I would like to drop all data in all
> tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
> relationships cascading, and indexes and such. I want to keep intact all
> that is not data. So basically truncate or delete all data and leave all
> table structures with all their constraints etc ..
> I wonder if such a script exists? that would be a great help.
> Thank You In Advance.
> Wise -
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200704/1
You can script out the entire database ( with PK,FK, INDEX ,
Constraint options) and create a new database from the script .
Dropping all Data but keeping constraints and Structure Intact!
Hi All -
I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
FK, and other relationships on them. I would like to drop all data in all
tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
relationships cascading, and indexes and such. I want to keep intact all
that is not data. So basically truncate or delete all data and leave all
table structures with all their constraints etc ..
I wonder if such a script exists? that would be a great help.
Thank You In Advance.
Wise -
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200704/1On Apr 11, 7:20 pm, "wiseteufel via droptable.com" <u4067@.uwe> wrote:
> Hi All -
> I have a SQL 2000 database of 60 some tables, with constraints, indexes, P
K-
> FK, and other relationships on them. I would like to drop all data in all
> tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
> relationships cascading, and indexes and such. I want to keep intact all
> that is not data. So basically truncate or delete all data and leave all
> table structures with all their constraints etc ..
> I wonder if such a script exists? that would be a great help.
> Thank You In Advance.
> Wise -
> --
> Message posted via droptable.comhttp://www.droptable.com/Uwe/Forums.aspx/sql-ser
ver/200704/1
You can script out the entire database ( with PK,FK, INDEX ,
Constraint options) and create a new database from the script .
I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
FK, and other relationships on them. I would like to drop all data in all
tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
relationships cascading, and indexes and such. I want to keep intact all
that is not data. So basically truncate or delete all data and leave all
table structures with all their constraints etc ..
I wonder if such a script exists? that would be a great help.
Thank You In Advance.
Wise -
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200704/1On Apr 11, 7:20 pm, "wiseteufel via droptable.com" <u4067@.uwe> wrote:
> Hi All -
> I have a SQL 2000 database of 60 some tables, with constraints, indexes, P
K-
> FK, and other relationships on them. I would like to drop all data in all
> tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
> relationships cascading, and indexes and such. I want to keep intact all
> that is not data. So basically truncate or delete all data and leave all
> table structures with all their constraints etc ..
> I wonder if such a script exists? that would be a great help.
> Thank You In Advance.
> Wise -
> --
> Message posted via droptable.comhttp://www.droptable.com/Uwe/Forums.aspx/sql-ser
ver/200704/1
You can script out the entire database ( with PK,FK, INDEX ,
Constraint options) and create a new database from the script .
Subscribe to:
Posts (Atom)