SqlAst: script

Mostrando entradas con la etiqueta script. Mostrar todas las entradas

miércoles, 25 de enero de 2012

Migrar logins de dominio...

...o cambios de login sin cambio de dominio:

ALTER LOGIN [dominio\login WITH NAME=[nuevo dominio\login]

lunes, 21 de febrero de 2011

Renombrar instancia SQL Server

Por defecto (nombre del servidor:

sp_dropserver NombreServidorAntiguo
GO
sp_addserver NuevoNombreServidor, local
GO

Instancia

sp_dropserver NombreServfidorAntiguo\NombreInstanciaAntigua
GO
sp_addserver NombreservidorNuevo\NombreInstanciaNueva, local
GO

Clúster:

Usar el Adminitrador de Clúster:

Poner 'offline' SQL Server y volver a ponerlo de nuevo en linea.

En todos los casos, una vez cambiado el nombre verificar que haya funcionado

Select @@servername

Recordar que habrá que manualmente modificar los paquetes SSIS para actualizar conel nuevo nombre del servidor/instancia

Ejemplo de error:

Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2011-02-21 10:35:27.16 Code: 0x80019002 Source: OnPreExecute

Hay un script que facilita las cosas. Está publicado en MSDN aunque ellos no lo crearan y se curen en salud diciendo que no admiten responsabilidades. funcionar funciona bien

use msdb
DECLARE @oldservername as varchar(max)
SET @oldservername='\'

-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername=@@servername

declare @xml as varchar(max)
declare @packagedata as varbinary(max)
-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor
FOR
SELECT    id
FROM         sysdtspackages90
WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix


declare @planid uniqueidentifier
fetch next from PlansToFix into @planid
while (@@fetch_status<>-1)  -- for each plan

begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysdtspackages90 where id= @planid  -- get the plan's xml converted to an xml string

declare @planname varchar(max)
select @planname=[name] from  sysdtspackages90 where id= @planid  -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
UPDATE    sysdtspackages90 SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

end
fetch next from PlansToFix into @planid  -- get the next plan

end

close PlansToFix
deallocate PlansToFix

Más información:

cómo renombrar una instancia de SQL (inglés) 2008R2

Cómo renombrar una instancia 2005 (incluye scipts par amodificar SSIS)
Cómo renombrar un clúster

viernes, 28 de enero de 2011

Modificar varios trabajos a la vez

Una manera rápida de generar scripts para modificar trabajos de forma masiva. Por ejemplo, tras crear varios planes de mantenimiento, queremos cambiar el propietario del trabajo a 'SA', si el trabajo falla lo escriba en el evento de windows y lo notifique por correo a un determinado operador:

SELECT 'EXEC msdb.dbo.sp_update_job @job_name=N'''+NAME+''' , @owner_login_name=N''sa'', @notify_level_eventlog= 2, @notify_level_email = 2
,@notify_email_operator_name =N''USUARIO''' FROM msdb..sysjobs where category_id = 3

Ejecutar el código y automáticamente os creará los scripts.

Para más información y opciones del procedimeinto sp_update_job, echadle un vistazo a los libros en linea o aqui.

lunes, 25 de octubre de 2010

Mover la Tempdb

La tempdb se crea por defecto en el mismo directorio que los binarios de SQL. Una buena (y recomendable) práctica es separar la tempdb del resto de bbdd.

(en el ejemplo se mueve el archivo mdf a G:\MSSQL\DATA\ y el log a H:\MSSQL\LOGS\. Los directorios deben existir)

lunes, 20 de septiembre de 2010

T-SQL y Algoritmos Genéticos

Os pongo este script como ejemplo de la utilización de T-SQL para programar algoritmos genéticos

Ttrata de calcular la siguiente función (típico ejemplo):

Maximizar f(x1, x2) = 21.5 + x1 sin(4πx1) + x2 sin(20πx2)
Sujeto a:
-3.0 <- x1 <- 12.1
4.1 <- x2 <- 5.8

Este script está basado en la solución que Willian Talada aporta en “A Genetic Algorithm Sample in T-SQL" para el problema del robot y las latas que plantea Melanie Mitchell en su libro “Complexity: A Guided Tour”, 2009

Para convertir los valores binarios a base 10, el algoritmo utiliza una simple función definida
de usuario llamada ConvertFromBase

/* Función que convierte cualquier base a base 10
http://dpatrickcaldwell.blogspot.com/2009/05/converting-hexadecimal-or-binary-to.html*/
CREATE FUNCTION ConvertFromBase  
(  
    @value AS VARCHAR(MAX),  
    @base AS BIGINT  
) RETURNS BIGINT AS BEGIN  
  
    -- just some variables  
    DECLARE @characters CHAR(36),  
            @result BIGINT,  
            @index SMALLINT;  
  
    -- initialize our charater set, our result, and the index  
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',  
           @result = 0,  
           @index = 0;  
  
    -- make sure we can make the base conversion.  there can't  
    -- be a base 1, but you could support greater than base 36  
    -- if you add characters to the @charater string  
    IF @base < 2 OR @base > 36 RETURN NULL;  
  
    -- while we have characters to convert, convert them and  
    -- prepend them to the result.  we start on the far right  
    -- and move to the left until we run out of digits.  the  
    -- conversion is the standard (base ^ index) * digit  
 WHILE @index < LEN(@value)  
        SELECT @result = @result + POWER(@base, @index) *   
                         (CHARINDEX  
                            (SUBSTRING(@value, LEN(@value) - @index, 1)  
                            , @characters) - 1  
                         ),  
               @index = @index + 1;  
  
    -- return the result  
    RETURN @result;  
  
END

Debido a las particularidades del lenguaje T-SQL, para simplificar elegí la selección
elitista (sólo los dos mejores) como mecanismo para obtener los distintos padres.
El resultado final son dos listas: una que incluye todos los padres, con todos los detalles y otra
con el valor- número de generación más alto

SET nocount on 
-- declarar tabla variable  que albergará resultados 
DECLARE @results table (secuencia int identity, NumGeneracion int, DNA varchar(33), x1 float, x2 float, valor float) 
 
DECLARE 
  @i int, 
  @dna varchar(33),      -- hijo que estará siendo testeado 
  @loop int, 
  @dna1 varchar(33),    -- mejor hijo 
  @dna2 varchar(33),    -- segundo mejor hijo 
  @Poblacion int,      -- Población que se está evaluando 
  @k1 int, 
  @k2 int, 
  @NumGeneraciones int, 
  @NumDescendencias int 
 
-- Número de generaciones  a evaluar 
SET @NumGeneraciones = 1000    -- generaciones 
SET @NumDescendencias = 1000   -- descendencias 
 
-- Test rápido  
--SET @NumGeneraciones = 100 
--SET @NumDescENDencias = 100 
 
 
-- generar primera población 
SET @loop = @NumDescENDencias 
SET @Poblacion = 1 
 
WHILE @loop > 0 
BEGIN 
  SET @dna=''  
  SET @i=0  
  WHILE @i < 33 -- ya que el cromosoma tendrá una longitud de 33 bits       
    BEGIN  
   
      SET @dna = @dna + CAST(CAST(RAND() * 2 as int) as varchar(1)) -- generamos uno a uno cada bit 
      SET @i = @i+1  
    END 
-- Una vez generado el cromosoma, pasamos a calcular x1, x2 y el valor de f 
 
      DECLARE @valor float 
      DECLARE @var1 dec 
      DECLARE @x1 float 
      DECLARE @x2 float 
      DECLARE @var2 dec 
      SET @var1 = CAST(SUBSTRING(@dna, 1, 18)as dec) -- extraemos 18 primeros bits 
      SET @var2 = CAST (SUBSTRING (@dna,19,15)as dec) -- extraemos restantes 15 
 
-- Convertimos a decimal. Usamos Función personal (código adjunto) 
      SET @x1= CAST( dbo.convertfromBase(@var1,2)as float)  
      SET @x2= CAST( dbo.convertfromBase(@var2,2)as float)  
      SET @x1 = -3+( @x1 * 0.00005760 ) 
      SET @x2 = 4.1+( @x2 * 0.0000518) 
 
      SET @valor = (SELECT 21.5 +( @x1 *sin(4*3.14159*@x1))+(@x2* SIN(20*3.14159*@x2))) 
 
  INSERT INTO @results (NumGeneracion, DNA, x1, x2, valor) SELECT @Poblacion, @dna, @x1, @x2,@valor 
   
  SET @loop = @loop -1 
   
END 
 
 
  
--Loop Generaciones 
WHILE @Poblacion <= @NumGeneraciones 
BEGIN 
  --Aplicamos una selección elitista: salvamos los mejores padres 
   
SELECT @k1 = secuencia FROM @results WHERE NumGeneracion=@Poblacion AND valor = (SELECT max(valor) FROM @results 
WHERE NumGeneracion=@Poblacion) 
 
SELECT @k2 = secuencia FROM @results WHERE NumGeneracion=@Poblacion AND secuencia <> @k1 AND valor = (SELECT 
max(valor) FROM @results WHERE NumGeneracion=@Poblacion AND secuencia <> @k1) 
 
SELECT @dna1 = DNA FROM @results WHERE NumGeneracion=@Poblacion AND valor = (SELECT max(valor) FROM @results WHERE 
NumGeneracion=@Poblacion) 
 
SELECT @dna2 = DNA FROM @results WHERE NumGeneracion=@Poblacion AND DNA <> @dna1 AND valor = (SELECT max(valor) FROM 
@results WHERE NumGeneracion=@Poblacion AND DNA <> @dna1) 
 
  -- Borramos el resto 
  DELETE FROM @results  
  WHERE NumGeneracion = @Poblacion 
  AND secuencia not in (@k1, @k2) 
 
  SET @Poblacion = @Poblacion + 1 
 
  -- hijos loop 
  SET @loop = @NumDescendencias 
     
WHILE @loop > 0 
     
BEGIN 
    -- aproximadamente mitad padre1 -mitad padre2  
    SET @i = CAST(RAND()* 33 as int)+1 
    SET @dna = left(@dna1,@i) + right(@dna2, 33 - @i) 
 
    -- + 5 mutaciones 
    SET @dna=STUFF(@dna,CAST(RAND()* 33 as int)+1,1, CAST(CAST(RAND() * 2 as int) as varchar(1))) 
    SET @dna=STUFF(@dna,CAST(RAND()* 33 as int)+1,1, CAST(CAST(RAND() * 2 as int) as varchar(1))) 
    SET @dna=STUFF(@dna,CAST(RAND()* 33 as int)+1,1, CAST(CAST(RAND() * 2 as int) as varchar(1))) 
    SET @dna=STUFF(@dna,CAST(RAND()* 33 as int)+1,1, CAST(CAST(RAND() * 2 as int) as varchar(1))) 
    SET @dna=STUFF(@dna,CAST(RAND()* 33 as int)+1,1, CAST(CAST(RAND() * 2 as int) as varchar(1))) 
 
    -- hijos nuevos y salvar resultados    
    SET @var1 = CAST(SUBSTRING(@dna, 1, 18)as dec) 
    SET @var2 = CAST (SUBSTRING (@dna,19,15)as dec) 
 
    SET @x1= CAST( dbo.convertFrombase(@var1,2)as float) 
    SET @x2= CAST( dbo.convertFrombase(@var2,2)as float) 
 
    SET @x1 = -3+( @x1 * 0.00005760 ) 
    SET @x2 = 4.1+( @x2 * 0.0000518) 
 
--21.5 + x1 sin(4πx1) + x2 sin(20πx2) 
    SET @valor = (SELECT 21.5 +( @x1 *sin(4*3.14159*@x1))+(@x2* sin(20*3.14159*@x2))) 
 
    INSERT INTO @results (NumGeneracion, DNA, x1, x2, valor) SELECT @Poblacion, @dna, @x1, @x2,@valor 
 
    SET @loop = @loop - 1 
    END 
END 
 
-- Sumario 
SELECT *  
FROM @results 
WHERE NumGeneracion <= @NumGeneraciones 
ORDER BY NumGeneracion asc, valor desc 
 
-- Resultado Final 
SELECT NumGeneracion,(valor)  
FROM @results  
WHERE valor in (SELECT max(valor) FROM  @results) 
ORDER BY NumGeneracion DESC

lunes, 3 de marzo de 2008

Copiar permisos de un usuario

Os pongo un sencillo script que puede ser de utilidad si queremos copiar los permisos que un usuario tiene sobre los distintos objetos de una bbdd del tipo:

Grant References ON XTable TO user GO
Grant Select ON XTable TO user GO

El script genera a su vez otros scripts para poder reproducir estos permisos en otra bbdd

use db
go

create table #dba_user_rights(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
Action varchar(20),
Columna sysname
)

insert into #dba_user_rights EXEC ('sp_helprotect NULL, ''usuario''')

select protecttype + ' ' + action + ' ON ' + object + ' TO ' + grantee + ' GO 'from #dba_user_rights

drop table #dba_user_rights

jueves, 28 de febrero de 2008

Evitar el uso del cursor

Una de las recomendaciones más tópicas a la hora de escribir scripts en T-SQL es la de eliminar el empleo de cursores siempre que sea posible.

Brad McGehee en sql-server-performance.com habla del tema. Y aunque menciona el uso de WHILE como alternativa al cursor no pone ningún modelo como ejemplo

Aquí va uno sencillo (simplemente toma los nombres de todas las bbdd del servidor y los muestra )

declare @loop int

create table #test (clave int identity(1,1) not null, dbname varchar (60))

insert into #test select name from master..sysdatabases

SET @loop = isnull((SELECT count(*) FROM #test),0)

SET @counter= 1

WHILE @loop >0 and @counter <=@loop

BEGIN

SELECT @dbname = rtrim(dbname) FROM #test

WHERE clave = @counter

print @dbname

SET @counter = @counter +1

END

Select * from #test

DROP TABLE #test

El mismo ejemplo pero usando cursor sería algo así:

declare @counter int

declare @dbname varchar (60)

create table #testcur (dbname varchar (60))

insert into #testcur select name from master..sysdatabases

declare dbcursor cursor for select dbname from #testcur

open dbcursor

fetch next from dbcursor into @dbname

while @@fetch_status = 0

begin

print @dbname

fetch next from dbcursor into @dbname

end

close dbcursor

deallocate dbcursor

drop table #testcur

miércoles, 13 de febrero de 2008

Ejecutar a la vez un mismo script en varias instancias

El pasado viernes, os puse un ejemplo de cómo correr una misma consulta en varios servidores y presentar todos los resultados en un mismo archivo de texto. Os ponía en el ejmplo un sencillo script para monitorizar el estado de la tempdb.

A continuación tenéis otro ejemplo, esta vez en perl. El script crea una carpeta con la fecha presente y en ella por cada instancia consultada crea un archivo de texto.

En mi caso, empleo este script para la monitorización diaria de logs entre otros.

#!perl

#---------------------------------------------------------

#--GETTING DATES FROM WHICH LOGS WILL BE WRITTEN

#---------------------------------------------------------

#--gets dates:current & day before to grep those entries

#--from the log file generated

#--gets date to delete seven days old rpts

use Time::localtime;

#-- get dates

#$tm = localtime(time - (86400)); #for day before

$tp = localtime; #for current

#$td = localtime(time - (604800)) ; # seven days ago

#--for each date, two different formats: yyyy-mm-dd -> SQL 7, 2k, 2005

#--yyyy/mm/dd -> SQL 6.5

#-- & eliminate possible blank spaces

$variable= sprintf("%04d-%02d-%02d\n", $tp->year+1900, ($tp->mon)+1, $tp->mday);

$variable=~s/\s//g;

$report = "script.sql";

#-----------------------------------------------

#--MAKE DIRECTORY WHERE RPTS WILL BE PLACED

#-----------------------------------------------

$directory ="$variable";

system("mkdir $directory");

@todo = (

'servidor1',

'servidor1\instancia1',

);

system( "del $directory\\*.log");

foreach (@todo) {

#--assign scalar value server

$server=$_; # server name itself

$output=$_; # since win cannot create files with "\"...

$output=~s/\\/X/; #...remove backslash and replace it for "X"

#--remove spaces

$server=~s/\s//g;

$output=~s/\s//g;

system("osql -E -S $server -t60 -n -w1000 -i $report -o $directory\\$output.log");

}

#--END

Podéis copiar el ejemplo en un archivo y salvarlo con extensión pl. Si estáis interesados en cómo usar perl con SQLl Server os recomiendo este libro

viernes, 8 de febrero de 2008

Comprobar el estado de la Tempdb en varios servidores

Una buena solución para correr un script en varios servidores es usar OSQL dentro de un batch file u otro lenguaje script

Por ejemplo, supongamos que queremos ver el estatus de la tempdb en una serie de servidores y queremos que todos los resultados aparezcan en un archivo de texto que llamo jobstatus.txt y que presenta el siguiente formato:

Fri 02/08/2008

09:41 AM

*****************************************************************

TEMPDB STATUS

*****************************************************************

Server Name DB Name DB Size Mb Space Used Mb

Servidor1 Tempdb 3316.44 0.55

Server Name DB Name DB Size Mb Space Used Mb

Servidor2 Tempdb 150.00 0.70

Server Name DB Name DB Size Mb Space Used Mb

Servidor3 Tempdb 65.56 0.59

Para ello crearemos en este caso un batch file en el cual incluiremos la lista de servidores a auditar y una línea OSQL (consultar la ayuda en línea acerca de esta utilidad para ver opciones de salida)

En primer lugar creamos el script que queremos correr y lo salvamos con extensión *.sql

set nocount on

declare @dbsize dec(15,2)

declare @database_size nvarchar(13)

declare @spaceused nvarchar(13)

select @dbsize = sum(convert(dec(15,2),size))

from tempdb.dbo.sysfiles

select @database_size =(convert(dec(15,2),@dbsize / 128)),

@spaceused=(select convert(dec (15,2),(sum(convert(dec(15,2),reserved))/128))

from tempdb..sysindexes

where indid in (0, 1, 255))

select convert(nvarchar(30),@@servername) as 'Server Name','Tempdb' as 'DB Name',

@database_size as 'DB Size Mb', @spaceused as 'Space Used Mb'

set nocount off

A continuación, necesitamos el batch file:

date /t >>jobstatus.txt

time /t >>jobstatus.txt

echo *****************************************************************>> jobstatus.txt

echo TEMPDB STATUS >>JOBSTATUS.TXT

echo *****************************************************************>>jobstatus.txt

::Aquí tenemos los servidores.

for %%t in (

servidor1

servidor2

servidor3

) do osql -E -S %%t -t30 -n -w500 -i TEMPDB.sql >> jobstatus.txt

echo ------------------------------------------------------->> jobstatus.txt

echo END OF REPORT

::si queremos que automáticamente se nos abra el fichero

notepad jobstatus.txt

Salvamos con extensión *.bat. Para ejecutar, ya sabéis doble click o bien podéis programarlo como un job (aseguraros que la cuenta que lo ejecuta tiene permisos en cada uno de los servidores).

El próximo día os pondré otro ejemplo, esta vez utilizando perl, salvando un fichero para cada servidor.

viernes, 25 de enero de 2008

Auditar Usuarios en SQL Server

Los auditores siempre piden ver usuarios con sus roles y sus bbdd por defecto. El siguiente script saca un informe en excel con esos datos para todas las bbdd de un servidor, ordenadas alfabéticamente, todo en una línea
Estos son los datos que extrae
DATABASE
USER NAME
LOGIN NAME
DEFAULT DB
CREATION DATE
MODIFIED
LOGIN TYPE
ROLES

Podéis copiar el script en Query analizer y recordad poner la salida a fichero. El nombre poned el que queráis con la extensión XLS (de excel. o *.HTML si lo queréis en este formato)

Este script es una modificación de otro similar que podéis encontrar en bastantes webs sobre SQL Server y que sinceramente no recuerdo dónde descargué.

--  SQL SERVER USERS AUDIT

-- Process
--    CREATETemp TABLE for Report
--    CREATETemp TABLE for Users
--    CREATETemp TABLE for Roles
--    Populate Db's 
--    Populate Users
--    Populate Roles
--    Iterate though each user AND update their roles into a single column for each db
--    Return the users, their logins (reports orphaned if so) and their roles

SET NOCOUNT ON

DECLARE @db    varchar (128)
DECLARE @defdb    varchar(64)
DECLARE @createdate   varchar (25)
DECLARE @lAStmodifieddate  varchar(25)
DECLARE @logintype   varchar(50)
DECLARE @loginname   varchar(64)

CREATE TABLE #rpt
(  
db    varchar(64),
Name              varchar(128),
Loginname   varchar(64),
defdb   varchar (64),
CreateDate         varchar(25),
LAStModifiedDate    varchar(25),
LoginType         varchar(50),
Roles             varchar(300)
)

CREATE TABLE #Temp_Users
(
Name              varchar(128),
Defdb   varchar(64),
CreateDate         datetime,
LAStModifiedDate    datetime,
LoginType         varchar(50),
Roles             varchar(1024),
sid   varbinary(64)

)

CREATE TABLE #Temp_Roles
(
Name              varchar(128),
Role             varchar(128)
)
DECLARE databASes CURSOR

FOR SELECT name FROM master..sysdatabASes

OPEN databASes 
FETCH NEXT FROM databases INTO @db

WHILE @@fetch_status = 0 
BEGIN
DELETE  #temp_users
INSERT INTO #Temp_Users
EXEC('SELECT m.[Name],null AS Defdb,  m.CreateDate, m.UpdateDate,
LoginType = CASE
WHEN m.IsNTName = 1 THEN ''Windows Account''
WHEN m.IsNTGroup = 1 THEN ''Windows Group''
WHEN m.isSqlUser = 1 THEN ''SQL Server User''
WHEN m.isAliased =1 THEN ''Aliased''  
WHEN m.isSQLRole = 1 THEN ''SQL Role''
WHEN m.isAppRole = 1 THEN ''Application Role''
ELSE ''Unknown''
END,
Roles = '''', sid
FROM ['+@db+']..sysusers m
WHERE m.SID IS NOT NULL AND name <> ''guest''
ORDER BY m.Name')
DELETE  #temp_roles
INSERT INTO #Temp_Roles
EXEC('SELECT MemberName = u.name, DbRole = g.name
FROM ['+@db+']..sysusers u,['+@db+']..sysusers g,['+@db+']..sysmembers m
WHERE   g.uid = m.groupuid
AND g.issqlrole = 1
AND u.uid = m.memberuid
ORDER BY 1, 2')



DECLARE @Name    varchar(128)
DECLARE @Roles   varchar(1024)
DECLARE @Role    varchar(128)

DECLARE UserCursor CURSOR FOR
SELECT name FROM #Temp_Users

OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @Name

WHILE @@FETCH_STATUS = 0

BEGIN
SET @Roles = ''
DECLARE RoleCursor CURSOR FOR
SELECT Role FROM #Temp_Roles WHERE Name = @Name

OPEN RoleCursor
FETCH NEXT FROM RoleCursor INTO @Role

WHILE @@FETCH_STATUS = 0

BEGIN
IF (@Roles > '')
SET @Roles = @Roles + ', '+@Role
ELSE
SET @Roles = @Role

FETCH NEXT FROM RoleCursor INTO @Role

END

CLOSE RoleCursor
DEALLOCATE RoleCursor

SET    @loginname = 'ALERT ORPHANED!!!'
SELECT @createdate = convert(varchar(25),createdate) FROM #temp_users WHERE Name = @Name
SELECT @Lastmodifieddate = convert(varchar(25),lastmodifieddate) FROM #temp_users WHERE Name = @Name
SELECT @logintype = logintype FROM #temp_users WHERE Name = @Name
SELECT @defdb = dbname FROM  mASter..syslogins a, #temp_users b WHERE b.name = @name AND a.sid = b.sid
SELECT @loginname= loginname FROM mASter..syslogins a, #temp_users b WHERE b.name = @name AND a.sid = b.sid

INSERT INTO #rpt VALUES(rtrim(@db),rtrim(@name),isnull(rtrim(@loginname), 'orphaned'),rtrim(@defdb),@createdate,@lastmodifieddate,rtrim(@logintype),'public, '+rtrim(@roles))

FETCH NEXT FROM UserCursor INTO @Name

END
CLOSE UserCursor
DEALLOCATE UserCursor

FETCH NEXT FROM databASes INTO @db
END

CLOSE databASes
DEALLOCATE databASes
PRINT '<b>'
PRINT '<p ALIGN = "left"> Server Name: ' +convert(char(24), @@SERVERNAME)+'</P>'
PRINT '<p ALIGN = "left"> Created by: ' + convert(char(30),SESSION_USER)+'</P>'
PRINT '<p ALIGN = "left"> Created from: ' + convert(char(30),host_name())+'</P>'
PRINT '<p ALIGN = "left"> Date: '+CONVERT(VARCHAR(32), getdate())+'</P>'
PRINT '</b>'

print '<p ALIGN = "left"><A HREF="http://url de referencia donde se reflejan las políticas en cuanto a usuarios</p></A> '
select '<DIV ALIGN="center"><TABLE BORDER="1" CELLPADDING="8" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="8" ALIGN="center"><B><h4>USERS LOGINS AND ROLES</B></h4></TD></TR>'
union  all
select     '<TR BGCOLOR="EEEEEE">
<TD ALIGN="left" WIDTH="5%"><B>DATABASE</B> </TD>
<TD ALIGN="left" WIDTH="5%"><B>USER NAME</B> </TD>
<TD ALIGN="left" WIDTH="5%"><B>LOGIN NAME</B> </TD>
<TD ALIGN="left" WIDTH="5%"><B>DEFAULT DB</B> </TD>'
union all
select
'<TD ALIGN="left" WIDTH="5%"><B>CREATION DATE</B> </TD>
<TD ALIGN="left" WIDTH="5%"><B>MODIFIED</B> </TD>
<TD ALIGN="left" WIDTH="5%"><B>LOGIN TYPE</B> </TD>
<TD ALIGN="left" WIDTH="40%"><B>ROLES</B> </TD>
</TR>'

union all
SELECT '<TR>
<TD>'+rtrim(db)+'</TD>
<TD>'+rtrim(name)+'</TD>
<TD>'+rtrim(loginname)+'</TD>
<TD>'+rtrim(defdb)+'</TD>
<TD>'+createdate+'</TD>
<TD>'+lastmodifieddate+'</TD>
<TD>'+rtrim(logintype)+' </TD>
<TD>'+rtrim(roles)+'</TD>
</TR>'
FROM #rpt
UNION ALL
SELECT '</table>'

DROP TABLE #Temp_Users
DROP TABLE #Temp_Roles
DROP TABLE #rpt

SET NOCOUNT OFF

miércoles, 26 de diciembre de 2007

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};

}}};

jueves, 20 de diciembre de 2007

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

miércoles, 19 de diciembre de 2007

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

martes, 18 de diciembre de 2007

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 ' '
print 'DBCOE ON CALL LOG REPORT'
print ' from ' + convert(char(20),getdate()-8) +'to '+ convert(char(20),getdate())+''
print ' '

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>THERE ARE NOT ONCALL RECORDS</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>REPORT </A> </TD></TR>'
union all
select ' <TR BGCOLOR="EEEEEE">
<TD ALIGN= "left" WIDTH= "5%">Dba Name</TD>
<TD ALIGN= "left" WIDTH= "7%">Date Occurred</TD>
<TD ALIGN= "left" WIDTH= "5%">PQR #</TD>
<TD ALIGN= "left" WIDTH= "7%">Server Name</TD>
<TD ALIGN= "left" WIDTH= "7%">Instance Name DB</TD>
<TD ALIGN= "left" WIDTH= "29%">Problem Description</TD>
<TD ALIGN= "left" WIDTH= "40%">Resolution Description</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 ' '
print 'END OF REPORT'
END

SET NOCOUNT OFF

lunes, 17 de diciembre de 2007

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'

martes, 4 de diciembre de 2007

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:

miércoles, 28 de noviembre de 2007

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

SqlAst

miércoles, 25 de enero de 2012

Migrar logins de dominio...

lunes, 21 de febrero de 2011

Renombrar instancia SQL Server

viernes, 28 de enero de 2011

Modificar varios trabajos a la vez

lunes, 25 de octubre de 2010

Mover la Tempdb

lunes, 20 de septiembre de 2010

T-SQL y Algoritmos Genéticos

lunes, 3 de marzo de 2008

Copiar permisos de un usuario

jueves, 28 de febrero de 2008

Evitar el uso del cursor

miércoles, 13 de febrero de 2008

Ejecutar a la vez un mismo script en varias instancias

viernes, 8 de febrero de 2008

Comprobar el estado de la Tempdb en varios servidores

viernes, 25 de enero de 2008

Auditar Usuarios en SQL Server

miércoles, 26 de diciembre de 2007

Comprobar que los servicios SQl Server están corriendo

jueves, 20 de diciembre de 2007

Arreglar usuarios huérfanos

miércoles, 19 de diciembre de 2007

Transferir usuarios y passwords en SQL Server

martes, 18 de diciembre de 2007

Generar informes en Excel desde SQL Server

lunes, 17 de diciembre de 2007

SQL Server Agent Tokens

martes, 4 de diciembre de 2007

Extraer SQl Server Logs

miércoles, 28 de noviembre de 2007

Monitorizar espacio backups y más

Entradas populares

Etiquetas