Wednesday, November 18, 2015

, , ,

SQLServer [ Check INDEX ]

No comments  
How do I get a list of all index?
---

 SELECT DB_NAME() AS Database_Name
,count (o.name) AS Table_Qtd
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U';

 ---

 SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
,count (o.name) AS Table_Qtd
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
GROUP BY o.name , sc.name;

 ---

 SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type;

Tuesday, November 3, 2015

, ,

Rotinas de manutenção [ Reindexação & Integridade ]

No comments  

Rotinas
  • Procedimentos padrões para banco de dados (Tarefa de Manutenção);
CHECK DATABASE INTEGRITY:
Executa verificação de integridade dos bancos. Durante essa task são executados os comandos DBCC CHECKCATALOG, DBCC CHECKTABLE e DBCC CHECKALLOC;
REORGANIZE INDEX:
Reordena os índices em tabelas;
UPDATE STATISTICS:
atualiza as estatísticas de consulta às tabelas;
CLEANUP HISTORY:
Remove histórico de informações do banco MSDB, responsável por gerenciar as atividades de manutenção;
REBUILD INDEX:
Dropa os índices atuais e recria novos;
  • Criar usuários específicos por aplicação e suas respectivas permissões;
Usarios: USERSYSTEM, USERAPP, USEREST
  • Gerar relatórios mensais dos bancos de dados para controle interno com foco na ProAtividade;
  • Taxa de crescimento, Taxa de acesso, Error frequentes, verificação da estatísticas entre outros;
Obs: Em ambientes críticos, envolver a equipe de T.I. interna da empresa para dar suporte os processos;

ANALISE DE INDEX SQLSERVER
avg_fragmentation_in_percent value Corrective statement
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

SELECT DB_ID() AS [Database ID];
GO

select
object_schema_name(ps.object_id) as ObjectSchema,
object_name (ps.object_id) as ObjectName,
ps.object_id ObjectId,
i.name as IndexName,
'ALTER INDEX ' + i.name + ' ON ' + object_name (ps.object_id) + ' REBUILD ;',
ps.avg_fragmentation_in_percent,
ps.page_count
from sys.dm_db_index_physical_stats(7, null, null, null, null) ps
inner join sys.indexes i
on i.object_id = ps.object_id and
 i.index_id = ps.index_id
where
avg_fragmentation_in_percent > 5 -- reorganize and rebuild
and ps.index_id > 0
order by avg_fragmentation_in_percent desc


LISTAR INDEX E INFO
SELECT
    TableName = t.name,
    IndexName = ind.name,
    IndexId = ind.index_id,
    ColumnId = ic.index_column_id,
    ColumnName = col.name,
    ind.*,
    ic.*,
    col.*,
    'ALTER INDEX ' +  ind.name + ' ON ' + t.name + ' REBUILD WITH (ONLINE=ON);',
'ALTER INDEX ' +  ind.name + ' ON ' + t.name + ' REBUILD;',
    'ALTER INDEX ALL ON'  + t.name +  ' REBUILD;'
FROM
    sys.indexes ind
INNER JOIN
    sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
    sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
    sys.tables t ON ind.object_id = t.object_id
WHERE
    ind.is_primary_key = 0
    AND ind.is_unique = 0
    AND ind.is_unique_constraint = 0
    AND t.is_ms_shipped = 0
ORDER BY
    t.name, ind.name, ind.index_id, ic.index_column_id

UPDATE STATISTICAS COMPLETA
EXEC sp_updatestats;

REBUILD POR TABELA
SELECT 'ALTER INDEX ALL ON '  + TABLE_NAME +  ' REBUILD;'
FROM information_schema.tables

, ,

SQLServer DeadLock Victim SQL-Show

No comments  


sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'blocked process threshold', 20
GO
RECONFIGURE
GO

--

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0;
--

SELECT DTL.resource_type,
CASE
WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type
WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN
(
    SELECT OBJECT_NAME([object_id])
    FROM sys.partitions
    WHERE sys.partitions.hobt_id =
    DTL.resource_associated_entity_id
)
ELSE 'Unidentified'
END AS requested_object_name, DTL.request_mode, DTL.request_status,
DOWT.wait_duration_ms, DOWT.wait_type, DOWT.session_id AS [blocked_session_id],
sp_blocked.[loginame] AS [blocked_user], DEST_blocked.[text] AS [blocked_command],
DOWT.blocking_session_id, sp_blocking.[loginame] AS [blocking_user],
DEST_blocking.[text] AS [blocking_command], DOWT.resource_description
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.sysprocesses sp_blocked
ON DOWT.[session_id] = sp_blocked.[spid]
INNER JOIN sys.sysprocesses sp_blocking
ON DOWT.[blocking_session_id] = sp_blocking.[spid]
CROSS APPLY sys.[dm_exec_sql_text](sp_blocked.[sql_handle]) AS DEST_blocked
CROSS APPLY sys.[dm_exec_sql_text](sp_blocking.[sql_handle]) AS DEST_blocking
WHERE DTL.[resource_database_id] = DB_ID();
--

