quinta-feira, 11 de outubro de 2012

Tabelas corrompidas no MySQL

O que fazer quando ocorre o erro "ERROR 145 (HY000): Table 'nome da tabela' is marked as crashed and should be repaired" ? 

Acessar o banco com problemas: 

mysql> use 'nome_do_banco';

Testar as tabelas até descobrir qual é a tabela ruim:

mysql> check table 'nome_da_tabela';

Reparar a tabela ruim:

mysql> repair table 'nome_da_tabela'; 

Quanta memória RAM cada banco de dados está utilizando?


Em vários momentos precisamos saber o uso de memória por banco de dados, seja pra migração, seja para monitorar a performance de escrita em disco. Mas bem, vamos à query:


SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSEDB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count',
cast((COUNT (*) * 8) as numeric(10,2))/1024 AS 'MBs used'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY [database_id], [is_modified]


Então vamos entender os registros acima. O que acontece é que o SQL Server faz uso de memória para o Buffer Pool, que é onde ele mantém uma cópia em memória de dados acessados a partir de consultas feitas anteriormente para que ele não precise acessar o disco/storage caso a consulta seja feita novamente. A consulta acima mostra quão grande é o Buffer Pool para cada banco de dados.

Pages são as unidades de armazenamento de dados do SQL Server, e por padrão tem 8 KBytes. O resultado da coluna Page Reads, que pode ser Dirty ou Clean, diferencia o status dos pages. Existem os que contém dados que estão alinhados com o banco de dados (clean) e os que já foram modificados em memória mas ainda não em disco (dirty). Multiplicando o número de pages por 8Kb e dividindo por 1024 temos o número de MBs alocados por banco.

Como ver o espaço ocupado por cada tabela


Aí vão 2 dicas. A primeira dica é de uma stored procedure não documentada que existe desde o SQL Server 6.5 e permite que se execute uma função, query ou stored procedure por cada tabela existente no banco de dados. A segunda dica é como usar essa stored procedure para ver o tamanho ocupado por cada tabela.

A stored procedure não documentada é a sp_MSforeachtable, que permite que você rapidamente execute uma função usando as tabelas existentes no banco como parâmetro. Caso você queira saber o count(*) de cada tabela no banco, é possível fazer isso:

exec sp_MSforeachtable ' select ''?'' as Tabela, count(*) as Registros from ? '

E para utilizar isso para ver o tamanho das tabelas, é só utilizar a procedure sp_spaceusedda seguinte maneira:

exec sp_MSforeachtable 'EXEC sp_spaceused ''?'''

O resultado é apresentado no formato abaixo:


Temos o nome da tabela, número de registros, espaço reservado no datafile, espaço ocupado pelos dados, espaço ocupado pelos índices e o espaço não utilizado, ou seja, a diferença entre o que foi utilizado por dados e índices e o espaço reservado. Métricas úteis em várias situações. O problema é que as queries são executadas individualmente e não é possível fazer alguma ordenação como, por exemplo, listar as tabelas ordenadas decrescentemente por espaço em disco reservado, etc. Mas aproveito e já mostro um script para isso.

terça-feira, 9 de outubro de 2012

Alterando diretório do MDF ou LDF no SQLServer 2008

Se você precisar alterar um arquivo de dados ou Transaction Log de diretório, isso pode ocorrer por diversos motivos, pelo simples fato de uma padronização e organização de arquivos, para ganho de performance, quando coloca-se arquivos de dados (MDF ou NDF) dos arquivos de Log (LDF).

Execute a query abaixo e anote o nome lógico do arquivo que vamos alterar de diretório/pasta:

USE AdventureWorks
sp_helpfile
Verifica se tem conexões ativas no banco de dados:

USE master
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID ('AdventureWorks')

Fecha as conexões ativas no banco de dados:
ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE


Atenção: Antes de colocar o banco em estado offline, verifique se o usuário que está usando para logar SQL Server tem permissões para logar em outro banco de dados, além desse que sera colocado em Offline. Se o banco default for o que vamos colocar Offline, devemos colocar alterar a opção Connect to database para outro banco que ele tenha acesso.
Documento isso, pois não sabia desse processo e estava tentando logar no banco de dados default (que estava offline), logo o SQL Server me retornada o error 4094
Agora vamos colocar o banco de Offline: Tasks >> Take Offline
Ok, agora o banco está em estado Offline.
Após mover o arquivo MDF ou LDF, execute a seguinte query. Essa query fala para o SQL Server alterar o banco AdventureWorks, modificando a localização do arquivo. Em NAME vamos informar o nome lógico do arquivo que pegamos no primeiro passos desse procedimento.
USE MASTER
GO
ALTER DATABASE AdventureWorks MODIFY FILE (NAME = 'AdventureWorks_Log'

FILENAME ='D:\LogsSQLServer\AdventureWorks_Log.ldf') GO
Agora, colocaremos o banco novamente Online.
Para verificar se a mudança foi feita corretamente, execute novamente a query abaixo e note o novo caminho do arquivo de LOG

USE AdventureWorks
sp_helpfile

Relação de IPs e Conexões - SQLServer 2005, 2008 e R2

Em algumas situações temos a necessidade de descobrir o que determinado Endereço IP (Internet Protocol) esta realizando em nossa rede, como também, que recursos e aplicações este IP está fazendo troca de dados como Microsoft SQL Server.
O código utilizado para esta dica vai muito, além disso, ele nos possibilita obter o seguinte conjunto de informações:
  • Endereço IP;
  • Aplicação, Recurso e Processo;
  • Hostname da Estação de Trabalho, Máquina Local ou Servidor; e
  • Logins Conectados.
Relação de IPs conectados ao SQL Server
SELECT  ec.client_net_address, es.[program_name], es.[host_name], es.login_name
FROM sys.dm_exec_sessions AS es INNER JOIN sys.dm_exec_connections AS ec                                                           ON es.session_id = ec.session_id
ORDER BY ec.client_net_address,  es.[program_name];
Como podemos observar, o código é bastante simples, fazendo uso de duas DMV: Visões de Gerenciamento Dinâmico:
  • Sys.dm_exec_sessions: Retorna uma linha por sessão autenticada no SQL Server. Trata-se de uma exibição de escopo de servidor que mostra informações sobre todas as conexões de usuário ativas e tarefas internas..
  • Sys.dm_exec_connections: Retorna informações sobre as conexões estabelecidas com esta instância do SQL Server e os detalhes de cada conexão.
Observação: A coluna Client_net_address, apresenta a lista de Endereços IP, quando for retornado nesta coluna o valor <local machine>, isso indica que o recurso, aplicação ou conexão esta sendo executada na mesma máquina ou instância em que o Código 1 esta sendo processado.
Figura 1 apresentada abaixo, ilustra o conjunto de informações retornadas após a execução do Código 1.

Testando conexão no SQL com arquivo UDL


Arquivos .UDL (Universal Data Link) é uma maneira simples e rápida para testar conexões com o SQL Server ou ainda criar Connections Strings

1- Criamos um arquivo teste.TXT e vamos renomear para teste.UDL (o ícone mudará)
imageimage
2- Execute o arquivo teste.udl e clique sobre a aba Providers, como você pode observar existem diversos providers, mas para esta artigo em específico vou usar o Native Client do SQL 2008 (10.0), após selecionar o provider clique sobre o botão Next.
image
3- Na aba Connection você deve informar o nome do seu servidor SQL no campo 1 nocampo 2 você deve informar o modo de autenticação (SQL ou Windows) se o usuário estiver correto e com permissões no SQL server no campo 3 você conseguirá selecionar o database
image
4- Agora para testar basta clicar sobre o botão Test Connection, se o usuário estiver correto e com as devidas permissões no SQL você deverá receber a mensagem abaixo:
image

