.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
satyapriyanayak
SP
Sambanthamoorthy
Swati Kadam
Ashutosh Jha
Post New Web Links

Does ALTER DATABASE SET RESTRICTED_USER block until connections are killed?

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server
 

Hi, we have a database restore script that runs an ALTER DATABASE [Database] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE command.  The next statement is a WAITFOR DELAY with 30 seconds.  This is to ensure any non compliant connections are killed before continuing.

However, I'm wondering if the WAITFOR is actually necessary?  Would the ALTER DATABASE command not block until all connections have been killed?

Thanks,
Tom




View Complete Post


More Related Resource Links

RECOVERY_PENDING - ALTER DATABASE emergencydemo SET EMERGENCY; Fails

  

I have SQL Express 2008 and SQL 2008 both installed on a server. There are a variety of dbs installed on SQL Standard all with many backups. SQL Express has only 4 small dbs and it turns out these are only backed up using DPM 2010.

One of these is stuck on recovery_pending, apparently since August 31. It is a blog with irregular entries and absence was not noted.

I tried all the steps at http://sqlhera.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx. The SET Emergency and also the SET Single_User command reports success, but then the DBCC part just says:

Msg 922, Level 14, State 1, Line 1
Database 'emergencydemo' is being recovered. Waiting until recovery is finished.

At this point there is only one backup in DPM dated August 30. I restored this to a different location and tried to attach it ot SQL Standard, but it fails:

Database 'emergencydemo' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Could not open new database 'emergencydemo'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 3415)

It is looking pretty grim for both the database and the backup.

ALTER DATABASE AdventureWorks SET ENABLE_BROKER takes too long to finish, how long does it run

  
try to run ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
but still running after 8mins, is this normal, what is approximate time? can it be cancelled?

In a button's control template, how can I alter the color of a text block?

  

Using WPF 4, I'm trying to create a button style (that would preferably work in SL, as well) that alters the text color of any contained text when the button is mouseover'd. Since I'm trying to make this compatible with Silverlight, I'm using the visual state manager:

<Style TargetType="{x:Type Button}">
  <Setter Property="Template">
    <Setter.Value>
      <ControlTemplate TargetType="Button">
        <Border x:Name="outerBorder" CornerRadius="4" BorderThickness="1" BorderBrush="#FF757679">
          <VisualStateManager.VisualStateGroups>
            <VisualStateGroup x:Name="CommonStates">
              <VisualState x:Name="Normal" />
              <VisualState x:Name="MouseOver">
                <Storyboard>
                  <ColorAnimation Duration="0" To="#FFFEFEFE"
                          Storyboard.TargetProperty="(TextElement.Foreground).(SolidColorBrush.Color)"
                          Storyboard.TargetName="contentPresenter"/> 
                </Storyboard>
              </VisualState>
            </VisualStateGroup>
          </VisualStateManager.VisualStateGroups>
          <Grid>
          

Add Database through App_Data Error- Connections to SQL Server Files (*.mdf) require SQL Express

  

I am trying to add a new database via the App_Data Folder and I receive an error described below.

I had attached an mdf file to try to use it in SQL Server 2008 Developer and Visual Studio 2010, which was is included in a book's lesson.  Whenever I try to add a database within the App_Data Folder I get an error.

I can create a db by attaching an mdf via SQL Server Mgmt Studio, it connects fine, and I can see tables.

Mgmt Studio- Right Click Databases, Attach, Add, then once mdf is located:

The mdf file location, Database Name, Attach As, -- are all the same:

CHAPTER13\USEWEBPARTS\USEWEBPARTS\APP_DATA\ASPNETDB.MDF

Do I need to change the Database Name?

Visual Studio- Server Explorer connects fine to mdf OR regular Database, I can see the tables.

However when I attempt to add ANY TYPE OF SQL SERVER DATABASE through the App_Data folder I receive this error "Connections to SQL Server Files (*.mdf) require SQL Server Express 2008 to function properly.  Please verify the installation of the component or download from the URl."

I tested this for both a normal dbo (non mdf) and the mdf.dbo I attached before, with the same error!

Im running XP SP3 w/ Visual Studio 2010 and SQL Server 2008 R2 Developer.  I had previously removed SQL Server Express.


Need to execute ALTER DATABASE inside stored procedure

  

SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?

Thanks.


Change database owner using Alter Database 2008

  

Hello,

I need to change the owner of a bunch of databases, and I do not want to use sp_changedbowner as it is deprecated for the next sql releases. I there a way to do this in an Alter Database statement?

Any help would be appreciated

Dave


Dave SQL Developer

generic use of database connections, commands, datareaders

  

I have an app that is reading and writing from databases that are structurally identical but are from different systems, primarily SQL Server and Access 2010.  When using the respective .Net objects such as connections, commands, datareaders, the code seems to be often the same (except for transactions - Access doesn't do transactions?).  But the objects are different - sqlconnection versus oledbconnection, etc.

Is there a standard way of writing data access code that works regardless of the database type?


How to Alter a view to remove the Database Name on the from table in bulk

  

I'm not sure if there's an easy way to do this, but I want to change the "... from [MyDatabase]..[MyTable]" part in my view to remove the database part so it's just "... from [MyTable]".  There are about 100 views that have this.

We use this 3rd party tool to create our database tables and views (no way around this) and in their views they put "... from [MyDatabase]..[MyTable]" .  Then later we need to take a snapshot of that database, so we copy the mdf file and change the database name.  The copy is fine, but the views won't work because of this.  Is there an easy way to alter all the views to remove the datbase name?  There are 100-300 views I have to change, I don't want to write that many alter scripts.

 


dan

Detach database with drop connections

  

Hello,

What is the significance of dropping connections (by chosing drop connection option) while detaching the database? Can it create problems if I don't select this option?

Thanks.

 


Database mirroring trouble configuring 2 connections

  

I am trying to set up the following. 

http://msdn.microsoft.com/en-us/library/cc917713.aspx

I have configured the sql server side no problem.  i am having issue getting the application that uses the database to connect to the mirrored server in the event of  failure.

The application that utalises the database connects through the data sources (ODBC), System DSN, SQL driver.  I see no option in the configuration for the addition of a 2nd IP.  Many of the technical references i have read refer to the data source connection string, i cannot find this in the registry however.  Please help!

I am used to a 3rd party mirroring software where you connect to a logical IP address which then routes through to whichever database is running.


Help- Enterprise Library Logging Block (Database trace listener)

  

Hi,

I'm new to enterprise application blocks. I'm trying to use database trace listener to write the logs to Database. Can someone help me on this .

I would apprciate your response.

 

Thanks

 

 

 

 


Saving database connections

  

I am trying to save connections in management studio so I don't have to re-enter them every time.  I have to connect to multiple servers and I just want to be able to open management studio and have them already in there.  Is there anyway to do this?


KC MCTS, MCP

Multiple database support with Entity Framework

  
One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Export Visio Database Table Names to Excel

  
If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend