Find highest latency in transactional replication in SQL Server 2012

Find highest latency in transactional replication in SQL Server 2012

Replication is one of the very handy and useful high availability features SQL Server has provided. It is very easy to setup but maintaining health and keeping eye on performance of replication is very crucial and mandatory.

We can find “MSTracer_History”, “MSDistribution_Agents” and “MSTracer_Tokens” tables into distribution database of replication and hence the query I am going to give you, would be used under distribution database only.

Let me give you brief introduction, from MSDN, of all three system tables of distribution database.

MSTracer_History: The MStracer_history table maintains a record of all tracer tokens that have been received at the Subscriber. This table is stored in the distribution database and is used by replication for performance monitoring.

MSDistribution_Agents: The MSdistribution_agents table has one row for each Distribution Agent running at the local Distributor. This table is stored in the distribution database.

MSTracer_Tokens:  The MStracer_tokens table maintains a record of tracer token records inserted into a publication. This table is stored in the distribution database and is used by replication for performance monitoring.

Now here is the query I use often to check the latency of replication.

[sourcecode language=”sql”]
SELECT
                SysServ.name AS SubscriberName,
                MSDA.subscriber_db AS SubscriberDatabase,
                MSDA.publication AS PublicationName,
                MAX(DATEDIFF(SS,publisher_commit,distributor_commit)) AS LatencyPublisherToDistributor,
MAX(DATEDIFF(SS, distributor_commit,subscriber_commit)) AS LatencyDistributorToSubscriber,
MAX(DATEDIFF(SS,publisher_commit,distributor_commit)
                + DATEDIFF(SS, distributor_commit,subscriber_commit)) AS TotalLatency
FROM
                MStracer_history MSTH
INNER JOIN
                MSdistribution_agents MSDA
ON
                MSTH.agent_id = MSDA.id
INNER JOIN
                sys.servers SysServ
ON
                MSDA.subscriber_id = SysServ.server_id
INNER JOIN
                MStracer_tokens
ON
                MSTH.parent_Tracer_id=MStracer_tokens.tracer_id
WHERE
                subscriber_commit >DATEADD(hh,-1,GETDATE())
GROUP BY
                SysServ.name,
                MSDA.subscriber_db,
                MSDA.publication
GO[/sourcecode]

You can modify above given query for your customize need. I have filtered (in WHERE clause) records for last one hour so it will give you highest latency of last one hour. You can even filter records on TotalLatency field, If you want to see resultset only if it cross certain seconds/minutes while transferring records from publisher to subscriber.

Do let me know what technique do you use to keep your eye on replication health?

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Generate script to create and drop Primary Key and Foreign Key in SQL Server

Generate script to create and drop Primary Key and Foreign Key in SQL Server

Primary Key and Foreign key is two main pillars to enforce referential integrity in relational database management system.

I had requirement to drop few tables which has Primary Key and that Primary Key has been used as a reference (Foreign Key) in few other table. After deleting table, I had to create that table again in our beta database, create same Primary Key and Foreign Key. Manually generating script to create/drop foreign key script for few tables is little tedious task and there is a chance that we miss some of the referenced table so I finally decided to quickly write down a script which can give me create and drop statement of primary key as well as all related foreign key once I pass the table name of primary key in filter area (WHERE clause).

I have used following system views/tables to cater the need of this article:

  • INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  • Sys.Foreign_Keys

Here is the script I have generated with the help of above given system views and ran it in “AdventureWorks2012” database for “Department” table. You can change database name as well as table name as per your requirement.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO
SELECT
*
,’ALTER TABLE [‘+ FKTableSchema +’].[‘ + FKTableName + ‘] DROP CONSTRAINT ‘ + ForeignKeyName AS FKDrop
,’ALTER TABLE [‘+ FKTableSchema +’].[‘ +FKTableName + ‘] ADD CONSTRAINT ‘ + ForeignKeyName + ‘ FOREIGN KEY(‘ +
FKColumnList +’) REFERENCES [‘+ PKTableSchema+’].[‘+ PKTableName + ‘] (‘+PKColumnList +’)’ AS FKCreate
,’ALTER TABLE [‘+ PKTableSchema +’].[‘ +PKTableName + ‘] DROP CONSTRAINT ‘ + PrimaryKeyName AS PKDrop
,’ALTER TABLE [‘+PKTableSchema +’].[‘ +PKTableName + ‘] ADD CONSTRAINT ‘ + PrimaryKeyName + ‘ PRIMARY KEY(‘+PKColumnList+’)’ AS PKCreate
FROM
(
SELECT
DISTINCT
FKTableSchema=(SELECT DISTINCT table_schema FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE constraint_name=rc.constraint_Name),
FKTableName=(SELECT DISTINCT table_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE constraint_name=rc.constraint_Name),
rc.constraint_name AS ForeignKeyName,
FKColumnList=(SELECT left(t.column_name,len(t.column_name)-1) AS ‘ColumnList’ FROM
(
SELECT Column_Name + ‘,’ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name=rc.constraint_name
FOR XML PATH(”)
) AS t(column_Name)),
cu.table_schema AS PKTableSchema,
cu.table_name AS PKTableName,
cu.constraint_Name AS PrimaryKeyName,
PKColumnList=(SELECT left(t.column_name,len(t.column_name)-1) AS ‘ColumnList’ FROM
(
SELECT Column_Name + ‘,’ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name=cu.constraint_name
for xml path(”)
) AS t(column_Name))
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
ON
rc.Unique_Constraint_name= cu.Constraint_name
WHERE
table_name=’department’ –and table_schema=’sales’
) AS tab

GO[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Delete Article from Transactional Replication in SQL Server 2012

Delete Article from Transactional Replication in SQL Server 2012

I have already demonstrated how to established snapshot replication as well as transactional replication in SQL Server 2012. Apart from that we have already seen how to remove replication and how to add new article (database objects like table, views, functions, stored procedures etc.) in transactional replication along with few other tips and tricks of replication. Now it is time to remove some of the unwanted articles from transactional replication. I will use the same transactional replication which I have created in my earlier article.

Removing article from replication and removing objects from replicated (subscription) database is two different things. Replication will stop replicating the changes in schema/data from publisher to subscriber once you remove article from replication but it will not remove schema/data from the subscriber database, you have to do it manually after removing object from replication.

Let us now proceed towards removing article from transactional replication.

1.)  open the replication option under your SSMS and move to the publication, right click on publication and click on “Properties” from the popup menu.

2.) Publication Properties dialog box, go to “Articles” option from the menu in left hand side and deselect the checkbox of article you want to remove from replication. I have remove “Sales.Customer” table and click on “Ok” button.

3.) Once you will click on OK button from Publication Properties dialog box above, replication will stop populating new data change to the “Sales.Customer” table but now I want to update my snapshot of replication so right click on “Replication” option and click on “Launch Replication Monitor” option from the popup menu.

4.) From the replication monitor, you can find “Agent” tab under your publication, you have to right click on “Snapshot Agent” and click on “Start Agent” from popup menu. This action will update snapshot which takes time based on the size of database.

All these four steps will successfully remove articles from replication now if you wish; you can manually go to subscriber database and drop objects which you have removed from replication.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Add new article in transactional replication in SQL Server 2012

Add new article in transactional replication in SQL Server 2012

Creating replication is one thing and maintaining it is a different yet equally important thing. Over the time, you might add or remove some objects in your primary database and hope the same for your replication database and this article comes into the picture when you need to add some more object from primary (Publication) database to replicated (subscriber) database.

I have already written few articles on replications which are listed below:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)

I will use the Publisher and Subscriber of Transaction Replication to demonstrate this article. You can create the same replication from here.

If you already having your replication ready to add article (Table, Stored Procedure, View, Index View, Functions etc.), follow the steps given below.

 

1.)  Connect to your publisher server and right click on publication name and click on “Property” from pop up menu.

 

