Dropping a RECOVERING database

Hi,

Is there any way(better) to drop a RECOVERING database?

We tried to change a db from READ ONLY state to READ/WRITE state and this took forever (db is very small 40 MB) and we tried to kill this process and this went to KILLED/ROLLBACK state and blocking other processes.

We tried to even stop the sql server but that didn't work. We had to reboot the windows server. Now that particular database is in 'RECOVERING' state and we want to get rid of it. We've a backup of it.

Any ideas how to drop this database?

Because of this RECOVERING database, SQL Agent is not starting. The Agent error log says, it's waiting on SQL server to complete the recovery.

We've SQL 2005 with service pack1.

Any ideas are highly appreciated.

Thanks,

Siva.

You can stop sqlserver service. Rename the log file. Restart sqlserver service - this will put the database into suspect mode. You should be able to drop the database now.
(do be sure you have a good backup before forcing the deletion outlined above).|||

Thanks........that worked....

Still don't understand why it took so long to change from READ ONLY to READ/WRITE and also why it took so long for rollback as well.

|||Do you still have the errorlogs?

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:1acc8880-1e4a-44e2-80d0-1cdb75e87ad6@.discussions.microsoft.com...

> Thanks........that worked....

>

> Still don't understand why it took so long to change from READ ONLY to

> READ/WRITE and also why it took so long for rollback as well.

>

>

>

>

>

>|||

We did see in the error log the STACK DUMP thing and the following message many times:

Process 71:0:0 (0x69c) Worker 0x78FF80E8 appears to be non-yielding on Scheduler 2. Thread creation time: 12804246994487. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 98%. Interval: 7331203 ms.

71 was the process we killed but was unyielding.....

|||Can you post the errorlog and the .mdmp from the stack dump?

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:270127c2-61e0-44c0-bda8-fa176b32924f@.discussions.microsoft.com...

> We did see in the error log the STACK DUMP thing and the following

> message many times:

>

>

>

> Process 71:0:0 (0x69c) Worker 0x78FF80E8 appears to be non-yielding on

> Scheduler 2. Thread creation time: 12804246994487. Approx Thread CPU

> Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 98%.

> Interval: 7331203 ms.

>

>

>

> 71 was the process we killed but was unyielding.....

>

>|||

I've 4 .mdmp files each is about 2.5 MB.

I don't know how I can post it here...

Do you need any specific info?

|||Can you please mail one to me directly? The one with the lowest numbered

extension would likely be the best one.

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:9aeb4aec-aa39-43b3-9ab2-658e5d06f246@.discussions.microsoft.com...

> I've 4 .mdmp files each is about 2.5 MB.

>

> I don't know how I can post it here...

>

> Do you need any specific info?

>

>

>

>|||

What's your e-mail address?

Is it 9aeb4aec-aa39-43b3-9ab2-658e5d06f246@.discussions.microsoft.com ?

or ?

|||Its peterbyr@. microsoft.com.

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:a76af29a-9309-4a90-b52f-b469160de25a@.discussions.microsoft.com...

> What's your e-mail address?

>

>

>

> Is it 9aeb4aec-aa39-43b3-9ab2-658e5d06f246@.discussions.microsoft.com ?

>

> or ?

>

>sql

Drop all strored procedures

Hello,
How do I write an sql query that drops all stored procedures
in a database if I do not know how many there are or what
they are called?
--
Mikael EngdahlYou could use a cursor and loop through INFORMATION_SCHEMA.ROUTINES WHERE
ROUTINE_TYPE='PROCEDURE', then EXEC('DROP PROC '+@.routine_name) within the
loop...
"Mikael Engdahl" <mikael-l@.engdahl.no.spam.com> wrote in message
news:eyvDqK0ZDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello,
> How do I write an sql query that drops all stored procedures
> in a database if I do not know how many there are or what
> they are called?
>
> --
> Mikael Engdahl
>

Drop all objects in database?

I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:

1. If I run this command

EXEC sp_MScheck_uid_owns_anything 5

I get this weird error message:

"The user owns objects in the database and cannot be dropped."

Not sure why that is since I'm just trying to list the objects, not drop them.

2. I tried running a simple query to get the objects from the system table instead:

SELECT * from [dbo].[sysobjects] where uid = 5

This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:

The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.

Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!

-CliffUse the TYPE column of sysobjects to filter out foreign keys, which should drop automatically when the tables are dropped.|||Thanks for responding. Your approach requires that the tables be dropped in a particular order so that there won't be any constraint violations, right? I was hoping to avoid that by deleting all the foreign keys first and then dropping all the tables...|||select 'alter table ' + object_name (fkeyid) + ' drop constraint ' + object_name (constid)
from sysreferences|||Thanks, that's getting me closer! I'll let you know how it goes ;)|||Thanks for your help, everyone. This is what I ended up putting together:

ALTER PROCEDURE [rvts].[clean_database]
as

set nocount on

-- first obtain all foreign keys and delete
declare @.fkTableName varchar(255)
declare @.fkConstName varchar(255)

declare cursor1 cursor for
select object_name (fkeyid), object_name (constid) from sysreferences

open cursor1

fetch next from cursor1
into @.fkTableName, @.fkConstName

