Unable To Bring Database Back Into MULTI_USER Mode.
Sep 24, 2007
I'm wondering if somebody could provide insight into a problem I'm having with SQL Server 2005. Although the problem is happening wthin an SSIS ETL, I don't think this problem is SSIS related.
In the ETL I need to rename a database, so I first put the database into single-user mode by issuing the command:
ALTER DATABASE foobar SET SINGLE_USER WITH ROLLBACK 30
The database then goes into single-user mode, and after the renaming occurs, I attempt to put the same database back into multi-user mode:
ALTER DATABASE foobar SET MULTI_USER WITH ROLLBACK IMMEDIATE
However, whenever I have a query pane opened against the same database in SQL Server Management Studio, the ETL fails and I get this error message:
"Error: Changes to the state or options of database 'foobar' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."
I'm wondering why the ALTER DATABASE command does not kill off the active connections? This is on my development box, and I'm the only one connected to the database. I've tried with ROLLBACK 30 as well, same thing. If I cut and paste the same command into Mangement Studio, the command succeeds so I don't think its a permission issue (using Windows Authentication both Management Studio and the ETL are executed by the same login). If I close the query pane the ETL succeeds at restoring multi-user mode. Is there something I am missing? Thanks in advance!
I am doing a logshipping in sqlserver2000. I have PrimaryServer database A and Secondaryserver A which is already existing. Now if i am trying to establish logshipping with this existing db in secondaryserver it says db A in destination is not in standy mode.
Please help me how to bring the db in standy mode that can be used to do logshipping.
Hi all,I have been wrestling with this problem all morning with no success sofar where I have a need to bring back an excluded field.Basically I have a list of order numbers. Each order number can havemany order types attached one of which is a ‘P’ type. Most order typeshave an account number attached in its own field however when a ‘P’ typeis selected the account number is not brought back.Is there someway I can get this brought back for each P type or do Ihave to do some fancy insert in a data warehouse to get this done (i.e.insert account numbers into all P types)?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches. This is my code:
select d.lwia,
cast((d.closegoal + d1.opengoal) as float)denominator
from
(
select yg.lwia,
cast(count(yg.appid)as float) closegoal
from dbo.wiayouthgoals yg
where yg.lwia = @RWB
-- Attained a goal in the timeframe timely or untimely
and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @EndDte) -- Parm date for end of time frame needed
-- Goal due but not attained
or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @EndDte -- Parm date for end of time frame needed
and yg.youthattaingoal <> 1))
group by yg.lwia
)d,
(
-- Closure with open goal
select cast(count(yg.appid)as float) opengoal
from dbo.tbl_caseclosure cc,
dbo.wiayouthgoals yg
where yg.appid = cc.col_idnum
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @EndDte -- Parm date for end of time frame needed
I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?
I have a column that has 75 values, 50 are unique/25 are duplicates. Ineed to be able to bring back a list of the duplicates, listing allrows even if more than two have the same value. I need to be able todo this using t-sql.Thanks,Tim
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated. 2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db. ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is: select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user) I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net. Many thanks
This is the following Error when i tried to back up database in MS SQL SERVER 2005 Express Edition.
" TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Backup failed for Server 'OFIX07SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
System.Data.SqlClient.SqlError: The backup of the file or filegroup "sysft_article" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
I have try to back up database in MS SQL SERVER 2005 SP2 Express Edition ,It shows the following error .Please help me to solve it and tell me why it happend like this.Thanks
" ===================================
Backup failed for Server 'OFIX07'. (Microsoft.SqlServer.Express.Smo)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
===================================
System.Data.SqlClient.SqlError: The backup of full-text catalog 'article' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Express.Smo)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages) at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries) at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
I have a database that I am trying to recover space from, it consists mostly of unallocated space, but I can’t seem to get that unused space released.
The DB is in simple recovery mode. There are no open transactions (used dbcc opentran).
The server is running SQL Server 2014 and the DB is in compatibility mode SQL Server 2008 (100). It was upgraded to 2014 a month or two ago.
I have tried to re-size the log to 100mb, but any way I have tried (none gave errors), the log file remains the same size. I have tied to shrink the log file (through the UI and via DBCC commands) without success; no errors, but also no change in file size.
I have checked Log Reuse Waits, just in case, and as expected it showed “NOTHING” (select log_reuse_wait_desc, name from sys.databases)
I tried running a checkpoint, but that did not allow any resize or shrink to work.
I have tied creating large transactions to move the used point in the log file, in case this was the issue. I did this by creating tables that I drop after large inserts. While it shows me that the log space % used increased, the log file still does not allow the space to be reduced.
The following is what I was using for the transactions to get the log used.
BEGIN TRAN select a.* into testtable from sysobjects a, sysobjects b, sysobjects c ROLLBACK TRAN
Do I just need to continue running large transactions until the log space used gets high enough to get the “end point” in the log to really move? Is there an easier way to accomplish this (I have several DBs that have the almost identical problem), what I am using moves the Log Space Percent Used about a percent on each execution.
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
I'm using a number of drill-through reports, which are working great. However, if I have drilled through to another report (enabling the back button) and then go into print layout mode, and click the back button while in print layout mode, the report viewer becomes completely unresponsive.
It's easy enough for me to simply not push the button, but if users encounter this error they'll have no choice but to end-task (crashing the viewer and the application that called it), which could result in loss of unsaved data.
Has anyone else run into this problem/discovered a fix for it?
We are using MSSQL 2005 and Log Shipping. After making our secondary SQL server primary, how can we put the secondary SQL server back into standby mode?
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I have sa rights.. I went to the Enterprise Manager and choose a Server and under that server choose a database.
Then I clicked "New Maintenance Plan" and set up a Backup plan for my database.
The problem when I checked the next day the database was not backed up. I have tried this on other database and it has always worked. Why is it not working now? There is enough space on the Drive I am backing to.
One of our database size 90 Gb was backed up and restored on a standyby server with NoRecovery option .A Stored Procedure was written to do production Database transaction log Backup every 15 min and restore the Tlog File on Standby server.
Once the last transaction log file is restored on standby server the tlog file is deleted.
Now due to some reason the restore tlog job failed but the tlog file got deleted.
Now till we will not restore the last database backup we will not be able to start the restore tlog job.
Now the database is in restoring mode.Is there any methord to get that database online without any transactionlog.
Hi there, i would please like to know how to get a database online in T-SQL, I managed to get it offline in T-SQL, but know I can't get the database back online.
Please help.
If someone can please help me with the T-SQL command.
Or direct me to a link where I can get more information.
I have an ASP.NET project that uses a ASPNETDB.MDF file. This file is attached with a SQL Server Express everytime when I run the application, include debuging session. There are two debugging modes under Visual Studio 2005, through WebDev.WebServer or IIS. Both ways worked fine with this project until yesterday. Right now, whenever I try to debug it with WebDev.WebServer, it will show me an error: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. But if I debug it with IIS it works fine. Also, using the same machine and same installation of Visual Studio 2005 I can debug another project with WebDev.WebServer, that also uses a .mdf file (a different file). So, seems to me I jsut have problem with one mode and one project. The only thing that I did yesterday that I think may be relevant was that I reinstalled Visual Studio 2005 SP1 to fix another issue. Anyone has an idea? Thanks!
Hello, I have a configuration where my SharePoint services are running on a web machine and the SQL server reporting services are running on a different machine. I installed SQL SP2 and the SQL Server Reporting Services Add-in for SharePoint by following all the instructions listed in €śConfiguring Reporting Services for SharePoint 3.0 Integration€? (http://msdn2.microsoft.com/en-us/library/bb326356.aspx). My issue is that when I Deploy a report from a VS Report Server project by running Visual Studio on the machine where reporting servicers is running, everything works fine. But if I try the same thing on any other machine on the network, I get a €śThe file you are attempting to save or retrieve has been blocked from this Web site by the server administrators.€? message.
I€™m sure I€™m missing something on the configuration but I can€™t tell what.
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
One annoying problem has occurred....I want to put database in offline mode....but it is giving me some error...
"ALTER DATABASE failed because a lock could not be placed on database [database]. Try again later. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)" ...
I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.
I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.
Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.
Thanks for your help,
Jonathan.
Microsoft SQL Server Management Studio 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 7.0.5730.11 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