2.) You will get Publication property dialog box and you have to move to “Article” page from the left hand menu to get a list of articles available to publish in publication database. Select the object you want to replication. Make sure the database table you select is having the primary key. Without primary key, table won’t be able to be a part of transaction replication.

 

 

3.) Once you select object and click on “Ok” button in above given dialog box,  right click on “Replication” option in objection explorer of SSMS and click on “Launch Replication Monitor”.

 

 

4.) You will see “Agent” tab in Replication Monitor dialog box, right click on “Snapshot Agent” and click on “Start Agent” option; it takes little time, based on the size of object and data, to complete the snapshot agent.

As soon as Snapshot Agent builds completely, you can check your subscriber database, you will find the entire selected object in subscriber database.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Setup Transaction Replication in SQL Server 2012

Setup Transaction Replication in SQL Server 2012

Before you move ahead for setting up Transaction Replication, You have to understand few definitions which are heavily used in replication, for example Publisher, Distributor and subscriber etc. You can get an idea of all these terminology from my earlier article “Understanding replication with Implementation of Snapshot Replication in SQL Server 2012”.

Transaction Replication: Transaction Replication is used when DML or DDL schema changes performed on an object of one database on one SQL Server Instance/ server needs to be replicated immediately on the database residing on another SQL Server Instance/server.

Change in Schema as well as in data will be reflected in replicated database immediately but this is depends on the network/internet setup you have between both SQL Server Instance/Server.

You can select any number of articles (object) as a candidate of replication no matter whether it is function, stored procedure or table. Just keep one thing in mind that any table you choose as a candidate of transaction replication, supposed to have Primary Key. Table without primary key will not be a part of transaction replication.

You can select two different SQL Server installation located in two different Server may or may not be in same premises but in same network. I have had two different instance of SQL Server 2012 in one server while writing this article.

Instance 1: “WIN-9H6QATRKY81\SQL2K12DEV”, I will call this instance as ServerA in this article.

Instance 2: “WIN-9H6QATRKY81\SQL2K12DEVTest”, I will call this instance as ServerB in this article.

If you are ready with AdventureWorks2012 database or any other database of your choice in one server and have another server available for subscriber, let us start creating replication.

1.) Create Distributor by login to ServerB (Subscriber) as I want to create Distributor in Subscriber server. If budget permits, it is good to have third dedicated server for distributor.

2.) Select or add the server where you wanted to create distributor database, since we are already logged into to ServerB, we can directly select first radio button from following screen capture.

3.) If your SQL Server Agent not set to start automatically at startup, it will ask you to start it as SQL Server Agent is responsible to run the job which will transmit data.

4.) Give shared network path where replication can create snapshot and use that location as a primary source for data. Note that both or all three server should have proper access to this shared folder as we are going to save all script file along with distributor database in this path.

5.) Give path where SQL Server can create “Distributor” database which is responsible to receive data from publisher and send it to subscriber.

6.) Select Publisher server for distribution database. By default it has selected ServerB but we want ServerA to be the publisher so add ServerA instance here.

7.) login to publisher database and it will be selected.

8.) Now deselect ServerB and Select ServerA where we have just logged in from above screen.

9.) I have provided password for my SA login password so that distributor can use this password while connecting to publisher. You can use any other login with sufficient permission.

10.) Select “Configure Distribution” checkbox along with “Generate Script” check box

11.) Give the path for saving a script file. I have used the same shared path I have used before.

Here is the script I have received after clicking on the “Finish” button from above screen.

[sourcecode language=”sql”]

/****** Scripting replication configuration. Script Date: 11/25/2012 3:49:05 PM ******/

/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 11/25/2012 3:49:05 PM ******/

use master

exec sp_adddistributor @distributor = N’WIN-9H6QATRKY81\SQL2K12DEVTEST’, @password = N”

GO

exec sp_adddistributiondb @database = N’distribution’, @data_folder = N’E:\ReplicationShare’, @log_folder = N’E:\ReplicationShare’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))

