none
SQL 2005 Database 'TempDb', espansione esagerata del file di dati (no Log) RRS feed

  • Domanda

  • Buongiorno, il database TempDb si espande in maniera esagerata in proporzione ai processi in esecuzione su un server SQL, non si compatta neppure da Maintenance Plan

     

    Scenario

    IL TempDb standard si espande in modo esagerato, in realtà lo visualizza di 8MB ma il file è in realtà di 15GB; l'ho fatto ricreare più volte ma torna sempre di quelle dimensioni.

     

    l'unico metodo efficace che ho trovato per ricrearlo è quello di riavviare il servizio sql server (soluzione non ottimale) in quanto tutte le procedure di mia conoscenza provate (shrink datafile, modifica del file di catalogo per impostare una dimensione più piccola, blocco dell'espansione etc. del datafil pare funzionino solo con single user).

     

    Ho provate anche a killare i processi che utilizzano le tabelle temporanee singolarmente leggendoli dal catalogo dei processi in esecuzione sulle tabele #, mi da sempre errore e non riesco a liberare il file.

     

    E' come se trovasse degli errori nelle pagine dati durante l'utilizzo del datafile del Tempdb e poi non utilizza e non compatta più i dati esistenti generand un'espansione anormale.

      

    Uno degli errori nel log tentando la compattazione in sigle user

    DBCC SHRINKFILE: impossibile spostare la pagina 1:1863208 perché è una pagina della tabella di lavoro.

    Msg 2555, Level 16, State 2, Line 1

    Impossibile spostare in un'altra posizione tutto il contenuto del file "tempdev" per completare l'operazione EMPTYFILE.

    Esecuzione DBCC completata. Se sono stati visualizzati messaggi di errore DBCC, rivolgersi all'amministratore di sistema.

    Potrebbe essere danneggiato il disco su cui risiede il tempDB ?

    Cosa p0otrei fare per capire il motivo di questa situaziobe ?

    Grazie per il supporto.

    Roberto


    Roberto Stefanetti
    venerdì 6 maggio 2011 08:02

Risposte

  • Per verificare se ci sono problemi di disco puoi abilitare l'opzione CHECKSUM per il TEMPDB. Con questa modalità SQL Server rinuncia a un 2% circa di prestazioni, ma ti segnala quando non riesce a scrivere correttamente le pagine sul disco (errori 823,824,825).

    Nelle nuove installazioni di SQL Server 2008 se non ricordo male è impostato già di default.

    Per abilitare questa modalità: ALTER DATABASE nomedb SET PAGE_VERIFY CHECKSUM.

     


    Danilo Dominici MCP MCDBA MCITP MCSE MCAD ..::.. Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    venerdì 6 maggio 2011 19:49

Tutte le risposte

  • Ciao Roberto,

    il TEMPDB, in particolare da SQL Server 2005, svolge una serie di funzioni che vanno dal puro e semplice mantenimento delle tabelle temporanee (# e ##) all'allocazione dei dati in transito per operazioni di SORT o GROUP BY, alla gestione del row versioning. Perciò la sua dimensione non dipende solamente dalle query che ci fai tu sopra, ma anche da come SQL Server opera internamente.

    Perciò non credo sia una buona idea killare i processi che lavorano sul tempdb, pena il rischio di incasinare il sistema.

    Proverei invece a capire quali sono le operazioni utente che impattano sul tempdb per ottimizzarle. Qui trovi un elenco dei possibili candidati (in particolare per quanto riguarda il version store) che potrebbero generare il carico esagerato che lamenti. Puoi utilizzare SQL Profiler per monitorare le query in esecuzione sul tempdb.

    Ti segnalo anche questo link al blog di Paul Randal dove trovi informazioni su come configurare, dimensionare e manutenere il tempdb, mentre qui ci sono informazioni sul monitoraggio del tempdb utilizzando i contatori del Performance Monitor o le DMV.

     

     


    Danilo Dominici MCP MCDBA MCITP MCSE MCAD ..::.. Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    venerdì 6 maggio 2011 12:23
  • Ciao, grazie per le dritte.

    ..ovviamente il fatto di 'killare' i processi era solo un modo per insistere sul fatto che non era possibile liberare lo spazio perchè figurava nelle proprietà del datafile 'non allocato'... darò un'occhiata ai link che mi ha girato e proverò con Sql Trace per capire cosa accade alla macchina.

    la cosa strana è che fino a poche settimane fa non era mai accaduto, per questo pensavo a qualche problema sul disco.

     

     

     

     


    Roberto Stefanetti
    venerdì 6 maggio 2011 12:40
  • Per verificare se ci sono problemi di disco puoi abilitare l'opzione CHECKSUM per il TEMPDB. Con questa modalità SQL Server rinuncia a un 2% circa di prestazioni, ma ti segnala quando non riesce a scrivere correttamente le pagine sul disco (errori 823,824,825).

    Nelle nuove installazioni di SQL Server 2008 se non ricordo male è impostato già di default.

    Per abilitare questa modalità: ALTER DATABASE nomedb SET PAGE_VERIFY CHECKSUM.

     


    Danilo Dominici MCP MCDBA MCITP MCSE MCAD ..::.. Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    venerdì 6 maggio 2011 19:49