Deja un comentario

Diferencias entre TRUNCATE TABLE y DELETE FROM


Grimpi IT Blog

Una duda bastante habitual entre los desarrolladores, es cuál es la diferencia entre un TRUNCATE TABLE y un DELETE FROM TABLE. Este post (y el blog en general) está centrado en SQL Server, sin embargo, la mayoría de las diferencias entre ambas sentencias aplican a cualquier motor de bases de datos (Oracle, MySQL, DB2, etc).
Primero voy a enumerar las diferencias y luego voy a explicar el porqué de dichas diferencias.

TRUNCATE TABLE

DELETE FROM

  • Es una operación DDL.
  • Es una operación DML.
  • No permite el borrado selectivo. TRUNCATE TABLE elimina todo el contenido de la tabla.
  • Permite el borrado selectivo, mediante la clausula WHERE.
  • No se puede ejecutar, si la tabla tiene asociadas, aun si no existiesen registros en la tabla que contiene la FK.
  • Se puede ejecutar si hay FK asociadas a la tabla, pero siempre y cuando no tenga registros asociados o la FK este deshabilitada.
  • Es…

Ver la entrada original 470 palabras más

1 comentario

Mejores practicas para manejo de fechas en SQLServer


Manejando fechas de la manera correcta en SQLServer

Si eres desarrollador con SQLServer, seguramente alguna vez te habras encontrado con algun problema derivado con campos tipo fecha,  si claro es tipico, bueno aqui te dejo un post con una recomendación que te podria salvar la vida en el futuro o al menos te ahorraria algunos topes contra la pared.

ANSI,Datetime, Fechas, Formato fechas, SQLServer

Creo que siempre existe un tope en el tema del manejo de fechas en SQLServer, de modo que voy a tratar de clarificar algunos puntos en este artculo.

Existen dos tipos de datos para almacenar fecha enSQLServer,

Datetime y SmallDatetime.

La diferencia entre ambas es el espacio que ocupan y la precisin de cada tipo de datos

.Datetime es el tipo de datos mas preciso.

El punto es saber como es que SQLServer almacena internamente las fechas. Algunos pensarn en los formatos comunes mm/dd/yyyy o dd/mm/yyyy o formatos similares. Pero no es as.

SQL Server en realidad almacena las fechas como un nmero entero de 8 bytes, con los primeros 4 bytes almacenados fecha y los otros 4 bytes almacenado la hora. El Smalldatetime por su parte ocupa la mitad de esto, 4 bytes totales, 2 para fecha y 2 para la hora.

Ahora bien, como gente astuta que son me preguntaran porque motivo  los selects traen la fecha en un formato dado(americano/español, japones, coreano, papuense, etc)????

Ese formato me genera un problema al momento de filtrar por fechas, porque 3/1/2008 podra en realidad resultar el 1 de marzo del 2008 y no el 3 de enero del 2008. Me explico hasta aqui??

Como hago entonces para librarme de las configuraciones regionales, seteos, formatos y ser libre de todas estas cosas y despreocuparme por siempre y que mis consultas filtradas por fechas funcionen siempre??

Alguno de ustedes dira que quizas usando el

Standard ANSI y yo le contesto SI ! Esa es la respuesta !. El estandar ansi es as: YYYYMMDD HH:mm:ss

Ese es el formato que yo tendria que usar para mis consultas.

Veamos el caso ejemplificado con Northwind la cual viene con SQLServer 2000 o pueden bajarlo directamente desde Microsoft en el peor de los casos.

/* Creo un usuario con idioma predeterminado en Español */

  sp_addlogin‘usuario’,‘pepenervos’,‘master’,‘Espaol’

/* vamos a darle acceso a Northwind */

use

Northwind

GO

sp_grantdbaccess ‘usuario’ 

GO

Nos logueamos en el Query Analizer como “usuario”

.Con la siguiente instruccion confirmamos el idioma espaol para el usuario “usuario”:

Select @@Language

La consulta devuelve:

——————————————————— Español (1 filas afectadas)

Ahora usamos la tabla Orders y hacemos la consulta de fechas de la forma tradicional y la segunda usando el formato ANSI propuesto.

use

northwind

go

 — la opcion que siempre hemos usado hasta hoy que os he mostrado el camino select count(*) from orders where orderdate >=’01-08-1997′  

 — La opcion que cambiara tu vida. select count(*) from orders where orderdate >=’19970801′

Ambas consultas devuelven 460 registros para pedidos posteriores al 1 de agosto del 97.

Cambiamos el lenguaje al idioma ingls y volvemos a probar a ver que pasa

.

use

northwind

go

 

SET

LANGUAGE us_english

GO

  — ya dijimos que esta opcin no corre mas. select count(*) from orders where orderdate >=’01-08-1997′ 

 — la opcion ANSI que hizo que dejes de sufrir. select count(*) from orders where orderdate >=’19970801′ 

La primera consulta devolvi 670 registros y la segunda sigue devolviendo la cantidad correcta

, 460 registros. Lo que ocurri es que al cambiar el formato regional la primera consulta en realidad est buscando registros mayores el 8 de enero y eso no tiene nada que ver con el 1 de agosto que nosotros queremos filtrar !

La segunda sentencia sigue siendo correcta, de modo que ya saben, a usar el formato ANSI que se acaba el mundo.

Y si quiero traer solamente los registros de un solo da, teniendo en cuenta que el ansi me toma la hora y eso puede hacer que no devuelva todos los registros de ese dia, podemos escribir algo como lo que sigue:

Select * from orders Where orderdate >=‘19970805’and orderdate <>

En pocas palabras, le sumo un dia a la fecha que estoy buscando y hago que el filtro funcione por menor a ese dia, de ese modo vienen todos los registros del dia que quiero recuperar.

Y listo el pollo

.

Una solucin sencilla y efectiva al tema de las fechas

 

Espero le sirva

 

Hugo Román Bernachea Mail de contacto: SQLServer777@gmail.com

http://sqldata.blogspot.mx/2008/09/manejando-fechas-de-la-manera-correcta.html

.

2 comentarios

Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” (sp_tables_info_rowset_64)


sp_tables_info_rowset_64 for SQL 2000

Si te encontraste problemas con linked server de SQL SERVER 2005 de 64 bits y SQL SERVER 2000 de 32 bits, aqui te dejo este post que te podria salvar la vida.

Si tienes una maquina con una instalación de SQLServer 2005 de 64 bits (x64) algunos de los queries del sistema tienen nombres diferentes, como [comando]sys_64 y cuando tratas de conectar tu SQL2005 remotamente a una version de SQL2000 que tenga una instalación en 32 bits (x32)  es probable que recibas un extraño error  como el siguiente: Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”.

En varios foros puedes encontrar un link a microsoft KB que explica que necesitas instalar el SP4 en el servidor antiguo y con eso podrias corregir el problema,

Usualmente esta solución no funciona del todo

Cuando ejecutas un query desde SQL2005 como el siguiente:

select * from sql2000.mybase.dbo.mytable

SQLServer 2005 x64 corre el siguiente query en el remoto SQLServer2000

exec [mybase]..sp_tables_info_rowset_64 N’mytable’, N’dbo’, NULL

La solución es agregar el siguiente stored procedure en el servidor remoto con SQL2000  dentro de la base de datos master,

create procedure sp_tables_info_rowset_64     @table_name sysname,     @table_schema     sysname = null,       @table_type nvarchar(255) = null as declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

este funciona y no necesitas corrar paquetes extraños en tus servidores,

o aqui esta otra version del mismo SP, pueden probar con cualquiera:

http://social.msdn.microsoft.com/Forums/pl-PL/transactsql/thread/058d4055-67c8-446f-9d02-2dbc494363e8

Saludos espero les sirva

1 comentario

Operador Pivot y Unpivot



 Pivot y Unpivot en SQL Server 2005

Autor: Salvador Ramos
http://www.helpdna.net/

PIVOT y UNPIVOT es una de las novedades que nos proporciona Transact SQL 2005. Aquí tenéis un ejemplo de uso que os resultará muy familiar, sobre todo a los que ya habéis utilizado las Referencias cruzadas de Access.

Como convertir columnas en filas y viceversa?,  aqui les dejo este post que explica muy bien la utilizacion del operador que aparecio en la version 2005 de SQLServer, 

Se ha escuchando hablar bastante de la nueva sentencia PIVOT que viene con SQL Server 2005, ps bien, esta vez sólo quiero ejemplificar  acerca del tema. PIVOT es un sentencia que se usa para cruzar filas en columnas para mostrar resultados, la cual por cierto, toma un vison más estructurada, clara y agradable,… esta sentencia debe usarse para obtener resultados resumen más claros y descriptivos. Más información de esta sentencia pueden leerlo:  http://msdn2.microsoft.com/es-es/library/ms177410.aspx.

Bueno,
pues ahora vamos al tema.

 

Pivot y Unpivot en SQL Server 2005

Me he decidido a escribir sobre una de las novedades que nos proporciona el
lenguaje Transact SQL de SQL Server 2005, los operadores relacionales PIVOT y
UNPIVOT, que nos permitirán hacer cosas similares a las que se realizan en
Access con las Consultas de Referencias Cruzadas, y que todos echáis de
menos cuando migráis a SQL Server y necesitáis hacer algo similar.

Vamos a
comenzar poniendo un ejemplo de cómo se debía realizar algo similar, aunque con
mucha menos potencia en SQL Server 2000. En el que no disponíamos de ellos y
teníamos que realizarlo mediante la función CASE.

 

Supongamos
que tenemos que mostrar una columna con las unidades vendidas de cada artículo
de nuestro almacén por mes del año. Este es un caso típico que muchas veces
hemos almacenado en tablas renormalizadas con acumulados. Aquí os muestro los
datos que queremos mostrar:

 

Año 1997

Ene

Feb

Mar

Abr

May

Jun

Jul

Ago

Sep

Oct

Nov

Dic

Art.1

Art.2

Art.3

Art.4

 

 

Para que
podáis probar el ejemplo vamos a utilizar la tan utilizada base de datos
Northwind (base de datos de ejemplo suministrada con SQL Server 7 y 2000).
Utilizaremos las tablas Orders, Order Details y Products (que traducidas serían
algo así como Cabecera de pedidos, Detalle de pedidos, y Productos). La
información que queremos mostrar, ya agrupada, la podemos obtener con la
siguiente instrucción SELECT, aunque no en el formato que la necesitamos
mostrar:

 

select P.ProductName as
Producto, month(O.OrderDate) as Mes, sum(D.Quantity) as Cantidad

from [Order Details]
D inner join
Orders O on D.OrderID
= O.OrderID

                       inner join Products P
on D.ProductID = P.ProductID

where year(O.OrderDate) = 1997

group by P.ProductName, month(O.OrderDate)

order by 1, 2

 

El resultado obtenido es:

 

Producto          Mes   Cantidad

Alice
Mutton      1     97

Alice
Mutton      2     10

Alice
Mutton      5     73

Alice
Mutton      6     34

Alice
Mutton      7     100

Alice
Mutton      9     30

Alice
Mutton      10    55

Alice
Mutton      11    20

Alice
Mutton      12    108

Aniseed
Syrup     1     50

Aniseed
Syrup     3     20

Aniseed
Syrup     5     60

 

Ahora, si
queremos mostrarlo según el formato que hemos definido anteriormente, y estamos
utilizando SQL Server 2000, tenemos que recurrir a la función CASE, como se
muestra a continuación:

 

select P.ProductName as
Producto,

 sum(CASE WHEN month(O.OrderDate) = 1 THEN D.Quantity ELSE 0 END) AS Ene,

 sum(CASE WHEN month(O.OrderDate) = 2 THEN D.Quantity ELSE 0 END) AS Feb,

 sum(CASE WHEN month(O.OrderDate) = 3 THEN D.Quantity ELSE 0 END) AS Mar,

 sum(CASE WHEN month(O.OrderDate) = 4 THEN D.Quantity ELSE 0 END) AS Abr,

 sum(CASE WHEN month(O.OrderDate) = 5 THEN D.Quantity ELSE 0 END) AS May,

 sum(CASE WHEN month(O.OrderDate) = 6 THEN D.Quantity ELSE 0 END) AS Jun,

 sum(CASE WHEN month(O.OrderDate) = 7 THEN D.Quantity ELSE 0 END) AS Jul,

 sum(CASE WHEN month(O.OrderDate) = 8 THEN D.Quantity ELSE 0 END) AS Ago,

 sum(CASE WHEN month(O.OrderDate) = 9 THEN D.Quantity ELSE 0 END) AS Sep,

 sum(CASE WHEN month(O.OrderDate) = 10 THEN D.Quantity ELSE 0 END) AS Oct,

 sum(CASE WHEN month(O.OrderDate) = 11 THEN D.Quantity ELSE 0 END) AS Nov,

 sum(CASE WHEN month(O.OrderDate) = 12 THEN D.Quantity ELSE 0 END) AS Dic

from [Order Details]
D inner join
Orders O on D.OrderID
= O.OrderID


inner join
Products P on D.ProductID
= P.ProductID

where O.OrderDate between ‘19970101’ and ‘19971231’

group by P.ProductName

order by
1

 

El
resultado obtenido es:

 


Ene Feb Mar  Abr May Jun  Jul Ago  Sep Oct Nov Dic

Alice
Mutton 97  10   0    0  73  34  100   0   30  55  20 108

Aniseed
Syrup     50   0  20    0  60   0   14   0    0   6  20  20

Boston Crab Meat  52  20
61   30  35  52   42  60  144   0  99   1

Camembert
Pierrot 60   0  60  106  35  24  125  30  135  10  65  15

 

Como veis
no es algo tan trivial y flexible como en Access, pero con un poquito de
trabajo adicional si que se pueden realizar este tipo de consultas.

 

Si ya
disponemos de SQL Server 2005, entonces tendremos disponible una nueva
funcionalidad que nos permite realizar de forma más sencilla este tipo de
tareas, al ofrecernos los operadores relacionales PIVOT y UNPIVOT.

 

PIVOT nos
permite convertir filas en columnas. Lo podemos incluir en la cláusula FROM de
nuestras instrucciones SELECT.

 

