none
Banco SQL 2005 em rede - Quedas RRS feed

 > 

  • Pergunta

  • Question
    Entrar para Votar
    0
    Entrar para Votar

     

    Ativei um banco em minha instância e direcionei os .mdf, ndf e ldf para um host na rede(Storage).

    Com receio de perder desempenho ou corromper a base, ativei uma VLAN giga em meu switch ligando o servidor SQL ao Storage.

    A primeira vista tudo correu bem...
    O problema só vem depois de horas com o erro que segue abaixo ao tentar abrir o banco no Management Studio

    "Database 'DB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)" 

    Fica inacessível...

    E com apenas um restart no serviço do SQL tudo volta ao normal.

    Alguém pode me dar uma ajuda? Será que algum maintance plan ajudaria?

    Server1 - SQL 2005 / Windows 2003
    Server2 - Windows Storage server 2003

     

    Obrigado!

     

    Bruno Silva

    sexta-feira, 7 de março de 2008 17:57
    |

Todas as Respostas

  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Olá Bruno, BEM VINDO!

     

    Recomendo vc a realizar a seguinte verificação:

    * Verifique se no log do SQL existem registros que database em status de suspect ou falhas nos datafiles, caso confirme isso, então pode realmente ser quedas momentaneas de conexão com o storage. Quando vc restart o SQL o mesmo restabelece a conexão com os datafiles.

    *  Verifique se existe alguma limitação de espaço no disco(storage) ou limitação  de crescimento dos datafiles, nas propriedades do teu database.

     

    Execute xp_readerrorlog no SSMS ou no QA para visualizar os logs SQL

    sábado, 8 de março de 2008 04:20
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar
    Olá! Obrigado!

    - Os datafiles estão como "unrestricted growth".
    - E no storage chequei e não vi nenhuma limitação.

    Peguei o log conforme solicitou.
    E os erros que dão são:
    • Backup    Error: 18210, Severity: 16, State: 1.
    • BackupIoRequest::WaitForIoCompletion: write failure on backup device '\\server\Trauma_Zero\Tz0_log.ldf'. Operating system error 59(An unexpected network error occurred.).
    • spid3s    LogWriter: Operating system error 59(An unexpected network error occurred.) encountered.
    • fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    • SidePageTable::Init() DeviceIoControl() : Operating system error 59(An unexpected network error occurred.) encountered.

    2008-03-07 23:00:04.930    Backup    Log was backed up. Database: Tz0, creation date(time): 2008/02/27(18:05:36), first LSN: 3086:173:1, last LSN: 3087:387:1, number of dump devices: 1, device information: (FILE=138, TYPE=DISK: {'Backup Trauma'}). This is an informational message only. No user action is required.
    2008-03-08 00:00:03.760    Backup    Error: 18210, Severity: 16, State: 1.
    2008-03-08 00:00:03.760    Backup    BackupIoRequest::WaitForIoCompletion: write failure on backup device '\\server\Trauma_Zero\Tz0_log.ldf'. Operating system error 59(An unexpected network error occurred.).
    2008-03-08 00:00:03.790    Backup    Error: 3041, Severity: 16, State: 1.
    2008-03-08 00:00:03.790    Backup    BACKUP failed to complete the command BACKUP LOG Tz0. Check the backup application log for detailed messages.
    2008-03-08 00:00:03.820    Backup    Error: 18210, Severity: 16, State: 1.
    2008-03-08 00:00:03.820    Backup    BackupIoRequest::WaitForIoCompletion: write failure on backup device '\\server\Trauma_Zero\Tz0_log.ldf'. Operating system error 59(An unexpected network error occurred.).
    2008-03-08 00:00:04.000    spid21s    This instance of SQL Server has been using a process ID of 664 since 3/7/2008 5:50:53 PM (local) 3/7/2008 8:50:53 PM (UTC). This is an informational message only; no user action is required.
    2008-03-08 00:00:46.330    spid3s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.330    spid3s    LogWriter: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:46.330    spid3s    Write error during log flush.
    2008-03-08 00:00:46.330    spid59    Error: 9001, Severity: 21, State: 4.
    2008-03-08 00:00:46.330    spid59    The log for database 'Tz0' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2008-03-08 00:00:46.370    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.370    spid23s    fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:46.370    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.370    spid23s    fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:46.370    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.370    spid23s    fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:47.200    spid23s    Starting up database 'Tz0'.
    2008-03-08 00:00:47.230    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:47.230    spid23s    SidePageTable::Init() DeviceIoControl() : Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:47.230    spid23s    Error: 17204, Severity: 16, State: 1.
    2008-03-08 00:00:47.230    spid23s    FCB:Surprisepen failed: Could not open file \\server\Trauma_Zero\Tz0.mdf for file number 1.  OS error: 59(An unexpected network error occurred.).
    2008-03-08 00:00:47.230    spid23s    Error: 5120, Severity: 16, State: 104.
    2008-03-08 00:00:47.230    spid23s    Unable to open the physical file "\\server\Trauma_Zero\Tz0.mdf". Operating system error 59: "59(An unexpected network error occurred.)".
    2008-03-08 00:00:47.260    spid23s    Error: 17207, Severity: 16, State: 1.
    2008-03-08 00:00:47.260    spid23s    FileMgr:Tongue TiedtartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file '\\server\Trauma_Zero\Tz0_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
    2008-03-08 00:00:47.260    spid23s    File activation failure. The physical file name "\\server\Trauma_Zero\Tz0_log.ldf" may be incorrect.
    2008-03-08 01:00:00.750    Logon    Error: 18456, Severity: 14, State: 16.
    2008-03-08 01:00:00.750    Logon    Login failed for user 'Domain\user'. [CLIENT: <local machine>]
    sábado, 8 de março de 2008 16:12
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar
    Olá! Obrigado!

    - Os datafiles estão como "unrestricted growth".
    - E no storage chequei e não vi nenhuma limitação.

    Peguei o log conforme solicitou.
    E os erros que dão são:
    • Backup    Error: 18210, Severity: 16, State: 1.
    • BackupIoRequest::WaitForIoCompletion: write failure on backup device '\\server\Trauma_Zero\Tz0_log.ldf'. Operating system error 59(An unexpected network error occurred.).
    • spid3s    LogWriter: Operating system error 59(An unexpected network error occurred.) encountered.
    • fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    • SidePageTable::Init() DeviceIoControl() : Operating system error 59(An unexpected network error occurred.) encountered.

    2008-03-07 23:00:04.930    Backup    Log was backed up. Database: Tz0, creation date(time): 2008/02/27(18:05:36), first LSN: 3086:173:1, last LSN: 3087:387:1, number of dump devices: 1, device information: (FILE=138, TYPE=DISK: {'Backup Trauma'}). This is an informational message only. No user action is required.
    2008-03-08 00:00:03.760    Backup    Error: 18210, Severity: 16, State: 1.
    2008-03-08 00:00:03.760    Backup    BackupIoRequest::WaitForIoCompletion: write failure on backup device '\\server\Trauma_Zero\Tz0_log.ldf'. Operating system error 59(An unexpected network error occurred.).
    2008-03-08 00:00:03.790    Backup    Error: 3041, Severity: 16, State: 1.
    2008-03-08 00:00:03.790    Backup    BACKUP failed to complete the command BACKUP LOG Tz0. Check the backup application log for detailed messages.
    2008-03-08 00:00:03.820    Backup    Error: 18210, Severity: 16, State: 1.
    2008-03-08 00:00:03.820    Backup    BackupIoRequest::WaitForIoCompletion: write failure on backup device '\\server\Trauma_Zero\Tz0_log.ldf'. Operating system error 59(An unexpected network error occurred.).
    2008-03-08 00:00:04.000    spid21s    This instance of SQL Server has been using a process ID of 664 since 3/7/2008 5:50:53 PM (local) 3/7/2008 8:50:53 PM (UTC). This is an informational message only; no user action is required.
    2008-03-08 00:00:46.330    spid3s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.330    spid3s    LogWriter: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:46.330    spid3s    Write error during log flush.
    2008-03-08 00:00:46.330    spid59    Error: 9001, Severity: 21, State: 4.
    2008-03-08 00:00:46.330    spid59    The log for database 'Tz0' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2008-03-08 00:00:46.370    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.370    spid23s    fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:46.370    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.370    spid23s    fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:46.370    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:46.370    spid23s    fcb::close-flush: Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:47.200    spid23s    Starting up database 'Tz0'.
    2008-03-08 00:00:47.230    spid23s    Error: 17053, Severity: 16, State: 1.
    2008-03-08 00:00:47.230    spid23s    SidePageTable::Init() DeviceIoControl() : Operating system error 59(An unexpected network error occurred.) encountered.
    2008-03-08 00:00:47.230    spid23s    Error: 17204, Severity: 16, State: 1.
    2008-03-08 00:00:47.230    spid23s    FCB:Surprisepen failed: Could not open file \\server\Trauma_Zero\Tz0.mdf for file number 1.  OS error: 59(An unexpected network error occurred.).
    2008-03-08 00:00:47.230    spid23s    Error: 5120, Severity: 16, State: 104.
    2008-03-08 00:00:47.230    spid23s    Unable to open the physical file "\\server\Trauma_Zero\Tz0.mdf". Operating system error 59: "59(An unexpected network error occurred.)".
    2008-03-08 00:00:47.260    spid23s    Error: 17207, Severity: 16, State: 1.
    2008-03-08 00:00:47.260    spid23s    FileMgr:Tongue TiedtartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file '\\server\Trauma_Zero\Tz0_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
    2008-03-08 00:00:47.260    spid23s    File activation failure. The physical file name "\\server\Trauma_Zero\Tz0_log.ldf" may be incorrect.
    2008-03-08 01:00:00.750    Logon    Error: 18456, Severity: 14, State: 16.
    2008-03-08 01:00:00.750    Logon    Login failed for user 'Domain\user'. [CLIENT: <local machine>]
    sábado, 8 de março de 2008 16:16
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Parece realmente ser um problema de conectividade. Mas esse erro de conectividade pode estar sendo ocasionado por um alto consumo de recursos do servidor causando indisponibilidade momentanea do mesmo. vc deve checar se essa indisponibilidade está sendo ocasionada por alto consumo ou erro de de dispositivo de rede.

    sábado, 8 de março de 2008 17:56
    |