deleting table phisically

hello
I delete a table from a DataBase in enterprise manager(drop table) ,
but it is not deleted phisically and hard's free space does not
increas.
How can I delete table phisically?
thanksYou need to shrink the database to recover disk space on the drive...
"nsh" <nsh5776@.yahoo.com> wrote in message
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||How do you know the table is not deleted phisically? because of the database
file (.mdf) file?
Most likely you just vacated space within the .mdf file.
Back up the database, then try to shrink it.
using Enterprise manager, Right mouse on the database -> All Tasks ->
Shrink dbase
You can also see how much space is being used in the .mdf file grafically
using Enterprise manager, Right mouse on the database -> View -> Task
Pad
Hope this helps
"nsh" <nsh5776@.yahoo.com> escribió en el mensaje
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||thanks for helping.
Before delete table .mdf & .ldf size are the same after delete table
(each table size is about 5GB ).
when I make Data Base ,I set Autoshrink .Is not it enough?
please help me ...
thanks
"Marcelo" <marcelo.no@.spam.santiago.cl> wrote in message news:<#YAUSvkRDHA.1324@.TK2MSFTNGP11.phx.gbl>...
> How do you know the table is not deleted phisically? because of the database
> file (.mdf) file?
> Most likely you just vacated space within the .mdf file.
> Back up the database, then try to shrink it.
> using Enterprise manager, Right mouse on the database -> All Tasks ->
> Shrink dbase
> You can also see how much space is being used in the .mdf file grafically
> using Enterprise manager, Right mouse on the database -> View -> Task
> Pad
> Hope this helps
> "nsh" <nsh5776@.yahoo.com> escribió en el mensaje
> news:534bf457.0307091048.714766fe@.posting.google.com...
> > hello
> > I delete a table from a DataBase in enterprise manager(drop table) ,
> > but it is not deleted phisically and hard's free space does not
> > increas.
> > How can I delete table phisically?
> > thanks|||> when I make Data Base ,I set Autoshrink .Is not it enough?
No, because it doesn't auto-shrink every single time the database changes --
this would cause a performance nightmare.

Deleting old unused database issue

I have an old DATABASE that is not more in use , so I want to drop that database.
But before that I want to check that is there any application running on it or not.
can any one suggest me how to do this?
thanksSimplest is execute sp_who2. That will tell you if there are any current connections. If you need something more bullet proof then you will need to audit connections over a time period. Or you could try a scream test if it is not critical - which presumably it is not since you are contemplating dropping it.|||Use sql profiler and let it dump the results to a table and query that

Most of my servers are dedicated to a single database though

Just make sure you have a current dump

Now a soapbox issue

I like to have sproc only access to my databases, and each sproc has sproc logging, so I know everything that's going on|||...Or you could try a scream test...One of my phrases has entered the public lexicon!|||There'll be scads of them soon I'm sure.|||The "scream test" is one of my best, but the "Boxers vs Briefs solution" is more colorful.sql

Deleting a Database

I am using ADO to execute a DROP DATABASE command. Before that I run some
query against the DB to do some checks. However I get the meesage "Cannot
drop the database XXX because it is currently in use". If I do not run the
query before the DROP works. I have made sure that all connections to the DB
are closed.
I run the SQL Profiler and noticed that there is an "Audit Login" line added
before a command and a corresponding "Audit Logout" afterwards. Now I
noticed that the "Audit Logout" command takes some time to appear in the
trace window. I put a message box before my DROP command and after my query
and waited till the "Audit Logout" appeared. Then the DROP worked. So it
seems that there is some kind of delay before the DB is "released" after my
query is run.
Is there some expalnation/solution to that?
Thanks
YannisYannis wrote on Wed, 17 May 2006 10:30:01 +0300:

