SQL Server Performance

Sql 6.5 Problem

Discussion in 'General DBA Questions' started by siuwake, Jul 24, 2006.

Page 1 of 2

  1. siuwake New Member

    My Sql server have problem, i only can copy the Data.DAT file, Can i restore the Sql server?
    My Sql server version is 6.5. thx
    siuwake, Jul 24, 2006
    #2

  2. shashank sharma New Member

    hi
    can u be a bit clear siuwake....

    sasank

    failure is elder brother of sucess
    shashank sharma, Jul 24, 2006
    #2

  3. siuwake New Member

    ????
    siuwake, Jul 25, 2006
    #2

  4. satya Moderator

    IF you are sure about that .DAT file is a backup file then yes, before that you have to create the database with similar size and then try to load.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Jul 25, 2006
    #2

  5. siuwake New Member

    Thx the Reply , my dat file not backup file , can not use the restore tools. what can i do ? Thx
    siuwake, Jul 25, 2006
    #2

  6. satya Moderator

    You are not giving enough information to give a solution, thats what Shashank asked above.
    Be specific with your details and you will get solution.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Jul 25, 2006
    #2

  7. siuwake New Member

    Thx !

    My Server work in the window 2000 server. use sql 6.5 , My server hard disk have problem, the os can not start. and the partition C: can not read . the data file save in the partition D: and E: . so i only can copy the Dat file to New server. but i can not use the restore tools. The Dat file is not backup file. Can u help me Pls ? Thx
    siuwake, Jul 25, 2006
    #2

  8. satya Moderator

    Thats better now, before we go into details confirm whether you've a database backup for this server or not.

    In SQL 6.5 it is not easy to attach a data file, only way is to restore from a backup file.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Jul 25, 2006
    #2

  9. siuwake New Member

    i try to copy the old dat file in to the new server, the sql server can start but the Database is suspect. what can i do ? thx
    siuwake, Jul 25, 2006
    #2

  10. satya Moderator

    Are the files between the old and new server are same in size, refer the following extract from SQL 6.5 Books online:
    quote:
    The sp_resetstatus procedure 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 this manual. 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

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

    Database 'PRODUCTION' status reset!
    WARNING: You must reboot SQL Server prior to
    accessing this database!

    Stored Procedure Code

    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



    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Jul 25, 2006
    #2

  11. thomas New Member

    you could also try running a series of DISK REINIT statements, but it's tricky. do you have a database rebuild script, from sp_help_revdatabase? This would be most handy.
    thomas, Jul 25, 2006
    #2

  12. siuwake New Member

    Thx satya and thomas,

    satya , this statment can not work . i use this statment after reboot the database show me suspect. Thx

    thomas , i don't have the database rebuild script. can i found the books online? Thx

    I'm can not use the single user mode, can u give me some solution?

    Command

    sqlservr /c/dc:mssqldatamaster.dat /m

    this command is ture?
    siuwake, Jul 25, 2006
    #2

  13. siuwake New Member

    The Error Log

    2006/07/25 18:32:46.01 kernel Microsoft SQL Server 6.50 - 6.50.416 (Intel X86)
    Jan 23 1999 14:10:24
    Copyright (c) 1988-1997 Microsoft Corporation

    2006/07/25 18:32:46.01 kernel Copyright (C) 1988-1994 Microsoft Corporation.
    2006/07/25 18:32:46.01 kernel All rights reserved.
    2006/07/25 18:32:46.01 kernel Logging SQL Server messages in file 'C:MSSQLLOGERRORLOG'
    2006/07/25 18:32:46.01 server Using Critsecs w/ spincount of 4000
    2006/07/25 18:32:46.03 kernel initconfig: number of user connections limited to 15
    2006/07/25 18:32:46.03 kernel SQL Server is starting at priority class 'normal' with dataserver semaphore = 1 (2 CPUs detected, SMPStat=0).
    2006/07/25 18:32:46.03 server LogLRU buffers: 0
    2006/07/25 18:32:46.03 kernel Attempting to initialize Distributed Transaction Coordinator
    2006/07/25 18:32:46.03 server Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
    2006/07/25 18:32:46.03 kernel initializing virtual device 0, C:MSSQLDATAMASTER.DAT
    2006/07/25 18:32:46.03 kernel Opening Master Database ...
    2006/07/25 18:32:46.04 spid1 Loading SQL Server's default sort order and character set
    2006/07/25 18:32:46.04 spid1 Recovering Database 'master'
    2006/07/25 18:32:46.06 spid1 Recovery dbid 1 ckpt (6940,16) oldest tran=(6940,15)
    2006/07/25 18:32:46.07 spid1 1 transactions rolled forward
    2006/07/25 18:32:46.09 spid1 Activating disk 'MSDBData'
    2006/07/25 18:32:46.09 kernel initializing virtual device 127, C:MSSQLDATAMSDB.DAT
    2006/07/25 18:32:46.09 spid1 Activating disk 'MSDBLog'
    2006/07/25 18:32:46.09 kernel initializing virtual device 126, C:MSSQLDATAMSDBLOG.DAT
    2006/07/25 18:32:46.09 spid1 server name is 'SQLSERVER2K'
    2006/07/25 18:32:46.09 spid1 Recovering database 'model'
    2006/07/25 18:32:46.09 spid1 Recovery dbid 3 ckpt (338,9) oldest tran=(338,0)
    2006/07/25 18:32:46.09 spid1 Clearing temp db
    2006/07/25 18:32:46.20 kernel Read Ahead Manager started.
    2006/07/25 18:32:46.20 kernel Using 'SQLEVN60.DLL' version '6.00.000'.
    2006/07/25 18:32:46.20 kernel Using 'OPENDS60.DLL' version '6.00.01.02'.
    2006/07/25 18:32:46.20 kernel Using 'NTWDBLIB.DLL' version '6.50.409'.
    2006/07/25 18:32:46.20 ods Using 'SSNMPN60.DLL' version '6.5.0.0' to listen on '\.pipesqlquery'.
    2006/07/25 18:32:48.21 spid11 Recovering database 'pubs'
    2006/07/25 18:32:48.23 spid11 Recovery dbid 4 ckpt (779,24) oldest tran=(779,23)
    2006/07/25 18:32:48.25 spid11 1 transactions rolled forward in dbid 4.
    2006/07/25 18:32:48.25 spid12 Recovering database 'msdb'
    2006/07/25 18:32:48.25 spid12 Recovery dbid 5 ckpt (3591,23) oldest tran=(3591,22)
    2006/07/25 18:32:48.25 spid12 1 transactions rolled forward in dbid 5.
    2006/07/25 18:32:48.25 spid1 Recovery complete.
    2006/07/25 18:32:48.25 spid1 SQL Server's default sort order is:
    2006/07/25 18:32:48.25 spid1 'nocase' (ID = 52)
    2006/07/25 18:32:48.25 spid1 on top of default character set:
    2006/07/25 18:32:48.25 spid1 'iso_1' (ID = 1)
    2006/07/25 18:32:48.28 spid1 Launched startup procedure 'sp_sqlregister'
    2006/07/25 18:33:00.96 kernel udcreate: Operating system error 80(The file exists.) encountered
    2006/07/25 18:33:23.54 kernel initializing virtual device 1, X:cca_data.DAT
    2006/07/25 18:34:08.45 kernel initializing virtual device 2, X:cca_log.DAT
    2006/07/25 18:37:10.28 kernel dbsvolopen: Backup device 'C:Documents, DISK = and, DISK = SettingsAdministratorDesktopNew, DISK = Folderackup.DAT' failed to open, operating system error = 3(The system cannot find the path specified.)
    2006/07/25 18:37:19.32 kernel dbsreadcheck: Backup device 'E:ackup.DAT' failed to open, operating system error = 3(The system cannot find the path specified.)
    2006/07/25 18:38:26.06 kernel dbsreadcheck: Backup device 'E:ackup.DAT' failed to open, operating system error = 3(The system cannot find the path specified.)
    2006/07/25 18:40:17.18 kernel dbsreadcheck: Incorrect File indicator specified for dump device E:ackup.DAT : Cannot find file 2 on device 'E:ackup.DAT'
    2006/07/25 18:44:50.67 spid10 Recovery dbid 6 ckpt (1122199,3) oldest tran=(1122199,0)
    siuwake, Jul 25, 2006
    #2

  14. siuwake New Member

    sorry the Error is wrong

    This one is ture
    2006/07/25 18:30:07.40 kernel Microsoft SQL Server 6.50 - 6.50.416 (Intel X86)
    Jan 23 1999 14:10:24
    Copyright (c) 1988-1997 Microsoft Corporation

    2006/07/25 18:30:07.40 kernel Copyright (C) 1988-1994 Microsoft Corporation.
    2006/07/25 18:30:07.40 kernel All rights reserved.
    2006/07/25 18:30:07.40 kernel Logging SQL Server messages in file 'C:MSSQLLOGERRORLOG'
    2006/07/25 18:30:07.40 server Using Critsecs w/ spincount of 4000
    2006/07/25 18:30:07.40 kernel initconfig: number of user connections limited to 15
    2006/07/25 18:30:07.40 kernel SQL Server is starting at priority class 'normal' with dataserver semaphore = 1 (2 CPUs detected, SMPStat=0).
    2006/07/25 18:30:07.40 server LogLRU buffers: 0
    2006/07/25 18:30:07.40 kernel Attempting to initialize Distributed Transaction Coordinator
    2006/07/25 18:30:07.40 server Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
    2006/07/25 18:30:07.40 kernel initializing virtual device 0, C:MSSQLDATAMASTER.DAT
    2006/07/25 18:30:07.40 kernel Opening Master Database ...
    2006/07/25 18:30:07.40 spid1 Loading SQL Server's default sort order and character set
    2006/07/25 18:30:07.40 spid1 Recovering Database 'master'
    2006/07/25 18:30:07.42 spid1 Recovery dbid 1 ckpt (6941,19) oldest tran=(6941,0)
    2006/07/25 18:30:07.42 spid1 1 transactions rolled forward
    2006/07/25 18:30:07.43 spid1 Activating disk 'MSDBData'
    2006/07/25 18:30:07.43 kernel initializing virtual device 127, C:MSSQLDATAMSDB.DAT
    2006/07/25 18:30:07.43 spid1 Activating disk 'MSDBLog'
    2006/07/25 18:30:07.43 kernel initializing virtual device 126, C:MSSQLDATAMSDBLOG.DAT
    2006/07/25 18:30:07.43 spid1 server name is 'SQLSERVER2K'
    2006/07/25 18:30:07.43 spid1 Recovering database 'model'
    2006/07/25 18:30:07.43 spid1 Recovery dbid 3 ckpt (338,9) oldest tran=(338,0)
    2006/07/25 18:30:07.45 spid1 Clearing temp db
    2006/07/25 18:30:07.54 kernel Read Ahead Manager started.
    2006/07/25 18:30:07.54 kernel Using 'SQLEVN60.DLL' version '6.00.000'.
    2006/07/25 18:30:07.54 kernel Using 'OPENDS60.DLL' version '6.00.01.02'.
    2006/07/25 18:30:07.54 kernel Using 'NTWDBLIB.DLL' version '6.50.409'.
    2006/07/25 18:30:07.54 ods Using 'SSNMPN60.DLL' version '6.5.0.0' to listen on '\.pipesqlquery'.
    2006/07/25 18:30:09.57 spid10 Recovering database 'pubs'
    2006/07/25 18:30:09.57 spid11 Recovering database 'msdb'
    2006/07/25 18:30:09.57 spid10 Recovery dbid 4 ckpt (779,22) oldest tran=(779,0)
    2006/07/25 18:30:09.59 spid11 Recovery dbid 5 ckpt (3591,21) oldest tran=(3591,0)
    2006/07/25 18:30:09.59 spid1 Recovery complete.
    2006/07/25 18:30:09.60 spid1 SQL Server's default sort order is:
    2006/07/25 18:30:09.60 spid1 'nocase' (ID = 52)
    2006/07/25 18:30:09.60 spid1 on top of default character set:
    2006/07/25 18:30:09.60 spid1 'iso_1' (ID = 1)
    2006/07/25 18:30:09.65 spid1 Launched startup procedure 'sp_sqlregister'
    2006/07/25 18:31:22.82 kernel initializing virtual device 1, X:cca_dat.DAT
    2006/07/25 18:32:13.82 kernel udcreate: Operating system error 80(The file exists.) encountered
    2006/07/25 18:32:28.90 kernel SQL Server terminating due to 'stop' request from Service Control Manager
    siuwake, Jul 25, 2006
    #2

  15. satya Moderator

    operating system error = 3(The system cannot find the path specified.)
    THis seems a path problem to me, make sure the SQL Server service account has required privileges on the drives mentioned to acquire backup files and SQL data files.

    Before we go into details confirm whether you've a database backup for this server or not.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Jul 26, 2006
    #2

  16. siuwake New Member

    Satya thx your reply,

    operating system error , i'm fixed.

    i start the single user mode , use disk reinit and refit the data db, hope can rebuild the master db and data db. i don't know is ture or not.
    siuwake, Jul 26, 2006
    #2

  17. siuwake New Member

    Satya thx your reply,

    operating system error , i'm fixed.

    i start the single user mode , use disk reinit and refit the data db, hope can rebuild the master db and data db. i don't know is ture or not.
    siuwake, Jul 26, 2006
    #2

  18. satya Moderator

    Yes you can rebuild the master database as per the BOL documentation keeping SQL Server in single-user mode.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Jul 26, 2006
    #2

  19. siuwake New Member

    Thx Satya

    I'm can read the DAT File, but i don't know how to inport the DATA into the Sql Server. biz have some error!
    siuwake, Jul 27, 2006
    #2

  20. satya Moderator

    When you say it is giving error, you should specify the error text here.
    Otherwise it is hard to guess and we maygo round circles to provide a solution.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Jul 28, 2006
    #2

  21. starwarsbigbang New Member

    Hi,

    Just saw this topic. I have come across such types of issues with my sql server 6.5. I have worked on this version for a very long time than any other versions of sql servers.

    The best solution (although not the right thing to do) would be to create a database and replace the dat file in the place where the new dat file is. This will have to be done in offline mode. When you restart the server, the server will go to suspect mode. Next, set the option to update system tables. Update the status of this table to the state in which normal databases are (i.e., 0,4,8,16, 256, 512 etc)

    Refresh and the database will come back to normal status. Run DBCC statements. Use DISK REINIT commands if you find it necessary.

    Let me know if thsolution does not work.




    Thanks,

    StarWarsBigBang
    starwarsbigbang, Jul 31, 2006
    #2

  22. mmarovic Active Member

    Sql server 6.5 was the first version I worked with, but I was far from expert. I was impressed by the moving database to another server one guy applied. If I remember correctly, he made sure he had all paths on the new server are the same as on the old one. Then he stopped sql server and copied both user database and master database (overwritting existing file) on right directories. After that he started the server and voila it worked! I would never come-up with such solution. I hope my memory is not decieving me.
    mmarovic, Aug 1, 2006
    #2

  23. satya Moderator

    True, but you have to consistent in sizes of each database and here I think problem here is missing of data files and on top of that no more information, thats why we keep on giving the solution that may or may not be applicable to this case. It depends on the originator to give more information to get a fix.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
    satya, Aug 1, 2006
    #2
Page 1 of 2

Share This Page