Start by looking in the error log. It should tell you what file it couldn't access. If the file was temporarily unavailable while SS was trying to open the database, try taking the DB offline and then online again.
suspect db
6 posts
• Page 1 of 1
- Microsoft Project
- mac
- Microsoft Project
- printer
- c++
- MS Office Access
- MS Office Access
- xml
- delphi
- MS SQL SERVER
Jump to:
- 1. Backup - filename
Heillo I want to get a timestamp in my filename when I take a backup I have tried the following syntax === S T A R T == BACKUP DATABASE databas TO DISK SELECT 'C:\Backup\database\ + (CONVERT(char(4), Year(GetDate())) + (CONVERT(char(2), Month(GetDate())) + (CONVERT(char(2), Day(GetDate())) + (CONVERT(char(2), DATEPART(hh, GetDate())) + (CONVERT(char(2), DATEPART(hh, GetDate())) + '.bak === S T A R T == When I try to run this I get Incorrect syntax near the keyword 'SELECT' Anyone who have an idea how to automatic get date and time in the filename Please reply to may E-mail address so that I don't need to read the news for reply ;- regard John - 2. Errors during Maintenance Plan execution
I am getting error on several database when trying to use the Maintenance Plan to backup all databses in our SQL Server 2000 with Service Pack 3. The following is the text generated from one of the database in the log file. The other databases generate the same error message Log portion============================== [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'SQLCatalog' [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed. [Microsoft][ODBC SQL Server Driver][SQL Server] sp_dboption command failed. [18] Database SQLCatalog: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. The following errors were found: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. ** Execution Time: 0 hrs, 0 mins, 1 secs ** ======================================== Any ideas on how to handle this? Is this a matter of running the maintenance plan at a different time? - 3. Maintenance job failure..
Any idea about this error? [Microsoft SQL-DMO] Error 21268: [SQL-DMO]Row or column specified is outside the range of the specified query result set - 4. What user id is used when you use xp_cmdshell
Dear All, We have a requirement to copy files onto a virtual unix drive. We will not be using DTS, instead we will use a standard .bat command to copy the files over, and we are doing this though SQL Server. This will be though the user application i.e. the user presses a button which in turn creates a file on the server, which in turn copies it onto the virtual drive. The users who do this will not be systems administrators. My question is this, in the unix drive we will need to set up users, what user should be set up on the virtual drive, MSSQLServer, SQLExecutiveCmdExec, or SQLAgentCmdExec ? Thanks in advance J - 5. Active directory and SQL server 2000
Does anybody know of any gotchas that can occur if we were to move to active directory? Somebody has mentioned that there may be problems moving a database from an AD server to a non-AD server, is this true? From looking at BOL, it appears that SQL is added in by sql itself and not by AD. Not knowing a lot about this I'm not in a position to comment. Any tips or pointers to links on this would be appreciated. TIA
Next
Re: suspect db
by Don Ferguson » Sat, 12 Jul 2003 05:47:51 GMT
Re: suspect db
by Dinesh.T.K » Sat, 12 Jul 2003 06:00:04 GMT
Francisco, Please refer my reply in .setup and please dont crosspost.If you want to include more relevant newsgroups, please do so in a single post.This way you wont be wasting other's time and effort when the same suggestion is already supplied elsewhere. Thank you! Dinesh.
Suspect DB
by Miguel » Sat, 15 Nov 2003 04:31:58 GMT
Hi, How can i recover a Suspect DB ????? Thanks CMLC
Re: Suspect DB
by Sue Hoegemeier » Sat, 15 Nov 2003 04:59:39 GMT
Depends on why it bacame suspect. You need to check your logs (SQL and OS) and address the issues that caused the database to become suspect. -Sue On Thu, 13 Nov 2003 11:31:58 -0800, "Miguel"
Re: Suspect DB
by Tibor Karaszi » Sat, 15 Nov 2003 05:03:46 GMT
Here are the general recommendations for handling a suspect or corrupt database: 0. Ensure you have a backup strategy that you can use to recover from hardware failures (including corruption). I recommend performing both database and log backup in most situations. 1. If you can run DBCC CHECKDB against the database: Search Books Online and KB for the error numbers that CHECKDB gives you. There might be specific info for that type of error. 2. Find out why this happened. Check eventlog, do HW diagnostics etc.; search Books Online and KB for those errors. You don't want this to happen again! If the database is suspect, the file might have been in use by for instance an anti-virus program and restarting SQL Server might be all that is needed - but you still want to read logs etc to find out what happened. 3. If there is a hardware problem, ensure the faulty hardware is replaced. 4. Backup the log. This assumes that log backup schedule is in place, of course. If the database is suspect, then the NO_TRUNCATE option for the RESTORE command must be used. Also, you might want to do a file backup of the mdf and ldf files, for extra safety. 5. Restore is the best thing to do now. If you managed to backup log as per step 4, then you will most probably have zero dataloss. You should restore the latest clean database backup and the subsequent log backups including the one taken in above step. If the database isn't suspect, then DBCC with a REPAIR option might be a secondary option but this will often result in loss of data. Additional solutions, depending on the errors, may be to manually rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on. If the database is suspect, a secondary option can be to try to "un-suspect" the database using sp_resetstatus. Read about it (books online, KB, google etc). It might help but if the database is too damaged, it might just pop back to suspect again. There's also something called "emergency mode" which is a "panic" status you can set in order to try to get data out of a damaged database. I think the name of that option speaks for itself. Again search the net for info. If you feel uncertain with above steps, I recommend letting MS hand-hold you through the steps appropriate for your particular situation. -- Tibor Karaszi, SQL Server MVP Archive at: http://www.**--****.com/
Suspect DB
by anonymous » Sat, 15 Nov 2003 07:28:34 GMT
Royal way is to restore from Good backup. Or change status to Good in Sysdatabases table for this DB and restart server and see.If this dose not work restore. But investigating why it went suspect will help a lot.
Similar Threads:
1.'Suspect' DB won't open - OS error 32- Help!
Help! One of my main databases has been marked as suspect - the initial problem is that my disk had no space. So I moved some files, made some space, stopped & restarted the service, and now I get this error in the errorlogs. There can't be anything else using the database - I restarted the machine to kill everything that might be touching it, including the client application. 2005-03-30 14:35:21.96 kernel udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device c:\mssql7\data\<database>.mdf. 2005-03-30 14:35:22.01 kernel FCB::Open failed: Could not open device c:\mssql7\data\<database>.mdf for virtual device number (VDN) 1. 2005-03-30 14:35:22.03 spid6 Device activation error. The physical file name 'c:\mssql7\data\<database>.mdf' may be incorrect.
2.suspected db? (SQL Server 2005)
Hi, I created a database with an additional file group (FG2). Then created two tables: one on primary and the other on FG2. FG2 is located on my flash memory(USB) and when I disconnect the memory, the table is still queried and I can insert new records, while DBCC CHECKDB reports error. How is it possible? Thanks in advance, Leila
Running SQL 2K & WIn 2K A dB appeared as suspect. I ran sp_resetstatus 'dB', restarted SQL & it is still supect. When I run sp_detatch_db 'dB' in QA, it will not allow this because the dB is being used for replication. The dB is 5 gig & log is 34 gig (it has not been getting backed up, opps). I have moved the suspect dB to another folder, when SQL is restarted, the dB still appears in EM as suspect, though the dB nor log file is in the folder where is should be. HELP!!!! What can I do to get this dB out of suspect mode & back on line?? AHIA, Larry....
4.Remove Merge Replication (Suspect DB)
If I have a DB with suspect status (failed to close properly after failed back-up) is there any way I can remove the replication? At the moment it says I cannot as the DB is not accessable. I can't remove the DB because it says it is being replicated. Cheers
Hi, I have recently installed a monitoring tools on the sql server. I want to simulate the suspect db. I used 2 approaches: Approach 1 ------------ 1. Shutdown sql server 2. File level rename one of the data file 3. Startup the sql server Approach 2 ------------ 1. Shutdown sql server 2. File level truncate one of the data file 3. startup the sql server for both approaches, i see the 'suspect' state when i view the databases via EM. however, when i 'select name,status from master.dbo.sysdatabases where status&256=256', only the database made by approach 2 appears. Can anyone tell me if the database made by approach 1 is really 'suspect'??? i get confused why the database in approach one is viewed as 'suspect' but from the definition from BOL, it is not?
7. Suspect DB.
6 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 24 guest