> I am using ADO to execute a DROP DATABASE command. Before that I run some
> query against the DB to do some checks. However I get the meesage "Cannot
> drop the database XXX because it is currently in use". If I do not run the
> query before the DROP works. I have made sure that all connections to the
> DB are closed.
> I run the SQL Profiler and noticed that there is an "Audit Login" line
> added before a command and a corresponding "Audit Logout" afterwards. Now
> I noticed that the "Audit Logout" command takes some time to appear in the
> trace window. I put a message box before my DROP command and after my
> query and waited till the "Audit Logout" appeared. Then the DROP worked.
> So it seems that there is some kind of delay before the DB is "released"
> after my query is run.
> Is there some expalnation/solution to that?
> Thanks
> Yannis
Do you have connection pooling enabled? Connections which are "closed" by
the application will be passed to the pool so they can be reused by another
request, rather than starting a new connection from scratch. The delay will
be due to the connection pool holding the connection open and then releasing
it after the set timeout.
Dan|||Thank you Daniel,
I don't do anything relating to connection pooling.
Any idea where that may occur in ADO and what I can do to overcome it?
Yannis
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:%23GWlEFZeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Yannis wrote on Wed, 17 May 2006 10:30:01 +0300:
>
> Do you have connection pooling enabled? Connections which are "closed" by
> the application will be passed to the pool so they can be reused by
> another request, rather than starting a new connection from scratch. The
> delay will be due to the connection pool holding the connection open and
> then releasing it after the set timeout.
> Dan
>|||You must prevent other connections to access the database:
alter database <db name>
set single_user
with rollback immediate;
use master;
drop database <db name>;
ML
http://milambda.blogspot.com/|||If you're using ODBC drivers, you'll need to make a change to your ODBC
settings in Windows. If you're not, you'll have to dig around for how to
change this for OLE DB or whatever connection type you're using.
Dan
Yannis wrote on Wed, 17 May 2006 15:34:14 +0300:
> Thank you Daniel,
> I don't do anything relating to connection pooling.
> Any idea where that may occur in ADO and what I can do to overcome it?
> Yannis
> "Daniel Crichton" <msnews@.worldofspack.com> wrote in message news:%23GWlEF
ZeGHA.380@.TK2MSFTNGP04.phx.gbl...

Delete Snapshot Replication

When I try to drop the publication in this order:

exec sp_dropsubscription @.publication = @.publicName, @.subscriber = @.servName, @.article = N'all'

exec sp_droppublication @.publication = @.publicName

I am getting the following error after dropping the publication

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Could anyone give me a suggestion about how to avoid this error?

Thanks!

Which if the above two stored procs is raising the error, and what version of sql server are you running?|||

The error is raised when trying to delete the publication with:

exec sp_droppublication @.publication = @.publicName

I am working with SQL Server 2005, service pack 1.

I hope with this information you can help me, or else please let me know.

Thanks!

|||this is a dumb suggestion, rather a shot in the dark, but can you stop/start the sql server service, and then try it again? Something about transactions/cursors that may be stuck/lingering somewhere?|||

I tried that already. I finally got to work the deleting of the publication, but the error remains as reported earlier. I am thinking this is a 'MS bug' that has not been totally fixed since SQL Server 7... pretty sad, huh? If still anyone has any idea what to do, please let me know.

Thanks!

|||

If you can provide a full repro of the problem, it would help us tremendously. If you can reproduce it from scratch, please post the TSQL code, otherwise this issue has been intermitent throughout the years, nothing solid for us to fix.

Other questions - are you wrapping this in a transaction? What's the db compat level? How many subscriptions are there?

Delete Snapshot Replication

When I try to drop the publication in this order:

execsp_dropsubscription @.publication = @.publicName, @.subscriber = @.servName, @.article = N'all'

exec sp_droppublication @.publication = @.publicName

I am getting the following error after dropping the publication

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Could anyone give me a suggestion about how to avoid this error?

Thanks!

Which if the above two stored procs is raising the error, and what version of sql server are you running?|||

The error is raised when trying to delete the publication with:

exec sp_droppublication @.publication = @.publicName

I am working with SQL Server 2005, service pack 1.

I hope with this information you can help me, or else please let me know.

Thanks!

|||this is a dumb suggestion, rather a shot in the dark, but can you stop/start the sql server service, and then try it again? Something about transactions/cursors that may be stuck/lingering somewhere?|||

I tried that already. I finally got to work the deleting of the publication, but the error remains as reported earlier. I am thinking this is a 'MS bug' that has not been totally fixed since SQL Server 7... pretty sad, huh? If still anyone has any idea what to do, please let me know.

Thanks!

|||

If you can provide a full repro of the problem, it would help us tremendously. If you can reproduce it from scratch, please post the TSQL code, otherwise this issue has been intermitent throughout the years, nothing solid for us to fix.

Other questions - are you wrapping this in a transaction? What's the db compat level? How many subscriptions are there?

Delete Replication per TSQL

Hello,
i use MSDE and try to setup a merge replication. I want to drop the
replicated db, but get some error. Sorry for the german error msg:
1> sp_helpdistributor
2> go
distributor
distribution database
directory
account
min distrib retention
max distrib retention history retention history cleanup agent
distribution cleanup agent
rpc server name
rpc login name
---
---
---
---
---
--- --
-- --
--
---
---
distrib
D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
LocalSystem
0
72 48 Agentverlaufscleanup:
distrib
Verteilungscleanup: distrib
repl_distributor
distributor_admin
1> sp_helpserver
2> go
name
network_name status
id
collation_name
connect_timeout query_timeout
---
-- --
---
-- --
rpc,rpc out,use remote collation
0
NULL
0 0
repl_distributor
rpc,dist,rpc out,system,use remote collation
1
NULL
0 0
1> sp_dropdistributor
2> go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp_dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
1> sp_dropdistributiondb distrib
2> go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
1> sp_dropdistpublisher
2> go
Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 0
Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
dieser wurde nicht bergeben.
1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
2> go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 99
Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
gelscht werden, da auf diesem Server Datenbanken fr die Replikation
aktiviert
sind.
1> sp_dropmergepublication @.publication=N'TTPMerge'
2> go
Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
1> sp_helpmergepublication
2> go
How can i delete safely all replication entries so i can drop the db ?
Are there any docs beside BOL to Replication per TSQL ? I see the book
of Hilary and quick check the testchapter , are there more chapter
about replication with TSQL ?
Thx Baumgart
right click on your publication and select delete. Then go to Tools,
Replication, configure distributor, publishers, and subscribers and select
the publication databases tab. Uncheck the sahred databse you wish to drop.
Accept the defaults. Then you should be able to drop the database.
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
"Alexander Baumgart" <al.baumgart@.gmx.de> wrote in message
news:d586cm$g87$1@.mail1.sbs.de...
> Hello,
> i use MSDE and try to setup a merge replication. I want to drop the
> replicated db, but get some error. Sorry for the german error msg:
> 1> sp_helpdistributor
> 2> go
> distributor
> distribution database
> directory
>
> account
>
> min distrib retention
> max distrib retention history retention history cleanup agent
> distribution cleanup agent
> rpc server name
> rpc login name
>
>
>
>
> --- --
> -- --
> --
> --
>
> distrib
> D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
>
> LocalSystem
>
> 0
> 72 48 Agentverlaufscleanup:
> distrib
> Verteilungscleanup: distrib
> repl_distributor
> distributor_admin
> 1> sp_helpserver
> 2> go
> name
> network_name status
> id
> collation_name
> connect_timeout query_timeout
> --
> -- --
> -- --
> rpc,rpc out,use remote collation
> 0
> NULL
> 0 0
> repl_distributor
> rpc,dist,rpc out,system,use remote collation
> 1
> NULL
> 0 0
> 1> sp_dropdistributor
> 2> go
> Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
> sp_dropdistributor, Zeile 150
> Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
> besitzt zugeordnete Verteilungsdatenbanken.
> 1> sp_dropdistributiondb distrib
> 2> go
> Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistributiondb, Zeile 49
> Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
> Verteilerdatenbank ist einem Verleger zugeordnet.
> 1> sp_dropdistpublisher
> 2> go
> Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 0
> Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
> dieser wurde nicht bergeben.
> 1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
> 2> go
> Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 99
> Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
> gelscht werden, da auf diesem Server Datenbanken fr die Replikation
> aktiviert
> sind.
> 1> sp_dropmergepublication @.publication=N'TTPMerge'
> 2> go
> Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_MSCheckmergereplication, Zeile 12
> Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
> 1> sp_helpmergepublication
> 2> go
> How can i delete safely all replication entries so i can drop the db ?
> Are there any docs beside BOL to Replication per TSQL ? I see the book
> of Hilary and quick check the testchapter , are there more chapter
> about replication with TSQL ?
> Thx Baumgart
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
I didnt have any tools , like i told +ADs-(( Only MSDE. I set all per
SP ,
and tried the reverse way to drop the replication but that didnt work.
What i tried, error msg got translate by me from german to english:
a) 1+AD4- sp+AF8-dropmergepublication +AEA-publication+AD0-N'TTPMerge'
Meldung 21147, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht f+APw-r die Mergereplikation
publiziert.
(the master database didnt get shared per mergereplication)
b) 1+AD4- sp+AF8-dropdistpublisher 'BW1H049C+AFw-SUDSQLDB'
2+AD4- go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistpublisher, Zeile 99
Der Server 'xxx+AFw-SUDSQLDB' kann nicht als Verteilungsverleger
gel+APY-scht werden, da auf diesem Server Datenbanken f+APw-r die
Replikation
aktiviert sind.
(the server xxx+AFw-instancename couldnt be delete as distributor,
there are database active for replication)
c) 1+AD4- sp+AF8-dropdistributiondb distrib
2+AD4- go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gel+APY-scht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
(the distrib - distributiondb couldnt be deleted, its assigned to a
distributor)
d) 1+AD4- sp+AF8-dropdistributor
2+AD4- go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp+AF8-dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gel+APY-scht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
(the distributor +ACI-xxx+AFw-instancename+ACI- couldnt be deleted ,
its assigned with distribution-databases)
so how can i delete a merge-replication per osql.exe (TSQL) ?
Thx for your help Hilary , i will see if i can use the GUI way to find
the right SP's to delete the merge replication.
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
Hello,
after 2 day of work i was able to drop the merge replication. The
important cmd was sp+AF8-removedbreplication, as mentioned in
http://support.microsoft.com/default...en-us+ADs-3244
01
I check your book specially the example chapter where u described a way
to drop a replication, that also didnt work for me, because there was a
replication active. How deep u discusss manual setup of replication in
your book (in the example chapter u say more in chapter 6) ?
thx Baumgart