segunda-feira, 8 de outubro de 2012

O que é o Data Base Tunning Advisor?

O Database Engine Tuning Advisor é uma das novas features do SQL Server, ele analisa arquivos de carga de trabalho e propõe alterações no banco de dados, a fim de melhorar seu desempenho geral. 

Para as alterações propostas, o Tuning Advisor também mostra o impacto que causará cada modificação.

Entre as suas capacidades, estão:
  • Query Optimazer, para propor índices e visões indexadas;
  • Recomendação de particões;
  • Análise de impacto das recomendações;
  • Fornecimento de informações sobre o número de consultas e o número de índices.

Opções de ajuste:
  • Quais objetos o Tuning Advisor poderá recomendar;
  • Quais partições analisará;
  • Quais estruturas serão mantidas no banco de dados;
  • Espaço máximo para recomendações;
  • Número máximo de columas por índice.

Criando analises com o DTA

Para iniciar o DTA, vá em StartAll ProgramsMicrosoft SQL Server 2005Performance ToolsDataBase Engine Tunning Advisor. 
http://www.oficinadanet.com.br//imagens/coluna/2606/iniciar.png


Quando o programa for iniciado, clique em File/New Connection.
http://www.oficinadanet.com.br//imagens/coluna/2606/newconection.png


Aparecerá a tela de conexão para qual servidor você deseja criar a análise.
http://www.oficinadanet.com.br//imagens/coluna/2606/autenticadta.png

Obs: Para o exemplo será usado um ambiente de testes.

O servidor será mostrado no canto superior direito da tela, clique com o botão direito e escolha a opção New Session.
http://www.oficinadanet.com.br//imagens/coluna/2606/newsession.png


A seguinte tela será mostrada:

http://www.oficinadanet.com.br//imagens/coluna/2606/td_teladta.png



Clique na guia Tunning Options e será mostrado a seguinte tela:
http://www.oficinadanet.com.br//imagens/coluna/2606/td_tunningoptions.png


Physical Design Structure(PDS) to use in database: Esta opção avalia o que a Engine do DTA irá analisar referente a objetos de design na estrutura do banco(índex,índex views e etc). A opção de Índex views é desabilitada no SQL Server STD.

Partitioning strategy to employ: A opção de particionamento, verifica se existem objetos que podem ser particionados e avalia o particionamento que já existente. Disponível somente na versão Enterprise do SQL Server.

Physical Design Structure(PDS) to keep in database: Avalia a estrutura fisica do banco, abalia se existem índices clustereds e nonclustereds devem ser deletados(ele não deleta os indices), ou se existe algo divergente na no modelo como um todo. O padrão é Keep All existing PDS.

Voltando a guia General, aonde:
  • Session Name: Nome da Sessão
  • WorkLoad: A origem de ondes virão os dados que serão analisados(no caso do profiler o mesmo pode ser um arquivo ou uma tabela)
  • DataBase for WorkLoad Analysis: Aonde serão gaurdadas as analises temporarias.
  • Select DataBase and tables to tune: Lista de banco de dados e de tabelas que serão parte da analise. (sempre coloque os bancos de dados que [*] foram filtrados no profiler)


Coloque o nome no profiler e escolha o caminho aonde está o arquivo de trace do profiler. Ao clicar no Radio Button File, clique no binóculo a esquerda e será aberta a tela do Windows. Escolha o profiler que deseja analisar.
http://www.oficinadanet.com.br//imagens/coluna/2606/td_selecionandoprofiler.png

Clique em abrir.

Caso o trace tenha sido guardado em uma tabela, marque o Radio Button Table, clique no binóculo a esquerda e será aberta a tela de conexão:
Escolha qual banco, schema e tabelas estão os profilers que foram armazeandos.

