Monday, February 28, 2011

Some system functions we can play with


Don't know much details but seems simple functions, worth exploring

Ghost clean up

Excellent post by Paul S Randall.
When a record is deleted from the table, SQL Server marks it as Ghost record which will be cleaned up when the background ghost clean job runs every 5 sec.

Thanks to Paul, he has just accepted my request to refer his post, Here is the link for his post.

Unusual script which updates all the system objects

I was goggling and found this bizarre script, this seems its dropping all the system objects and recreating it.
'll investigate on this more and 'll post updates later.
This war written somewhere before sql server 2000, my guess is that someone might've just generated scripts only for system objects.

Check this here:

Sunday, February 27, 2011

Read Transaction log using fn_dblog()

Use this if you want to read the transaction log

Its easy and simple

select * from fn_dblog(null,null) gives you the data
query it with partition numbers gives you the complete details.

select partition_id from sys.partitions where object_id=3435334

select * from fn_dblog(null,null) where partition_id= 343457576523
Now you can see the page ids, transaction types, etc.
Page ids you can get from
dbcc ind and dbcc page


select * from fn_dblog(null,null) where allocunitid in
 select allocation_unit_id from sys.allocation_units where container_id in
 (select partition_id from sys.partitions where object_id=1555184986)

dbcc ind(5,1396968103,9)

dbcc page(5,1,7905811,3)

Finally we got the individual row and key values after the DBCC page,
this wya we can find out what are the rows existing in each page and can now easily find out which pages are modified/updated/deleted in the Tlog.

Friday, February 25, 2011

Issues in SQL Server 2005 installation

Here are some:

1) distmdl.mdf and dismdl.ldf are database files for distribution database used when we configure replication, donot delete them otherwise you wn't be able to install new service packs

2) you can move the system databases to any other drive very easily like this:

a) for tempdb, execute the below statements and then restart sql, that's it

alter database tempdb
modify file (name=tempdev,filename='f:\data\tempdb.mdf')

alter database tempdb
modify file (name=templog,filename='f:\data\tempdb_log.ldf')

 b) for msdb, model
execute the above alter db statements then stop sql and move the files to the new location and then start it that's it done

c) for master
stop sql server and copy the mdf,ldf files to the new location
In the startup parameters give the new paths for "-d" and "-l" options as for data and log files.
then start sql service, that's it

d) for resourcedb

stop sql server and copy the mdf,ldf files to the new location

start sql server with limited options like this in command prompt
"net start mssqlserver /f /T3608

Trace flag 3608 prevents SQL Server from recovering any database except the master database

then open query analyzer as only one connection is allowed and then execute the alter db statements

restart sql server and thta's it

Its actually very simple doing in 2005 where as in 2000 we have to start sql server in single user mode with options "-c" and "-m" and then detach and attach msdb,model dbs.

Issues in SQL Server 2005 Failover cluster installation

Recently I've been into building new infrastructure where I have to build sql server 2005 daily and change the configurations every now and then, so had a chance to look at each minute details/errors etc.

Here are some issues I faced and the solutions I found:

1) We can rename the cluster service account even after installation but need to take care of the following:

a) Make sure the new service account is part of system administrators group on both the nodes
b) Go to here: run "gpedit.msc" -> windows settings-> security settings -> local policies->user rights assignment
c) check your cluster service account is part of most of those listed there like
 logon as a service
logon as a batch
create a pagefile
create global objects etc.

2) When AWE is enabled, make sure sql service account is added to "lock pages in memory" on both the nodes, adding it to only one node results in issues while failing over to the next node

3) When configuring MSDTC as a seperate group in failover cluster
a) configure MSDTC IP first with no dependencies
b) Then MSDTC network name with ip as dependency
c) Then MSDTC disk with no dependency
d) MSDTC with all the above as dependencies

Wednesday, February 9, 2011


One of the best DMVs I've seen in SQL Server 2008

This will give you all the information about the pages in your database which are in buffer.

This is indeed an excellent way to find out the pages of your tables you are querying frequently are in buffer or not.

I just love this DMV and 'll start using it for my troubleshooting

more info at here:

Output will be something like this:

database_id    file_id    page_id    page_level    allocation_unit_id    page_type    row_count    free_space_in_bytes    is_modified
5    1    13200546    0    72057612741115904    DATA_PAGE    18    680    0
5    1    13200547    0    72057612741115904    DATA_PAGE    18    680    0
5    1    13200548    0    72057612741115904    DATA_PAGE    18    680    0

The below query will give you the table names and the data pages which are in buffer for each database, this excludes objects from system databases.