SELECT DTL.resource_type,
CASE
WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type
WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])
WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN
(
    SELECT OBJECT_NAME([object_id])
    FROM sys.partitions
    WHERE sys.partitions.hobt_id =
    DTL.resource_associated_entity_id
)
ELSE 'Unidentified'
END AS requested_object_name, DTL.request_mode, DTL.request_status,
DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame
FROM sys.dm_tran_locks DTL
INNER JOIN sys.sysprocesses SP
ON DTL.request_session_id = SP.spid
--INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST
WHERE SP.dbid = DB_ID()
AND DTL.[resource_type] <> 'DATABASE'
ORDER BY DTL.[request_session_id];
--

EXEC sp_who2 ;

--
, , ,

SQLSERVER - Base Suspect

No comments  


RECUPERAR BASE DE DADOS EM SUSPECT SQLSERVER

1. PARAR O SERVIÇO DO SQL;

2. FAZER UMA CÓPIA DA PASTA ONDE ESTÁ ARMAZENADO OS ARQUIVOS MDF E LDF ;
(C:\Program Files\Microsoft SQL Server\MSSQL*.SQLEXPRESS\MSSQL\DATA)

3. EXCLUIR OS ARQUIVOS MDF E LDF DESTA PASTA;

4. INICIAR O SERVIÇO DO SQL;

5. EXCLUIR O BANCO DE DADOS;

OBS: TENTEI EXCLUIR DIRETAMENTE O BANCO QUE ESTAVA EM SUPECT MAS NÃO EXCLUÍA, ENTÃO POR ISSO ELIMINEI O MDF E LDF PRIMEIRO E DEPOIS O BANCO.

6. RESTAURAR UM BACKUP DO BANCO ANTIGO COM O MESMO NOME QUE ESTAVA ANTERIORMENTE E NO MESMO CAMINHO PARA OS ARQUIVOS MDF E LDF;

7. PARAR O SERVIÇO DO SQL NOVAMENTE;

8. EXCLUIR OS ARQUIVOS MDF E LDF DESTA PASTA E SUBSTITUÍ-LOS PELOS 2 ARQUIVOS MAIS RECENTES (OS QUE ESTAVAM EM SUSPECT);

9. INICIAR O SERVIÇO DO SQL;

----EXECUTAR OS SCRIPTS ABAIXO---- 

10. ALTER DATABASE BANCO SET EMERGENCY

11. ALTER DATABASE BANCO SET SINGLE_USER

12. DBCC CHECKDB (BANCO, repair_allow_data_loss ) WITH ALL_ERRORMSGS

13. ALTER DATABASE BANCO SET read_write

14. ALTER DATABASE BANCO SET multi_user
, , , ,

Trying to get source code of a webpage in PERL

No comments  

use strict;
use warnings;
use LWP::Simple;

print "ExaCodigo\tEctCodigo\tExcCodigo\tTUSS\tSQL";

open(IN,'db')or die $!;

while(<IN>){
    chomp;
    my($ExaCodigo,$EctCodigo,$ExcCodigo)=split (/\t/,$_);
    my $link = "http\:\/\/conmedhsaude.com.br\/tuss.php\?c\=\&e\=$ExcCodigo\&p=\&q\=\&consultar\=";
    my $page = get($link) or die "cannot retrieve code\n";
    $page =~ s/\n//g;
    my $TUSS = $1 if ($page =~ /\sTUSS:\s(\d+)\<\/span\>\s+/);
    unless($TUSS) { $TUSS = 'null' }
    print "$ExaCodigo\t$EctCodigo\t$ExcCodigo\t$TUSS\tinsert into ExameCodigoTipo ExaCodigo      values ($TUSS) where ExaCodigo = $ExaCodigo and EctCodigo = $EctCodigo\n";

}
close(IN); _________________________________________________________________________________
DB 16 1 28061144 23 1 28040350 26 1 28061098 33 1 28050037 44 1 28150082 48 1 28060156 64 1 28061608 65 1 28061667 66 1 28061594 67 1 28061748 69 1 28050070 75 1 28060725 77 1 28060229 81 1 28061756 89 1 28060342 90 1 28100140 102 1 28060423
_________________________________________________________________________________ HTML