Obs: O login qual abriu a sessão no DTA deve ter acesso as tabelas que estão os traces, caso contrário, uma mensagem de erro será retornada.
http://www.oficinadanet.com.br//imagens/coluna/2606/selecionandotabela.png


O preenchimento deve ficar parecido com a tela abaixo:
http://www.oficinadanet.com.br//imagens/coluna/2606/td_teladta.png


Clique no botão Start Analysis. Após clicar em Star Analysis, uma tela de progresso será exibida.
http://www.oficinadanet.com.br//imagens/coluna/2606/td_progress.png


Na guia recomendações, é apresenta a porcentagem de melhoria da análise.
http://www.oficinadanet.com.br//imagens/coluna/2606/finalizandoprocesso.png


No menu Actions aparecerá a opção de aplicar as recomendações ou savá-las.
Clique em Save Recommendations
http://www.oficinadanet.com.br//imagens/coluna/2606/aplicandorecomendacoes.png

Será gerado um arquivo .sql com as recomendações
.

Verificar se um Banco de Dados existe em uma instância do SQL Server


Verificar se um banco de dados existe em um servidor SQL Server é uma tarefa simples quando se usa uma ferramenta gráfica ou assistente de conexão ao banco.

Porém, ao criar scripts mais complexos, pode ser necessário verificar manualmente se um banco de dados existe no servidor.

No SQL Server 2008, para cada banco de dados criado em uma instância, um novo registro na view de sistema sys.databases.

Assim, basta consultar esta tabela para realizar a tarefa desejada:

SELECT * FROM sys.databases WHERE name = 'dbTest'

Veja o resultado:


Observe que através da view sys.databases pode-se obter diversas informações sobre as bases de dados de uma instância. Pode-se também usá-la para exibir uma lista dos bancos de dados do servidor. Até a próxima!

Conexões postgres


select *  from pg_stat_activity where client_addr='IP_SERVIDOR' order by query_start;

Obter o tamanho do banco de dados MySQL Query Browser

SELECIONE table_schema "Nome da Base de Dados",  soma (+ data_length index_length) / 1024 / 1024 "Tamanho da Base de Dados em MB", sum (data_free) / 1024 / 1024 "Espaço livre em MB" 
FROM INFORMATION_SCHEMA.TABLES 
GROUP BY table_schema; 

Conexões MySQL


show full processlist;

Para listar ou visualizar a lista de banco de dados Oracle


1)Para visualizar o banco de dados:
select * from v$database;

2) Para visualizar exemplo:
select * from v$instance;

3) Para visualizar todos os usuários:
select * from all_users;

4) Para visualizar a tabela e as colunas para um determinado usuário:
select tc.table_name Table_name,tc.column_id Column_id,lower(tc.column_name) Column_name,lower(tc.data_type) Data_type,nvl(tc.data_precision,tc.data_length) Length ,lower(tc.data_scale) Data_scale,tc.nullable nullable
FROM all_tab_columns tc ,all_tables t
WHERE tc.table_name = t.table_name;

Recuperando a senha do GLPI


1º Passo: Executar a QUERY (abaixo) no banco de dados e verificar se o usuário existe:

SELECT ID, name, password FROM glpi_users WHERE id = 2 AND name='glpi';


2º Passo: Depois executar o UPDATE para atualizar a senha do usuário para 'glpi' (tudo em minúsculo)

UPDATE glpi_users SET password = '0915bd0a5c6e56d8f38ca2b390857d4949073f41' WHERE ID = 2;


Depois de realizar esses procedimentos tentei logar no GLPI, mas estava aparecendo uma mensagem informando que o usuário estava bloqueado, fui na tabela (glpi_users) no campo is_deleted e alterei o valor de 1 para 0.
Após isso basta acessar a tela de login e entrar com o usuário glpi e senha glpi

Alterando schema de tabela no SQL Server 2005 e 2008


ALTER SCHEMA [nome_schema_novo]
TRANSFER [nome_schema_antigo].[tabela_que_mudará_de_schema];