select Producto, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,

       [7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic

from (

    — select inicial, a
pivotar. Podría ser una tabla

   select P.ProductName
as Producto, month(O.OrderDate) as Mes, 

          D.Quantity as Cantidad

   from
[Order Details] D inner join Orders O on D.OrderID = O.OrderID

          inner join Products P
on D.ProductID = P.ProductID

      where O.OrderDate between ‘19970101’ and ‘19971231’

     ) V PIVOT (
sum(Cantidad) FOR Mes IN ([1], [2], [3], [4], [5],

                 [6],
[7], [8], [9], [10], [11], [12]) ) as PT

 

Como
podéis comprobar, simplemente tenemos que usar la función PIVOT, a la cual le
indicamos una función de agregado, la columna que queremos girar (pivotar) y
desglosamos las columnas en las que queremos mostrar la información, esto nos
permite utilizar ese desglose realizado como cualquier otra columna más en
nuestra instrucción SELECT:

 

La
función PIVOT nos permite girar (pivotar), convirtiendo los valores únicos de
una columna en varias columnas de salida, y realizando agregaciones para dichos
valores. De UNPIVOT poco, más bien nada he hablado, simplemente quiero citar
que hace justo lo contrario. Si nos encontramos con la típica tabla de
acumulados, con una columna por cada mes podemos convertirla en varias filas,
una por cada mes.

 

Ya para finalizar,
quiero comentaros que, a parte de la función PIVOT, que en ciertas ocasiones se
puede quedar corta para las tareas que deseáis realizar, y que podéis echar en
falta algunas de las características de las referencias cruzadas de
Access, en SQL Server 2005, tenéis todo un motor multidimensional con servicios
OLAP, llamado Analysis Services 2005. No pretendo en este momento presentar
aquí un producto tan amplio y potente como los Analysis Services, sino que
simplemente quiero dejar una referencia. Y si alguien quiere comenzar a ampliar
información, puede comenzar por la sección que tengo dedicada a este tema en mi
sitio (www.helpdna.net), concretamente
en la sección dedicada a este tema:

http://www.helpdna.net/sqlserver_olap_datawarehouse_analysis_services.htm

FUENTE: http://www.elguille.info/NET/ADONET/firmas_salva_Pivot_Unpivot.htm

36 comentarios

Scripts de utilidad para administradores de bases de datos


Aqui  les dejo una serie de scripts que son de gran utilidad cuando de administrar bases de datos en SQL Server hablamos, divididos en 8 secciones:

  • Log de transacciones
  • Seguridad (USUARIOS, LOGGINS, PASSWORD, ESQUEMAS, ROLES, PERMISOS)
  • Data files y files groups
  • MSSQL Agent
  • Backup and restore
  • Indices
  • Reporting services
  • Utilidades y trucos

———————————-

——- LOG DE TRANSACCIONES —–

———————————-

 

—-VERIFICAR USO DEL LOG DE TRANSACCIONES ESPACIOS DISPONIBLES

DBCC SQLPERF( LOGSPACE )

 

 

—-TRUNCAR EL LOG DE TRANSACCIONES EN SQL SERVER 2000 Y 2005

use DB

CHECKPOINT

GO

BACKUP LOG Exercise WITH
TRUNCATE_ONLY

DBCC SHRINKFILE(‘Exercise_log’, 10) –Dejamos el archivo de log con un tamaño de 10 Mb

DBCC sqlperf(logspace)

 

—-TRUNCAR EL LOG DE TRANSACCION EN SQL SERVER 2008

USE DB;

GO

ALTER DATABASE AdventureWorks2008R2

SET RECOVERY SIMPLE;

GO

— Deja el log en 1 MB.

DBCC SHRINKFILE(AdventureWorks2008R2_Log, 1);

GO

— Regresar el
modelo de recuperacion a FULL.

ALTER DATABASE DB

SET RECOVERY FULL;

GO

–******************************———————–

 

 

—-AÑADIR ARCHIVOS LOG A UNA BASE DE DATOS

USE master;

GO

ALTER DATABASE DB

ADD LOG FILE

(

    NAME = DB_test1log2,

    FILENAME = ‘RUTA\DB2log.ldf’,

    SIZE = 5MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 5MB

),

(

    NAME = DB_test1log3,

    FILENAME = ‘RUTA\DB3log.ldf’,

    SIZE = 5MB,

    MAXSIZE = 100MB,

    FILEGROWTH =
5MB

);

GO

 

 

—-VERIFICAR EL ESTATUS DEL LOG DE TRANSACCIONES CUANDO ESTE LLENO

Select name as base_datos,
log_reuse_wait, log_reuse_wait_desc,

case
log_reuse_wait

when 0 then ‘Hay actualmente uno o
más archivos de registro virtual reutilizables.’

when 1 then ‘No se ha producido
ningún punto de comprobación desde el ÚLTIMO AVISO! para Ganar $$$$Se Buscan
Personas para Trabajar desde sus Casas por Internet – +Infoúltimo truncamiento
o el encabezado del registro no se ha movido más allá de un archivo de registro
virtual (todos los modelos de recuperación). Éste es un motivo habitual para
retrasar el truncamiento.’

when 2 then ‘Se necesita una copia
de seguridad del registro para hacer avanzar el encabezado del registro
(modelos de recuperación completos o registrados de forma masiva sólo). Cuando
se completa la copia de seguridad del registro, se avanza el encabezado del
registro y algún espacio del registro podría convertirse en reutilizable.’

when 3 then ‘Existe una recuperación
o copia de seguridad de datos en curso (todos los modelos de recuperación).

La copia de seguridad
de datos funciona como una transacción activa y, cuando se ejecuta, la copia de
seguridad impide el truncamiento.’

when 4 then ‘Podría existir una
transacción de larga duración en el inicio de la copia de seguridad del
registro. En este caso, para liberar espacio se podría requerir otra copia de
seguridad del registro.

Se difiere una
transacción. Una transacción diferida es efectivamente una transacción activa
cuya reversión se bloquea debido a algún recurso no disponible.’

when 5 then ‘Se realiza una pausa en
la creación de reflejo de la base de datos o, en el modo de alto rendimiento,
la base de datos reflejada está notablemente detrás de la base de datos de la
entidad de seguridad (sólo para el modelo de recuperación completa).’

when 6 then ‘Durante las
replicaciones transaccionales, las transacciones relevante para las
publicaciones no se han entregado aún a la base de datos de distribución (sólo
para el modelo de recuperación completa).’

when 7 then ‘Se está creando una
instantánea de base de datos (todos los modelos de recuperación). Éste es un
motivo habitual, por lo general breve, para retrasar el truncamiento del
registro.’

when 8 then ‘Se está produciendo un
recorrido del registro (todos los modelos de recuperación). Éste es un motivo
habitual, por lo general breve, para retrasar el truncamiento del registro.’

when 9 then ‘No se utiliza este
valor actualmente.’

end as columna,

recovery_model_desc as
modo_recuperacion_log, page_verify_option_desc as page_verify_bbdd,
user_access_desc as user_access,

state_desc as estado_bbdd from sys.databases

 

 

 

 

 

 

—————————————————————————

——- Seguridad (USUARIOS, LOGGINS, PASSWORD, ESQUEMAS, ROLES, PERMISOS–

—————————————————————————

 

—-DESCRIPCION DE LOS ROLES DE SERVIDOR

sp_helpsrvrole

 

—-MIEMBROS QUE PERTENECEN A UN ROL DE SERVIDOR

sp_helpsrvrolemember

 

—-ROLES DEFINIDOS EN LA BASE DE DATOS

sp_helprole

 

—-MIEMBROS DE LOS ROLES DE BASES DE DATOS

sp_helprolemember

 

—-FIXED DATABASE ROLES

sp_helpdbfixedrole

 

—-LISTA DE LOGINS POR BASE DE DATOS

sp_helplogins

 

sp_srvrolepermission permisos que tiene cada rol en el servidor

sp_addsrvrolemember  agrega un inicio de sesion a un rol

sp_dropsrvrolemember quita usuario, inicio de sesion o grupo del rol del servidor

sp_helpuser          informacion de usuarios en el servidor actual

sp_helpntgroup       Grupos

xp_cmdshell ‘whoami.exe’ para saber quien tiene permisos de ejecutar xp_cmdshell

 

—-ASIGNAR PERMISOS A USUARIOS SOBRE OBJETOS

grant
tipo_de_permiso on objeto to rol   

 

—- CREAR ROLES

CREATE ROLE role_name [ AUTHORIZATION owner_name ]

 

—- INFORMACION DE LOS ROLES

select * from sys.database_role_members 

 

—- INFORMACION DE LOS ROLES

select * from sys.database_principals

  

—- INFORMACION DE LOS ESQUEMAS

select * from sys.schemas 

 

—- INFORMACION DE LOGGINS

SELECT * FROM sys.server_principals

 

—- NOMBRE DE USUARIO ACTUAL

SELECT CURRENT_USER

 

—- NOMBRE DE SYSTEM_USER

SELECT SYSTEM_USER

 

—- USUARIOS HUERFANOS SIN INICIO DE SESION

EXEC sp_change_users_login‘Update_One’, ‘ExistingUser’, ‘NewLogin’

 

 

use master select sid, name from master..syslogins — *** Ver los Inicios de Sesión de la Instancia de SQL
Server ***

use DB select uid, name, sid from sysusers where
islogin=1 — *** Ver los Usuarios de la Base de Datos actual de SQL Server ***

 

 

 

CAMBIAR DUEÑO DE UN OBJETO

EXEC sp_changeobjectowner‘CurrentOwner.TableName’,
‘newOwner’

 

—-CHECAR QUIEN ES DUEÑO DE OBJETOS

select s.name, u.name from sysusers u, sysobjects s

 where s.uid=u.uid and s.uid!=1

 

—-LIGAR LOGGIN CON USUARIO

 sp_change_users_login ‘AUTO_FIX’,‘usr_reports’

 

—-CAMBIAR PASSWORD SQL SERVER 2000

 sp_password @old = null, @new = ‘pasword’, @loginame = user

 

—-CAMBIAR PASSWORD SQL SERVER 2005 y 2008

ALTER LOGIN user WITH PASSWORD = ‘pasword’

 

 

—- LISTAR PERMISOS DE LOS USUARIOS

select dp.NAME usuario, dp.type_desc AS tipo, o.NAME AS nombre_de_objeto,
p.permission_name nombre_de_permiso,

p.state_desc AS permisos from sys.database_permissions p

left    OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID

inner   JOIN sys.database_principals dp on  p.grantee_principal_id
= dp.principal_id
order by
usuario

 

 

—- Estadísticas de inicio de sesión sql server 2008

select * from sys.dm_exec_sessions

 

—- número de sesiones de cada usuario  sql server 2008

SELECT login_name
,COUNT(session_id) AS session_count

FROM sys.dm_exec_sessions

GROUP BY login_name;

 

 

—- Buscar sesiones inactivas que tienen transacciones abiertas sql server 2008

SELECT s.*

FROM sys.dm_exec_sessions AS
s

WHERE EXISTS

    (

    SELECT *

    FROM sys.dm_tran_session_transactions
AS t

    WHERE t.session_id = s.session_id

    )

    AND NOT EXISTS

    (

    SELECT *

    FROM sys.dm_exec_requests AS r

    WHERE r.session_id = s.session_id

    );

 

 

 

—- NUMERO DE CONEXIONES A LA INSTANCIA POR APLICACION

SELECT

     CPU            = SUM(cpu_time)

    ,WaitTime
= SUM(total_scheduled_time)

    ,ElapsedTime
= SUM(total_elapsed_time)

    ,Reads
= SUM(num_reads)

    ,Writes
= SUM(num_writes)

    ,Connections
= COUNT(1)

    ,Program
= program_name

FROM sys.dm_exec_connections con

LEFT JOIN sys.dm_exec_sessions
ses

    ON ses.session_id = con.session_id

GROUP BY program_name

 

 

 

—- NUMERO DE CONEXIONES A LA INSTANCIA POR USUARIO

SELECT

     CPU            = SUM(cpu_time)

    ,WaitTime
= SUM(total_scheduled_time)

    ,ElapsedTime
= SUM(total_elapsed_time)

    ,Reads
= SUM(num_reads)

    ,Writes
= SUM(num_writes)

    ,Connections
= COUNT(1)

    ,[login]
= original_login_name

from sys.dm_exec_connections con

LEFT JOIN sys.dm_exec_sessions
ses

ON ses.session_id = con.session_id

GROUP BY original_login_name

 

 

 

 

 

—————————–

–DATA FILES Y FILES GROUPS–

—————————–

 

 

sp_helpfilegroup MOSTRAR FILES Y FILES GROUPS

sp_helpfile       MOSTRAR INFORMACION DE LOS FILES DE LA BASE DE DATOS ACTUAL

sp_helpdb CICLISMO  MOSTRAR INFORMACION DE LAS BASES DE DATOS

DBCC
SHRINKDATABASE(DB, 25)REDUCIR BASE DE DATOS

 

—- Mostrar informacion de los archivos log y mdf de la base de datos actual

select * from sys.database_files

 

—- Añadir datafiles

use master

go

Alter database tempdb add file (name = tempdev1,

filename = ‘G:\MSSQL.2\MSSQL\Data\tempdb1.ndf’)

 

 

—- ESPACIO DISPONIBLE EN DATAFILES  SQL SERVER 2000

use db

select a.FILEID,

[FILE_SIZE_MB] =  convert(decimal(12,2),round(a.size/128.000,2)),

[SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,‘SpaceUsed’)/128.000,2)),

