DENY permission,SQL server

Thursday, March 29, 2012

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 Table

I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jake
Hi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake
|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
>news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com.. .
>
sql

Deleting Table

I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jakeHi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
> news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
>

Deleting Table

Deleting stored procedures through query analyzer

Hi All!

I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:

1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day

I've got everything except removing the stored procedure so I will really appreciate the help.

Thank you all!

Rijckewaert wrote:

Hi All!
I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database?

Transact-SQL ... here Oracle is notwelcome

the actual traditional code is

IF OBJECT_ID('[schema/owner].[object]') IS NOT NULL

DROP PROCEDURE [schema/owner].[object];

Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:
1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day
I've got everything except removing the stored procedure so I will really appreciate the help.
Thank you all!

this "design" is not clear to me... why would you always drop an object to be recreated (if the inner code remains the same)?

is this becouse of the well known "orphaned users" problem?
this problem (perhaps the problem you are experimenting) is involved when a "restored" database user(s) is no longer mapped to a corresponding server's standard SQL Server login..
in SQL Server 2000 this means the relationship between master.dbo.syslogins is broken with database.dbo.sysusers...
actually the JOIN on master.dbo.syslogins.sid ~ database.dbo.sysusers.sid

if this is the case, you do not need to delete the object and the user... you just need to resync it/them, via the sp_change_users_logins system stored procedure http://msdn2.microsoft.com/en-us/library/ms174378.aspx ... please have a look at http://msdn2.microsoft.com/en-us/library/ms175475.aspx as well..

if this is not the case... please expand

regards

deleting SQL Srv 2K5 publication with no Agents

I have the case where a SQL Srv 2K5 publication has no agents - agents were accidentally erased during backup/restore. I was abel to erase subscription.

The error is "The specified @.job_name <jab name> does not exist. (Microsft SQL Server, Error: 14262)

Is there any way to remove publication?

Help will be greatly appreciated!

Vladimir

Do you have trouble in dropping subscription/publication? Error looks like to happen when you drop a remote pull subscription.

Anyway, you may call SP directly to drop the subscription, then publication. Please refer to BOL.

Similar call will be like:

use [PubDB]
exec sp_dropmergesubscription

exec sp_mergesubscription_cleanup

exec sp_dropmergepublication

Hope it will be helpful.

Thanks

Yunjing

|||

Hello,

I am dropping xactional publication, not merge subscription. Normally, it is not a problem, but in this case all three agent jobs are gone.

When I try to delete it, I am getting message about absent snapshot agent, Error 20678 "|Could not find the regular snapshot job for the specified publication <publication name>. Supply either @.job_id or @.job name to idnetify the job."

But the jobs like I said is gone.

I have tried sp_droppublication. Got error: "Message 14013, This database is not enabled for publication", which does not make sense since publication is there already.

Thanks for trying.

Vladimir

|||

Hello, Vladimir,

I think meta data got messed up somehow. Could you check whether publications are still there?

please call the below two sql to see if you can get any thing.

On publisher server, select * from [PublicationDB]..syspublications

On distributor server, select * from [distributionDB]..MSpublications

Thanks

Yunjing

|||

Hi Yunjing,

The syspublications does not have any rows.

MSpublications does not list this "orphaned" publication, but it lists all other "healthy" publication I created.

Yesterday I backed up and restored the publisher database. There is the switch during restore 'keep_publication' which gave me hope that

if I will not set it during restore that will break link b/w db and publication. But after restore error still percists.

R-clicking on publication allows to create publication drop script. I ran it, and against every article in publication the message was "the publication does not exist'. This is while I see that publication in GUI.

Also, r-clicking, I tried to start Snapshot agent to create snapshot - the absent snapshot may be the root of the problem.

I guess it was erase together with erasure of snapshot agent.

It failed at the end with error "Cannot insert NULL into column "publisher_database_id", table "distribution.dbo.MSrepl_transactions".

Thanks Yunjing for keeping up.

Vladimir

sql

Thursday, March 29, 2012

deleting table phisically

Deleting Table

Deleting Table

Deleting Table

Deleting Table

Deleting stored procedures through query analyzer

deleting SQL Srv 2K5 publication with no Agents

DENY permission,SQL server

Blog Archive

About Me