while @.@.fetch_status = 0
begin
exec ('ALTER TABLE ' + @.fkTableName + ' DROP CONSTRAINT ' + @.fkConstName)
fetch next from cursor1
into @.fkTableName, @.fkConstName
end

close cursor1
deallocate cursor1

-- now do the same for tables
declare @.pkTableName varchar(255)

declare cursor2 cursor for
select object_name (id) from sysobjects where xtype = 'U'

open cursor2

fetch next from cursor2
into @.pkTableName
while @.@.fetch_status = 0
begin
exec ('DROP TABLE ' + @.pkTableName)
fetch next from cursor2
into @.pkTableName
end

close cursor2
deallocate cursor2

I'm a T-SQL noob so if anything there looks out of place just give me a good smack ;)|||Thanks for your help, everyone. This is what I ended up putting together:

ALTER PROCEDURE [rvts].[clean_database]
as

set nocount on

-- first obtain all foreign keys and delete
declare @.fkTableName varchar(255)
declare @.fkConstName varchar(255)

declare cursor1 cursor for
select object_name (fkeyid), object_name (constid) from sysreferences

open cursor1

fetch next from cursor1
into @.fkTableName, @.fkConstName

while @.@.fetch_status = 0
begin
exec ('ALTER TABLE ' + @.fkTableName + ' DROP CONSTRAINT ' + @.fkConstName)
fetch next from cursor1
into @.fkTableName, @.fkConstName
end

close cursor1
deallocate cursor1


-- now do the same for tables
declare @.pkTableName varchar(255)

declare cursor2 cursor for
select object_name (id) from sysobjects where xtype = 'U'

open cursor2

fetch next from cursor2
into @.pkTableName
while @.@.fetch_status = 0
begin
exec ('DROP TABLE ' + @.pkTableName)
fetch next from cursor2
into @.pkTableName
end

close cursor2
deallocate cursor2

I'm a T-SQL noob so if anything there looks out of place just give me a good smack ;)

hmmmmmmmm cursorssssssssss, im loving it!|||Bah! The performance problems will be lost in the wash of "Hey, where'd my table go?" complaints.

Hopefully, you have everything owned by dbo. If not, you will have to go back, and add in the user_name(uid) function.

Drop all objects

HI,

Been poking around in sysobjects and information_schema.routines trying to work out how to best write scripts that will drop all specific objects from a database.

That is, scripts to drop all tables, views, stored procs, functions ( FN, IF, TF ) but can't seem to figure out appropriate way to do it.

A pointer on how to drop all of any one of the above object types would be greatly appreciated and I should be able to work out the others.

Further, when executing multiple scripts I am writing scripts like this...

ddl_batch.sql

Code Snippet

:R table1.sql

:R table2.sql

:R ufn_func1.sql

:R ufn_func2.sql

:R view_table1

:R view_table2

:R usp_proc1.sql

:R usp_proc2.sql

And executing via:

sqlcmd -S server\instance -i ddl_batch.sql

This is to maintain individual object type scripts, and then to execute them together in dependency order, rather than executing one monolithic batch script. Is this a reasonable way to go about it or is there a better way?

Many thanks in advance for your help.

Compose your SQL Strings on the fly using something like the following:

SELECT 'DROP TABLE [' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'),'IsMSShipped') = 0 --You do not want to drop the MS Shipped, right ;-) ?

You can use the output and store it in a file or execute it on the fly. Make sure that you first frop the foreign keys on the tables, then drop the tables, then the views, afterwards the functions.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Once again Jens many thanks for your help.

Driver''s SQLSetConnectAttr failed from script component

Hi

I am currently trying to get a script component to write data into a openedge database.

I am stumped by the below message.

Validation error. Bulk Load ProjPeriod Bplan: Script Component [9378]: System.Data.Odbc.OdbcException:

ERROR [HYC00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Optional feature not implemented. ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server rejects connection on attach.

ERROR [IM006] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Optional feature not implemented. ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server rejects connection on attach.

ERROR [IM006] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Driver's SQLSetConnectAttr failed. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) Package.dtsx 0 0

The two areas’ that I have found that might be causing this is transaction and codepage, however I have tried setting these appropriately with no success.

Any help would be much appreciated.

Cheers,

Ken C

Does the connection work correctly if you use the connection manager from a DataReader source?

|||

Hi jwelch

As far as I can tell, using this connection from connection manager with Datareader does work.

I created the connection and used in a datareader and it displayed the field names from the supplied sql.

Cheers,

Ken

|||

Hi Again

A bit wierd.... I changed the Fetch Array Size, in the odbc connection to 0 .....this seemed to remove the error from ssis. (This seemed to work for somone else connecting to Oracle db)

Since then i have put the value back to 50 and the error messege has not returned ?

Will develop futher and post back

Cheers

Drivers of Sqlserver 200

hi

I want to know file's list of SQLSERVER2000 Driver

I write a program , it connect to sqlserver2000 in Eternet and

client side of software install in every computer but i have to install sqlserver2000 in every client pc

how can i connect to SQLServer2000 Server (in a network) with out installing Sqlserver2000 ?

Tanks

Hi,

you just have to install the MDAC Components to access SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Depending on your exact needs in addition to MDAC you may also want to try SQL Server Native Client, JDBC, or ADO.NET.

Check out http://msdn.microsoft.com/data for more info.