[FREE_SPACE_MB] = convert(decimal(12,2),round((a.sizefileproperty(a.name,‘SpaceUsed’))/128.000,2)) ,

NAME = left(a.NAME,15), FILENAME = left(a.FILENAME,30)

from dbo.sysfiles a

 

 

ESPACIO DISPONIBLE EN LOS DATA FILES SQL SERVER 2005 – 2008

SELECT name
Nombre_Archivo ,size/128.0
CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS
EspaciodisponibleMB FROM sys.database_files;

 

 

 

 

 

—————-

— MSSQL Agent–

—————-

 

sp_help_job  lista de jobs en msdb

 

—- LISTA DE JOBS FALLIDOS EN SU ULTIMO INTENTO

SELECT name AS [JOBS FALLIDOS] FROM
msdb.dbo.sysjobs
A, msdb.dbo.sysjobservers B

WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

 

—- INFORMACION DE LOS SCHEDULES DE UN JOB

USe msdb;

GO

 

EXEC dbo.sp_help_jobschedule

    @job_name = N’FTM: ActualizaStatusConvenio’,

    @schedule_name =
N’JobActualizaStatusConvenio’ ,

    @Include_description = 1;

 

GO

 

—- Ver un listado de Jobs Deshabilitados:

SELECT name FROM msdb.dbo.sysjobs

WHERE enabled = 0 ORDER BY name

 

—- Permisos necesarios para crear jobs

USE [msdb]

 GO

 CREATE USER
[MyUser] FOR LOGIN
[MyUser]

 GO

 USE [msdb]

 GO

 EXEC sp_addrolemember‘SQLAgentUserRole’, ‘MyUser’

 GO

 

 

 

 

 

 ————————–

 — BACKUP AND RESTORE  —

 ————————–

 

 

—- LISTA DE BACKUPS REALIZADOS EN LA INSTANCIA

Select
database_name,server_Name,Machine_Name,b.[name]as Backup_Name,Backup_Start_Date,Backup_Finish_Date
,Physical_Device_Name

from  master.sys.databases a

Inner Join msdb..backupset
b             on
a.Name = b.Database_Name

Inner Join msdb..backupmediafamily
c     on    c.media_set_id
= b.media_set_id

 

—- LISTA DE ULTIMOS RESTORES HECHOS A LA BASE DE DATOS

USE MSDB

GO

SELECT TOP 5 *

FROM
RESTOREHISTORY WITH (nolock)

WHERE (DESTINATION_DATABASE_NAME =
‘NOMBREBD’)

ORDER BY RESTORE_DATE DESC

 

—- ULTIMO RESPALDO HECHO A LA BASE DE DATOS

select
database_name,max(backup_finish_date) as backup_finish_date from
msdb..backupset

where
database_name=‘your_database’

group by database_name

 

 

—- ULTIMA RESTAURACION HECHA A LA BASE DE DATOS

select
destination_database_name,max(restore_date) as restore_date from msdb..restorehistory

where
destination_database_name=‘your_database’

group by destination_database_name

 

 

 

 

———–

–INDICES–

———–

 

DBCC DBREINDEX(“HumanResources.Employee”, PK_Employee_EmployeeID,80); COMO RECONSTRUIR UN INDICE

 

sp_rename @objname = ‘ciclista.PK_ciclista’
, @newname = ‘PK_ciclistarenombrado’,
@objtype = ‘INDEX’   COMO RENOMBRAR UN INDICE

 

sp_helpindex Ejemplo VER INDICES SOBRE LA TABLA

 

sp_help Ejemplo   MOSTRAR INFORMACION SOBRE EL OBJETO

 

—- DEFRAGMENTAR INDICES EN LINEA SQLSERVER 2000

DBCC
INDEXDEFRAG(CS_PACC, “DBO.sotbl_MultPro”, PK_MultPro)

 

—- COMPROBAR NIVEL DE FRAGMENTACION SQLSERVER 2000

USE CS_PACC

GO

DBCC SHOWCONTIG(‘DBO.sotbl_MultPro’)

GO

 

 

 

 

————————–

— Reporting services  —

————————–

 

—- LISTADO DE LAS SUSCRIPCIONES EXISTENTES

select ‘SubnDesc’ = s.Description,‘ReportPath’= c.Path,

‘SubnOwner’ = us.UserName,‘LastStatus’= s.LastStatus,

‘LastRun’= s.LastRunTime,‘ReportModifiedBy’= uc.UserName,

‘ScheduleId’= rs.ScheduleId,‘SubscriptionId’= s.SubscriptionID

from
ReportServer.dbo.Subscriptions
s

join
ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID

join
ReportServer.dbo.ReportSchedule
rs on rs.SubscriptionID
= s.SubscriptionID

join
ReportServer.dbo.Users
uc on uc.UserID
= c.ModifiedByID

join
ReportServer.dbo.Users
us on us.UserID
= s.OwnerId

join msdb.dbo.sysjobs j on j.name = CONVERT(nvarchar(128),rs.ScheduleId)

 

 

—- INFORMACION DE LAS SUSCRIPCIONES EN TIEMPO DE EJECUCIÓN

select

‘Report’= c.Path, ‘Subscription’ = s.Description,

‘SubscriptionOwner’= uo.UserName,‘SubscriptionModBy’=
um.UserName,

‘SubscriptionModDate’= s.ModifiedDate,‘ProcessStart’= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessStart),

‘NotificationEntered’= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.NotificationEntered),

‘ProcessAfter’= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessAfter), n.Attempt,

‘SubscriptionLastRunTime’= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.SubscriptionLastRunTime),

n.IsDataDriven, ‘ProcessHeartbeat’= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessHeartbeat), n.Version,n.SubscriptionID

from
Notifications n

join
Subscriptions s on n.SubscriptionID
= s.SubscriptionID

join Catalog c on c.ItemID = n.ReportID

join Users uo on uo.UserID = s.OwnerID

join Users um on um.UserID = s.ModifiedByID

 

 

—- INFORME DE LOS REPORTES QUE MAS SE UTILIZAN EN REPORTING SERVICES

SELECT

ex.UserName, ex.Format, ex.TimeStart, cat.Name, ex.Parameters, CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate

FROM
ExecutionLog AS ex INNER
JOIN

Catalog AS cat ON ex.ReportID = cat.ItemID

ORDER BY ex.TimeStart DESC

–log de report server

Select * from ExecutionLog3

 

 

 

————————–

— UTILIDADES Y TRUCOS  —

————————–

 

sp_spaceused ESPACIO USADO POR UN OBJETO

 

 

/* importantando datos desde Excel*/ 

GO 

SELECT orderId, orderDate,
customerId, employeeId INTO
orders FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’, 

‘Data Source=C:\datos.xls;Extended Properties=Excel
8.0’
)…[Sheet2$] 

go

/* importantando
los datos para la tabla [ orders ]*/

 

 GO 

 INSERT  INTO EXPERIMENTO select
ID, NOMBRE,
MATERIA   FROM
OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’, 

 ‘Data Source=C:\Libroimportar.xls;Extended Properties=Excel
8.0’
)…[IMPORTAR$] where ID NOT IN

 (select ID from EXPERIMENTO)

 go

 

—- COMO RENOMBRAR SERVIDOR/INSTANCIA SQL SERVER

SELECT @@SERVERNAME

sp_dropserver ‘OLD_SERVER’ 

go sp_addserver ‘NEW_SERVER’, ‘local’  go  — POR ULTIMO REINICIAR SERVIDOR

 

 

—- HABILIDAR XP_CMDSHELL

EXEC sp_configure‘show advanced options’,
–Permitir
que las opciones avanzadas puedan ser cambiadas.

GO

RECONFIGURE

GO

EXEC sp_configure‘xp_cmdshell’,–Permitir el uso de SP XP_CMDSHELL.

GO

RECONFIGURE

GO

 

—- Espacio en cada disco para la instancia SQL SERVER

EXEC master..xp_fixeddrives

 

—- Ver que comandos estan corriendo actualmente

select session_id, Text

from

sys.dm_exec_requests r

cross

apply sys.dm_exec_sql_text(sql_handle) t

 

 

COMPROBAR ESTADO DE SERVICIOS

exec master.dbo.xp_servicecontrol‘QUERYSTATE’, ‘MSSQLServer’

exec master.dbo.xp_servicecontrol‘QUERYSTATE’, ‘SQLServerAgent’

exec master.dbo.xp_servicecontrol‘QUERYSTATE’, ‘SQLBrowser’

exec master.dbo.xp_servicecontrol‘QUERYSTATE’, ‘ReportServer$CSPDBA’

 

 

—- Ver Información de las transacciones abiertas

select * from sys.dm_tran_database_transactions

 

—- Como resetear la semilla de un identity

DBCC CHECKIDENT(‘dbo.Customers’,
RESEED, 1);

 

 

—- INSERTAR RESULTADO DE UN STORED PROCEDURE EN TABLA TEMPORAL Y EVITAR NESTED

CREATE TABLE #MYTEMPO(spid int, ecid int,[status] varchar(200), loginame varchar(200), hostname varchar(200),blk int,

dbname varchar(200),cmd varchar(200))

INSERT INTO #MYTEMPO

SELECT * FROM OPENQUERY(LOCALSERVER, ‘SET FMTONLY OFF EXEC
msdb..sp_help_job’
)

select * from #MYTEMPO

 

 

—- CREAR LINKED SERVER

EXEC sp_addlinkedserver@server
= ‘LOCALSERVER’,  @srvproduct = ,


@provider = ‘SQLOLEDB’, @datasrc = @@servername

                                              

                       

—- BUSCAR POR NOMBRE DE COLUMNA EN UNA BASE DE DATOS                       

SELECT TABLE_NAME,*

FROM INFORMATION_SCHEMA.COLUMNS

WHERE
COLUMN_NAME LIKE ‘%a%’

 

 

—- BUSCAR TEXTO EN STORED PROCEDURE

SELECT
ROUTINE_NAME, ROUTINE_DEFINITION, *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE
ROUTINE_DEFINITION LIKE ‘%BranchConvenio%’

AND
ROUTINE_TYPE=‘PROCEDURE’

1 comentario

Reparar Base de Datos Sospechosa (Suspect) SQL Server


Como reparar una base de datos sospechosa o en modo suspect?, que opciones tenemos? por que podria estar una base de datos en modo suspect? ,  a continuación dejo un post, donde podemos encontrar la solución de las preguntas anteriores,, no dejen de echarle un vistazo.

SQL Server pone una base de datos como sospechosa si alguno de los archivos de dispositivo para la base de datos no está disponible cuando intenta iniciarse. Al iniciarse, SQL Server intenta obtener un bloqueo exclusivo en el archivo de dispositivo (archivos físicos de base de datos). Si el dispositivo está siendo utilizado por otro proceso (por ejemplo, por software de copia de seguridad) o si no se encuentra el archivo, se encontrará con el escenario en donde marcará una base de datos como sospechosa. En estos casos, normalmente no hay ningún problema con los dispositivos ni con la base de datos. Para que la base de datos se recupere correctamente, el dispositivo debe estar disponible y se debe restablecer el servicio de la base de datos

Otra causa donde podemos encontrar una base de datos en estado Sospechosa (Suspect), es cuando SQL Server no es capaz de garantizar la integridad de sus datos, siendo este un error habitualmente relacionado con problemas de acceso a disco, y con caídas no ordenadas de SQL Server. Este comportamiento, de marcar la base de datos como sospechosa y prevenir que se pueda acceder a dicha base de datos, nos asegura que no se produce el acceso a una base de datos en dicho estado ya que sólo podría generar aún más problemas. En este caso (fichero o ficheros corruptos), será necesario restaurar una copia de la base de datos, aunque quizás nos pueda interesar previamente poner la base de datos en modo de emergencia para realizar una descarga de los datos de las tablas, y así poder recuperar cuanto nos sea posible.

Recuperar una Base de Datos Sospechosa (Suspect) mediante: sp_resetstatus y DBCC DBRECOVER

sp_resetstatus desactiva el indicador de sospechoso de una base de datos. Este procedimiento actualiza las columnas de modo y estado de la base de datos con nombre en sys.databases. Se debe consultar el registro de errores de SQL Server y resolver todos los problemas antes de ejecutar este procedimiento. Después de ejecutar sp_resetstatus, detenga y reinicie la instancia de SQL Server, ya que si tenemos realmente un problema de integridad, al reiniciar la instancia completa de SQL Server, el proceso de inicio comprueba el estado de integridad de la base de datos, y en caso de que se vuelvan a detectar problemas de integridad en dicha base de datos, se volverá a establecer la base de datos en estado Sospechoso (Suspect).

USE master
GO
EXEC SP_CONFIGURE ‘Allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_resetstatus ‘BBDD_Suspect’
GO

— “Raincoat la instancia de SQL Server”

USE master
GO
EXEC SP_CONFIGURE ‘Allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO

El anterior proceso tiene el inconveniente de que es necesario reiniciar la instancia de SQL, ya que si tenemos una Instancia con múltiples bases de datos de usuarios, esto producirá un corte de servicio para todas las bases de datos.

Como segunda opción:

Si no queremos reiniciar la instancia de SQL, tenemos el comando DBCC DBRECOVER, que podemos utilizar, tras la ejecución del comando sp_resetstatus. El comando DBCC DBRECOVER permitirá levantar y recuperar la base de datos de forma similar a como se hace durante el inicio de la instancia, de tal modo que no sea necesario reiniciar la instancia de SQL Server. El procedimiento a seguir sería el siguiente:

USE máster

GO

EXEC SP_CONFIGURE ‘Ello updates’,1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_resetstatus ‘BBDD_Suspect’

GO

DBCC DBRECOVER (‘BBDD_Suspect’)

GO

USE master

GO

EXEC SP_CONFIGURE ‘Allow updates’, 0

GO

RECONFIGURE WITH OVERRIDE

GO

Se recomienda revisar la información de ERRORLOG de SQL Server, después de la anterior ejecución.

 

Si no conseguimos reparar una Base de Datos en estado Sospechoso (Suspect) que tenemos identificada con problemas de corrupción en sus ficheros, podrás proceder a recuperar la copia de seguridad más reciente. Previamente, podes intentar poner la base de datos en modo de emergencia, de tal modo, que puedas intentar acceder a dicha base de datos para realizar una descarga del contenido de las tablas.

Establecer El Modo de Emergencia, en una base de datos, sólo nos deja realizarse accesos de sólo lectura, no permite realizar modificaciones, y no utiliza el Log de transacciones para las acciones que realicemos.

Establecer el Modo de Emergencia en una base de datos SQL Server 2000:

USE master
GO
EXEC SP_CONFIGURE ‘Allow updates’, 1
GO
RECONFIGURE WITH OVERRIDE
GO

UPDATE sysdatabases
SET status = status | -32768
WHERE name=’BBDD_Suspect’
GO

EXEC SP_CONFIGURE ‘Allow updates’, 0
GO
RECONFIGURE WITH OVERRIDE
GO

Establecer el Modo de Emergencia en una base de datos SQL Server 2005:

USE master
GO

ALTER DATABASE BBDD_Suspect SET EMERGENCY

GO

 

Para mas información sobre el mode EMERGENCY les dejo el siguiente link:

https://dbasqlserver.wordpress.com/2012/03/22/como-recuperarse-de-errores-en-bases-utilizando-emergency-mode/

 