Exemplo:
ALTER SCHEMA cadastro TRANSFER [dbo].[cliente];

Zerar compo auto-incremento


DBCC CHECKIDENT ('nomedatabela', RESEED, 0)

Script para saber quais Backups foram executados e quando


Select B.backup_start_date, B.backup_finish_date, B.database_name as Nome_database, C.physical_device_name as backup_file_used_for_restore
From msdb..backupset B
INNER JOIN msdb..backupmediafamily C ON B.media_set_id = C.media_set_id
Order by B.backup_start_date DESC

Script para saber quais Restores foram executados e quando


Select A.destination_database_name, A.restore_date, B.backup_start_date, B.backup_finish_date, B.database_name as source_database_name, C.physical_device_name as backup_file_used_for_restore
From msdb..restorehistory A
INNER JOIN msdb..backupset B ON A.backup_set_id = B.backup_set_id
INNER JOIN msdb..backupmediafamily C ON B.media_set_id = C.media_set_id
Order by A.restore_date DESC

Quer saber como está o tamanho físico dos arquivos do seu banco?


SELECT Name, [Physical_Name],
Left(CONVERT(VARCHAR, CAST( [SIZE]*8 AS MONEY ), 1),
Len(CONVERT(VARCHAR, CAST( [SIZE]*8 AS MONEY ), 1)) - 3) as Size_KB
FROM sys.database_files

Espaço em disco - SQLSERVER 2008R2


Para verificar o espaço livre nos discos do seu servidor de banco de dados, dentro do Query Analyser ou Management Studio execute esta procedure: xp_fixeddrives

O resultado será algo parecido com este aí:

Drive    MB free
C           38177
D           130733
X           138790
Y           8140
Z           57013
-------------------------
(5 row(s) affected)

EXIBIR NÚMERO DAS LINHAS NO SSMS - SQLSERVER 2008



Para quem coloca a mão na massa no T-SQL pode ser muito útil exibir no editor do código o números das linhas. Digamos que isto facilita o desenvolvedor a se achar no emaranhado do código. O Management Studio (SSMS) é a ferramenta utilizada pela grande maioria de desenvolvedores T-SQL, portanto mostraremos aqui como exibir a numeração das linhas neste editor:
1º acesse o menu Tools, opção Options…

2º dentro da nova janela, expanda a árvore Text Editor > Transact-SQL >General. À direita desta janela, na área Display, marque a opção Line numbers e clique em OK;

Pronto:

 A configuração é aplicada na hora, sem precisar iniciar uma nova janela :)

Mostra todos os logins que tiveram a senha trocada a mais de X dias


SELECT name, LOGINPROPERTY([name],'PasswordLastSetTime')AS'SenhaTrocada'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name],'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE());

Tamanho do banco - MySQL


SELECT table_schema "NOME_DA_BASE", SUM( data_length + index_length ) / 1024 / 1024 "Tamanho de banco de dados em MB" FROM information_schema.TABLES
GROUP BY table_schema

Script remove os arquivos que estão no diretório a X dias.


#!/bin/sh

find /backup/ -type f -ctime XDIAS | xargs rm

COLOCANDO O SCRIPT NA CRONTAB

30 23 * * 0,2,4,6  root /Scripts/remove_arquivos.sh

O script acima irá rodar nos seguintes dias: Domingo, terça, quinta, sábado

Backup e Restore do MySQL


BACKUP

mysqldump -u XXX -pXXX XXX > XXX.sql


  • ·         XXX à usuário do banco
  • ·         XXX à senha do banco
  • ·         XXX à nome da base de dados
  • ·         XXX.sql à arquivo de saída (Backup)



RESTORE

mysqldump -u XXX -pXXX XXX < XXX.sql


  • ·         XXX à usuário do banco
  • ·         XXX à senha do banco
  • ·         XXX à nome da base de dados
  • ·         XXX.sql à arquivo de saída (Backup)