EXEC sp_updateextendedproperty N’SnapshotFolder’, N’E:\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

else

EXEC sp_addextendedproperty N’SnapshotFolder’, N’E:\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

GO

exec sp_adddistpublisher @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distribution_db = N’distribution’, @security_mode = 0, @login = N’sa’, @password = N”, @working_directory = N’E:\ReplicationShare’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’

GO

[/sourcecode]

12.)  Now manually confirm that you have received “Distributor” database under “System Databases” in the ServerB or not.

13.) Go to ServerA, this is the time to create publication now.

14.) Select ServerA as a pubcation.

15.) Give the same shared path to the replication to save and keep snapshot of publication.

16.) Select the database you wanted to replicate. I have selected “AdventureWorks2012” from ServerA.

17.) Select “Transactional Replication” from following screen.

18.) Select Table, SP, Functions etc. which you want to replicate. Confirm the table you select for replication, supposed to have primary key. Select other supported objects of table too. For example you have one computed column populated from UDF in your table so you have to select that function along with table.

19.) Click on next from following screen as I don’t want to specify any criteria for data. I want to transfer all data from my table.

20.) Select checkbox for “Create a snapshot immediately”.

21.)Click on “Security Settings” from following screen shot.

22.) provide sufficient credential here so that replication works smooth without any security issues.

23.) Once you provide security credential and click on “Ok” button, you will get screen something like this:

24.) select both check box from following screen.

25.) give proper path to save script for your publication.

Here is the script generated for publication:

[sourcecode language=”sql”]

/****** Scripting replication configuration. Script Date: 11/25/2012 4:01:52 PM ******/

/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 11/25/2012 4:01:52 PM ******/

use master

exec sp_adddistributor @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @password = N”

GO

exec sp_adddistributiondb @database = N’distribution’, @data_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data’, @log_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))

EXEC sp_updateextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

else

EXEC sp_addextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

GO

exec sp_adddistpublisher @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distribution_db = N’distribution’, @security_mode = 0, @login = N’sa’, @password = N”, @working_directory = N’\\win-9H6QATRKY81\ReplicationShare’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’

GO

use [AdventureWorks2012]

exec sp_replicationdboption @dbname = N’AdventureWorks2012′, @optname = N’publish’, @value = N’true’

GO

use [AdventureWorks2012]

exec [AdventureWorks2012].sys.sp_addlogreader_agent @job_login = N’win-9H6QATRKY81\Administrator’, @job_password = null, @publisher_security_mode = 0, @publisher_login = N’sa’, @publisher_password = N’ ‘, @job_name = null

GO

— Adding the transactional publication

use [AdventureWorks2012]

exec sp_addpublication @publication = N’AdventureWorks2012′, @description = N’Transactional publication of database ”AdventureWorks2012” from Publisher ”WIN-9H6QATRKY81\SQL2K12DEV”.’, @sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, @allow_pull = N’true’, @allow_anonymous = N’true’, @enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’, @compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’, @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’, @repl_freq = N’continuous’, @status = N’active’, @independent_agent = N’true’, @immediate_sync = N’true’, @allow_sync_tran = N’false’, @autogen_sync_procs = N’false’, @allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1, @allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’, @enabled_for_het_sub = N’false’

GO

exec sp_addpublication_snapshot @publication = N’AdventureWorks2012′, @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N’win-9H6QATRKY81\Administrator’, @job_password = null, @publisher_security_mode = 0, @publisher_login = N’sa’, @publisher_password = N”

use [AdventureWorks2012]

exec sp_addarticle @publication = N’AdventureWorks2012′, @article = N’Customer’, @source_owner = N’Sales’, @source_object = N’Customer’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’, @destination_table = N’Customer’, @destination_owner = N’Sales’, @vertical_partition = N’false’, @ins_cmd = N’CALL sp_MSins_SalesCustomer’, @del_cmd = N’CALL sp_MSdel_SalesCustomer’, @upd_cmd = N’SCALL sp_MSupd_SalesCustomer’

GO

[/sourcecode]

26.) Provide the name for your publisher. I have provided “AdventureWorks2012”

27.)  now connect to ServerB and add “New Subscription”

28.) Subscriber needs publisher to get data so select out publisher we have created.

29.) our publication name was “AdventureWorks2012” which I have selected from following screen:

30.) select “Pull Subscription” as I want to run all replication related job in ServerB and pull the data from ServerA. In Push method, replication related job runs from my ServerA which is primary server for my application and I doesn’t want to add overhead of replication job.

31.) If you already have Subscription database then select it otherwise create a new one. I have created new database named “AdventureWorks2012Subscriber”

32.) my newly created subscription database “AdventureWorks2012Subscriber” is selected now.

33.) SQL Server Agent would need proper credential to connect with Distrubtor database and subscription database. Give proper credential to keep your replication smooth.

34.) select “Run Continuously” for agent.

35.) select “Immediately” so that subscription gets populated immediately.

36.)  select both the checkbox as usual.

37.)  Give the path for script of subscription.

Here is the script generated for subscription.

[sourcecode language=”sql”]

—————–BEGIN: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–

use [AdventureWorks2012]

exec sp_addsubscription @publication = N’AdventureWorks2012′, @subscriber = N’WIN-9H6QATRKY81\SQL2K12DEVTEST’, @destination_db = N’AdventureWork2012Subscriber’, @sync_type = N’Automatic’, @subscription_type = N’pull’, @update_mode = N’read only’

GO

—————–END: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–

 

—————–BEGIN: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–

use [AdventureWork2012Subscriber]

exec sp_addpullsubscription @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publication = N’AdventureWorks2012′, @publisher_db = N’AdventureWorks2012′, @independent_agent = N’True’, @subscription_type = N’pull’, @description = N”, @update_mode = N’read only’, @immediate_sync = 1

 

exec sp_addpullsubscription_agent @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publisher_db = N’AdventureWorks2012′, @publication = N’AdventureWorks2012′, @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distributor_security_mode = 0, @distributor_login = N’sa’, @distributor_password = null, @enabled_for_syncmgr = N’False’, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20121125, @active_end_date = 99991231, @alt_snapshot_folder = N”, @working_directory = N”, @use_ftp = N’False’, @job_login = N’win-9H6QATRKY81\Administrator’, @job_password = null, @publication_type = 0

GO

—————–END: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–

[/sourcecode]

So finally we have implemented Transaction Replication. You can confirm whether replication is working or not by inserting few records in one of the table in ServerA which supposes to take effect immediately in ServerB’s database.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Find buffer pool usage of database in SQL Server

Find buffer pool usage of database in SQL Server

SQL Server stores data in datapage of 8KB. Whenever you read datapage first time, SQL Server used to physically read it from disk and load that page into buffer memory so that subsequent call of that page can cater the need fast. Memory in server may be limited so it is better to keep an eye on buffer memory usage so that less important database/object doesn’t occupy more space in buffer.

Note:  Know more about datapage, read my earlier article here.

We can find information about buffer from the DMV “sys.dm_os_buffer_descriptors”. When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server.  sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database which is hidden system database.

Here is the query to find buffer usage:

[sourcecode language=”sql”]
SELECT
database_id AS DatabaseID,
DB_NAME(database_id) AS DatabaseName,
COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
FROM
sys.dm_os_buffer_descriptors
GROUP BY
DB_NAME(database_id),database_id
ORDER BY
BufferSizeInMB DESC
GO[/sourcecode]

You can get output something like this:

Please note first row in above screen shot which has NULL in DatabaseName column whose databaseID is 32767. That row belongs to ResourceDatabase which is system database, hidden from the user and always having databaseID 32767.

You can clear up your buffer memory with following command.

[sourcecode language=”sql”]
DBCC DROPCLEANBUFFERS
GO[/sourcecode]

Please note that executing above given command in production server will degrade performance of your queries temporary as it will remove all datapage from buffer pool so I would highly recommend not using above command in production server.

Let us execute SELECT query for buffer pool again and see what is the status of buffer pool with the same SELECT query.

[sourcecode language=”sql”]
SELECT
database_id AS DatabaseID,
DB_NAME(database_id) AS DatabaseName,
COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
FROM
sys.dm_os_buffer_descriptors
GROUP BY
DB_NAME(database_id),database_id
ORDER BY
BufferSizeInMB DESC
GO[/sourcecode]

You can see most of your buffer memory free due to DROPCLEANBUFFERS command. Here is the screen capture in my SQL Server Instance.

You may not get exactly same output as above screen capture but you can definitely see the difference the buffer memory distribution in your both execute (Before and after DROPCLEANBUFFERS).

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find Log path for SQL Server 2012

Find Log path for SQL Server 2012

I have observed so many times that many developers don’t know where SQL Server log file is located. Log file is one of the primary places where we can start troubleshooting from. There is one DMV “sys.dm_os_server_diagnostics_log_configurations” available in SQL Server 2012 which helps you to find out the error file location.

[sourcecode language=”sql”]
SELECT
[path],
is_enabled,
max_size,
max_files
FROM
sys.dm_os_server_diagnostics_log_configurations
GO[/sourcecode]

First column in above given query will provide you with the information about Path of log file. Second column will convey whether log is enabled or not. Rest of two columns gives you information about Max file size and max number of files.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Sys.dm_os_windows_info DMV to find Operating system information from SQL Server 2012

Sys.dm_os_windows_info DMV to find Operating system information from SQL Server 2012

Microsoft always tries to give more & more power to SQL Developer. Today I come up with one more wonderful DMV named “Sys.dm_os_windows_info” which gives you information about windows operating where SQL Server Instance is installed. You can find this DMV in SQL Server 2008 R2 or later version.

As a Developer or DBA, it is mandatory to know the version of Operating System where SQL Server Instance is installed especially when you are developing script to read system information in Powershell or vbscript. It is not possible to know the operating system information if you don’t have your in-house server but depend on hosting company.

Here is the small and wonderful script to read information about Operating System right from your query windows or SQL Server 2012 or 2008 R2.

[sourcecode language=”sql”]
SELECT
@@VERSION AS SQLVersion
,windows_release
,windows_service_pack_level
,windows_sku
,os_language_version
FROM
sys.dm_os_windows_info
GO[/sourcecode]

Once you run above given query, you will get output something like this:

First column “SQLVersion” uses @@VERSION system variable to display SQL Server version.

Second column “Windows_Release” gives information about Operating system version.

Third column “Windows_Service_Pack_Level”, as name suggests, gives information about latest service pack installed on the top of operating system.

Fourth column “Windows_SKU”  gives information about Windows Stock Keeping Units id.

Fifth & last column “os_language_version” shows the windows locale identifier. Each number represents language. 1033 represent English US. For more information about LCID (Windows Locale Identifier), click here.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find TCP IP Port in SQL Server 2012

 

 

Find TCP IP Port in SQL Server 2012

As an SQL Server Administrator you might need to find SQL Server TCP IP port configured for your SQL Server Instance. There are many reason to find it but most famous reasons is to give access to developer and set firewall exception if there is any. Generally 1433 is the default port but you may configure different ports if you have more than once SQL Server instance running or may be due  to security reason you would like to change the default port of SQL Server.

There are few different ways to find the SQL Server 2012 TCP/IP port  out of which I am going to mention few of the most used ways to reach TCP/IP port of SQL Server 2012.

1.)  Read TCP IP Port of SQL Server 2012 from Registry:

This is one of the most used ways. Either go to registry to find TCP IP port or execute extended stored procedure in SQL Server itself to read registry.

[sourcecode language=”sql”]DECLARE @tcp_port nvarchar(10)
EXEC XP_REGREAD
@rootkey    =    ‘HKEY_LOCAL_MACHINE’,
@key        =    ‘SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQLServer\SUPERSOCKETNETLIB\TCP\IPAll’,
@value_name    = ‘TcpPort’,
@value        =    @tcp_port OUTPUT
SELECT @tcp_port AS TCPIP_Port
GO[/sourcecode]

2.) Read TCP IP Port From SQL Server 2012 Configuration Manager.

Open “SQL Server Configuration Manager” from Start Menu-> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager. Refer following screen shot for more details:

Now move towards “SQL Server Network Configuration”-> Protocol for “SQLServerInstance”->TCP/IP property.

Note: My SQL Server instance name is “SQL2K12DEV”, you may have different name

From the property dialog box of TCP/IP, move to the “IP Addresses” tab and all the way down, you can fine “IP ALL”. You can read or configure TCP port from here.

3.) Find TCP IP port from XP_ErrorLog extended Stored Procedure.

[sourcecode language=”sql”]
USE master
GO
XP_READERRORLOG 0, 1, N’Server is listening’
GO[/sourcecode]

You can get output something like this:

4.) Read SQL Server TCP IP Port from Event Viewer

Now last but not least “Event Viewer” which you can find under Control Panel -> Administrative Tools -> Event Viewer. Go to Windows Log->Application in Event Viewer and read the log as shown in following screen capture:

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

New Logical Function IIF in SQL Server 2012

New Logical Function IIF in SQL Server 2012

IIF is one of the new logical functions in SQL Server 2012 which Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

Generally we had to use IF condition or CASE….WHEN….THEN statement to achieve the functionality being offered by IIF. IIF is shorter function to use as against IF condition and CASE….WHEN….THEN statement. If you write IIF function compiler actually rewrites the IIF function as a CASE statement so the behavior of IIF and the simple CASE statement are identical though you will save some key stroke as compare with CASE…WHEN.

Let us see one small example which demonstrates the use of IIF function in SQL Server 2012.

[sourcecode language=”sql”]IF OBJECT_ID(‘tempdb..#FunctionTesting’) IS NOT NULL

DROP TABLE #FunctionTesting

CREATE TABLE #FunctionTesting
 (
 ID INT IDENTITY(1,1)
 ,EmpName VARCHAR(50)
 ,NightShiftDay INT
 )

INSERT INTO #FunctionTesting VALUES
 (‘Ritesh Shah’,1)
 ,(‘Rajan Shah’,2)
 ,(‘Bihag Thakar’,3)
 ,(‘Kavan Dhruv’,4)
 ,(‘Paresh Prajapati’,5)
 ,(‘Kalu Bhuva’,6)
 ,(‘Prapa Acharya’,7)
 ,(‘Bhushan Shah’,1)

–Use of IIF
SELECT
 IIF(NightShiftDay=1 or NightShiftDay=7,’WeekendNight’,’RegularDayNight’ ) AS ShiftDetail
 ,EmpName
 FROM
 #FunctionTesting

–used to achieve the same with CASE….WHEN since long
 SELECT
 CASE WHEN NightShiftDay=1 or NightShiftDay=7 THEN ‘WeekendNight’ ELSE ‘RegulardayNight’ END AS ShiftDetail
 ,EmpName
 FROM
 #FunctionTesting[/sourcecode]

Here is the output of both SELECT statements

I have earlier written few articles to demonstrate few different SQL Server function, you can refer those articles from the table given below:

Function Link to learn
LEAD Click Here
LEG Click Here
CUME_DIST Click Here
CHOOSE Click Here
PERCENT_RANK Click Here
LAST_VALUE Click Here
FIRST_VALUE Click Here
EOMONTH Click Here
DATEADD Click Here
DATEFROMPARTS Click Here
DATETIMEFROMPARTS Click Here
TIMEFROMPARTS Click Here
PARSE Click Here
TRY_PARSE Click Here
TRY_CONVERT Click Here
ISNULL Click Here
COALESCE Click Here
CONCAT_NULL_YIELDS_NULL Click Here

 

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.