Un poco de humor...

...sobre bases de datos

Problema con la instalación del Ms SQL Server Express 2005

En ocasiones la instalación se queda atascada una vez llega a "Setting File Security". Puede estar así incluso hasta varias horas. La causa parece ser la resolución de seguridad con el dominio del programa de instalación. La solucción más rápida a este problema es desconectar el cable de red; a partir de ahí se acaban los problemas.

En este foro (está en inglés) aparecen todos los detalles. Microsoft tiene disponible un parche que podéis encontrar aquí

Comprobar que los servicios SQl Server están corriendo

Os pongo un script en Perl que comprueba si los servicios SQL Server están funcionando.

use Win32;
use Win32::Service;
use Time::localtime;

$tp = localtime; #for current
$variable= sprintf("%04d-%02d-%02d-%02d\n", $tp->year+1900, ($tp->mon)+1, $tp->mday);
$variable=~s/\s//g;
$user = Win32::LoginName();
$node = Win32::NodeName();

print "***********************************\n";
print " SQL SERVICES REPORT \n";
print " \n";
print "Creation Date: $variable\n";
print "User: $user\n";
print "Node: $node\n";
print "**********************************\n";
print "\n";

%state = (
0 => 'unknown',
1 => 'stopped',
2 => 'starting',
3 => 'stopping',
4 => 'running',
5 => 'resuming',
6 => 'pausing',
7 => 'paused',
8 => 'undefined', # used only by Show_Service.pl
);

@computers = (

" ServidorA",
" ServidorB",
" ServidorC",
" ServidorD"


);

sub ltrim($)
{
my $string = shift;
$string =~ s/^\s+//;
return $string;
}


foreach $Server (@computers) {
$Server= ltrim($Server);
#$key = shift;
$services= shift;
%status = shift;
%services = shift;


my ($key, $services, %status );


print "\nStatus on $Server:\n";
print "==========================================\n";
Win32::Service::GetServices($Server,\%services) or print "***ERROR: Can't retrieve the info from $Server\n";

foreach $key (sort keys %services){

if ($services{$key} =~ /SQL/){
Win32::Service::GetStatus( $Server, $services{$key}, \%status);

print "SERVICE: $key ,STATUS: $state{$status{CurrentState}} \n";

push @MyServices, $services{$key};


}}};

Nueva versión de las herramientas RML

En el blog de PPS engineers Microsoft anuncian que SQL Server support Team pone a disposición pública la nueva versión de algunas de herramientas que ellos emplean internamente (curioso que en el blog del 'supporte team' de momento no aparezca noticia alguna sobre esto)

Esta es la entrada donde se pueden encontrarse más detalles de las 'RML Utilities'
Para descargar las utilidades aquí para 32bits x86
o en este para la versión de 64bits 64 bits

Arreglar usuarios huérfanos

Este script simplemente ejecuta sp_change_users_login con la opción 'auto_fix' de manera automática para cada uno de los usuarios huérfanos de una bbdd


declare @usr varchar(100)
declare @cmd varchar(100)

declare cur insensitive cursor for

select name as 'Usuario' from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name

for read only

open Cur

fetch next from Cur into @usr
while @@fetch_status=0

begin

select @cmd=' sp_change_users_login ''auto_fix'', '''+@usr+''' '

exec(@cmd)

fetch next from Cur into @usr

end

close Cur

deallocate Cur

Transferir usuarios y passwords en SQL Server

Todo un clásico: sp_help_revlogin.
El script original fue creado por Microsoft. La versión que pongo es una modificación de un compañero (gracias Javier) que añade la bbdd por defecto del usuario e introduce la posibilidad de de seleccionar sólo los usuarios de una bbdd .

Para correr este script:

Ejecutar en servidor origen. Se crearán dos procedimientos almacenados: sp_hexadecimal and sp_help_revlogin (en caso que existieeran se borrarían y se volverían a crear)
Una vez creadas, ejecutar en QA sp_help_revlogin con las opciones deseadas. EL procedimiento generará un script con los usuarios a transferir. Ya sólo queda lanzar el script resultante en el servidor destino (copiar y pegar en QA destino)


EXEC master..sp_help_revlogin -- todos los logins
EXEC master..sp_help_revlogin 'myDB' -- logins para bbdd
EXEC master..sp_help_revlogin 'myDB', 'myUser' -- usuario en my bbdd

----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
DECLARE @dbid int --jgs

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password,dbid FROM master..sysxlogins --jgs, added dbid field
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password,dbid FROM master..sysxlogins --jgs, added dbid field
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd,@dbid --jgs added dbid variable
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN

Generar informes en Excel desde SQL Server

Excel reconoce las etiquetas HTML. Así pues, para generar informes en este formato basta sólo darle formato a la salida de nuestra consulta. Puede usarse directamente desde QA o bien crear un 'Job' que genere un archivo *.xls y luego automáticamente lo envíe por correo.

Dejo un ejemplo de un script que genera un informe sobre incidencias ocurridas durante la semana de guardia. Los comentarios están en inglés ( idioma oficial de mi empresa)

El script se invoca desde un 'job' que luego envía el informe por correo usando xp_smtp_sendmail

/*

File: oncall.sql
Desc: Script to generate a xls report of the oncall records
Author: Luis González
@@bof_revsion_marker
revision history

yyyy/mm/dd by description
========== ======= ========================================================
2006/10/11 luis v1.0.0.0 created
@@eof_revsion_marker
***************************************************************************
*/
/*
Instructions to run this script on query analizer:

Copy and paste this script on Query Analyzer
On the Menu, go to TOOLS-OPTIONS and on the RESULTS tab, on the MAXIMUM CHARACTER PER COLUMN set the value to 1000
On Menu, go to QUERY and click RESULTS ON FILE
Run the query
*/

use DBMONITOR
GO
SET NOCOUNT ON

print '<BR>'
print '<P ALIGN ="left" ><B>DBCOE ON CALL LOG REPORT</B></P>'
print '<P ALIGN ="left"><I> from ' + convert(char(20),getdate()-8) +'to '+ convert(char(20),getdate())+'</I></P>'
print '<BR>'

IF (SELECT COUNT(*) FROM oncalllog WHERE datediff(day,dateoccurred,getdate())<8 )= 0 /*checks only the last seven days*/


select '<DIV ALIGN="center"><TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="7" ALIGN="center" VALIGN="top"><A><B>THERE ARE NOT ONCALL RECORDS</B></A> </TD></TR>'

ELSE

BEGIN
select '<DIV ALIGN="center"><TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="7" ALIGN="center" VALIGN="top"><A><B>REPORT </B></A> </TD></TR>'
union all
select ' <TR BGCOLOR="EEEEEE">
<TD ALIGN= "left" WIDTH= "5%"><B>Dba Name</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Date Occurred</B></TD>
<TD ALIGN= "left" WIDTH= "5%"><B>PQR #</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Server Name</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Instance Name DB</B></TD>
<TD ALIGN= "left" WIDTH= "29%"><B>Problem Description</B></TD>
<TD ALIGN= "left" WIDTH= "40%"><B>Resolution Description</B></TD>
</TR>'
union all



select '<TD VALIGN="top"> '+ rtrim(dbaname)+ ' </TD>
<TD VALIGN="top"> '+ convert(char(10),dateoccurred,121)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(pqr#)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(servername)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(instancename)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(problemdesc)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(resolutiondesc)+ ' </TD>
</TR>' from oncalllog where datediff(day,dateoccurred,getdate())<8 -- the last seven days

union all


SELECT '</TABLE>'

print '<BR>'
print '<P ALIGN ="left" ><B>END OF REPORT</B></P>'
END



SET NOCOUNT OFF

SQL Server Agent Tokens

Los token son de gran utilidad y sin embargo su uso no es tan generalizado como debiera. En el siguiente enlace tenéis muy buena información sobre ellos. A partir de Ms SQL 2005 Microsoft
incluye xp_smtp_sendmail como correo por defecto. Asimismo ofrece la posibilidad de usar de una manera fácil los tokens del Agente para enviar información personalizada de los errores.

Pero para las versiones 7 y 2000, aún hay que seguir usando una 'manera más artesanal'. Aquí os pongo un ejemplo que utilizo para notificar errores en jobs o alertas que tengo configuradas (otro día comentaré algo sobre ellas)



declare @msg varchar(4000)
declare @srvr varchar(80)

set @srvr = '[SRVR] Error on [A-DBN]'

set @msg = ' Error on [SRVR]' + char(10)
set @msg = @msg + 'Error: [A-ERR]' +char(10)
set @msg = @msg + 'Date: [STRTDT], Time: [STRTTM]' + CHAR(10)
set @msg = @msg + 'Database: [A-DBN]' + CHAR(10)
set @msg = @msg + 'Message: ' + REPLACE("[A-MSG]", '''','') + CHAR(10) + CHAR(10)
set @msg = @msg + 'Please, verify Error Log for details'

exec master.dbo.xp_smtp_sendmail @From=N'dbalert@domain.com',
@to=N'sqlast@sqlast.com',
@Subject=@srvr ,
@Message= @msg ,
@server=N'emailserver.com'

Error 3205:Restaurar bbdd de SQL 2005 a SQL 2000

Cuando se intenta restaurar desde SQL 2005 a 2000 se produce el siguiente error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

Si se intenta hacer un 'attach' de la base de datos aparece el siguiente error:

Error 602 Severity Level 21
Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes

Estos errores se deben a que SQL 2005 tiene una estructura interna diferente de versiones anteriores, haciéndolo inteligible a versiones previas. Esto es común a todos los sistemas de bbdd; una nueva versión suele conllevar cambios estructurales.

Por el momento, la única manera posible es crear una bbdd vacía en SQL 2000, crear los objetos y luego exportar los datos bien por bcp o SSIS

En este foro, aparece un tutorial muy bueno ( en inglés) que indica los pasos a seguir

Simplificando y de modo rápido :

En Managment Studio, boton derecho en bbdd , tareas, genera scripts. Esto abririá un asistente.
Seleccionar bbd, siguiente.
En General seleccionar:

a. Script Collation , a TRUE

b. Script Database Create, a TRUE

c. Script of SQL Version, a SQL SERVER 2000

d. Script foreign keys, a FALSE

e. Script Triggers, a FALSE

Seleccionar siguiente y elegir cómo queréis salvar los scripts y finalizar.
con los scripts ya creados, usando el QA crearlos en la version 2000. Revisad los script que SQL genera porque podréis encontraros las vistas, en el caso que las tengáis puestas sin ningún orden.

El paso siguiente, para mover los datos, de nuevo botón derecho- tareas- exportar. Seguir las instrucciones del asistente (origen-destino). El tutorial advierte de un posibles problemas cuando se trata de transferir más de 12 elementos a la vez (personalmente no me ha pasado, igual era en SP1) Si os pasa, no queda más remedio que hacerlo en varias veces.

Por último, habrá que generar los scripts para los Triggers y las claves foráneas. Volved al Managment Studio, botón derecho en bbdd- tareas- Generar scripts. Aseguraros que el botón de 'Script todos los objetos' no está seleccionado, siguiente y

a. Incluir IF NOT EXISTS , a TRUE

b. Script Owner, a TRUE

c. Script of SQL Version, a SQL SERVER 2000

d. Script foreign keys, a TRUE

e. Script Triggers, a TRUE

Tras leer los elementos de la bbdd, aparece una nueva lista y un cuadro de diálogo. Seleccionar sólo las tablas y darle a siguiente. sólo queda seleccionar cómo queréis salvarlo.

Revisar los scripts y sustituir SYS.OBJECTS por SYSOBJECTS OBJECT_ID por ID

Ejemplo

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))

sería entonces

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))




SQL Server 2005, significado de Error 18456

Los fallos de 'login' genéricamente tiene asignado el número 18456, seguido de un'severity' y un 'state' en SQL Server
Recientemente el equipo SQL Protocols de Microsoft, ha publicado en su blog el significado de los 'state'

ERROR STATE DESCRIPCIÓN
2 y 5 Inválido userid
6 Intento de usar login de windows con autentificación SQL
7 Login deshabilitado y contraseña no coincide
8 Contraseña no coincide
9 Contraseña inválida
11 y 12 Login válido pero fallo en acceso al servidor
13 Servicio SQL Server en pausa
16 Usuario no tienen permisos para conectarse a la bbdd
18 Requerido cambio de contraseña

Nota: A partir de Windows Vista, el error 18456 puede ocurrir en conexiones locales debido a cómo Windows maneja las cuentas de usuario. En este post más información

Error: 17806, Severity: 20, State: 2

En los últimos días he comenzado a ver este error en SQL 2005: Error: 17806, Severity: 20, State: 2, seguido de esta otra entrada SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: named pipes ]

El problema era que un usuario intentaba conectarse al servidor de SQL 2005 desde otro dominio con el que no existía relación de confianza.

El usuario se mapeaba a una unidad del servidor SQL, dominioA, usando sus credenciales del dominioA, pero desde una máquina en dominioB.

Esta fórmula que funionó perfectamente para versiones anteriores de SQL ya no es válida desde que Micorosoft consideró que utilizar un canal de seguridad existente supone un alto riesgo.

La solucción fue ejecutar el 'managment studio' con las credenciales del dominioA ('runas' o ejecutar como')

Podéis encontrar más información en el blog de sql_protocols

Extraer SQl Server Logs

Funciona en SQL 7, 200 y 2005. Extae los logs de los últimos dos días (para más o menos días podéis modificar la tabla temporal que recoge la información de xp_enumerrorlog)



use MASTER
go

SET NOCOUNT ON
DECLARE @archive int
if (SELECT @@VERSION ) like '%Microsoft SQL Server 2005%'

goto yukon

else

goto twokey

twokey:




CREATE TABLE #Errors (Text varchar(255), ID int)
CREATE INDEX idx_msg ON #Errors(ID, Text)


CREATE TABLE #tmp
(
archive int
,[date]datetime
,[size] int -- no retrieved by xp_enumerrorlogs 7.0
)
--SO

IF (SELECT @@version ) like '%7.00%'

INSERT INTO #tmp(archive,[date])
EXEC xp_enumerrorlogs

ELSE
INSERT INTO #tmp EXEC xp_enumerrorlogs



DECLARE dbcursor cursor FOR SELECT archive
FROM #tmp WHERE archive <>0 AND
datediff(day,[date],getdate())< 3 -- errorlogs from two days old
ORDER BY archive DESC
OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @archive
WHILE @@fetch_status = 0

BEGIN
INSERT #Errors EXEC xp_readerrorlog @archive

FETCH NEXT FROM dbcursor INTO @archive
END

CLOSE dbcursor

DEALLOCATE dbcursor

DROP TABLE #tmp
INSERT #Errors EXEC xp_readerrorlog

-- to retrieve from the last errorlog
-- since with 0 as parameter won't return data but an error

declare @date1 nvarchar(11)
declare @date2 nvarchar(11)

select @date1 = convert(char(10),getdate(),121)
select @date2 = convert(char(10),getdate()-1,121)

set @date1 = @date1 +'%'
set @date2 = @date2 +'%'


PRINT ' '
SELECT 'SQL SERVER ERROR LOG. CREATION DATE: '+ CONVERT(char(20),getdate(),120)
print '=========================================================================================================='
SELECT Text FROM #Errors WHERE Text NOT LIKE '%Log backed up%' AND
Text NOT LIKE '%.TRN%' AND Text NOT LIKE '%Database backed up%' AND
Text NOT LIKE '%.BAK%' AND Text NOT LIKE '%Run the RECONFIGURE%' AND
Text NOT LIKE '%Copyright (c)%'AND
Text NOT LIKE '%Login succeeded%'AND
(Text like @date1 or Text like @date2)

print '==========================================================================================================='


DROP TABLE #Errors
goto fin

yukon:





create table #errors2 (logdate datetime, process varchar(16), texto varchar (800))
CREATE TABLE #tmp2
(
archive int
,[date]datetime
,[size] int
)

insert into #tmp2 exec xp_enumerrorlogs

DECLARE dbcursor cursor FOR SELECT archive
FROM #tmp2 WHERE archive <>0 AND
datediff(day,[date],getdate())< 3 -- errorlogs from two days old
ORDER BY archive DESC
OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @archive
WHILE @@fetch_status = 0

BEGIN
INSERT #Errors2 EXEC xp_readerrorlog @archive

FETCH NEXT FROM dbcursor INTO @archive
END

CLOSE dbcursor

DEALLOCATE dbcursor

DROP TABLE #tmp2
INSERT #Errors2 EXEC xp_readerrorlog

-- to retrieve from the last errorlog
-- since with 0 as parameter won't return data but an error

declare @date3 datetime
declare @date4 varchar(12)

select @date3 = getdate()-1


select @date4= convert(varchar(11),@date3,121)





PRINT ' '
PRINT ' '
SELECT 'SQL SERVER ERROR LOG. CREATION DATE: '+ CONVERT(char(20),getdate(),120)
print '=========================================================================================================='
SELECT LogDate,Process, rtrim(Texto) as 'Mssge' FROM #Errors2 WHERE Texto NOT LIKE '%Log backed up%' AND
Texto NOT LIKE '%.TRN%' AND Texto NOT LIKE '%Database backed up%' AND
Texto NOT LIKE '%.BAK%' AND Texto NOT LIKE '%Run the RECONFIGURE%' AND
Texto NOT LIKE '%Copyright (c)%'AND
Texto NOT LIKE '%Login succeeded%'AND
logdate > @date4
order by logdate

print '==========================================================================================================='


DROP TABLE #Errors2

fin:

Herramientas gratuitas

Os dejo aquí una recopilación de enlaces a herramientas gratuitas.

FullSun SQl server Toolkit
Ofrece información sobre el servidor, espacio, performance, etc
SQLCheck
Un monitor de rendimiento de Idera
Examdiff
Para comparar código
Sysinternals
un conjunto de herramientas creadas por Mark Russinovich que han sido compradas por Microsoft. Recomendable sobre todo ProcessExplorer, muy util para librar esos archivos que quedan pillados por software antivirus o backup
DTSBackup2000
Para mover, copiar, restaurar DTS
SQLSpy
Fantástica herramienta para monitorización y creación de informes. Además permite añadir scripts propios
WinDirStat
Visualiza qué está usando espacio en los servidores. Puede usarse remoto \\servidor\disco$
ClearTrace
Importa archivos de trazas del 200 al 2005
SQLPErmissions
De Idera; para transferir logins con susu passwords
SQLNeatener
Utilidad para formatear scripts y hacerlos un poco más presentables. Incluye GUI y linea de comando

SQL Log Rescue gratis!!!

RedGate ofrece ahora gratuitamente una gran herramienta para SQL Server: SQL Log Rescue. Podéis haceros con una copia aquí.
La herramienta sirve para el análisis de transacciones de una bbdd. Puede adjuntarse la bbdd o el log de transacciones. No provoca impacto alguno sobre el servidor.
Con esta utilidad puedes recuperar tablas borradas, deshacer consultas, descubrir quién hace qué.
Hasta hace bien poco (sólo unos meses) era necesaria la compra de una licencia (unos 200$ por servidor) pero ahora es completamente freeware.

Muy recomendable

Monitorizar espacio backups y más

Produce un informe sobre:

- cuándo fue reiniciado el servidor
-espacio en discos
-bb en servidor
-backup
-espacio para cada bbdd
- evolución del tamaño de backups (datos y log si aplicable)
- restores

También avisa si alguna bbdd está cercana a su limite de tamaño (tanto para datos como para lo) si así está configurado. Además informa si el espacio no usado en la bbdd es el 80% del total.
Utiliza sp_OAs, (opcioón no permitida por defecto en el 2005)

EL script es contiene partes que encontré en otras páginas junto con cosecha propia.

Como siempre, probarlo antes de usarlo y modificarlo, distribuirlo como queráis









SET NOCOUNT ON

/*declare variables*/

DECLARE @MB dec(28)-- should be bigint, but it wouldn't work on sql 7. arithmetic overflow might happen (on 2K)
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @low nvarchar(11)
DECLARE @name sysname
DECLARE @exec_stmt nvarchar(625)
SET @MB = 1048576

/*****************************
create temp tables
*****************************/

CREATE TABLE #drives (
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL
)


CREATE TABLE #dbsize
(
[dbname] sysname,
dbid smallint null,
dbsize nvarchar(13)null,
owner nvarchar(60)null ,
created datetime

)

CREATE TABLE #helpfile (


[DbName] varchar(140) NULL,
FileLogicalName varchar(400) NULL,
FileID int NULL,
FileGroupID int NULL,
FilePath varchar(400) NULL,
FileGroupName varchar(140) NULL,
FileTotalSizeKB varchar(20) NULL,
FileMaxSizeSetting varchar(20) NULL,
FileGrowthSetting varchar(20) NULL,
FileUsage varchar(20) NULL,
FileTotalSizeMB dec(19,4) NULL,
FileUsedSpaceMB dec(19,4) NULL,
FileFreeSpaceMB dec(19,4) NULL,
)

CREATE TABLE #filestats (
[DbName] varchar(140) NULL,
FileID int NULL,
FileGroupID int NULL,
FileTotalSizeMB dec(19,4) NULL,
FileUsedSpaceMB dec(19,4) NULL,
FileFreeSpaceMB dec(19,4) NULL,
FileLogicalName varchar(400) NULL,
FilePath varchar(400) NULL
)

CREATE TABLE #sqlperf (
[DbName] varchar(140) NULL,
LogFileSizeMB dec(19,4) NULL,
LogFileSpaceUsedpct dec(19,4) NULL,
Status int NULL
)

--get #drives using OA's

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur
FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

-- get #sqlperf

INSERT #sqlperf ([DbName], LogFileSizeMB, LogFileSpaceUsedpct, Status) EXEC ( 'DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS ')

--get #helpfile and #filestats

EXEC sp_MSForeachDB
@command1 = 'Use [?];Insert #helpfile (FileLogicalName, FileID, FilePath, FileGroupName, FileTotalSizeKB, FileMaxSizeSetting, FileGrowthSetting,FileUsage) Exec sp_helpfile; update #helpfile set dbname = ''?'' where dbname is null',
@command2 = 'Use [?];Insert #filestats (FileID, FileGroupID, FileTotalSizeMB, FileUsedSpaceMB, FileLogicalName, FilePath) exec (''DBCC SHOWFILESTATS WITH NO_INFOMSGS ''); update #filestats set dbname = ''?'' where dbname is null'


UPDATE #filestats SET FileTotalSizeMB = Round(FileTotalSizeMB*64/1024,2), FileUsedSpaceMB = Round(FileUsedSpaceMB*64/1024,2)
WHERE FileFreeSpaceMB IS null

UPDATE #filestats SET FileFreeSpaceMB = FileTotalSizeMB - FileUsedSpaceMB
WHERE FileFreeSpaceMB IS null

UPDATE #helpfile SET FileGroupID = 0 WHERE FileUsage = 'log only'

UPDATE #helpfile SET FileGroupID = b.FileGroupID, FileTotalSizeMB = b.FileTotalSizeMB, FileUsedSpaceMB = b.FileUsedSpaceMB, FileFreeSpaceMB = b.FileFreeSpaceMB
FROM #helpfile a, #filestats b

WHERE a.FilePath = b.FilePath and a.FileUsage = 'data only'

UPDATE #helpfile SET FileTotalSizeMB = Round(Cast(replace(FileTotalSizeKB,' KB', '')as dec(19,4))/1024,2)
WHERE FileTotalSizeMB is NULL

UPDATE #helpfile SET FileUsedSpaceMB = Round(FileTotalSizeMB * b.LogFileSpaceUsedpct * 0.01, 2), FileFreeSpaceMB = Round(FileTotalSizeMB * (100 - b.LogFileSpaceUsedpct) * 0.01, 2)
FROM #helpfile a, #sqlperf b
WHERE a.dbname = b.dbname and a.FileUsage = 'log only'


UPDATE #helpfile SET FilePath = STUFF ( FilePath , 1 , 1 , Upper(Left(FilePath,1)) ) Where Unicode(Left(FilePath,1)) between 97 and 122

-- get #dbsize

INSERT INTO #dbsize ([dbname], dbid,dbsize, owner, created)
SELECT [name], dbid, null, convert(nvarchar(60),suser_sname(sid)), crdate
FROM master.dbo.sysdatabases WHERE status & 32 != 32
and status & 256 != 256 and status & 512 != 512
and status & 1024 != 1024 and status & 4096 != 4096
and status & 32768 !=32768 and status & 1073741824 !=1073741824



SELECT @low = CONVERT(varchar(11),low) FROM master.dbo.spt_values
WHERE type = N'E' and number = 1

DECLARE ms_crs_c1 CURSOR FOR
SELECT [dbname] FROM #dbsize
OPEN ms_crs_c1
FETCH ms_crs_c1 INTO @name
WHILE @@fetch_status >= 0
BEGIN

/* Insert row for each database */
SELECT @exec_stmt = 'update #dbsize
SET dbsize = (SELECT STR(CONVERT(DEC(15),SUM(SIZE))* ' + @low + '/ 1048576,10,2)+ N'' MB'' FROM '
+ quotename(@name, N'[') + N'.dbo.sysfiles) WHERE current of ms_crs_c1'

EXECUTE (@exec_stmt)

FETCH ms_crs_c1 INTO @name
END
CLOSE ms_crs_c1
DEALLOCATE ms_crs_c1

--Create Report


PRINT '*************************************************'
PRINT 'Server Name: ' +convert(char(24), @@SERVERNAME)
PRINT 'Session User: ' + convert(char(30),SESSION_USER)
PRINT 'Workstation: ' + convert(char(30),host_name())
PRINT CONVERT(VARCHAR(32), getdate())
PRINT '**************************************************'
PRINT ' '
PRINT ' ================== '
PRINT '************ DBCoE SPACE REPORT ******************'
PRINT ' ================== '
PRINT ' '
PRINT ' '
DECLARE @serverup varchar(20)
SET @serverup = (SELECT convert(varchar(20),crdate) from master..sysdatabases where [name] = 'tempdb')
PRINT 'SERVER UP SINCE: ' + @serverup
PRINT ' '
PRINT ' '
PRINT 'DRIVES REPORT.-'
PRINT '---------------'
PRINT ' '
SELECT Drive, TotalSize AS 'Total(MB)',

FreeSpace AS 'Free(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 AS dec(10,2)) AS 'Free(%)'

FROM #drives

ORDER BY drive

PRINT ' '
PRINT ' '
PRINT 'DATABASE REPORT.-'
PRINT '-----------------'
PRINT ' '
IF (select count([name]) from master..sysdatabases where crdate > getdate()-8 and [name] <>'tempdb')=0

print 'No dbs added in the last seven days'

ELSE

BEGIN

print 'New dbs added in the last seven days:'

SELECT convert(varchar(36),[name]) AS 'DBName', crdate AS 'CreatedDate'

FROM master..sysdatabases

WHERE crdate > getdate()-8 AND [name] <> 'tempdb'

END

print ' '



select convert(varchar(36),a.name) as 'DBName',b.dbsize, a.dbid, suser_sname(a.sid)as 'Owner', a.crdate from master..sysdatabases a

JOIN #dbsize b

ON a.[name]= b.[dbname]

ORDER BY a.crdate
PRINT ' '
PRINT ' '
PRINT 'BACKUP REPORT.-'
PRINT '---------------'
PRINT ' '
SELECT CONVERT(char(25),a.[name]) AS 'DBName', --lists all the db in the server

ISNULL(CONVERT(VARCHAR(62),c.physical_device_name),'WARNING: NO BACKUP!!!!') AS 'Bakup Path',

Type= case type --backups type:
WHEN 'D' then 'Db'
WHEN 'I' then 'Diff'
WHEN 'L' then 'Log'
WHEN 'F' then 'File'
ELSE ' '
END,

ISNULL(CONVERT(CHAR(12),BACKUP_FINISH_DATE),' ') AS 'Last Success Bak Run',

ISNULL(RTRIM(CONVERT (CHAR(5),DATEDIFF(SECOND, BACKUP_START_DATE,BACKUP_FINISH_DATE)/60))+' m '+

RTRIM(CONVERT (CHAR(3),DATEDIFF(SECOND, BACKUP_START_DATE,BACKUP_FINISH_DATE)%60))+' s',' ')AS 'Duration'



FROM master..sysdatabases a

LEFT OUTER JOIN -- left join in order to show db´s without bak as well

(
SELECT DISTINCT MAX(media_set_id)as media_set_id,database_name,type,

MAX(backup_start_date)AS 'BACKUP_START_DATE', --get only the latest for each bak

MAX(backup_finish_date)AS 'BACKUP_FINISH_DATE'



FROM msdb..backupset

WHERE DATEDIFF(DAY,backup_finish_date,getdate())<8
--since there are bak that only run weekly

GROUP BY DATABASE_NAME,USER_NAME,type

) B

ON A.NAME= B.DATABASE_NAME

LEFT OUTER JOIN msdb..backupmediafamily c ON c.media_set_id = b.media_set_id

ORDER BY A.CRDATE

PRINT ' '
PRINT ' '
PRINT 'DATABASE SPACE REPORT.-'
PRINT '-----------------------'
PRINT ' '
print 'Review the following files (too much unused space):'
print ' '
SELECT convert(varchar(36),FileLogicalName) AS 'File Logical Name',

str(convert(numeric (15,2),FileTotalSizeMB),10,2) AS 'File Size(Mb)',

str(convert(numeric(15,2),(FileFreeSpaceMb*100/FiletotalSizeMb)),6,2)+'%'AS 'File Free Space (%)'

FROM #helpfile b

WHERE convert(numeric(15,2),filetotalsizemb) > 3000 -- will show only > 3Gb

AND CONVERT(numeric(15,2),(filefreeSpaceMb*100/FiletotalSizeMb))> 90 -- % free space on file

print ''
print ''

-- verify if growth settings are near the limit

IF (SELECT COUNT (a.name) from ( SELECT CONVERT(varchar(36),FileLogicalName) AS 'Name',

'SizeUsed' = case

WHEN b.FileMaxSizeSetting = 'Unlimited' THEN 0

ELSE convert(dec (4,4),b.FileTotalSizeMB*100/

cast(replace(b.fileMaxSizeSetting,' kb','') AS dec(19,2))/1024)
END

FROM #helpfile b) a

WHERE sizeused > 80) = 0

print ' ' -- no files getting their limits
ELSE

BEGIN
print 'Warning!!! Please, check the growth settings of the following dbs:'
print ' '
SELECT a.Name, a.SizeUsed from
(
SELECT convert(varchar(36),FileLogicalName) AS 'Name','SizeUsed' = case

WHEN b.FileMaxSizeSetting = 'Unlimited' THEN 0
--when b.FileMaxSizeSetting = '2147483648 KB ' THEN 0

ELSE
convert (dec (16,2),(convert(numeric(16,2), b.filetotalsizemb)*100)
/ (convert(numeric (16,2),replace(b.filemaxsizesetting,'kb',' '))/1024))

END
FROM #helpfile b) a

where a.sizeused > 80
END



print ' '
print ' '


SELECT convert(varchar(36),FileLogicalName) AS 'File Logical Name',
convert(varchar(62),FilePath) as 'File Path',
str(convert(numeric(12,2),(convert(dec(10,2),(cast(replace(b.filetotalsizekb,' kb','')as dec(19,2))/1024)*100) /a.totalsize)),6,2)+'%' as 'Drive Ocupied(%)',
str(convert(numeric (15,2),FileTotalSizeMB),10,2) AS 'File Size(Mb)',
str(convert(numeric(15,2),FileUsedSpaceMB),10,2) As 'File Used (Mb)',
str(convert(numeric(15,2),FileFreeSpaceMB),10,2) AS 'File Free Space (Mb)',
str(convert(numeric(15,2),(FileFreeSpaceMb*100/FiletotalSizeMb)),6,2)+'%'AS 'File Free Space (%)',
FileMaxSizeSetting,
'Size Setting Used' = case

WHEN b.FileMaxSizeSetting = 'Unlimited' THEN 'Unlimited'
ELSE convert (varchar (9),convert(dec (4,4),b.FileTotalSizeMB*100/
cast(replace(b.fileMaxSizeSetting,' kb','') AS dec(19,2))/1024)) + '%'
END,
FileGrowthSetting as 'File Growth Setting'
FROM
#drives a, #helpfile b
WHERE a.drive= substring(b.filepath,1,1)
ORDER BY Dbname, FileID


PRINT ' '
PRINT ' '
PRINT 'DATABASE BACKUP SIZE PROGRESS.-'
PRINT '------------------------------- '
PRINT ' '

SELECT CONVERT(char(30),a.name) AS 'DATABASES IN SERVER', --lists all the db in the server

CONVERT(char(15),dbsize) as 'DB SIZE',

isnull((str(CONVERT(numeric(10,2),backup_size/1048576),10,2) + 'Mb'),space(7)+'n/a') AS 'BAK SIZE(MB)', --outputs size in Mb´s

isnull((sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEweek)/BACKUP_SIZE*100),10,2) +'%'),space(7)+'n/a')AS '%Last Week',

isnull((sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEMonth)/BACKUP_SIZE*100),10,2) +'%'),space(7)+'n/a')AS '%Last Month',

isnull((sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEqrtr)/BACKUP_SIZE*100),10,2) +'%'),space(7)+'n/a')AS '%Last Qrtr'



FROM master..sysdatabases a

JOIN #dbsize on a.name =#dbsize.dbname

LEFT OUTER JOIN -- to eliminate db´s without backup

--current size
(
SELECT DISTINCT database_name,


MAX(backup_size)AS 'BACKUP_SIZE'

FROM msdb..backupset


WHERE datediff(day,backup_finish_date,getdate())<8 --since there are bak that only run weekly

and TYPE <> 'l'


GROUP BY DATABASE_NAME

) B

ON A.NAME= B.DATABASE_NAME

LEFT OUTER JOIN
(

-- one week ago

SELECT DISTINCT database_name,



MAX(backup_size)AS 'BACKUP_SIZEWeek'

FROM msdb..backupset


WHERE datediff(day,backup_finish_date,getdate())>7 --since there are bak that only run weekly

and datediff(day,backup_finish_date,getdate())<15

and TYPE <> 'l'





GROUP BY DATABASE_NAME

) C

ON A.NAME= C.DATABASE_NAME

LEFT OUTER JOIN
(
--last month

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEMonth'

FROM msdb..backupset


WHERE datediff(month,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE <> 'l'




GROUP BY DATABASE_NAME

) D

ON A.NAME= D.DATABASE_NAME

LEFT OUTER JOIN
(
--last quarter

SELECT DISTINCT database_name,


MAX(backup_size)AS 'BACKUP_SIZEQrtr'

FROM msdb..backupset


WHERE datediff(quarter,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE <> 'l'



GROUP BY DATABASE_NAME

) E

ON A.NAME= E.DATABASE_NAME

ORDER BY CREATED
PRINT ' '
PRINT ' '
PRINT 'LOG BACKUP SIZE PROGRESS.-'
PRINT '--------------------------'
PRINT ' '


SELECT CONVERT(char(30),a.name) AS 'DATABASES IN SERVER', --lists all the db in the server

CONVERT(char(15),dbsize) AS 'DB SIZE',

str(CONVERT(numeric(10,2),backup_size/1048576),10,2) + 'Mb' AS 'LOG SIZE(MB)', --outputs size in Mb´s

sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEweek)/BACKUP_SIZE*100),10,2) +'%'AS '%Last Week',

sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEMonth)/BACKUP_SIZE*100),10,2) +'%'AS '%Last Month',

sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEqrtr)/BACKUP_SIZE*100),10,2) +'%'AS '%Last Qrtr'



FROM master..sysdatabases a

JOIN #dbsize on a.name = #dbsize.dbname

JOIN -- to eliminate db´s without backup


(
SELECT DISTINCT database_name,


MAX(backup_size)AS 'BACKUP_SIZE'

FROM msdb..backupset


WHERE datediff(day,backup_finish_date,getdate())<8 --since there are bak that only run weekly

and datediff(year,backup_finish_date, getdate())=0

and TYPE = 'l'


GROUP BY DATABASE_NAME

) B

ON A.NAME= B.DATABASE_NAME

LEFT OUTER JOIN
(

-- one week ago

SELECT DISTINCT database_name,



MAX(backup_size)AS 'BACKUP_SIZEWeek'

FROM msdb..backupset


WHERE datediff(day,backup_finish_date,getdate())>7 --since there are bak that only run weekly

and datediff(day,backup_finish_date,getdate())<15

and TYPE = 'l'




GROUP BY DATABASE_NAME

) C

ON A.NAME= C.DATABASE_NAME

LEFT OUTER JOIN
(
--last month

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEMonth'

FROM msdb..backupset


WHERE datediff(month,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE = 'l'



GROUP BY DATABASE_NAME

) D

ON A.NAME= D.DATABASE_NAME

LEFT OUTER JOIN
(
--last quarter

SELECT DISTINCT database_name,


MAX(backup_size)AS 'BACKUP_SIZEQrtr'

FROM msdb..backupset


WHERE datediff(quarter,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE = 'l'



GROUP BY DATABASE_NAME

) E

ON A.NAME= E.DATABASE_NAME

ORDER BY CREATED


print ' '
print ' '
print ' RESTORE HISTORY REPORT.-'
PRINT '-------------------------'
print ' '
print ' '
Select convert(varchar(26),a.name) as 'DbName', isnull(convert(varchar(32),x.user_name),' --')as 'Restored by',

isnull(x.[date],'00:00:00') as 'Date',

isnull(x.restore_type,'-- ') as 'Restore Type'

from master..sysdatabases a left outer join

( select b.destination_database_name, b.user_name,c.date ,b.restore_type


from MSDB..restorehistory b


join

(select destination_database_name, max(restore_date)as 'Date'

from msdb..restorehistory group by destination_database_name) c on

c.date = b.restore_date


)x on a.name = x.destination_database_name


order by x.date desc, a.name


PRINT '***********************************END OF REPORT**********************************'
print ' '
print ' '


DROP TABLE #HELPFILE
DROP TABLE #FILESTATS
DROP TABLE #SQLPERF
DROP TABLE #DRIVES
DROP TABLE #DBSIZE

SET NOCOUNT OFF

Qué monitorizar I

Quisiera comenzar este blog compartiendo las tareas, más o menos diarias, considero que un administrador de bbdd debe realizar. La mayoría forman parte de mi rutina diaria (un poco pesado, ya lo sé) Otras suelo realizarlas semanal o mensualmente. Iré dando más detalles sobre cada una de ellas en los proximos días ( esa es la intención) adjuntando scripts y herramientas que yo considero útiles

• Estado de backups
• Logs: SQL, agente, Windows
• Espacio en disco, espacio de bbdd, transaction log
  
• Trabajos: fallidos, duración excesiva, nuevos trabajos añadidos, modificados
• Utilización de la temp db
• Bloqueos, procesos corriendo durante demasiado tiempo
• Disponibilidad de servicios
• Disponibilidad de las bbdd
  
• Datos inválidos, inconsistencias, integridad
  
• Fragmentación de índices
• Fragmentación de mdf's
• Potencial creciemiento de bbdd
• Fallos de logins
• Performance:
• Performance Monitor:Memory - Pages/Sec
• Performance Monitor: Network Interface – Bytes Total/Sec
• Performance Monitor: PhysicalDisk - % Disk Time - _Total
• Performance Monitor: PhysicalDisk – Current Disk Queue Length - _Total
• Performance Monitor: System - % Total Processor Time
• Performance Monitor: System – Processor Queue Length
• Performance Monitor: SQL Server – User Connections
• Performance Monitor: SQLServer - Access Methods - Page Splits/sec
  
• Performance Monitor: SQL Server – Buffer Manager – Buffer Cache Hit Ratio
• Performance Monitor: SQL Server – Memory Manager - Target Server Memory (KB)
• Performance Monitor: SQL Server – Memory Manager – Total Server Memory (KB)
• Performance Monitor: SQL Server : SQL Statistics - SQL Recompilations / sec
• Performance Monitor:System Avg Disk queue Lenght
  

• Cualquier modificación en el schema
• Waittypes

Esta es mi lista. A lo largo de los próximos días iré uno por uno dando más detalles.

un saludo