T-SQL: SERVERPROPERTY built-in functon
- Details
- Written by ranjeet
SERVERPROPERTY built-in functions is very useful to get details about server in one line. Personally I always use this function. We can get almost all the property’s value by using this function but I am going to list limited one which is actually being used in day to day activities. Below is the list of property which I usually execute. You can refer MSDN to get full list.
-->> SERVERPROPERTY
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')as ActivePhysicalNodeName
SELECT SERVERPROPERTY('MachineName')as MachineNameOrClusterName
T-SQL: How to execute same query multiple times in SQL Server
- Details
- Written by ranjeet
How to execute same query multiple times. It is very interesting one came in my mind. I got the easy way to execute it. Let first list me out all the possible ways to execute same query multiple times say 5 times.
-
First way which come in mind that use LOOP. Either FOR or WHILE doesn't matter.
-
In SSMS copy the same command 5 times and execute. Doesn’t looks good.
-
Create a stored procedure and call the SPs for 5 times.
-
Just put 5 after end of statement “GO”
DATAFILE: How to get datafile logfile size
- Details
- Written by ranjeet
Yesterday, I asked to get the database size of all the databases starts with “XXXX_” name. I queried sys.master_files system table and sent the results. Since sys.master_files system can query all the databases, I chosen this system table. When today I was trying to get the same result using other system table like sys.database_files and sys.sysfiles I saw results were different for few databases.
Below is the query I used to send the result.
select db_name(database_id) as DBName, name as FileName,(size*8)/1024 as TotalSizeMB
,(FILEPROPERTY(smf.name,'SpaceUsed')*8)/1024 as UsedSpaceMB, 72074 -(FILEPROPERTY(smf.name,'SpaceUsed')*8)/1024 as FreeSpceMB
from sys.master_files smf
where db_name(database_id)like 'XXXX_%'
It means sys.master_files table is not always giving accurate result to us. I browsed different article on web and get similar response. See one of them from Microsoft.
Then I used below query to send the same result.
Query to get database datafile and log space details using sys.database_files
select db_name()as DBName, name as [FileName],(size*8)/1024 as TotalSpaceMB,(FILEPROPERTY(sdf.name,'SpaceUsed')*8)/1024 as UsedSpaceMB,(size*8)/1024 -(FILEPROPERTY(sdf.name,'SpaceUsed')*8)/1024 as FreeSpceMB
from sys.database_files sdf
Query to get database datafile and log space details using sys.sysfiles
select db_name()as DBName, name as [FileName],(size*8)/1024 as TotalSpaceMB,(FILEPROPERTY(sdf.name,'SpaceUsed')*8)/1024 as UsedSpaceMB,(size*8)/1024 -(FILEPROPERTY(sdf.name,'SpaceUsed')*8)/1024 as FreeSpceMB
from sys.sysfiles sdf
Query to get database datafile and log file space details using combination of sysaltfiles and sys.databases
select db_name(slf.dbid) as DBName, case when groupid =1 then 'DataFile' else 'LogFile' end as DataLogFile, sum(size*8/1024) as TotalSpace from sysaltfiles slf join sys.databases d on d.name=db_name(slf.dbid) group by db_name(slf.dbid), case when groupid =1 then 'DataFile' else 'LogFile' end order by db_name(slf.dbid)
ARCHITECTURE: What is Index Allocation Map IAM page type
- Details
- Written by ranjeet
I am really not able to understand from where I should start to explain IAM. Let’s start with table. One table can have one or more partitions and each partitions can have all three types of allocation unit. And as I explain in my other article that whenever we create any index it actually created one partition. So, to just understand we can say partition is an index since it represent one index.
Now come to IAM page type. IAM page type actually store about the extent allocation of particular allocation map. So, we can say using IAM we track/manage all the extents which comes under that particular allocation unit. One allocation unit can have multiple IAM page depending upon the number of file it use.
ARCHITECTURE: What is DCM and BCM page type
- Details
- Written by ranjeet
DCM (Differential Changed map): People usually have the question that with the same database how differential and log backup is different. How database engine take backup of only those extent which has changed since last full backup. Here is the answer with this page type.
It is a page which keep records of all the extents which has changed since last full database backup.
When SQL server takes a differential backup, database engine reads the DCM page and take the backup of only those pages which has been changed since last full backup which is also called base backup.
ARCHITECTURE: What is Page Free Space PFS page type
- Details
- Written by ranjeet
GAM and SGAM record the allocation of extent where PFS record
-- The allocation of each page and
-- The amount of free space available in each page.
Like of GAM and SGAM, PFS also store bit value for each page. 1 is for free and 0 is for used.
Once any extent get allocated to an object, database engine records which pages are allocated/used or free/available.
Data page needs to be tracked in PFS but Index page do not need to be tracked. In a datafile, below is the hierarchy of the pages from top to bottom. First four pages of a data file is as below:
-
Page header
-
PFS
-
GAM
-
SGAM
ARCHITECTURE: What is relation between a table a partition and allocation unit
- Details
- Written by ranjeet
Table is a database object which store the data as record. It is having columns and rows. When we talk about database internal. We can derive table as two types
-
Clustered Table
-
Heap Table
It is very important to understand Clustered and Heap table to understand database internals. Clustered table is a database object on which is having clustered index it. Whereas heap table is a database object which has no clustered index on it.
TABLE: How to check table is clustered or a Heap
- Details
- Written by ranjeet
The First question come in mind is where to check. By querying sys.partitions system table, we can check whether table is clustered or heap. There is column index_id in this table. Value 0 is for heap and 1 is for Clustered. Below is the query:
select case when index_id = 1 then 'Clustered Table' else 'Heap Table' end as' HeapOrClustered', object_name(object_id) as tablename
from sys.partitions
where object_name(object_id)='testp' and index_id in(0,1)
FCI: Cannot use file X:\XXXX\xxxxx.mdf for clustered server
- Details
- Written by ranjeet
One of application was trying to create a database on our SQL server instance. They came with below error. This error usually come if database you are creating is using some drive which is not added in dependency list in Cluster SQL service. Below is the error they shared:
Cannot use file 'X:\DATA\xxxxx.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Just after seeing this error I understood the problem since I had same experience in past while working for other projects.
SCOM installation Setup is unable to create DB on SQL server instance SCOM 2012
- Details
- Written by ranjeet
Today, I asked to help SCOM team to setup SCOM 2012. They were facing below error:
Setup is unable to create DB on SQL server instance "XXXXXX". Please make sure that the current user has permissions to create database on the SQL server instance specified."
In first sight, it looks like account which is installing SCOM 2012 doesn’t have the permission to create database in SQL Server. I checked thoroughly about the permission but didn’t see any problem with that. I asked them to check their setting. Let me step out what we did: