Hi there,
I'm new to this forum but was wondering if you could advise me on getting the DB back to an accessible state. I tried restoring the DB but unfortunately I don't have enough disk space. The error message appeared instantly informing me of the lack of disk space. Now it says its in (Loading state) and I cannot access the DB. Is there anyway I can manually change it back??
I'm testing log shipping locally and had it running between two boxes. I tried to remove log shipping so on the primary I unchecked "Enable this as a primary database in a log shipping configuration" as per another thread. The primary stopped after a bit but the secondary never came out of the (Restoring...) state.
I'm obviously missing a step that needs to be run on the secondary because what I did wouldn't work if site A exploded but I can't figure out what the last step is.
Thanks,
Jeff
HiI am trying to copy a 20 GB database from server 1 to 2. I took a fullbackup, copied to server2 and restored ( ready for use) using EM.After the message restore completed, I see the database's status as LOADING.We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv Servers.I ran dbcc on the source DB and it returned no errors. I tried the command'restore db with recovery ' which didnot help. I get an message saying thatthe data file is only partially restored.. It seems the last resort is tochange the status in system tables.Any ideas?ThanksRagu
View 13 Replies View RelatedI recently installed MSDE on a laptop. I then restored several databases from backup files without problem. However, one database is constantly stuck at the grey colour displaying the (Loading) message.
Why won't this database restore ? It is of course the most important database and I only have the one backup file.
This only appears to fail on his server. Do I need to uninstall MSDE and start over ?
I have a Win 2003 server running 3 instances of SQL 2000 Enterprise.
The problem is I can only start 2 of the 3 Job Agents at a time, as the 3rd gets stuck in a starting state.
Has anyone struck this problem before?
Thanks.
Hi
The database is stuck on a restoring mode.
How can i get it back online or in a read only mode?
Sql 2005 Server
Stefan:confused:
I seem to have a problem with one of my SQL Server Express databases. I started a restore task from a SQL Server 2005 BAK file (that included full-text data), which couldn't continue so I cancelled. Now, the database I attempted to restore to has the status "(Restoring...)".
This is a tiny database and a restore should (if it could) take only a few seconds.
I'm using SQL Server 2005 Management Studio (v9...).
Anyone know how to either cancel the restore or get the database into a good state again? I've tried stopping and starting (and restarting) the express instance, with no joy.
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
Thanks in advance.
while loading the transaction log dump on a 'STANDBY' database the connection got terminated. as a result the database was marked 'LOADING' and inaccesible. i would like to know if there is a way to get the db out of this mode without having to reapply a full database dump.
P.S. please ignore the previous two messages posted with the same subject line. i inadvertently pressed post twice. sorry for the spam.
The database in Mirrored server is always in the state of restoring only.
the database in mirrored server is restored with norecovery
then we started mirroring.
the status of mirroring is successfull and is synchronized.
but the Mirrored server is always in the state of restoting
not allowing use to open the database.
could any one know why this problem is comming and give some solution for this problem so that the mirrored database can be accessible opened and queryed to really verfiy that the changes made in principal database is mirrored to Mirrored database.
When performing a tail log backup to an online database through SSMS, the database is placed in restoring state. Is there a proper procedure to take the database out of restoring state after the backup is completed?
View 1 Replies View RelatedI am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
I have a database called PrimaryJunk that is being log shipped to another location, secondary database is secondaryjunk. From PrimaryJunk to SecondaryJunk logs ship and apply fine with no issues. So I figured lets make sure that I am able to perform a role change and swap the roles and that is not working well. My original primary db is stuck in restoring state.
I manually backed up the active transaction log on primary server by performing a transaction log backup with the option 'backup the tail of the log and leave the db in restoring state'
MS site has the same step but mentions NORECOVERY. I am not sure if my step above does that automatically. http://msdn2.microsoft.com/en-us/library/ms191233.aspx
Wonder if thats the reason my original primary db is still in restoring state. any idea?
Hi guys, right now I am having insufficient hard disc space at the secondary server (Mirror side) due to the large db that I have. Therefore, I get a new HDD on the server (Assuming located at drive G) and would like to shift the current mirroring db to that new drive. Is it possible for me to do that ? Hope able to get any suggestion from here ASAP. Thanks.
Best Regards,
Hans
I noticed that after a SQL AlwaysOn failover, one of the DB in the secondary replica is stuck in Restoring state. The primary replica shows that it is in a synchronized state. These are the error logs from SSMS. How do I trace the cause of the problem?
Error: 5901, Severity: 16, State: 1.
Nonqualified transactions are being rolled back in database for an AlwaysOn Availability Groups state change. Estimated rollback completion: 0%. This is an informational message only. No user action is required
Error: 18400, Severity: 16, State: 1.
One or more recovery units belonging to database failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources. This is an informational message only. No user action is required.
Why do I leave my DB in Restoring State after attaching to the instance? Could not this fire a redo activity on Transaction Log?
Or, How does sql server undo all uncommitted transactions on active vlf during recovery?
I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).
View 4 Replies View RelatedHow does one correctly go about restoring the MSDB database?
How/what should I do with the status (loading)?
I have a good backup of the MSDB.
Any help will be appreciated.
I am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
Any advice on how to get this thing finished out?
Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so
created a new database with text indexing enabled and the following table
create table support (problemId VARCHAR(50) NOT NULL PRIMARY KEY, problemTitle varchar(50) NOT NULL, problemBody text NOT NULL, linkOne varchar(50), linkTwo varchar(50), linkThree varchar(50), linkFour varchar(50), ftid int NOT NULL)
next
create fulltext catalog remoteSupportCatalog
create unique index ui_remotesupport ON support (ftid)
then
create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog
--------
I then populated some rows and issues a quesry
Select * from support where freetext(problemBody, 'test database')
it works pulls back all the data I expected it to pull back
In my asp page I created a database connection with the folling select command
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"
SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">
created the search parameter<SelectParameters>
<asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button
</SelectParameters>
and it doesnt give me back an error or data it does nothing. What am I missing?????
I hate making complecated queries....but this doesn't seem too hard and I cannot figure out where my error is. The message is "Incorrect syntax near the keyword 'FROM'."
The query is:
SELECT tblUsers.lUserID GROUP BY tblUsers.lUserID HAVING Count(tblDLHistory.lDownloadID) = 0 FROM tblUsers LEFT JOIN tblDLHistory ON tblUsers.lUserID = tblDLHistory.lUserID WHERE tblDLHistory.lVersionID = 5
The tables are
tblUsers
_______
lUserID
tblDLHistory
__________
lDownloadID
lUserID
lVersionID
What on earth am I doing wrong?
I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
Select a.company
From dbo.contact1 as a,
(
Select DISTINCT accountno
From dbo.contsupp
Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%'
) as b
Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
------------------------
|dbo.contact1 |
|------------------------|
|accountno | company |
|------------------------|
|5123 | Alpha |
|4158 | Beta |
|2121 | Gamma |
|5555 | Omega |
------------------------
--------------------------------------------
|dbo.contsupp |
|--------------------------------------------|
|accountno | contact | contsupref |
|--------------------------------------------|
|5123 |Product Inventory|device 01 |
|2121 |product note |the note |
|2121 |Product Inventory|device 02 |
|5123 |Email Address |a@b.com |
|4158 |ladkafndkaldkfna |device stopped|
--------------------------------------------
**note: i am using MSSQL 2000
Hello,
I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?
Hi
I am completely stuck trying to make a query
Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.
Here is an example of the tables in a shorten downed version (in both data and fields):
OrderHeader:
Sales Order Ref, Order Date,
1, 01/05/2008
2, 01/05/2008
3, 02/05/2008
4, 02/05/2008
OrderLines:
Sales Order Ref, Part Number
1, 222
1, 234
1, 333
2, 222
2, 555
2, 444
3, 333
3, 111
4, 222
StockCategory
Stock Category, Part Number,
A, 222
B, 234
C, 333
D, 444
E, 111
Stock Group:
Stock Group, Stock Category, Priority
Berr, A, 1
Berr, B, 1
OGD, C, 2
OGD, D, 2
DFID , E, 3
The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.
I am using the following query:
DECLARE @Period DATETIME
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
SET @Period =’2008-05-01’
SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT
COUNT(oh.[Sales Order Reference])
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])
GROUP BY oh.[Sales Order Reference]
Is this possible to do?
Thanking you in advance!!!
Hi,
Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?
<task id="2" name="Saw 1" color="#99ccff" expand="true" />
<task id="3" name="Saw 2" color="#99ccff" expand="true" />
<VJobs id="3" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="Unknown" />
<customproperty taskproperty-id="tpc1" value="17.938 " />
<customproperty taskproperty-id="tpc2" value="Unknown" />
<customproperty taskproperty-id="tpc3" value="0" />
<customproperty taskproperty-id="tpc4" value="Operator Unknown" />
</VJobs>
</task>
Here is the query, which details jobs to be done on different equipments
SELECT EquipmentID + 1 as id,
EquipmentDescr as [name],
'#99ccff' AS color,
'true' AS [expand],
(SELECT JobID + 2AS id,
'Layout#' AS [name],
'#99ccff' AS color,
(SELECT [taskproperty-id] AS [taskproperty-id],
[value] AS [value]
FROM dbo.JobDetails customproperty
WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE)
FROM VJobs
WHERE VJobs.EquipmentID = task.EquipmentID
FOR XML AUTO, TYPE)
FROM VEquipments task
ORDER BY EquipmentDescr
FOR XML AUTO, TYPE
Hello, Well actually I am beginner to ASP.Net, I am working on Microsoft Virtual PC with VS 2005 and SQL Server 2005 installed. Now when Create web application in Studio all works fine with databases but when i try to write some code for accessing SQL Databases I keep getting this error PLZ heLP.Following is my code<% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><html><script language="C#" runat="server" Debug="false" >SqlConnection sqlcon;protected void Page_Load(Object Src, EventArgs e){ sqlcon=new SqlConnection("Data Source=VS02005;Initial Catalog=Gaurav;Integrated Security=True"); SqlDataAdapter sqlcom = new SqlDataAdapter("select * from Employee", sqlcon); DataSet ds = new DataSet(); sqlcom.Fill(ds,"Employee"); DataGrid1.DataSource = ds.Tables["Employee"].DefaultView; DataGrid1.DataBind();}</script><form runtat="server"> <asp:datagrid id="DataGrid1" runat="server" /></form></html>This is the error i am gettingServer Error in '/' Application.--------------------------------------------------------------------------------SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. I am stuck dont know what to do, I hve checked all permissions for user gaurav, I also gave administrator rights to gaurav but nothing is working PLEASE help me.
View 4 Replies View RelatedSorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
View 2 Replies View RelatedHi All
I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file).
The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" ..
I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.
Any idea why it is happening ?
If any body ahs any solution you can call me at 1 860 520 7454.
Sujit
I have a table with 4 relevant fields (blank lines added for clarity).
State, City, Name, Primary_Contact
IL, Springfield, Bill, n
IL, Springfield, Frank, n
IL, Springfield, Larry, n
IL, Bloomington, Steve, n
IL, Bloomington, Chris, y
IL, Chicago, Betty, n
IL, Chicago, Linda, n
IL, Chicago, Sue, n
I need a query to return the state and cities that don't have a
Primary_Contact='y'
So the results would be:
IL, Springfield
IL, Chicago
That's it. Any help is greatly appreciated.
I need the results from the following query to be with the results of the second query. Any ideas?
Code:
SELECT
PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME,
CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4,
CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4,
Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud
FROM PR LEFT OUTER JOIN
Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN
CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN
CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3
CROSS JOIN
CFGMain
Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'
and
Code:
SELECT *
FROM LD
WHERE (BilledPeriod = '200408') AND (WBS1 = '001-298')
Thanks.
Hi,
I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:
Case 1:
PK | FK | Type | Text
--------------------------
1 | 226 | 0 | some text goes here
2 | 226 | 1 | NULL
Case 2:
PK | FK | Type | Text
--------------------------
3 | 334 | 0 | some text goes here
4 | 334 | 1 | actual text I want to select is in this cell
I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.
I had started writing it as
select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )
but this returns both rows. What I what is something that I think is more akin to
case a || case b
expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.
I hope you can understand what I'm trying to get and any suggestions would be much appreciated.
Thanks in advance,
Peter