select db_name(d.database_id),d.page_type,d.row_count,object_name(p.object_id),p.rows,p.index_id
from sys.dm_os_buffer_descriptors d
inner join sys.allocation_units  a
on a.allocation_unit_id=d.allocation_unit_id
inner join sys.partitions p
on p.partition_id=a.container_id
where d.database_id not in(1,2,3,4) and db_name(d.database_id) is not null

Metadata functions in sql server

DatabasePropertyex('dbname','property') gives info about that database 
property can be like 'version','collation','status','recovery' etc
Check MSDN for all properties here:

OBJECTPROPERTY ( id , property ) 
Property is like 'istable','isview','isprocedure' etc.
if true then output will be 1 else it is 0
More info at here:

3) Other easy functions are like
db_id, db_name, filegroup_id,filegroup_name,file_name,file_id,type_id,index_col

4) indexproperty(tablename,indexname,'property')
property can be like "fillfactor",'isclustered","indexdepth"

Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider.

Returns the names of shared drives used by the clustered server
we can also use sys.dm_io_cluster_shared_drives instead of the above.

fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } )
Returns I/O statistics for database files, including log files. 
we can also sys.dm_io_virtual_file_stats

DBCC Page, Ind, DBINFO,DatabasePropertyex


dbcc ind('dbname','tablename',<indexid>)
Output will be something like this:

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
1 2865665 NULL NULL 276964113 2 1 72057612875333600 In-row data 10 NULL 1 2872682 0 0
1 2676373 1 2865665 276964113 2 1 72057612875333600 In-row data 2 1 1 17518182 1 17518181

DBCC Page(<dbid or name>,fileid,pageno,printoptions)

Printoptions can be any of the below
  • 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 bitmaps)
  • 2 - page header plus whole page hex dump
  • 3 - page header plus detailed per-row interpretation

dbcc page(5,1,2865665,3)

PAGE: (1:2865665)


BUF @0x0000000122FCBE00

bpage = 0x00000001222F8000           bhash = 0x0000000000000000           bpageno = (1:2865665)
bdbid = 5                            breferences = 0                      bUse1 = 39810
bstat = 0x2c00009                    blog = 0x159a2159                    bnext = 0x0000000000000000


Page @0x00000001222F8000

m_pageId = (1:2865665)               m_headerVersion = 1                  m_type = 10
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 285393 m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057612741443584                                
Metadata: PartitionId = 72057612875333632                                 Metadata: IndexId = 2
Metadata: ObjectId = 276964113       m_prevPage = (0:0)                   m_nextPage = (1:2872682)
pminlen = 90                         m_slotCnt = 2                        m_freeCnt = 6
m_freeData = 8182                    m_reservedCnt = 0                    m_lsn = (3178619:17664:62)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 1                      

Allocation Status

GAM (1:2556160) = ALLOCATED          SGAM (1:2556161) = NOT ALLOCATED    
PFS (1:2863152) = 0x70 IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL            DIFF (1:2556166) = CHANGED
ML (1:2556167) = NOT MIN_LOGGED     

IAM: Header @0x0000000010EFC064 Slot 0, Offset 96

sequenceNumber = 0                   status = 0x0                         objectId = 0
indexId = 0                          page_count = 0                       start_pg = (1:2556160)

IAM: Single Page Allocations @0x0000000010EFC08E

Slot 0 = (1:2676373)                 Slot 1 = (1:2676423)                 Slot 2 = (1:2728190)
Slot 3 = (1:3036608)                 Slot 4 = (1:2875265)                 Slot 5 = (1:2875537)
Slot 6 = (1:2912256)                 Slot 7 = (1:2941226)                

IAM: Extent Alloc Status Slot 1 @0x0000000010EFC0C2

(1:2556160)  - (1:3067384)  = NOT ALLOCATED  

select databasepropertyex('dbname','version')

DBINFO('dbname') with tableresults 
gives u the output something like this:

ParentObject Object Field VALUE
DBINFO STRUCTURE: DBINFO @0x0000000012C9EAA0 dbi_dbid 5
DBINFO STRUCTURE: DBINFO @0x0000000012C9EAA0 dbi_status 4194328
DBINFO STRUCTURE: DBINFO @0x0000000012C9EAA0 dbi_nextid 1621281231
DBINFO STRUCTURE: DBINFO @0x0000000012C9EAA0 dbi_maxDbTimestamp 62568600
DBINFO STRUCTURE: DBINFO @0x0000000012C9EAA0 dbi_version 611

Tuesday, February 8, 2011

How to get index and table sizes in a database

select * from sys.partitions where object_id=276964113

select * from sys.allocation_units where container_id in
(select partition_id from sys.partitions where object_id=276964113)

p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
i.[object_id] = 276964113
order by

Tuesday, February 1, 2011

Statistics last updated date

To find out the last updated date of all statistics, you can use the below query:

select name,stats_date(object_id, index_id) from sys.indexes