Scripts de utilidad para administradores de bases dedatos-- LOG DE TRANSACCIONES --VERIFICAR USO DEL LOG DE TRANSACCIONES ESPACIOS DISPONIBLESDBCCSQLPERF(LOGSPACE)-TRUNCAR EL LOG DE TRANSACCIONES EN SQL SERVER 2000 Y 2005useDBCHECKPOINTGOBACKUPLOGExerciseWITHTRUNCATE_ONLYDBCCSHRINKFILE(Exercise_log,10)Dejamos el archivo de log con un tamao de 10 MbDBCCsqlperf(logspace)-TRUNCAR EL LOG DE TRANSACCION EN SQL SERVER 2008USEDB;GOALTERDATABASEAdventureWorks2008R2SETRECOVERYSIMPLE;GO Deja el log en 1 MB.DBCCSHRINKFILE(AdventureWorks2008R2_Log,1);GO Regresar elmodelo de recuperacion a FULL.ALTERDATABASEDBSETRECOVERYFULL;GO******************************-AADIR ARCHIVOS LOG A UNA BASE DE DATOSUSEmaster;GOALTERDATABASEDBADDLOGFILE( NAME=DB_test1log2,FILENAME=RUTA\DB2log.ldf, SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=5MB),( NAME=DB_test1log3,FILENAME=RUTA\DB3log.ldf, SIZE=5MB,MAXSIZE=100MB, FILEGROWTH=5MB);GO-VERIFICAR EL ESTATUS DEL LOG DE TRANSACCIONES CUANDO ESTE LLENOSelectnameasbase_datos,log_reuse_wait,log_reuse_wait_desc,caselog_reuse_waitwhen0thenHay actualmente uno oms archivos de registro virtual reutilizables.when1thenNo se ha producidoningn punto de comprobacin desde el LTIMO AVISO! para Ganar $$$$Se BuscanPersonas para Trabajar desde sus Casas por Internet +Infoltimo truncamientoo el encabezado del registro no se ha movido ms all de un archivo de registrovirtual (todos los modelos de recuperacin). ste es un motivo habitual pararetrasar el truncamiento.when2thenSe necesita una copiade seguridad del registro para hacer avanzar el encabezado del registro(modelos de recuperacin completos o registrados de forma masiva slo). Cuandose completa la copia de seguridad del registro, se avanza el encabezado delregistro y algn espacio del registro podra convertirse en reutilizable.when3thenExiste una recuperacino copia de seguridad de datos en curso (todos los modelos de recuperacin).La copia de seguridadde datos funciona como una transaccin activa y, cuando se ejecuta, la copia deseguridad impide el truncamiento.when4thenPodra existir unatransaccin de larga duracin en el inicio de la copia de seguridad delregistro. En este caso, para liberar espacio se podra requerir otra copia deseguridad del registro.Se difiere unatransaccin. Una transaccin diferida es efectivamente una transaccin activacuya reversin se bloquea debido a algn recurso no disponible.when5thenSe realiza una pausa enla creacin de reflejo de la base de datos o, en el modo de alto rendimiento,la base de datos reflejada est notablemente detrs de la base de datos de laentidad de seguridad (slo para el modelo de recuperacin completa).when6thenDurante lasreplicaciones transaccionales, las transacciones relevante para laspublicaciones no se han entregado an a la base de datos de distribucin (slopara el modelo de recuperacin completa).when7thenSe est creando unainstantnea de base de datos (todos los modelos de recuperacin). ste es unmotivo habitual, por lo general breve, para retrasar el truncamiento delregistro.when8thenSe est produciendo unrecorrido del registro (todos los modelos de recuperacin). ste es un motivohabitual, por lo general breve, para retrasar el truncamiento del registro.when9thenNo se utiliza estevalor actualmente.endascolumna,recovery_model_descasmodo_recuperacion_log,page_verify_option_descaspage_verify_bbdd,user_access_descasuser_access,state_descasestado_bbddfromsys.databases- Seguridad (USUARIOS, LOGGINS, PASSWORD, ESQUEMAS, ROLES, PERMISOS-DESCRIPCION DE LOS ROLES DE SERVIDORsp_helpsrvrole-MIEMBROS QUE PERTENECEN A UN ROL DE SERVIDORsp_helpsrvrolemember-ROLES DEFINIDOS EN LA BASE DE DATOSsp_helprole-MIEMBROS DE LOS ROLES DE BASES DE DATOSsp_helprolemember-FIXED DATABASE ROLESsp_helpdbfixedrole-LISTA DE LOGINS POR BASE DE DATOSsp_helploginssp_srvrolepermissionpermisos que tiene cada rol en el servidorsp_addsrvrolememberagrega un inicio de sesion a un rolsp_dropsrvrolememberquita usuario, inicio de sesion o grupo del rol del servidorsp_helpuserinformacion de usuarios en el servidor actualsp_helpntgroupGruposxp_cmdshellwhoami.exepara saber quien tiene permisos de ejecutar xp_cmdshell-ASIGNAR PERMISOS A USUARIOS SOBRE OBJETOSgranttipo_de_permisoonobjetotorol-CREAR ROLESCREATEROLErole_name [ AUTHORIZATION owner_name ]-INFORMACION DE LOS ROLESselect*fromsys.database_role_members-INFORMACION DE LOS ROLESselect*fromsys.database_principals-INFORMACION DE LOS ESQUEMASselect*fromsys.schemas-INFORMACION DE LOGGINSSELECT*FROMsys.server_principals

-NOMBRE DE USUARIO ACTUALSELECTCURRENT_USER

-NOMBRE DE SYSTEM_USERSELECTSYSTEM_USER

-USUARIOS HUERFANOS SIN INICIO DE SESIONEXECsp_change_users_loginUpdate_One,ExistingUser,NewLoginusemasterselectsid,namefrommaster..syslogins ***Ver los Inicios de Sesin de la Instancia de SQLServer***useDBselectuid,name,sidfromsysuserswhereislogin=1 ***Ver los Usuarios de la Base de Datos actual de SQL Server***CAMBIAR DUEODE UN OBJETOEXECsp_changeobjectownerCurrentOwner.TableName,newOwner-CHECAR QUIEN ES DUEO DE OBJETOSselects.name,u.namefromsysusersu,sysobjectsswheres.uid=u.uidands.uid!=1-LIGAR LOGGIN CON USUARIOsp_change_users_loginAUTO_FIX,usr_reports-CAMBIAR PASSWORD SQL SERVER 2000sp_password@old=null,@new=pasword,@loginame=user-CAMBIAR PASSWORD SQL SERVER 2005 y 2008ALTERLOGINuserWITHPASSWORD=pasword-LISTAR PERMISOS DE LOS USUARIOSselectdp.NAME usuario,dp.type_descAStipo,o.NAMEASnombre_de_objeto,p.permission_name nombre_de_permiso,p.state_descASpermisosfromsys.database_permissionspleftOUTERJOINsys.all_objectsoonp.major_id=o.OBJECT_IDinnerJOINsys.database_principalsdpon p.grantee_principal_id=dp.principal_idorderbyusuario-Estadsticas de inicio de sesin sql server 2008select*fromsys.dm_exec_sessions-nmero de sesiones de cada usuario sql server 2008SELECTlogin_name,COUNT(session_id)ASsession_countFROMsys.dm_exec_sessionsGROUPBYlogin_name;-Buscar sesiones inactivas que tienen transacciones abiertas sql server 2008SELECTs.*FROMsys.dm_exec_sessionsASsWHEREEXISTS(SELECT*FROMsys.dm_tran_session_transactionsAStWHEREt.session_id=s.session_id)ANDNOTEXISTS(SELECT*FROMsys.dm_exec_requestsASrWHEREr.session_id=s.session_id);-NUMERO DE CONEXIONES A LA INSTANCIA POR APLICACIONSELECT CPU=SUM(cpu_time),WaitTime=SUM(total_scheduled_time),ElapsedTime=SUM(total_elapsed_time),Reads=SUM(num_reads),Writes=SUM(num_writes),Connections=COUNT(1),Program=program_nameFROMsys.dm_exec_connectionsconLEFTJOINsys.dm_exec_sessionssesONses.session_id=con.session_idGROUPBYprogram_name-NUMERO DE CONEXIONES A LA INSTANCIA POR USUARIOSELECT CPU=SUM(cpu_time),WaitTime=SUM(total_scheduled_time),ElapsedTime=SUM(total_elapsed_time),Reads=SUM(num_reads),Writes=SUM(num_writes),Connections=COUNT(1),[login]=original_login_namefromsys.dm_exec_connectionsconLEFTJOINsys.dm_exec_sessionssesONses.session_id=con.session_idGROUPBYoriginal_login_nameDATA FILES Y FILES GROUPSsp_helpfilegroupMOSTRAR FILES Y FILES GROUPSsp_helpfileMOSTRAR INFORMACION DE LOS FILES DE LA BASE DE DATOS ACTUALsp_helpdbCICLISMOMOSTRAR INFORMACION DE LAS BASES DE DATOSDBCCSHRINKDATABASE(DB,25)REDUCIR BASE DE DATOS-Mostrar informacion de los archivos log y mdf de la base de datos actualselect*fromsys.database_files

-Aadir datafilesusemastergoAlterdatabasetempdbaddfile(name=tempdev1,filename=G:\MSSQL.2\MSSQL\Data\tempdb1.ndf)-ESPACIO DISPONIBLE EN DATAFILES SQL SERVER 2000usedbselecta.FILEID,[FILE_SIZE_MB]=convert(decimal(12,2),round(a.size/128.000,2)),[SPACE_USED_MB]=convert(decimal(12,2),round(fileproperty(a.name,SpaceUsed)/128.000,2)),[FREE_SPACE_MB]=convert(decimal(12,2),round((a.size-fileproperty(a.name,SpaceUsed))/128.000,2)),NAME=left(a.NAME,15),FILENAME=left(a.FILENAME,30)fromdbo.sysfilesaESPACIO DISPONIBLE EN LOS DATA FILES SQL SERVER 2005 2008SELECTnameNombre_Archivo,size/128.0-CAST(FILEPROPERTY(name,SpaceUsed)ASint)/128.0ASEspaciodisponibleMBFROMsys.database_files;- MSSQL Agent-sp_help_joblista de jobs en msdb-LISTA DE JOBS FALLIDOS EN SU ULTIMO INTENTOSELECTnameAS[JOBS FALLIDOS]FROMmsdb.dbo.sysjobsA,msdb.dbo.sysjobservers BWHEREA.job_id=B.job_idANDB.last_run_outcome=0-INFORMACION DE LOS SCHEDULES DE UN JOBUSemsdb;GOEXECdbo.sp_help_jobschedule@job_name=NFTM: ActualizaStatusConvenio, @schedule_name=NJobActualizaStatusConvenio, @Include_description=1;GO-Ver un listado de Jobs Deshabilitados:SELECTnameFROMmsdb.dbo.sysjobsWHEREenabled=0ORDERBYname-Permisos necesarios para crear jobsUSE[msdb]GOCREATEUSER[MyUser]FORLOGIN[MyUser]GOUSE[msdb]GOEXECsp_addrolememberSQLAgentUserRole,MyUserGO BACKUP AND RESTORE-LISTA DE BACKUPS REALIZADOS EN LA INSTANCIASelectdatabase_name,server_Name,Machine_Name,b.[name]asBackup_Name,Backup_Start_Date,Backup_Finish_Date,Physical_Device_Namefrommaster.sys.databasesaInnerJoinmsdb..backupsetbona.Name=b.Database_NameInnerJoinmsdb..backupmediafamilycon c.media_set_id=b.media_set_id-LISTA DE ULTIMOS RESTORES HECHOS A LA BASE DE DATOSUSEMSDBGOSELECTTOP5*FROMRESTOREHISTORYWITH(nolock)WHERE(DESTINATION_DATABASE_NAME=NOMBREBD)ORDERBYRESTORE_DATEDESC-ULTIMO RESPALDO HECHO A LA BASE DE DATOSselectdatabase_name,max(backup_finish_date)asbackup_finish_datefrommsdb..backupsetwheredatabase_name=your_databasegroupbydatabase_name-ULTIMA RESTAURACION HECHA A LA BASE DE DATOSselectdestination_database_name,max(restore_date)asrestore_datefrommsdb..restorehistorywheredestination_database_name=your_databasegroupbydestination_database_nameINDICESDBCCDBREINDEX(HumanResources.Employee,PK_Employee_EmployeeID,80);COMO RECONSTRUIR UN INDICEsp_rename@objname=ciclista.PK_ciclista,@newname=PK_ciclistarenombrado,@objtype=INDEXCOMO RENOMBRAR UN INDICEsp_helpindexEjemploVER INDICES SOBRE LA TABLAsp_helpEjemploMOSTRAR INFORMACION SOBRE EL OBJETO-DEFRAGMENTAR INDICES EN LINEA SQLSERVER 2000DBCCINDEXDEFRAG(CS_PACC,DBO.sotbl_MultPro,PK_MultPro)-COMPROBAR NIVEL DE FRAGMENTACION SQLSERVER 2000USECS_PACCGODBCCSHOWCONTIG(DBO.sotbl_MultPro)GO Reporting services-LISTADO DE LAS SUSCRIPCIONES EXISTENTESselectSubnDesc=s.Description,ReportPath=c.Path,SubnOwner=us.UserName,LastStatus=s.LastStatus,LastRun=s.LastRunTime,ReportModifiedBy=uc.UserName,ScheduleId=rs.ScheduleId,SubscriptionId=s.SubscriptionIDfromReportServer.dbo.SubscriptionssjoinReportServer.dbo.Catalogconc.ItemID=s.Report_OIDjoinReportServer.dbo.ReportSchedulersonrs.SubscriptionID=s.SubscriptionID

joinReportServer.dbo.Usersuconuc.UserID=c.ModifiedByID

joinReportServer.dbo.Usersusonus.UserID=s.OwnerIdjoinmsdb.dbo.sysjobs jonj.name=CONVERT(nvarchar(128),rs.ScheduleId)-INFORMACION DE LAS SUSCRIPCIONES EN TIEMPO DE EJECUCINselectReport=c.Path,Subscription=s.Description,SubscriptionOwner=uo.UserName,SubscriptionModBy=um.UserName,SubscriptionModDate=s.ModifiedDate,ProcessStart=dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessStart),NotificationEntered=dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.NotificationEntered),

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

n.IsDataDriven,ProcessHeartbeat=dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessHeartbeat),n.Version,n.SubscriptionIDfromNotifications njoinSubscriptions sonn.SubscriptionID=s.SubscriptionIDjoinCatalogconc.ItemID=n.ReportIDjoinUsers uoonuo.UserID=s.OwnerIDjoinUsers umonum.UserID=s.ModifiedByID-INFORME DE LOS REPORTES QUE MAS SE UTILIZAN EN REPORTING SERVICESSELECTex.UserName,ex.Format,ex.TimeStart,cat.Name,ex.Parameters,CONVERT(nvarchar(10),ex.TimeStart,101)ASrundateFROMExecutionLogASexINNERJOINCatalogAScatONex.ReportID=cat.ItemIDORDERBYex.TimeStartDESClog de report serverSelect*fromExecutionLog3 UTILIDADES Y TRUCOSsp_spaceusedESPACIO USADO POR UN OBJETO/* importantando datos desde Excel*/GOSELECTorderId,orderDate,customerId,employeeIdINTOordersFROMOPENDATASOURCE(Microsoft.Jet.OLEDB.4.0,Data Source=C:\datos.xls;ExtendedProperties=Excel8.0)[Sheet2$]go/* importantandolos datos para la tabla [ orders ]*/

GOINSERTINTOEXPERIMENTOselectID,NOMBRE,MATERIAFROMOPENDATASOURCE(Microsoft.Jet.OLEDB.4.0,Data Source=C:\Libroimportar.xls;ExtendedProperties=Excel8.0)[IMPORTAR$]whereIDNOTIN(selectIDfromEXPERIMENTO)go-COMO RENOMBRAR SERVIDOR/INSTANCIA SQL SERVERSELECT@@SERVERNAMEsp_dropserverOLD_SERVERgosp_addserverNEW_SERVER,localgo POR ULTIMO REINICIAR SERVIDOR-HABILIDAR XP_CMDSHELLEXECsp_configureshow advanced options,1Permitirque las opciones avanzadas puedan ser cambiadas.GORECONFIGUREGOEXECsp_configurexp_cmdshell,1Permitir el uso de SP XP_CMDSHELL.GORECONFIGUREGO-Espacio en cada disco para la instancia SQL SERVEREXECmaster..xp_fixeddrives-Ver que comandos estan corriendo actualmenteselectsession_id,Textfromsys.dm_exec_requestsrcrossapplysys.dm_exec_sql_text(sql_handle)tCOMPROBAR ESTADO DE SERVICIOSexecmaster.dbo.xp_servicecontrolQUERYSTATE,MSSQLServerexecmaster.dbo.xp_servicecontrolQUERYSTATE,SQLServerAgentexecmaster.dbo.xp_servicecontrolQUERYSTATE,SQLBrowserexecmaster.dbo.xp_servicecontrolQUERYSTATE,ReportServer$CSPDBA-Ver Informacin de las transacciones abiertasselect*fromsys.dm_tran_database_transactions-Como resetear la semilla de un identityDBCCCHECKIDENT(dbo.Customers,RESEED,1);-INSERTAR RESULTADO DE UN STORED PROCEDURE EN TABLA TEMPORAL Y EVITAR NESTEDCREATETABLE#MYTEMPO(spidint,ecidint,[status]varchar(200),loginamevarchar(200),hostnamevarchar(200),blkint,dbnamevarchar(200),cmdvarchar(200))INSERTINTO#MYTEMPOSELECT*FROMOPENQUERY(LOCALSERVER,SET FMTONLY OFF EXECmsdb..sp_help_job)select*from#MYTEMPO-CREAR LINKED SERVEREXECsp_addlinkedserver@server=LOCALSERVER, @srvproduct=,

@provider=SQLOLEDB,@datasrc=@@servername-BUSCAR POR NOMBRE DE COLUMNA EN UNA BASE DE DATOSSELECTTABLE_NAME,*FROMINFORMATION_SCHEMA.COLUMNSWHERECOLUMN_NAMELIKE%a%

-BUSCAR TEXTO EN STORED PROCEDURESELECTROUTINE_NAME,ROUTINE_DEFINITION,*FROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_DEFINITIONLIKE%BranchConvenio%ANDROUTINE_TYPE=PROCEDURE