Delete records in a table with 15 dependencies

Hi, I need to delete records in a table with 15 dependencies.
I can't drop the 15 dependencies before delete records.
Any ideas to delete records without dropping dependencies
Thanks,Do you mean you want to delete a PK record which is used as a FK in 15
tables?
You could change the cascade option on the FKs to be a setting other than
the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL | SE
T
DEFAULT }
Alternatively you could temporarily disable the FK constraint (ALTER TABLE
cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of this
approach.
Thinking a little wider, I'd like to know more about the design you have
which will need to allow for orphaned records, if I have understood this
correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks a lot.
Your concern is correct 100%.
We are doing the test only for the same concern
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23eBTMeZCHHA.4224@.TK2MSFTNGP06.phx.gbl...
> Do you mean you want to delete a PK record which is used as a FK in 15
> tables?
> You could change the cascade option on the FKs to be a setting other than
> the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL |
SET
> DEFAULT }
> Alternatively you could temporarily disable the FK constraint (ALTER TABLE
> cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of
> this approach.
> Thinking a little wider, I'd like to know more about the design you have
> which will need to allow for orphaned records, if I have understood this
> correctly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

Delete records in a table with 15 dependencies

Hi, I need to delete records in a table with 15 dependencies.
I can't drop the 15 dependencies before delete records.
Any ideas to delete records without dropping dependencies
Thanks,
Do you mean you want to delete a PK record which is used as a FK in 15
tables?
You could change the cascade option on the FKs to be a setting other than
the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL | SET
DEFAULT }
Alternatively you could temporarily disable the FK constraint (ALTER TABLE
cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of this
approach.
Thinking a little wider, I'd like to know more about the design you have
which will need to allow for orphaned records, if I have understood this
correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks a lot.
Your concern is correct 100%.
We are doing the test only for the same concern
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23eBTMeZCHHA.4224@.TK2MSFTNGP06.phx.gbl...
> Do you mean you want to delete a PK record which is used as a FK in 15
> tables?
> You could change the cascade option on the FKs to be a setting other than
> the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL | SET
> DEFAULT }
> Alternatively you could temporarily disable the FK constraint (ALTER TABLE
> cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of
> this approach.
> Thinking a little wider, I'd like to know more about the design you have
> which will need to allow for orphaned records, if I have understood this
> correctly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

Delete records in a table with 15 dependencies

Hi, I need to delete records in a table with 15 dependencies.
I can't drop the 15 dependencies before delete records.
Any ideas to delete records without dropping dependencies
Thanks,Do you mean you want to delete a PK record which is used as a FK in 15
tables?
You could change the cascade option on the FKs to be a setting other than
the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL | SET
DEFAULT }
Alternatively you could temporarily disable the FK constraint (ALTER TABLE
cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of this
approach.
Thinking a little wider, I'd like to know more about the design you have
which will need to allow for orphaned records, if I have understood this
correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks a lot.
Your concern is correct 100%.
We are doing the test only for the same concern
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23eBTMeZCHHA.4224@.TK2MSFTNGP06.phx.gbl...
> Do you mean you want to delete a PK record which is used as a FK in 15
> tables?
> You could change the cascade option on the FKs to be a setting other than
> the default - from BOL: ON DELETE { NO ACTION | CASCADE | SET NULL | SET
> DEFAULT }
> Alternatively you could temporarily disable the FK constraint (ALTER TABLE
> cnst_mytable NOCHECK CONSTRAINT myfk), although I'd doubt the wisdom of
> this approach.
> Thinking a little wider, I'd like to know more about the design you have
> which will need to allow for orphaned records, if I have understood this
> correctly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

delete or drop table then create table

which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.DELETE is a logged transaction, and so incurs overhead that DROP and RECREATE do not.
For another option, try TRUNCATE table, which is also unlogged.|||well said i did research truncate table and you are of course correct on this. I'll change the code to that :D|||delete *

You crack me up|||Why don't you show us what you're doing, what volume, ect

Where for example are you getting the data from...and if you got into an argument, what did your coworker say?|||Personally I would not allow the drop/create table here, as it carries too many permission implications. Take the matter of the logs up with the DBA involved, and see if the transaction logs will hold up. He may need to add more diskspace, more backups, or both.|||delete *
You crack me upThat's MS Access syntax...|||Heres the code i made

if 0< (select count(*) from dbo.sysobjects where id = object_id(N'[dbo].[_Bootfiles_Summary_Work_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
TRUNCATE TABLE _Bootfiles_Summary_Work_Table
end
else
begin
Create Table _Bootfiles_Summary_Work_Table
(Agency smallint,
Plate_State varchar(20),
Plate varchar(8),
Cite_Count int,
CiteBalance Money)
end

its really simple actually no index no nothing, and in case it gets deleted some code that i dont know this is will recreate the table.

and o yea no star on the delete my mistake.