/ Forside/ Teknologi / Udvikling / SQL / Spørgsmål
Login
Glemt dit kodeord?
Brugernavn 

Kodeord  


Reklame
Top 10 brugere
SQL
#NavnPoint
pmbruun 1704
niller 962
fehaar 730
Interkril.. 701
ellebye 510
pawel 510
rpje 405
pete 350
gibson 320
10  smorch 260
Suspect database?
Fra : bjef
Vist : 667 gange
400 point
Dato : 15-06-01 08:40

Hej jeg har en database i MS SQL 7.0 som har kørt fint i de sidste par år.

Her til morgen da jeg skulle prøve at gå ind i den via MS SQL server manager, skriver den, at databasen er suspect, og jeg kan hverken læse fra den eller skrive til den.

Hvad kan jeg gøre for at få den op og køre igen? Det er rimelige vigtige dataer som ligger i den, det er hele vores intranet her i firmaet, og der er selvfølgelig ingen backup.

"PLEASE HELP ME" inden min chef finder ud af det.

Bjef

 
 
Accepteret svar
Fra : Nyhedsbruger

Modtaget 410 point
Dato : 15-06-01 09:08

> databasen er suspect,
> Hvad kan jeg gøre for at få den op og køre igen?

copy&paste.......

What causes a suspect database? How can I fix this?
A database can become suspect if one of these conditions is true:
- If one or more database files are not available.
- If the entire database is not available.
- If one or more database files are corrupted.
- If a database resource is being held by the operating system.

To resolve a suspect database:
- Check the SQL Server error log and resolve all problems.
- Reset the suspect status by executing sp_resetstatus.



Resetting the Suspect Status
Microsoft® SQL Server™ returns error 1105 and sets the status column
of sysdatabases to suspect if SQL Server is unable to complete
recovery on a database because the disk drive no longer has any free
space. Follow these steps to resolve the problem:

Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL
Server should be able to complete recovery of the database.

Free disk space and rerun recovery.
sp_resetstatus, shown below, turns off the suspect flag on a database
but leaves all other database options intact.


--------------------------------------------------------------------------------

Caution Use sp_resetstatus only when directed by your primary support
provider or as recommended in Troubleshooting. Otherwise, you might
damage your database.


--------------------------------------------------------------------------------

Because this procedure modifies the system tables, the system
administrator must enable updates to the system tables before creating
this procedure. To enable updates, use this procedure:

USE master

GO

sp_configure 'allow updates', 1

GO

RECONFIGURE WITH OVERRIDE

GO



After the procedure is created, immediately disable updates to the
system tables:

sp_configure 'allow updates', 0

GO

RECONFIGURE WITH OVERRIDE

GO



sp_resetstatus can be executed only by the system administrator.
Always shut down SQL Server immediately after executing this
procedure.

The syntax is

sp_resetstatus database_name

This example turns off the suspect flag on the PRODUCTION database.

sp_resetstatus PRODUCTION



Here is the result set:

Database 'PRODUCTION' status reset!

WARNING: You must reboot SQL Server prior to accessing this database!



sp_resetstatus Stored Procedure Code
Here is the code of the sp_resetstatus stored procedure:

CREATE PROC sp_resetstatus @dbname varchar(30) AS

DECLARE @msg varchar(80)

IF @@trancount > 0

BEGIN

PRINT "Can't run sp_resetstatus from within a
transaction."

RETURN (1)

END

IF suser_id() != 1

BEGIN

SELECT @msg = "You must be the System Administrator (SA)"

SELECT @msg = @msg + " to execute this procedure."

RETURN (1)

END

IF (SELECT COUNT(*) FROM master..sysdatabases

WHERE name = @dbname) != 1

BEGIN

SELECT @msg = "Database '" + @dbname + "' does not exist!"

PRINT @msg

RETURN (1)

END

IF (SELECT COUNT(*) FROM master..sysdatabases

WHERE name = @dbname AND status & 256 = 256) != 1

BEGIN

PRINT "sp_resetstatus can only be run on suspect
databases."

RETURN (1)

END

BEGIN TRAN

UPDATE master..sysdatabases SET status = status ^ 256

WHERE name = @dbname

IF @@error != 0 OR @@rowcount != 1

ROLLBACK TRAN

ELSE

BEGIN

COMMIT TRAN

SELECT @msg = "Database '" + @dbname + "' status
reset!"

PRINT @msg

PRINT " "
PRINT "WARNING: You must reboot SQL Server prior
to "

PRINT " accessing this database!"

PRINT " "

END



GO



Godkendelse af svar
Fra : bjef


Dato : 15-06-01 17:03

Tak for svaret JH, du har lige reddet min weekend.
                        

Du har følgende muligheder
Eftersom du ikke er logget ind i systemet, kan du ikke skrive et indlæg til dette spørgsmål.

Hvis du ikke allerede er registreret, kan du gratis blive medlem, ved at trykke på "Bliv medlem" ude i menuen.
Søg
Reklame
Statistik
Spørgsmål : 177348
Tips : 31960
Nyheder : 719565
Indlæg : 6407366
Brugere : 218867

Månedens bedste
Årets bedste
Sidste års bedste