Hi! maybe this question is very common, but i can resolve that. I have a DB marked 'loading', and nothing seems to change this state. Is any way to revert to a normal state, or the only way is destroy and re-create?
Thanks in advance
I canceled a restore job, and now the database is marked (Loading). When I go to propertied it tells me "database can't be opened, it's in a middle of a restore" Yet from a Query window I can connect and run queries against that database. Should I just wait until it finishes "Loading" or should I re-start the server?
Thanks
Hi all,
I want to know if the System.Xml.XmlTextReader is marked with Host Protection Attributes(HPA) in the SQL Server 2005 CLR. I ask this because when I add a custom class that inherits from System.Xml.XmlTextReader, I get a code access security error at runtime. I am able to register the assembly just fine. I have tested it with all the cases and pretty much sure my code breaks in this class.
My code snippet is shown below:
public class CustomXmlTextReader : XmlTextReader
{
......
......
}
Doesn't really matter what is in this class. It gives an expection even when there is no code inside.
Any help is greatly appreciated.
Thanks,
Jai
I have SQL Express SP1 installed on serveral machines, each has one instance and each instance has more than one database.
Recently, I found out some dbs are marked Suspect. MOM tool reported something like:
The database "your-DB" in the instance "Your-Instance" is in a critical state. The state is: Suspect.
I tried to run "DBCC CHECKDB", but it failed.
Can someone tell me what could cause the problem, and how to fix it?
Thanks,
Peter
Hello Gurus,
After the power failure my database was marked suspect. According to my associate we will execute SQL command sp_resetstatus. My question is what are the things we have to do first before we execute the command? We are using NT 4.
thanks
Claudio
Hi all,
I have a big problem with a Database on SQL-Server7.0 running on windows 2000 advanced server. The msdb database is marked suspect. I have tried a few suggestions in the SQL BOL but no luck.
Can anyone help please!
We have a new installation on Friday which was running fine. Unfortunately today, the msdb is marked suspect. Do I uninstall and reload SQL server or change the status. We only have a training data on the server presently. Does anybody has any other suggestions? Please help. Thanks for your assistance.
View 4 Replies View RelatedI have a 3GB DB that I was running an update script on. The sript failed and ran out of transaction log space. I expanded it and stopped and restarted SQL. The user DB is now marked as recovering. How long should I wait for it to recover? I don't want to have to restore again and start all over with my script. Please help.
(By the way this is a restored DB)
Thanks.
LC
Usman Masood writes "we were using our database named db1. when suddenly it stopped working and upon checking it gave an error that db1 and db2 are marked as suspected. while we were only using db1,,,,what is the cause of this error?"
View 1 Replies View RelatedHi all
I tried this command
DBCC CHECKDB (A2PD,REPAIR_ALLOW_DATA_LOSS)
The above command was not executed as there was some long rollback was going from a user...
I stopped and re started the MSSQLSERVR service...
Then i found the follwing error..
how can i work with the database A2PD marked as suspect
Server: Msg 926, Level 14, State 1, Line 1
Database 'A2PD' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Thanks
Simon writes "Hi
using the following syntax in SQL 2005 (win 2003) to restore a log to a marked transaction. Code runs ok but I'm never getting back to the point in time where id 13 still exists , help !
kind regards
Simon
/*backup and overwrite */
BACKUP DATABASE AdventureWorks
TO DISK = N'C:SQL Backupsadw.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup'
GO
/* verify data */
restore verifyonly
from disk = 'C:SQL Backupsadw.bak'
with file = 1
go
-- with MARK to place a marker in the tranny log
BEGIN TRANSACTION CandidateDelete13a
WITH MARK N'Deleting a Job Candidate';
GO
USE AdventureWorks;
GO
DELETE FROM AdventureWorks.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION CandidateDelete13a;
GO
BACKUP LOG AdventureWorks
TO DISK = N'C:SQL Backupsadw_log.bak'
with format, NAME = N'AdventureWorks-Trans Log Backup'
GO
/*
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
*/
use master
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
use master
RESTORE LOG AdventureWorks FROM DISK = N'C:SQL Backupsadw_log.bak'
WITH recovery, STOPatMARK = 'CandidateDelete13a'
GO"
I've got trouble -- somehow my msdb is corrupt and is marked as "suspect". I cannot open it up or it is apparently devoid of any table, etc.
It's on my SQLExpress instance and I'm willing to blow the entire server away -- since this server has been used as a sandbox for personal use.
Can I simply re-install SQLExpress?
Hi,I restored my Windows XP system and discovered that I cannot access aMSDE DB that was saved in D: drive (I restored my primary partition inC). How can I restore it?Thanks a lot.
View 6 Replies View RelatedWe've got a Sharepoint 3.0 SQL database that was partially corrupted during an ill executed BackupExec restore. Now the database will not start up.
Error log reports:
2008-05-07 13:01:50.85 spid8s Starting up database 'model'.
2008-05-07 13:01:50.89 spid8s The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2008-05-07 13:01:50.89 spid5s The database 'msdb' is marked RESTORING and is in a state that does not allow recovery to be run.
2008-05-07 13:01:50.89 spid8s Error: 927, Severity: 14, State: 2.
2008-05-07 13:01:50.89 spid8s Database 'model' cannot be opened. It is in the middle of a restore.
2008-05-07 13:01:50.89 spid8s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2008-05-07 13:01:50.89 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
I'm looking for suggestions on how to get the database back up and running with minimal SQL Server knowledge. If anyone has a quick and dirty how-to that would be appreciated of course any detailed documents specific to this configuration would also be greatly appreciated.
Thank you,
~Pete
hi all,
i have a snapshot replication running on multiple publisher singel subscriber running on sql2k. i'm frequently encountering these error.
the subscription has been marked inactive and must be reinitialized.
no synch
i've set the subcription to never expire in the publications property tab.
thanks
Hi there,
I need urgent help regarding a database that has been
marked as suspect.
I need to get the data from the database!!
I have a copy of the structure of the database but I need to get cerain very recent data from the database.
I have tried bcp and it doesn't work.
The backup that I have won't sufice
If a database is marked as suspect, Will it recover?>
And if so how long will it take.
If it won't recover is there any other way of getting the data,
Thanks in advance,
Fin
Hello Friends
I had a really big Problem.
Sorry about my Englisch, came from Austria.
After a SQL Database Restore, who ends with no faults, i cannot connect to it.
In the Enterprise Manager it is marked as "in load" and in the Errorlogfile stand following.
"Bypassing Recovery für Database "WINLINE" because it is marked "in load"?
What does it mean! Please help!!
Thanks for all answers
Mike
Hi All,
Sql Server 7
I have database called ecatalog
i have a scheduled job which shrinks the database every day once at 12 am
today the job got failed
In the view Job history its showing the below contents
-------------------------------------------------------
Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error
924) DBCC execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528). The step failed.
And in Application Log of event viewer its showing the below contents
------------------------------------------------------------------------------
The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The
local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database
- ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed.
The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..
I ran the job manually it worked fine
but now when i go and see the database ecatalog in my Enterprise Manager
it is showing ecatalog(Single User)
What is the meaning of this, will this make any problem to my database
Please help me in this.
Waiting for Reply
Adil
Hi all,
Is there a method of identifying all the procedures in a database that are marked for autostart?
i.e. select name from sysobjects where blah = 'autostart'
Thanks,
rocr
I read in Microsoft SQL Server 2005 Integration Services by Kirk Haselden that the BULK INSERT task was provided for backward compatibility and its use is disrecommended.
But after looking on the web I cannot find information supporting this.
Do you think the BULK INSERT task should be used for new development?
Thanks
After I restore a database with SQL the database I cannot access it. However, after restoring using Enterprise Manager I do not have the same problem. The message I get is:
Database 'les_test' cannot be opened. It is in the middle of a restore.
The code I am trying to use for the restore is:
RESTORE DATABASE les_test
FROM DISK = 'c:MSSQL7BACKUPiztrobeta_db_200012011201.BAK'
WITH NORECOVERY,
REPLACE,
MOVE 'biztrobeta_PRI' TO 'c:mssql7DATAiztrobeta_PRI.ndf',
MOVE 'biztrobeta_FGE_Dat1' TO 'c:mssql7DATAiztrobeta_FGE_Dat1.ndf',
MOVE 'biztrobeta_FGX_Idx1 ' TO 'c:mssql7DATAiztrobeta_FGX_Idx1.ndf',
MOVE 'biztrobeta_LOG' TO 'c:mssql7Dataiztrobeta_LOG.ldf'
GO
I tried changing the status in the master..sysdatabases tables, but the restored database was still not accessible. I tried to do this using the following statment:
UPDATE sysdatabases
SET STATUS = 4194316
WHERE name = 'les_test'
GO
Any help is appreciated.
Thanks,
Les
i have a SQL 2008 R2 RTM production instance, in which we run dBCC CheckDB every weekend to check on the DB. This weekend this sql job returned the error:
DBCC RESULTS
--------------------
<DbccResults>
<Dbcc ID="0" Error="8928" Severity="16" State="1">Object ID 866531312, index ID 1, partition ID 72057602979266560
, alloc unit ID 72057603064397824 (type In-row data): Page (1:7650240) could not be processed. See other errors
for details.</Dbcc>
<Dbcc ID="1" Error="8939" Severity="16" State="98">Table error: Object ID 866531312, index ID 1, partition ID 720
57602979266560, alloc unit ID 72057603064397824 (type In-row data), page (1:7650240). Test (IS_OFF (BUF_IOERR, pB
UF->bstat)) failed. Values are 12716041 and -6.</Dbcc>
<Dbcc ID="2" Error="8990" Severity="10" State="1">CHECKDB found 0 allocation errors and 2 consistency errors in t
able 'tblDistpatch' (object ID 866531312).</Dbcc>
We tried to rebuild the indexes in the table: tblDistpatch...the non clustered indexes ran fine however the cluster index rebuilt returned an error:
Error: The statement has been terminated.
Msg 829, Level 21, State 1, Line 1
Database ID 3, Page (1:7650240) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.in TEST environment we were able to reproduce this error by restoring latest backup.
we ran :
dbcc checktable (tblDistpatch ,REPAIR_ALLOW_DATA_LOSS )
then we ran dbcc checkdb and no errors where found.
my question comes with in your experience is this the best possible way to fix this table?only one table, but running this in production environment will required to put db in single user mode first.
I am currently investigating how to safely back up the seven related databases that drive Microsoft Team Foundation Server. Microsoft is suspiciously vaigue and inconsistent about how to perform a safe TFS database backup.
This article states "Although SQL Server Management Studio allows you to back up individual databases at a time, restoring from such back ups can cause unexpected results because the databases are related and you risk restoring outdated versions."
Here, that requirement is strengthened (search for text in article 'Backing up Databases') "Because this data is stored in seven related databases, you might want to use marked transactions in the transaction logs of each database. Transaction-log marks help guarantee absolute consistency in the distributed data set."
but then it weakens the case again a few sentences later...
"Databases for Team Foundation Server are loosely coupled; any latency issues generally are limited to relationships between things such as work items and change sets that are created during the backup window."
There are various other TFS administrators with the same worry about backing up the TFS databases - see here and here.
This requirement to back up related databases to a consistent point in time is not new in Microsoft products. It seems that BizTalk server had exactly the same complication. The nice thing for the BizTalk administrators was that Microsoft included a stored procedure to manage backup of related databases in this way. Team Foundation server administrators don't seem to be so lucky.
I've read the documentation for distributed transaction marking but it doesn't give me that high-level overview that I'm looking for. In our installation, all of the seven databases are hosted on the same machine and same instance of SQL Server.
My initial misconception about transaction marking was that I assumed TFS itself would routinely insert marks into the transaction logs of the seven databases during its normal operation, at points in time where it knows that their state is consistent. This doesn't appear to be the case. The explanation of the BizTalk stored procedure indicates that the log mark insertions are initiated by SQL server as a DBA task. It seems that the insertion of these named marks is done just prior to performing database log backups. This makes sense, so presumably once SQL Server is told to make the marks, it waits for a point in time at which all transactions are closed across all related databases before inserting the marks simultaneously.
If I've got that overview correct, then my real problem is that I can't find an example of the T-SQL that would actually do this. In pseudocode I'd expect it to be something like...
begin distributed transaction across (list of 7 databases), timeout 300 seconds
insert transaction log mark 'safe to restore to this point'
end distributed transaction
I suspect that this page will probably end up having all the information on it I need, but after reading it several times I can't quite nail it. If anyone can help then I'd be most grateful.
James
Kill may be overdramatic, but...
I am testing a merge publication with a push subscription. The publication contains a single table. That table has an INSTEAD OF UPDATE trigger marked NOT FOR REPLICATION and an AFTER trigger that needs to go off during replication.
During Synchronization neither trigger goes off. If I drop the instead of trigger the after trigger will go off. What is going on?
Thanks,
Hello,
How can I find the cause on the error that marked one conversation with status 'ER' in sys.conversation_endpoints?
Thanks in advance.
I have the MS SQL2000 database failed to recover at computer restart. Now the database is marked suspect. How can I manage to recover the data?
Thank you.
We are getting below error while stored proc is being run:
SqlException: Cannot get the data of the row from the OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds".
OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds" returned message "Row handle referred to a deleted row or a row marked for deletion.".
Here is the definition of the stored proc:
CREATE PROC [dbo].[usp_MobileAppOnClick_InsertNoCabsTranData_Log]
(
@CustomerMobileNo VARCHAR(40),
@City VARCHAR(50) = '',
@PickUpLat REAL,
@PickUpLng REAL,
@NextAvailableSlot_Meru VARCHAR(20),
[Code] ....
Please, help
Since a few days ago we are viewing these messages, several time at a day:
3/14/2008 2:18:30 PM AppDomain 1668 (FDESK_CANCUN.dbo[runtime].1679) created.
3/14/2008 2:18:44 PM AppDomain 1668 (FDESK_CANCUN.dbo[runtime].1679) is marked for unload due to memory pressure.
3/14/2008 2:18:44 PM AppDomain 1668 (FDESK_CANCUN.dbo[runtime].1679) unloaded.
In this database only have one assemblie installed created using Vb.Net 2005, please help me how to diagnose where are the problem.
Greetings,
We periodically get the following message in our three server peer-to-peer transactional replication environment:
"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."
What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?
Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.
Thanks,
BCB
Would using the Restore cmd be the best option to
take a backup from one database(db1) and apply it to
another database(Edb2) even though they have different names?
I want to load the xml files data in the sqlserver database.
Please advice..
I am trying to load many XML files into SQL Server 2005 Standard Edition. Each XML file contains many individual XML items, each starting with: <?xml version="1.0" encoding="UTF-8"?> The files do not have a .xml extension. I do have a schema for the data. I can also get hold of a dtd file for the data.
Since I am new both to XML and to databases I have been trying to follow examples wherever I can find them. I have not found the right example or tutorial. The closest that I found was in Kirk Haselden's book (a very nice book to learn from, by the way). On page 73 he sets up an OLE DB Source. His example works fine. But when I try to use XML Source instead of OLE DB as the source, I cannot get it to work. A typical error that I get reads as follows:
"Error at Data Flow Task [XML Source [1]]: There was an error setting up the mapping. The 'http://www.w3.org/XML/1998/namespace:lang' attribute is not declared."
Evidently I am doing something wrong, but what? Can someone point me to an example of using SSIS to load many XML documents that I could use as a model?
Thanks.
We are trying to load flat text files with upwards of 7 million records into a table on SQL. The table has a clustered index on 3 fields. We are sometimes able to complete smaller tables (500,000-750,000 records) and build the indexes prior to importing the data, however when we try the larger tables an error occurs :
Error at Destination for row number 6785496. Errors encountered so far in this task: 1
Location: somerge.c:1573
Expression: mrP->mrStatus!=MERGERUN::NONE
SPID: 11
Process ID: 173
None of the recods end up importing. The row number it gives is always the total number of records that was in the text file I was trying to import. I tried to import the text files first and then build the clustered indexes but a table with only 300,000 records ran for nearly 4 days without completing before we killed it.
Please help me on where to look......:(
Thanks,
Cheri