ITPub博客

在SQL Server配置工具中的MSSQLSERVER协议中开启TCPIP协议即可。[@more@]

The values of some of these SET options can be changed using several methods:Using sp_configure stored procedure (for server-wide changes) Using sp_dboption stored procedure (for database-wide changes) Using SET clause of the ALTER DATABASE statement Using the SET statement during a user session[@more@]

Byte 0 is the TagA byte of the record metadata. Bit 4 means the record has a NULL bitmap and bit 5 means the record has variable length columns. If 0x40 (bit 6) was also set, that would indicate that the record has a versioning tag. If 0x80 (bit 7) was also set, that would indicate that byte 1 has a value in it. Bits 1-3 of byte 0 give the record type. The possible values are: 0 = primary record. A data record in a heap that hasn't been forwarded or a data record at the leaf level of a clustered

得到包括未文档化的所有dbcc命令的列表：1> dbcc traceon(2588) ===与SQL Server 2000的dbcc traceon(2520)不同2> goDBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。1> dbcc help('?')2> goactivecursorsaddinstanceauditevent……查看某个命令的语法，如dbcc page：1> dbcc help('page')2> godbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])[@more@]

通过导出各个日志记录，可以确认，MinLSN不是如很多文档所说的保存在数据库的boot page中，也不是保存在checkpoint结束的日志记录中。boot page只是保存了checkpoint开始的LSN，由此LSN可以定位对应日志记录，在日志记录中可以读取MinLSN，然后开始实例恢复操作。 2010-07-20补充：执行checkpoint操作时，如果存在尚未结束的事务，则会产生操作类型为LOP_XACT_CKPT的日志记录，其中保存了执行checkpoint操作时未结束的所有事务的LSN，这些LSN中的最小者就是MinLSN，同时标记checkpoint操作开始的LSN会记入数据库启动数据页。[@more@]

今天上午有时间完成自己一直未实施的一个想法，即修改SQL Server的重做日志，看是否能让SQL Server按照自己的修改执行重做操作，结果让人失望，大致原因应该是重做日志文件日志记录会有校验和之类的机制，修改后，SQL Server不认了。不过也算了却一桩心事。大致过程如下：执行数据库全库备份。执行事务日志备份。update表t中的一个列值，如把b=‘aaaa’，改为'bbbb'。查看上述update产生的重做记录，记下其LSN号码。根据LSN号码，计算其在日志文件中的位置，使用WinHex在重做日志文件中查找对应的日志记录，并把其中的bbbb对应的十六进制数

SQL Server行版本控制实现原理SQL Server数据文件分析程序实现原理SQL Server重做日志文件物理机构SQL Server重做日志记录逻辑结构SQL Server在数据库恢复时定位重做日志记录的机制SQL Server存储空间分配及释放原理SQL Server中索引对锁机制的影响[@more@]

1> create table t(a int identity, b char(5) default 'x')2> go1> insert into t default values2> go 51> select sys.fn_PhysLocFormatter(%%physloc%%) as RID, * from t2> goRID a b---------- ----------- -----(1:21:0) 1 x(1:21:1) 2 x(1:21:2) 3 x(1:21:3) 4 x(1:21:4) 5 x与Oracle的DBMS_ROWID包中的函数功能相同，但是这个更好用。另外一个函数是sys.fn_physLocCracker ，其用法如下：1> create table t(a int)2> insert into t values(1)3> insert into t values(2)4> insert into t values(3)

Anatomy of Log Record By ekasiswanto Let’s take some random log record from Microsoft SQL Server 2000’s LDF file to be examined :You can refer to my previous post about determining the exact position of log record of interest.In this anatomy autopsy session , the focus is to determine the operation, the page id and the slot id of affected database.Offset 0×0E = 0×06 is the log operation which means (LOP_MODIFY_COLUMNS). You can refer to the complete list of log operation at the end of this

Dateime 使用8个字节存储: 前4个直接用于表示日期, 表示的方式是相对 Jan 1, 1900的天数差 后4个字节表示时间, 从半夜开始的时间,单位是 1/300 秒 [@more@]

需要sys.partitions与sys.allocation_units做连接，如查询allocation_unit_id=281474980249600所属对象名称：1> SELECT object_name(object_id) AS object_name2> FROM sys.partitions p JOIN sys.allocation_units a3> ON p.partition_id = a.container_id4> WHERE a.allocation_unit_id=281474980249600;5> goobject_name------------------------------------------------------sysidxstats[@more@]

后者多出三个列：first_page，root_page，first_iam_page其他都是相同的。[@more@]

sp_ms_marksystemobject 'sp_desc'[@more@]

DBCC is an abbreviation for Database Console Command. DBCC commands are generally used to check the physical and logical consistency of a database, although they are also used for a variety of miscellaneous tasks, as you will see here.Note, the command:DBCC TRACEON (3604)is issued before each of the following DBCC examples in order to better demonstrate the effects of the command by displaying a trace of the output of the DBCC command. It is not actually required to run the DBCC commands examine

DBCC IND has three parameters. The first parameter is the database name or the database ID. The second parameter is the object name or object ID within the database. The third parameter is a specific index ID or one of the values 0, -1, or −2. Here's the syntax:DBCC IND ({'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 }) 0Displays the page numbers for all IAMs and data pages.-1Displays the page numbers for all IAMs, data pages, and index pages.-2Displays the page number

page 0：file headerpage 1：1st PFS(page free space)page 2：1st GAMpage 3：1st SGAMpage 4及page 5：unusedpage 6：1st DCM(differential changed map)page 7：1st BCM(bulk changed map)，也称为ML mapping page上述内容恰好占掉数据文件的第一个extent。[@more@]

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354.The printopt parameter has the following meanings:0 - print just the page header 1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitm

2009年3月11日，MS Press出版了Kalen Delaney的Inside SQL Server系列的2008版本，改名为SQL Server 2008 Internals(这是学习Inside Windows的做法，Inside Windows第4版改称Windows Internals)。与其2005版本(Inside SQL Server 2005 Storage Engine，Inside SQL Server 2005 Query Tuning and Optimization)相比，更新并不多，去掉了Storage Engine第一章的安装，毕竟一本讲internal的书讲安装有点说不过去。以下是其目录：第1章：SQL Server 2008 architecture and configuration，Storage Engine的第2、3章。第2章：Change tracking，

There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.[@more@]

SQL Server 2008白皮书：http://msdn.microsoft.com/en-us/library/bb418497.aspx SQL Server 2008 Books online：http://msdn.microsoft.com/en-us/library/ms130214.aspx关于SQL Server内存管理：http://blogs.msdn.com/slavao/archive/tags/SQL+Server+Memory+Management/default.aspx[@more@]