DBCC INDEXDEFRAG

Se dejara de usar a partir de la V12 en adelante.
El DBCC INDEXDEFRAG es muy útil y eficiente para defragmentar los índices, lo interesante es que no mantiene bloqueos a largo plazo, y no genera bloqueos en actualizaciones (Update) y consultas (Select).

El tiempo dependerá de la ejecución del DBCC INDEXDEFRAG, del tamaño de la tabla y cuan fragmentado esta el índice. Por lo general no hay que preocuparse por esto. Si la defragmentación tarda mucho sobre un índice especifico sera mejor aplicar una reconstrucción del índice.

Si se esta trabajando con grandes volúmenes de datos y los indices presentan una fragmentacion también grande hay que tener cuidado porque los resultados pueden genera demaciada informacion en el registro log que la creacion misma de un indice.

Script de microsoft para defragmentar:

/*Perform a 'USE ' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

REINDEX - Tabla de comportamientos de reindex

TABLA DE COMPORTAMIENTOS DE REINDEX

Scenario	SQL Server 2008 R2	SQL Server 2008	SQL Server 2005	SQL Server 2000
DBCC REINDEX to rebuild the Clustered Index ix_Col1	Update all Index Statistics	Update all Index Statistics	Update only the Clustered Index	All statistics updated
DBCC REINDEX in the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index
DBCC REINDEX without specify the index. That means all index must be updated	All statistics updated	All statistics updated	All statistics updated	All statistics updated
ALTER INDEX REBUILD to update the Clustered Index	Update only the Clustered Index	Update only the Clustered Index	Update only the Clustered Index	Not Apply
ALTER INDEX REBUILD to update the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Not Apply
ALTER INDEX ALL REBUILD to update the ALL Index	Update all Index Statistics	Update all Index Statistics	Update all Index Statistics	Not Apply

Monitoreo con PSSDIAG, SQLNEXUS y PerfStatsScripts

Para el servidor Pegaso

1.- instalar PSSDIAG en C:\PSSDIAG

Revisar el ejecutable en: C:\PSSDIAG\Pristine\pssdiag.cmd

Verificar donde se instaló el SQLdiag: F:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLdiag.exe

2.- Copiar SQL Nexux y PerfStatsScript2008 en C:\temp2

SQL Nexus: Es el editor de los reportes

PerfStatsScript2008: es la carpeta que contiene los reportes script y xml

3.- Donde y como ejecutar los monitoreos.

Para ejecutar un monitoreo vamos a la carpeta  PerfStatsScript2008 y modificamos el archivos: StartSQLDiagTrace2008.cmd o creamos una copia de este y lo modificamos.

@REM  To register the collector as a service, open a command prompt, change to this

@REM  directory, and run:

@REM 

@REM     SQLDIAG /R /I "%cd%\SQLDiagPerfStats_Trace.XML" /O "%cd%\SQLDiagOutput" /P

@REM 

@REM  You can then start collection by running "SQLDIAG START" from Start->Run, and

@REM  stop collection by running "SQLDIAG STOP".

@rem the command below sets sqldiag.exe path.  if your installation is different, adjust accordinly

@rem sql 2008 sqldiag.exe will be able to capture multiple platforms.

@rem any sqldiag will be able to detect 32 bit or 64 bit instances

set SQLDIAGCMD=F:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLdiag.exe

"%SQLDIAGCMD%" /I "C:\Temp2\PerfStatsScript2008\SQLDiagPerfStats_Trace2008.XML" /O "G:\SQLDiagOutput" /P

BLOQUEOS - Kb Microsoft

http://support.microsoft.com/kb/271509

JOB - Eliminar jobs obsoletos

Eliminar o dar de Baja Jobs


Se borraron BD en des-uso, el problema es que estas bases estuvieron operativas y productivas en su momento,
 y cuando se dieron de baja no se tomo en cuenta los job y usuarios que accedian a las bases desde alguna app.

El error que encontre en el log era el siguiente:

2013-05-09 10:18:03.88 Logon       Error: 18456, Severity: 14, State: 38.
2013-05-09 10:18:03.88 Logon       Login failed for user 'dominio\xxxxx'. Reason: Failed to open the explicitly specified database. [CLIENT: xx.xxx.xx.xx]

1.- Lo primero es ejecutar un trace con SQL Server Profiler, seleccionando los eventos: Error Log y User Error Message. y tildando en todas las columnas para un mejor análisis.

2.- Después navegar por el trace e identificar los problemas del usuario 'dominio\xxxxx', una vez identificado es bastante facil.


1.-
--sacar el database_id-- si no existe la base paso a la consulta 2
select * from sys.databases
where name = 'basededato'

2.-
--Buscar por "database_name" segun la consulta anterior
-- y sacar el job_id para ejecutar en la siguiente consulta
use msdb
go
select database_name, * from sysjobsteps
order by 1 desc

3.-
select name, job_id,* from sysjobs
where job_id = '36E88341-774B-4D26-BD63-863F53EE42BB'

SUSPECT - Recuperar base de datos

/*

--modo emergencia sql server 2000--

sp_configure ‘allow’ ,1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = ‘SQLDBPool’
GO
sp_configure ‘allow’, 0
GO
Reconfigure with override
go


*/

/*

Caso estado de la base emergencia en suspect, Servidor:

SQL Server 2005

--Antes de seguir estos pasos es posible que reiniciando el servicio

--de SQL solucione el problema de bases: suspect.

*/

--Pongo la base en modo emergencia

use  master

GO

ALTER DATABASE emergencias SET EMERGENCY

--Pongo la base en modo simple

ALTER DATABASE emergencias  SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

GO

--Ejecuto el check db para reparar datos perdidos

DBCC CHECKDB (emergencias, REPAIR_ALLOW_DATA_LOSS)

GO

--Con el resultado del dbcc checkdb identifico la tabla/vista/indice ------donde esta el problema

--Como indentifique la tabla del problema ejecuto

--dbcc checktable, siempre en el estado modo simple user

DBCC CHECKTABLE ('EM_Visitas_Enc', REPAIR_REBUILD)

GO

--Pongo la base modo multi user

ALTER DATABASE emergencias  SET MULTI_USER

--Ejecuto dbcc checkdb sobre toda la base

--ya no deberiamos tener errores.

use emergencias

go

DBCC CHECKDB

BACKUPS - Bases del Sistema - Device

1.- Ejecutar el  Device.sql
     Que genera los archivos de los device para backups de bases del sistema
     Después programar Job de bakups

/*Isaac Pacheco*/

/*Devices for system databases backup*/
/*Cambiar @physicalname donde corresponde*/
/*Bases Master,Model,msdb*/

----------------------/Master/-------------------------
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk', @logicalname = N'masterLMV',
@physicalname = N'D:\MSSQL2008R2\masterLMV.bak'
GO

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk', @logicalname = N'masterMJS',
@physicalname = N'D:\MSSQL2008R2\masterMJS.bak'
GO

------------------------/Model/------------------------
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk', @logicalname = N'modelLMV',
@physicalname = N'D:\MSSQL2008R2\modelLMV.bak'
GO

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk', @logicalname = N'modelMJS',
@physicalname = N'D:\MSSQL2008R2\modelMJS.bak'
GO

------------------------msdb----------------------------
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk', @logicalname = N'msdbLMV',
@physicalname = N'D:\MSSQL2008R2\msdbLMV.bak'
GO

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk', @logicalname = N'msdbMJS',
@physicalname = N'D:\MSSQL2008R2\msdbMJS.bak'
GO