suspect db



  • 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

Re: suspect db

Postby Don Ferguson » Sat, 12 Jul 2003 05:47:51 GMT

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.

Re: suspect db

Postby Dinesh.T.K » Sat, 12 Jul 2003 06:00:04 GMT


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!

Suspect DB

Postby Miguel » Sat, 15 Nov 2003 04:31:58 GMT


How can i recover a Suspect DB ????? 


Re: Suspect DB

Postby 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. 


On Thu, 13 Nov 2003 11:31:58 -0800, "Miguel"

Re: Suspect DB

Postby Tibor Karaszi » Sat, 15 Nov 2003 05:03:46 GMT

Here are the general recommendations for handling a suspect or corrupt

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:

Suspect DB

Postby 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

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)

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,

3.Suspect db

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.


What can I do to get this dB out of suspect mode & back on line??


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.


5.Simulate suspect DB


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 

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?

6. Suspect DB again...

7. Suspect DB.

8. Cannot detach suspect db



Who is online

Users browsing this forum: No registered users and 24 guest