<input type="text" id="c" name="c" value="" style="margin-left:40px;" />                    Código AMB:<input type="text" id="e" name="e" value="28040481" style="margin-left:40px;" />                    Descrção TUSS: <input type="text" id="p" name="p" value="" style="margin-left:28px;" />                    Descrção AMB: <input type="text" id="q" name="q" value="" style="margin-left:25px;" />                 <input name="consultar" type="submit" class="botao" value="" style="border:none;" >                        </form>            </div> <div class='linhaguia'></div>             <div class="guiaitemsup">                        <div class="guiaitem">             <span class="nome">Código TUSS: 40304361</span>                <span class="esp"><span class="tit2">Descrição TUSS: HEMOGRAMA COM CONTAGEM DE PLAQUETAS OU FRACOES (ERITROGRAM</span></span>                <span class="end"><b>Código do grupo TUSS: </b>40308<br><b>Grupo TUSS: </b>MEDICINA LABORATORIAL  - IMUNOLOGIA<br><b>Código AMB: </b>28040481<br><b>Descrição AMB: </b>HEMOGRAMA COMPLETO (ERITOGRAMA+LEUCOGRAMA+AVALIACAO DE PLAQ<br></span>            </div>            </div>                                                     <div class="clear"></div> <div id='paginacao'><p class="MyriadCH"><span paginacaobot >1</span></p></div>                     </div>                        <div id="lateral">     <div class="clear"></div>    <!-- <a href="http://177.21.147.100:8086/mvautorizadorguias/" title="Autorizador" target="_blank" class="latitem latitemautorizador" ></a> -->    <a href="http://201.59.94.100:8086/mvautorizadorguias/" title="Autorizador" target="_blank" class="latitem latitemautorizador" ></a>    <a href="tuss.php" title="TUSS" class="latitem latitemtuss" ></a>    <a href="trabalheconosco.php" title="Trabalhe Conosco" class="latitem latitemtrabalhe" ></a>        <a href="perguntasfrequentes.php" title="Perguntas Frequentes" class="latitem latitemperguntas" ></a>    <a href="faleconosco.php" title="Central de Atendimento" class="latitem latitematend" ></a>    <a href="linksuteis.php" title="Links Úteis" class="latitem latitemtels" ></a> <a href="commaissaude.php" title="Com + Saúde" class="latitem latitemprevent" ></a>    <a href="imagens/redecredenciada.pdf" title="Rede Credenciada" target="_blank" class="latitem latitemrede" ></a>    <br><br>       <!-- <div id="abramgeans">        <a href="http://www.abramge.com.br/" target="_blank">        <img src="imagens/site/abramge.png" class="abramge">        </a>            <a href="http://www.ans.gov.br/" target="_blank">        <img src="imagens/site/ans.png" class="ans">        </a>    </div>-->    <div class="clear"></div></div>                        <div class="clear"></div>        </div> <div class="linha"></div> <div id="rodape"> <div class="central">     <a href="http://www.taticaweb.com.br/" class="taticaweb" target="_blank"></a>            <div id="abramgeans1" style="float: left;margin: -9px 0 0 0;">                <a href="http://www.abramge.com.br/" target="_blank">                <img src="imagens/site/abramge.png" class="abramge" width="58" height="41">                </a>                            <a href="http://www.ans.gov.br/" target="_blank">                <img src="imagens/site/ans.png" class="ans" width="" height="39">            </a>       </div>        <div class="rodapeatend">         <span class="cat">Central de Atendimento:</span><span class="tel">(24)4009-4545 | (24)4009-4519</span>         <span class="cat">Central de Vendas:</span><span class="tel">(24)4009-8803</span><br />            <span class="cat" style="padding-top:12px; margin-left:-98px;">SAC:</span><span class="tel">0800-0302015
_________________________________________________________________________________
OUTPUT
ExaCodigo EctCodigo ExcCodigo TUSS SQL
23 1 28040350 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 23 and EctCodigo = 1
26 1 28061098 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 26 and EctCodigo = 1
33 1 28050037 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 33 and EctCodigo = 1
44 1 28150082 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 44 and EctCodigo = 1
48 1 28060156 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 48 and EctCodigo = 1
64 1 28061608 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 64 and EctCodigo = 1
65 1 28061667 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 65 and EctCodigo = 1
66 1 28061594 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 66 and EctCodigo = 1
67 1 28061748 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 67 and EctCodigo = 1
69 1 28050070 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 69 and EctCodigo = 1
75 1 28060725 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 75 and EctCodigo = 1
77 1 28060229 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 77 and EctCodigo = 1
81 1 28061756 null insert into ExameCodigoTipo ExaCodigo values (null) where ExaCodigo = 81 and EctCodigo = 1
89 1 28060342 40306631 insert into ExameCodigoTipo ExaCodigo values (40306631) where ExaCodigo = 89 and EctCodigo = 1