Tareas de mantenimiento en SQL Server 2008


A continuación publico 3 scripts que serirán para realizar tareas de mantenimiento sobre nuestras bases de datos en un servidor SQL Server 2008. Estos 3 scripts son para realizar una copia de seguridad, reducir el log de transacciones y regenerar índices. Lo bueno de estos scripts es que no son exclusivos para una base de datos, sino que realizarán las tareas sobre todas las bases de datos que tengas en el servidor (o las que tú quieras).

Copia de base de datos (Debes asignar la variable @backupPath con tu ruta):
  1. DECLARE @backupPath AS VARCHAR(MAX)  
  2. SET @backupPath = N'C:\SqlServer\MSSQL10.MSSQLSERVER\MSSQL\Backup\'  
  3.   
  4. DECLARE @dbName AS VARCHAR(100)  
  5. DECLARE @file AS VARCHAR(MAX)  
  6.   
  7. DECLARE c1 CURSOR FOR  
  8. SELECT name FROM master..sysdatabases sdb  
  9. WHERE sdb.name NOT IN ('master','model','msdb','pubs','northwind','tempdb')  
  10. ORDER BY name  
  11.   
  12. OPEN c1  
  13.   
  14. FETCH NEXT FROM c1  
  15. INTO @dbName  
  16.   
  17. WHILE @@FETCH_STATUS = 0  
  18. BEGIN  
  19.     SET @file = @backupPath + @dbName + '.BAK'  
  20.   
  21.     BACKUP DATABASE @dbName  
  22.     TO  DISK = @file WITH NOFORMAT, INIT,  NAME = @dbName, SKIP, NOREWIND, NOUNLOAD,  STATS = 10  
  23.   
  24.     FETCH NEXT FROM c1  
  25.     INTO @dbName  
  26. END  
  27.   
  28. CLOSE c1  
  29. DEALLOCATE c1  
Reducir el log de transacciones
  1. DECLARE @dbName AS VARCHAR(100)  
  2. DECLARE @cmd AS VARCHAR(MAX)  
  3. DECLARE c1 CURSOR FOR  
  4. SELECT name FROM master..sysdatabases sdb  
  5. WHERE sdb.name NOT IN ('master','model','msdb','pubs','northwind','tempdb')  
  6. ORDER BY name  
  7.   
  8. OPEN c1  
  9.   
  10. FETCH NEXT FROM c1  
  11. INTO @dbName  
  12.   
  13. WHILE @@FETCH_STATUS = 0  
  14. BEGIN  
  15.     --Establecer la base de datos en uso  
  16.     SET @cmd = 'USE ' + @dbName  
  17.   
  18.     --Si se establece el modo de restauración a simple, las partes inactivas del log de transacción deben ser borradas  
  19.     --Este comando reducirá el archivo de log un poco  
  20.     SET @cmd = @cmd + ' ALTER DATABASE ' + @dbName + ' SET RECOVERY SIMPLE'  
  21.   
  22.     --Obtener el nombre de log de la base de datos  
  23.     SET @cmd = @cmd + ' DECLARE @logFile AS NVARCHAR(1000)'  
  24.     SET @cmd = @cmd + ' SELECT @logFile = name FROM ' + @dbName + '.sys.database_files WHERE type_desc = ''LOG'''  
  25.     --Cambiar el modo de restauración a Simple no es suficiente, esto reduce el log a 1 MB  
  26.     SET @cmd = @cmd + ' DBCC SHRINKFILE (@logFile , 1)'  
  27.   
  28.     EXEC(@cmd)  
  29.   
  30.     FETCH NEXT FROM c1  
  31.     INTO @dbName  
  32. END  
  33.   
  34. CLOSE c1  
  35. DEALLOCATE c1  
Regenerar índices
  1. SET QUOTED_IDENTIFIER ON  
  2.   
  3. DECLARE @Table VARCHAR(255)   
  4. DECLARE @DataBase VARCHAR(255)  
  5. DECLARE @cmd NVARCHAR(500)   
  6. DECLARE @fillfactor INT  
  7.   
  8. SET @fillfactor = 90  
  9.   
  10. DECLARE DatabaseCursor CURSOR FOR   
  11. SELECT name FROM master.dbo.sysdatabases    
  12. WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')    
  13. ORDER BY 1   
  14.   
  15. OPEN DatabaseCursor   
  16.   
  17. FETCH NEXT FROM DatabaseCursor INTO @Database   
  18. WHILE @@FETCH_STATUS = 0   
  19. BEGIN   
  20.   
  21.    SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName    
  22.                     FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''    
  23.   
  24.    -- create table cursor   
  25.    EXEC (@cmd)   
  26.    OPEN TableCursor    
  27.   
  28.    FETCH NEXT FROM TableCursor INTO @Table    
  29.    WHILE @@FETCH_STATUS = 0    
  30.    BEGIN    
  31.   
  32.        -- SQL 2000 command   
  33.        --DBCC DBREINDEX(@Table,' ',@fillfactor)    
  34.           
  35.        -- SQL 2005 command   
  36.        SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'   
  37.        EXEC (@cmd)   
  38.   
  39.        FETCH NEXT FROM TableCursor INTO @Table    
  40.    END    
  41.   
  42.    PRINT @Database + ' indexes were rebuilt'  
  43.   
  44.    CLOSE TableCursor    
  45.    DEALLOCATE TableCursor   
  46.   
  47.    FETCH NEXT FROM DatabaseCursor INTO @Database   
  48. END   
  49. CLOSE DatabaseCursor    
  50. DEALLOCATE DatabaseCursor  
Estos scripts los puedes ejecutar cuando quieras o programarlos para que sean ejecutados periódicamente con el Agente SQL.

Puedes modificar la "SELECT" donde obtiene las bases de datos donde se realizarán las tareas para que se ejecuten sobre las bases de datos que quieras. Si lo ejecutas como está publicado aquí, se ejecutará sobre todas las bases de datos.

Comentarios

Publicar un comentario

Entradas populares de este blog

iscsiadm - Linux man page

Name iscsiadm - open-iscsi administration utility Synopsis iscsiadm  -m discoverydb [ -hV ] [ -d debug_level ] [ -P printlevel ] [ -I iface -t type -p ip:port [ -lD ] ] | [ [ -p ip:port -t type ] [ -o operation ] [ -n name ] [ -v value ] [ -lD ] ] iscsiadm  -m discovery [ -hV ] [ -d debug_level ] [ -P printlevel ] [ -I iface -t type -p ip:port [ -l ] ] | [ [ -p ip:port ] [ -l | -D ] ] iscsiadm  -m node [ -hV ] [ -d debug_level ] [ -P printlevel ] [ -L all,manual,automatic ] [ -U all,manual,automatic ] [ -S ] [ [ -T targetname -p ip:port -I iface ] [ -l | -u | -R | -s] ] [ [ -o operation ] [ -n name ] [ -v value ] [ -p ip:port ] ] iscsiadm  -m session [ -hV ] [ -d debug_level ] [ -P printlevel ] [ -r sessionid | sysfsdir [ -R ] [ -u | -s ] ] iscsiadm  -m iface [ -hV ] [ -d debug_level ] [ -P printlevel ] [ -I ifacename ] [ [ -o operation ] [ -n name ] [ -v value ] ] iscsiadm  -m fw [-l] iscsiadm  -m host [ -P printlevel ] [ -H host...
Leer más

ESTIMAR ESPACIO TABLA O INDICE

Desde Oracle 10g el paquete DBMS_SPACE incluye los procedimientos CREATE_TABLE_COST y CREATE_INDEX_COST, que permiten estimar el tamaño necesario para una tabla y un índice, respectivamente.Los datos básicos necesarios para estimar el tamaño de una tabla con CREATE_TABLE_COST son: el nombre del tablespace donde queremos crear la tabla, así como el número de filas que tendrá la tabla en cuestión. Para completar el proceso tenemos 2 opciones:  indicar longitud media de la fila (por ejemplo en el caso de que se trate de una tabla que ya existe, podemos sacarlo, si las estadísticas están actualizadas, de la columna USER_TABLES.AVG_ROW_LEN para la tabla en cuestión); o bien, indicar el tipo y longitud de cada columna, usando una variable del tipo SYS.CREATE_TABLE_COST_COLMNS. Sin embargo, para estimar el tamaño de un índice, tan sólo es necesaria la sentencia DDL básaica de creación del índice: "CREATE INDEX nombre_indice ON nombre de tabla (col1, ...)", pero l...
Leer más

T-SQL: Listar todas las tablas de una base de datos con sus respectivos tamaños

Imagen
Básicamente se trata de hacer una consulta de la tabla “sysindexes”, pero al tener muchos campos y algunos datos directamente ilegibles construimos una consulta haciendo las conversiones de datos solo de los campos que nos interesan, así como también los filtros respectivos para que no se incluya información que no sean propiedades de una tabla. Finalmente a esta consulta le podemos dar un formato numérico más legible y ordenarlo por el campo que nos sea de interés. En el siguiente ejemplo está ordenado descendente mente por número de registros que contiene la tabla. SELECT X.[name], REPLACE( CONVERT ( varchar , CONVERT (money, X.[ rows ]), 1), '.00' , '' ) AS [ rows ], REPLACE( CONVERT ( varchar , CONVERT (money, X.[reserved]), 1), '.00' , '' ) AS [reserved], REPLACE( CONVERT ( varchar , CONVERT (money, X.[ data ]), 1), '.00' , '' ) AS [ data ], REPLACE( CONVERT ( varchar , CONVERT...
Leer más