Para realizar esta descarga, del contenido de las tablas, propongo usar la utilidad bcp.exe (http://msdn.microsoft.com/en-us/library/ms162802.aspx).

Pongo un ejemplo, desde el CMD:

Importar a fichero desde tabla, desde la BBDD_Suspect:

bcp BBDD_Suspect.dbo.tb out e: b.dat -U sa -P xxxxx -n -S instanciaSQL

Importar a tabla desde fichero, a una base de datos no suspect:

bcp BBDD.dbo.tb in e: b.dat -U U sa -P xxxxx -n -S instanciaSQL

Para SQL Server 2000, una vez en el Modo de Emergencia, si detectamos que el único problemas de nuestra base de datos esta relacionado con el fichero o ficheros del Log de transacciones, podríamos volver a regenerar los ficheros de Log con el comando: DBCC REBUILD_LOG, ejem.:

DBCC REBUILD_LOG (BBDD_Suspect,’D:SQLDataBBDD_Log.LDF’)

El comando DBCC REBUILD_LOG no sólo no está soportado en SQL Server 2005, sino que además ni existe.

En SQL 2005/8 tenemos varias formas de poder regenerar el log de transacciones.

– CREATE DATABASE FOR ATTACH_REBUILD_LOG:

USE [master]
GO
CREATE DATABASE [BBDD]
ON (FILENAME = N’D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBBDD.mdf’)
FOR ATTACH_REBUILD_LOG

– sp_attach_single_file_db:

sp_attach_single_file_db @dbname = ‘BBDD’ , @physname = ‘D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBBDD.mdf’

Como última opción, podemos lanzar DBCC CHECKDB con la opción REPAIR_ALLOW_DATA_LOSS,

DBCC CHECKDB (BBDD, REPAIR_ALLOW_DATA_LOSS)

Les aconsejo revisar este link:

https://dbasqlserver.wordpress.com/2012/03/22/cuando-se-debe-reconstruir-el-transaction-log/

 

Recordar que para ejecutar un DBCC CHECKDB con la opción REPAIR_ALLOW_DATA_LOSS, es requisito poner en modo Single User la base de datos, si os da problemas por tener la base de datos en modo de emergencia, reiniciar la instancia de SQL y luego ejecutar ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE, antes de lanzar DBCC CHECKDB con la opción REPAIR_ALLOW_DATA_LOSS.

Les muestro la secuencia de comando, cuando encontramos bases de datos en “suspect”, y realmente existen paginas de datos dañadas, y los métodos tradicionales o comunes no funcionan para recupera la bbdd. Lo que hay que hacer es: “Tener en cuenta que lo que os voy a pasar, en un primer lugar es para un SQL2000, no para versiones SQL2005 o superiores”:

USE master

GO

EXEC SP_CONFIGURE ‘Allow updates’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

–Ponemos la bbdd en emergencia:

UPDATE sysdatabases

SET status = status | -32768

WHERE name=’BBDDsuspect’

GO

–Forzamos a poner la bbdd ONLINE, aun con Paginas de datos en mal estado:

UPDATE SYSDATABASES SET STATUS=24 WHERE NAME= ‘BBDDsuspect’

GO

EXEC SP_CONFIGURE ‘Allow updates’, 0

GO

RECONFIGURE WITH OVERRIDE

GO

–Chequeamos la integridad de la bbdd:

Use BBDDsuspect

GO

DBCC CHECKDB

–Subsanamos lo errors de integridad:

Use master

GO

ALTER DATABASE BBDDsuspect SET SINGLE_USER;

GO

DBCC CHECKDB (BBDDsuspect, REPAIR_ALLOW_DATA_LOSS)

GO

ALTER DATABASE BBDDsuspect SET MULTI_USER;

GO

–Verificamos que no existen errores de integridad:

Use BBDDsuspect

GO

DBCC CHECKDB

Para versiones SQL2005 o superiores:

ALTER DATABASE BBDDsuspect SET EMERGENCY;
GO

ALTER DATABASE BBDDsuspect SET SINGLE_USER;
GO
DBCC CHECKDB (BBDDsuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

ALTER DATABASE BBDDsuspect SET MULTI_USER;
GO

–Verificamos que no existen errores de integridad:

Use BBDDsuspect

GO

DBCC CHECKDB

 

Espero les sirva

Saludos

 

 

fuente:

http://microsoftsqlsecret.fullblog.com.ar/base-de-datos-sospechosa-suspect.html

 

 

 

 

2 comentarios

Log de transacciones SQL Server (.ldf)


Les dejo este post que contiene todo lo que requerimos saber acerca del log de transacciones en SQL Server, que hacer si se llena?, que estados puede tener?, como respaldarlo?, compatibilidad con modelos de recuperación? y mas.

Cada base de datos contiene al menos un archivo de datos y un archivo de registro de transacciones. SQL Server almacena los datos físicamente en el archivo de datos (.mdf y .ndf). El archivo de transacciones (.ldf) almacena los detalles de todas las modificaciones que se realizan sobre la base de datos de SQL Server.

La escritura en el Log de transacciones es secuencial, y esta optimizado para ello. Se podría decir que (por norma general) carece de sentido crear más de un fichero de log de transacciones. Aunque el algoritmo de escritura en los .ldf es algo más complejo: si tuviéramos más de un fichero, la escritura la haría formando un bucle circular pasando por cada uno de ellos, respetando la secuencialidad en las transacciones. A diferencia de los ficheros de datos, donde si es posible mejorar el rendimiento de una base de datos, aumentado su número.

Es aconsejable ubicar el fichero del log de transacciones en diferente disco donde se encuentren los ficheros de datos.

Modelo de recuperación del log de transacciones en una Base de datos

El objetivo de este punto, no es explicar los procesos de backup y restore, sino hacer un resumen de los distintos estados en que se pueden configurar el log de transacciones.

El modelo de recuperación de una base de datos puede cambiarse en cualquier momento. No es frecuente cambiar de modelo de recuperación. Tenemos tres modos de configurar el log de transacciones: Simple, Full (completo) y, bulk-logged (recuperación optimizado para cargas masivas de registros).

Modelo de recuperación Simple:

Sin necesidad de hacer copias de seguridad del log de transacciones, se reduce automáticamente el espacio de registro, manteniendo al mínimo el espacio del fichero segun termina las transacciones de las consultas. De este modo no es necesario administrar el espacio del log de transacciones.

Los cambios realizados después de la copia de seguridad más reciente no están protegidos. En caso de desastre, es necesario volver a realizar dichos cambios. Sólo se puede recuperar hasta el final de una copia de seguridad.

Modelo de recuperación Completa:

Requiere copias de seguridad del log de transacciones. No se pierde trabajo si un archivo de datos se pierde o resulta dañado. Se puede recuperar hasta cualquier momento, por ejemplo, antes del error de aplicación o usuario.

Si la base de datos resulta dañada, se deben repetir los cambios realizados desde la última copia de seguridad del log de transacciones. Se puede recuperar hasta un determinado momento, siempre que las copias de seguridad se hayan completado hasta ese momento.

Modelo de recuperación bulk-logged:

Requiere copias de seguridad del log de transacciones, para ir liberando espacio en el .ldf. Puede considerarse complemento del modelo de recuperación completa, pero no sustituto, ya que permite operaciones de copia masiva de alto rendimiento, (por ejemplo, operaciones realizadas con BCP.exe, Bulk Insert, etc.…), reduciendo el uso del espacio de registro.

Si la base de datos resulta dañada o se han realizado operaciones masivas desde la última copia de seguridad completa, se han de repetir los cambios desde esa última copia de seguridad. Se puede recuperar hasta el final de cualquier copia de seguridad completa. No admite recuperaciones a un momento dado.

El “set recovery…” es la opción que permite elegir el modelo de recuperación entre simple, bulk-logged y completo. Los tres permiten realizar restores, pero sólo el modelo de recuperación completo registra y mantiene en el log de transacciones todas las operaciones e implica la realización de backups del log dentro de la política de backups. Es la opción por defecto y la recomendable (salvo circunstancias excepcionales). Ello permite operaciones como estas (recuperar un backup hasta un punto en el tiempo, hasta una marca). En el modelo de recuperación simple no es así, las operaciones quedan mínimamente logadas, los backups del log no pueden realizarse (carecen de sentido).

A modo de ejemplo muestro como cambiar el modelo de configuración del log de transacciones:

alter database bbdd set recovery simple — Pone el modelo de recuperación del log a simple

go

alter database bbdd set recovery full — Pone el modelo de recuperación del log a completa.

go

Si cambias al modelo de recuperación simple, interrumpirá la cadena de copias de seguridad de registros. Por lo tanto, es muy recomendable realizar una copia de seguridad del registro inmediatamente antes de realizar el cambio. De esta manera, podrá recuperar la base de datos hasta ese momento. Tras el cambio, necesitará realizar copias de seguridad completas y periódicas para proteger sus datos y para truncar la parte inactiva del registro de transacciones.

El cambio al modelo de recuperación completa o bulk-logged sólo tiene efecto después de la primera copia de seguridad de base de datos.

backup database TU_bbdd to disk = ‘<Unida:_ruta> TU_bbdd.bak’ with init , NOUNLOAD , NAME = N’Copia de seguridad TU_bbdd’, NOSKIP , STATS = 10, NOFORMAT

Si no se quieren sobrescribir los ficheros de backup. Muestro un ejemplo, de cómo crear los backups, teniendo en cuenta el nombre de los ficheros, de esta forma mantendremos una secuencia en los nombres:

declare @fichero varchar(250)

select @fichero = ‘< Unida:_ruta>bbdd_tlog_’ + convert(varchar(20), getdate(),112) + left(replace(convert(varchar(10), getdate(), 114), ‘:’, ”), 4) + ‘.Bak’

backup database TU_bbdd to disk = @fichero with init;

Las copias de seguridad del log de transacciones son un aspecto fundamental de los modelos de recuperación completa o bulk-logged. Las copias de seguridad de registros permiten que se trunque el registro de transacciones. Si no realiza la copia de seguridad con la frecuencia suficiente, el registro de transacciones se puede expandir hasta quedarse sin espacio en disco. Muestro un ejemplo, de cómo crear los backups del log de transacciones, teniendo en cuenta el nombre de los ficheros, de esta forma mantendremos una secuencia en los nombres, por si fuera necesario restaurar, en un punto en el tiempo.

declare @fichero varchar(250)

select @fichero = ‘< Unida:_ruta TU_bbdd_tlog_’ + convert(varchar(20), getdate(),112) + left(replace(convert(varchar(10), getdate(), 114), ‘:’, ”), 4) + ‘.trn’

backup log [TU_bbdd] to disk = @fichero

Si cambia del modelo de recuperación completa o bulk-logged al modelo de recuperación simple, interrumpirá la cadena de copias de seguridad de registros. Por lo tanto, es muy recomendable realizar una copia de seguridad del registro inmediatamente antes de realizar el cambio. De esta manera, podrá recuperar la base de datos hasta ese momento. Tras el cambio, necesitará realizar copias de seguridad periódicas para proteger sus datos y para truncar la parte inactiva del registro de transacciones.

Registro de transacciones lleno (Error 9002)

En este tema se tratan las posibles respuestas a un registro de transacciones lleno y se sugiere cómo evitar esta situación en el futuro. Cuando el registro de transacciones se llena, SQL Server Database Engine (Motor de base de datos de SQL Server) genera un error 9002. El registro se puede llenar cuando la base de datos está en línea o en recuperación. Si el registro se llena cuando la base de datos está en línea, la base de datos seguirá en conexión, pero solo se puede leer y no actualizar. Si el registro se llena durante la recuperación, Motor de base de datos marca la base de datos como RESOURCE PENDING. En ambos casos, es necesaria la intervención del usuario para proporcionar espacio de registro.

Si la base de datos estaba en recuperación cuando se produjo el error 9002, una vez resuelto el problema, recupere la base de datos mediante:

ALTERDATABASE nombreDeBaseDeDatos SETONLINE

Las columnas log_reuse_wait y log_reuse_wait_desc de la vista de catálogo sys.database.

La respuesta apropiada a un registro de transacciones lleno depende en parte de la condición o condiciones que han causado que el registro se llene. Para descubrir qué impide el truncamiento del registro en un caso determinado, use las columnas log_reuse_wait y log_reuse_wait_desc de la vista de catálogo sys.database.

Podemos mostrar la información del estado en que se encuentra el log de transacciones con la consulta :

Select name as base_datos, log_reuse_wait, log_reuse_wait_desc, recovery_model_desc as modo_recuperacion_log, page_verify_option_desc as page_verify_bbdd, user_access_desc as user_access, state_desc as estado_bbdd fromsys.databases

Podemos cruzar el resultado de la anterior consulta, con la siguiente tabla, para entender el resultado de la consulta anterior:

valor log_reuse_wait valor log_reuse_wait_desc Descripción

0

NOTHING Hay actualmente uno o más archivos de registro virtual reutilizables.

1

CHECKPOINT No se ha producido ningún punto de comprobación desde el ÚLTIMO AVISO del último truncamiento o el encabezado del registro no se ha movido más allá de un archivo de registro virtual (todos los modelos de recuperación). Éste es un motivo habitual para retrasar el truncamiento.

2

LOG_BACKUP Se necesita una copia de seguridad del registro para hacer avanzar el encabezado del registro (modelos de recuperación completos o registrados de forma masiva sólo). Cuando se completa la copia de seguridad del registro, se avanza el encabezado del registro y algún espacio del registro podría convertirse en reutilizable.

3

ACTIVE_BACKUP_OR_RESTORE Existe una recuperación o copia de seguridad de datos en curso (todos los modelos de recuperación).La copia de seguridad de datos funciona como una transacción activa y, cuando se ejecuta, la copia de seguridad impide el truncamiento.

4

ACTIVE_TRANSACTION Podría existir una transacción de larga duración en el inicio de la copia de seguridad del registro. En este caso, para liberar espacio se podría requerir otra copia de seguridad del registro.Se difiere una transacción. Una transacción diferida es efectivamente una transacción activa cuya reversión se bloquea debido a algún recurso no disponible.

5

DATABASE_MIRRORING Se realiza una pausa en la creación de reflejo de la base de datos o, en el modo de alto rendimiento, la base de datos reflejada está notablemente detrás de la base de datos de la entidad de seguridad (sólo para el modelo de recuperación completa).

6

REPLICATION Durante las replicaciones transaccionales, las transacciones relevantes para las publicaciones no se han entregado aún a la base de datos de distribución (sólo para el modelo de recuperación completa).

7

DATABASE_SNAPSHOT_CREATION Se está creando una instantánea de base de datos (todos los modelos de recuperación). Éste es un motivo habitual, por lo general breve, para retrasar el truncamiento del registro.

8

LOG_SCAN Se está produciendo un recorrido del registro (todos los modelos de recuperación). Éste es un motivo habitual, por lo general breve, para retrasar el truncamiento del registro.

9

OTHER_TRANSIENT No se utiliza este valor actualmente.

Una de las cosas que se pueden revisar, cuando nos vemos ante un registro de transacciones lleno son:

1. Realizar copias de seguridad del registro.

2. Liberar espacio en disco para que el registro pueda crecer automáticamente.

3. Mover el archivo de registro a una unidad de disco con suficiente espacio.

Mover el fichero .ldf de una base de usuario de ubicación:

Antes de nada, realiza un backup de la bbdd, por lo que pueda pasar.

Extraiga la ubicación física de sus archivos de la base de datos.

USE DataBaseName

GO

EXECsp_helpfile

GO

Desatachamos la base de datos:

usemaster

GO

Execsp_detach_db ‘DataBaseName’

GO

Movemos los ficheros .mdf y .ldf a los nuevos volúmenes, en nuestro caso el .ldf:

usemaster

GO

Execsp_attach_db ‘DataBaseName’,'<Ruta y unidad>DataBaseName_Data.MDF’,'<Nueva ruta y unidad>DataBaseName_Log.LDF’

GO

Esta forma, tenderá a desaparecer en versiones futuras, por lo que os paso como se ha de realizar:

Borraremos la Base de datos, sin eliminar los ficheros de datos ni el fichero del log de transacciones. copiamos los .mdf y .ldf a sus discos definitivos. y:

USE [master]

GO

CREATEDATABASE [database_name] ON

(FILENAME=N'<Ruta y unidad>DataBaseName_Data.mdf’),

(FILENAME=N'<Nueva ruta y unidad>DataBaseName_Log.ldf’)

FORATTACH;

GO

4. Aumentar el tamaño del log de transacciones.

En este ejemplo aumentamos el tamaño del fichero .ldf a 2GB:

USEmaster

GO

ALTERDATABASE database_name

MODIFYFILE ( NAME =N'<Nombre_logico_ldf>’, SIZE = 2097152KB )

GO

5. Agregar un archivo adicional para el log de transacciones en un disco diferente. Para agregar un archivo de registro a la base de datos, utilice la cláusula ADD LOG FILE de la instrucción ALTER DATABASE

En este ejemplo se añaden 2 archivos adicionales para el log de transacciones:

USEmaster;

GO

ALTERDATABASE databasename

ADDLOGFILE

(

NAME = test1log2,

FILENAME='<Unidad y ruta>databasename2log.ldf’,

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

),

(

NAME = test1log3,

FILENAME='<Unidad y ruta>databasename 3log.ldf’,

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

);

GO

6. Terminar o eliminar una transacción de larga duración. Ve el siguiente punto, “transacción activa”.

Transacción activa

Una causa probable de que el registro esté lleno es que se haya realizado una transacción de ejecución prolongada. Una transacción de ejecución prolongada se mantiene activa en el log de transacciones e impide el truncamiento del archivo.

Una transacción de ejecución muy prolongada hará que el registro de transacciones se llene. Para buscar las transacciones de ejecución prolongada, use la vista: sys.dm_tran_database_transactions

Select*fromsys.dm_tran_database_transactions

Esta vista de administración dinámica devuelve información sobre las transacciones en la base de datos. En una transacción de ejecución prolongada, las columnas de especial interés incluyen la hora de la primera entrada del registro (database_transaction_begin_time), el estado actual de la transacción (database_transaction_state) y el número de secuencia (LSN) del registro inicial del log de transacciones (database_transaction_begin_lsn).

Valores que puede adoptar el campo database_transaction_type:

1 = Transacciones de lectura/escritura

2 = Transacción de sólo lectura

3 = Transacción de sistema

Valores que puede adoptar el campo database_transaction_state:

1 = La transacción no se ha inicializado.

3 = La transacción se ha inicializado, pero no se han generado registros.

4 = La transacción ha generado registros.

5 = La transacción se ha preparado.

10 = La transacción se ha confirmado.

11 = La transacción se ha revertido.

12 = La transacción se está confirmando. En este estado el registro se está generando, pero no se ha materializado o se ha hecho permanente.

Otra forma de detectar las transacciones activas es con: DBCC OPENTRAN:

Esta instrucción “DBCC OPENTRAN” permite identificar el Id. de usuario del propietario de la transacción, por lo que se puede realizar un seguimiento del origen de la misma para terminarla de forma más ordenada, otro dato que nos devuelve es el SPID de la transacción.

Para detener la transacción, puede que deba utilizar la instrucción KILL. Utilice esta instrucción con sumo cuidado, especialmente cuando se estén ejecutando procesos críticos

DBCC SQLPERF (LOGSPACE)

Puede supervisar el uso del espacio del log de transacciones mediante el comando DBCC SQLPERF (LOGSPACE). Este comando devuelve información sobre la cantidad de espacio del registro de transacciones actualmente en uso e indica cuándo es necesario el truncamiento del registro de transacciones. Para obtener información acerca del tamaño actual de un archivo de registro, su tamaño máximo y la opción de crecimiento automático de este archivo, se pueden usar las columnas size, max_size y growth de ese archivo de registro en sys.database_files.

Formas para liberar espacio o reducir el tamaño del log de transacciones.

Es conveniente que antes de cualquier reducción del log, realice un backup del mismo:

declare @fichero varchar(250)

select @fichero ='< Unidad:_ruta TU_bbdd_tlog_’+convert(varchar(20),getdate(),112)+left(replace(convert(varchar(10),getdate(), 114),’:’,”), 4)+’.trn’

backuplog [TU_bbdd] todisk= @fichero

Cuando hacemos backup del log y no se especifica lo contrario, marca la parte no activa del log (transacciones que ya se les hizo commit) para ser reusada con nuevas transacciones. Si la parte no activa del log de transacciones no se marca para ser reusada, con el “backup del log”, se podrá llegar al final del archivo, y si las transacciones continúan, entonces el log deberá crecer para seguir almacenando el resto de transacciones. Ojo, que el backup completo no trunca el log, así que en dependencia de la frecuencia con que hagas el backup del log, tiene sentido hacer un backup del log después del backup completo.

Casos prácticos 1.:

Backuplog“<BaseDatos_Name>”with no_log –>> borra la parte no activa del log, pero no reduce el log.

Backuplog<BaseDatos_Name>with truncate_only –>> Borra todo el log, pero no reduce el log.

Desde SQL Server 2008, ya no se puede truncar el log, realizando un backup, con las opciones no_log y truncate_only, no funcionan.

Luego, lo importante es saber qué impide que el log de transacciones se reduzca o se recicle, para lo cual, lo más fácil es mirarlo en sys.databases, en la columna log_reuse_wait_desc. Aquí tienes su explicación: http://msdn.microsoft.com/es-es/library/ms345414.aspx. En función de lo que marque esa columna, el modelo de recuperación que tengas y para la base de datos que quieres reducir, tendrás que tomar una acción u otra, a continuación, como realizar una reducción con un shrink:

DBCC SHRINKFILE(<NombreLogico_log>, 512) –>> Reduce el log a n Mb

Casos prácticos 2.:

Si no se deja comprimir o borrar el log por que no tiene sus transacciones como distribuidas habrá que ponerlas, de la forma:

EXECsp_repldone @xactid =NULL, @xact_segno =NULL, @numtrans = 0, @time = 0, @reset = 1

—>Ver el tamaño de los log y él % de ocupación de las bbdd de una instancia:

dbcc sqlperf (logspace)

—>”esto tarda mucho…” Mostrar información de espacio actualizada acerca de una base de datos

EXECsp_spaceused @updateusage =N’TRUE’;

GO

Casos prácticos 3.:

Recomendaron para SQL Server 2008, con problemas de reducción del log de transacciones:

Ejecuta dos o tres veces la instrucción CHECKPOINT. Esto asegurará que todas las páginas de memoria se han escrito en el fichero de datos:

CHECKPOINT

BACKUPLOG NombreBaseDeDatos TODISK='<Ruta y unidad>triton1.trn’

DBCC SHRINKFILE (N’Nombre_logico_Log’, 0, TRUNCATEONLY)

GO

Casos prácticos 4.:

Comprueba qué valor muestra el campo “log_reuse_wait” de sys.databases para esa base de datos en cuestión. Si ese campo indica efectivamente que el problema es que hay transacciones activas, puedes revisarlas con el comando DBCC OPENTRAN (http://msdn.microsoft.com/es-es/library/ms182792.aspx)

alterdatabase BBDD_Name setrecoverysimple

go

checkpoint

go

checkpoint

go

checkpoint

go

checkpoint

go

alterdatabase BBDD_Name setrecoveryfull

go

–Recomendable, para comenzar secuencia, para posterior backup del log.:

backupdatabase BBDD_Name todisk='<Ruta y unidad>.bak’withinit

go

DBCC SHRINKFILE(pubs_log, 2)

Go

Casos prácticos 5.:

La operación de reducción está bloqueada

Es posible bloquear las operaciones de reducción mediante una transacción que se ejecuta con un nivel de aislamiento basado en el control de versiones de filas. Por ejemplo, si se está ejecutando una operación de eliminación grande con un nivel de aislamiento basado en el control de versiones de filas cuando se ejecuta una operación DBCC SHRINK DATABASE, la operación de reducción esperará a que la operación de eliminación se haya completado antes de reducir los archivos. Cuando esto sucede, las operaciones DBCC SHRINKFILE y DBCC SHRINKDATABASE imprimen un mensaje informativo (5202 en el caso de SHRINKDATABASE y 5203 para SHRINKFILE) en el registro de errores de SQL Server cada cinco minutos durante la primera hora, y cada hora sucesivamente. Por ejemplo, si el registro de errores contiene el siguiente mensaje de error:

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot

transaction with timestamp 15 and other snapshot transactions linked to

timestamp 15 or with timestamps older than 109 to finish.

Esto significa que la operación de reducción está bloqueada por transacciones de instantánea que tienen marcas de tiempo anteriores a 109, que es la última transacción que ha completado la operación de reducción. También indica que las columnas transaction_sequence_num o first_snapshot_sequence_num de la vista de administración dinámica sys.dm_tran_active_snapshot_database_transactions contienen el valor 15. Si la columna transaction_sequence_num o first_snapshot_sequence_num contiene un número inferior al de la última transacción completada mediante una operación de reducción (109), la operación de reducción esperará a que las transacciones finalicen.

Para resolver el problema, puede llevar a cabo una de las tareas siguientes:

  • · Finalizar la transacción que está bloqueando la operación de reducción.
  • · Finalizar la operación de reducción. Si finaliza la operación de reducción, se conservará todo el trabajo completado.
  • · No hacer nada y permitir que la operación de reducción espere a que finalice la transacción que la está bloqueando.

 

Espero les sirva,  Saludos

 

Basado en:

TechNet, MSDN.

http://microsoftsqlsecret.fullblog.com.ar/log-de-transacciones-ldf.html

 

 

A %d blogueros les gusta esto: