日志分析操作全接触
发布时间：2020-12-12 15:48:13 所属栏目：MsSql教程 来源：网络整理
导读：???? --//清空日志???? checkpoint ???? ???? --//查看数据库日志???? select * from ::fn_dblog( null , null )???? select * from ::fn_dblog( null , null ) where [ current lsn]= '000001ae:00000074:0001' and [ current lsn]= '000001ae:00000085:000
|
????
--//清空日志???? checkpoint???? ???? --//查看数据库日志???? select * from ::fn_dblog( null,null)???? select * from ::fn_dblog( null,null) where [ current lsn]>= '000001ae:00000074:0001' and [ current lsn]<= '000001ae:00000085:0005'???? --//2005中常用列???? select [ current lsn],[operation],[context],[ transaction id],[allocunitname],[page id],[slot id],[rowlog contents 0] from ::fn_dblog( null,null)???? --//2000中常用列???? select [ current lsn],[object name],[ transaction name] from ::fn_dblog( null,null)???????? /*fn_dblog属性???? Current LSN,Operation,Context,Transaction ID,Log Record Length,Object Name,Index Name,Page ID,???????? Offset in Row,Num Elements,Row Data,checkpoint Begin,Server UID,UID,SPID,Beginlog Status,???????? Transaction Name,???????? */???? ???? --//查看所有日志详细信息???? declare @sql varchar(100)???? set @sql = 'dbcc log('+ db_name()+ ',3)'???? exec(@sql)???? /*???? dbcc log ('DbSrc',3,'LSN','0x0000006f:00000107:0001','XDESID','0000:0000074d','LOGRECS','LOP_INSERT_ROWS')???? 数据库名???????????????? Current LSN????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????trancation id????????????????????????????????????????????????????????????????????????Operation???? */???? ???? --//防止系统自动checkpoint???? dbcc traceon(3505)???? go???? ???? ???? --//查看数据库某个页面的内容???? dbcc traceon(3604)???? go???? dbcc page(dbName,fileID,pageID,3)???? go???? ???? --//查看数据库表的所有信息???? sp_help tablename???? ???? --//查看所有打开的开关???? DBCC TRACESTATUS (-1)???????? WITH NO_INFOMSGS???? --//也可用如下方法???? --// Create the table to accept the results???? CREATE TABLE #tracestatus (???? TraceFlag INT,???? Status INT???? )???? --// Execute the command,putting the results in the table???? INSERT INTO #tracestatus???????? EXEC ( 'DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')???? --// Display the results???? SELECT *???????? FROM #tracestatus???? GO???? ???? --//打开3604开关，只有打开该开关，才能执行dbcc page等其他相关指令???? dbcc traceon(3604)???? ???? --//打开3505开关，该开关禁止自动checkpoint，只对当前连接有效???? dbcc traceon(3505)???? ???? --//关闭3604开关???? DBCC traceoff(3604)???? ???? --//备份日志采用两种不同模式???? BACKUP LOG WITH TRUNCATE_ONLY 或 WITH NO_LOG???? ???? ???? --//查看数据库版本???? select @@version???? select???????? SERVERPROPERTY( 'productversion'),SERVERPROPERTY ( 'productlevel'),SERVERPROPERTY ( 'edition')???????? ???? --//查看用户设置???? DBCC USEROPTIONS???? ???? --//修复表???? sp_dboption '库名','SINGLE USER',TRUE???????? --//设置单用户模式???? go???? DBCC CHECKTABLE( 'b_domestic_d_sam',REPAIR_REBUILD) --//修复???? go???????? sp_dboption '库名','MULTI_USER',FALSE???????????????? go???? --//或者如下???? ALTER DATABASE 库名 SET 'SINGLE USER'???? GO???? DBCC CHECKDB( '库名',repair_allow_data_loss) WITH TABLOCK???? GO???? ALTER DATABASE 库名 SET 'MULTI_USER'???? GO???? ???? --//修复???? DBCC CHECKDB( 'dbg1',repair_allow_data_loss)???????? ???? --//查看索引信息???? select index_col( 'db1',1,1)???? ???? --//查看数据库系适时状态???? select * from master.. sysprocesses???? ???? --//获取对象id???? OBJECT_ID( 'b_domestic_d_sam')???? ???? --//设置专业连接开关???? sp_configure 'remote admin connections','1'???? --//应用设置???? reconfigure???? --//控制台连接???? osql -S192.168.2.189,1434 -Usa -P123456???? ???? --//创建索引???? create index index_test on t_Identity_1(lsn,columnname);???? ???? --//查看表所分配的所有页???? dbcc ind(库名称,表名称,-1)???? ???? --//其他常用关键字???? CASE???????????? CHARINDEX???? len???????? left???????? group by???? order by???? distinct???? when???? then???? else???? end???????? as???? is???? in???? max???? count???? begin tran???? commit tran???? rollback tran???? create table???? create database???? create index???? ???? --//常用系统表???? sysindexes???? sysobjects???? syscolumns???? sysindexkeys???? systypes???? ???? --//查询目前分析日志需要的列信息???? select name,xtype,length,xprec,xoffset,colid,bitpos,isnullable from syscolumns where id = ( select id from sysobjects where name = 'sysobjects')???? select name from syscolumns where id = object_id( 'tt') and colid???????? in( select colid from sysindexkeys where id = object_id( 'tt'))???? ???? select * from???????? syscolumns where id =????????( select id from sysobjects where xtype = 'U' and name = 'table1')???? ???? --//设置自增列可插入（一个数据库中只能有一个可插入子增列）???? SET IDENTITY_INSERT tablename on???? ???? --//设置自增列不可插入???? SET IDENTITY_INSERT tablename on???? ???? --//阻止checkpoint???? exec sp_dboption 'wdf100','trunc. log on chkpt.','off'???? ???? --//所有系统表???? select * from dtproperties???? select * from syscolumns???? select * from syscomments???? select * from sysdepends???? select * from sysfilegroups???? select * from sysfiles???? select * from sysfiles1???? select * from sysforeignkeys???? select * from sysfulltextcatalogs???? select * from sysfulltextnotify???? select * from sysindexes???? select * from sysindexkeys???? select * from sysmembers???? select * from sysobjects???? select * from syspermissions???? select * from sysproperties???? select * from sysprotects???? select * from sysreferences???? select * from systypes???? select * from sysusers???? ???? ???? --//设置大对象存放在rowdata中???? EXEC sp_dboption 'zhu','select into/bulkcopy','true'???? EXEC sp_dboption 'zhu','false'???? ???? --//特殊操作???? select * into 目标表名称(自动创建该表) from tablename???? insert into 目标表名称（表已经存在） select * from tablename???? --//从一个库中将数据导入到另外一个库中???? INSERT INTO [a] ([a1],[a2]) SELECT a1,5 FROM OPENROWSET( 'SQLOLEDB','192.168.2.237'; 'sa'; 'sa','SELECT a1,a2 FROM [db2].dbo.[a]');???? ???? --//查询所有聚集索引上的列信息???? select o. name,i.id,i.keyno,i.colid,c. name from sysindexkeys i left join syscolumns c on i.id = c.id???????? and i.colid = c.colid???????? left join sysobjects o on i.id = o.id???????? where i.indid = 1 and????????o.xtype = 'U' and o.status > 0???????? ???? ???? --//获取所有表信息???? exec sp_tables???? select * from information_schema.tables???? ???? --//获取所有库信息???? exec sp_databases???? select * from INFORMATION_SCHEMA.SCHEMATA???? ???? --//获取列的所有信息???? exec sp_columns @table_name = 't1'???? ???? --//常用的与元数据有关的系统存储过程有以下一些：???? --//系统存储过程 描述???? sp_columns 返回指定表或视图的列的详细信息。???? sp_databases 返回当前服务器上的所有数据库的基本信息。???? sp_fkeys 若参数为带有主键的表，则返回包含指向该表的外键的所有表；若参数为带有外键的表名，则返回所有同过主键/外键关系与该外键相关联的所有表。???? sp_pkeys 返回指定表的主键信息。???? sp_server_info 返回当前服务器的各种特性及其对应取值。???? sp_sproc_columns 返回指定存储过程的的输入、输出参数的信息。???? sp_statistics 返回指定的表或索引视图上的所有索引以及统计的信息。???? sp_stored_procedures 返回当前数据库的存储过程列表，包含系统存储过程。???? sp_tables 返回当前数据库的所有表和视图，包含系统表。???? ???? --//常用的与元数据有关的系统函数有以下一些：???? --//系统函数 描述???? COLUMNPROPERTY 返回有关列或过程参数的信息，如是否允许空值，是否为计算列等。???? COL_LENGTH 返回指定数据库的指定属性值，如是否处于只读模式等。???? DATABASEPROPERTYEX 返回指定数据库的指定选项或属性的当前设置，如数据库的状态、恢复模型等。???? OBJECT_ID 返回指定数据库对象名的标识号???? OBJECT_NAME 返回指定数据库对象标识号的对象名。???? OBJECTPROPERTY 返回指定数据库对象标识号的有关信息，如是否为表，是否为约束等。???? fn_listextendedproperty 返回数据库对象的扩展属性值，如对象描述、格式规则、输入掩码等。???? ???? ???? --//信息架构视图 描述???? INFORMATION_SCHEMA.CHECK_CONSTRAINTS???????? 返回有关列或过程参数的信息，如是否允许空值，是否为计算列等。???? INFORMATION_SCHEMA.COLUMNS???????? 返回当前数据库中当前用户可以访问的所有列及其基本信息。???? INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE???????? 返回当前数据库中定义了约束的所有列及其约束名。???? INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE???????? 返回当前数据库中定义了约束的所有表及其约束名。???? INFORMATION_SCHEMA.KEY_COLUMN_USAGE???????? 返回当前数据库中作为主键/外键约束的所有列。???? INFORMATION_SCHEMA.SCHEMATA???????? 返回当前用户具有权限的所有数据库及其基本信息。???? INFORMATION_SCHEMA.TABLES???????? 返回当前用户具有权限的当前数据库中的所有表或者视图及其基本信息。???? INFORMATION_SCHEMA.VIEWS???????? 返回当前数据库中的当前用户可以访问的视图及其所有者、定义等信息。???? ???? ???? --//系统表 描述???? syscolumns 存储每个表和视图中的每一列的信息以及存储过程中的每个参数的信息。???? syscomments 存储包含每个视图、规则、默认值、触发器、 CHECK 约束、 DEFAULT 约束和存储过程的原始 SQL 文本语句。???? sysconstraints 存储当前数据库中每一个约束的基本信息。???? sysdatabases 存储当前服务器上每一个数据库的基本信息。???? sysindexes 存储当前数据库中的每个索引的信息。???? sysobjects 存储数据库内的每个对象（约束、默认值、日志、规则、存储过程等）的基本信息。???? sysreferences 存储所有包括 FOREIGN KEY 约束的列。???? systypes 存储系统提供的每种数据类型和用户定义数据类型的详细信息。???? ???? --//修改用户登录关系???? sp_change_users_login 'Update_One','cb888','cb888'???? ???? --//修改对象的所有者???? sp_changeobjectowner [ @objname = ] 'object',[ @newowner = ] 'owner'???? ???? --//更改当前数据库的所有者。???????????????? sp_changedbowner???????? [???????? @loginame???????? =???????? ]???????? 'login'???????????????? [????????,???????? [???????? @map???????? =???????? ]???????? remap_alias_flag???????? ]???????????????? ???? ???? --//禁用所有约束???? alter table tablename nocheck CONSTRAINT all???? ???? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |