:::: MENU ::::

Thursday, September 16, 2021

DBCC OPENTRAN is one of the DBCC commands used to manage lot of stuff inside SQL Server.

DBCC OPENTRAN helps to identify oldest active transactions that may be preventing log truncation, and it displays information about the following:

  • The oldest active transaction.
  • The oldest distributed and non-distributed replicated transactions "if any".

DBCC OPENTRAN displays information about the transaction log of a specified database, whether by specifying USE clause before DBCC OPENTRAN or by specifying database name or ID within DBCC OPENTRAN command itself.

-- Syntax
DBCC OPENTRAN
[
    ( [ database_name | database_id | 0 ] )
    { [ WITH TABLERESULTS ]
      [ , [ NO_INFOMSGS ] ]
    }
]

-- Examples
-- This will provide the output in text format.
USE db_name
DBCC OPENTRAN
GO

-- This will provide the output in tabular format, allowing inserting the output in a table or temp table.
USE db_name
DBCC OPENTRAN WITH TABLERESULTS
GO

-- This will provide the output in tabular format, plus hiding infomational messages.
USE db_name
DBCC OPENTRAN WITH TABLERESULTSNO_INFOMSGS
GO

-- This way of specifying database name in DBCC command itself.
USE master
DBCC OPENTRAN (db_nameWITH TABLERESULTS


-- Inserting DBCC OPENTRAN output to a SQL Server table

-- 1- creating temp table to hold DBCC OPENTRAN data
IF OBJECT_ID('tempdb.dbo.#temp_dbcc_opentran'IS NOT NULL
DROP TABLE #temp_dbcc_opentran
CREATE TABLE #temp_dbcc_opentran
(
  RowName SYSNAME,
  Value sql_variant
)
-- 2- inserting DBCC OPENTRAN values to temp table
INSERT INTO #temp_dbcc_opentran
EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');
-- updating column's values
UPDATE #temp_dbcc_opentran
SET RowName = 'SPID' WHERE RowName = 'OLDACT_SPID'
UPDATE #temp_dbcc_opentran
SET RowName = 'UID' WHERE RowName = 'OLDACT_UID'
UPDATE #temp_dbcc_opentran
SET RowName = 'NAME' WHERE RowName = 'OLDACT_NAME'
UPDATE #temp_dbcc_opentran
SET RowName = 'RECOVERYUNITID' WHERE RowName = 'OLDACT_RECOVERYUNITID'
UPDATE #temp_dbcc_opentran
SET RowName = 'LSN' WHERE RowName = 'OLDACT_LSN'
UPDATE #temp_dbcc_opentran
SET RowName = 'STARTTIME' WHERE RowName = 'OLDACT_STARTTIME'
UPDATE #temp_dbcc_opentran
SET RowName = 'SID' WHERE RowName = 'OLDACT_SID'
UPDATE #temp_dbcc_opentran
SET RowName = 'Oldest distributed LSN' WHERE RowName = 'REPL_DIST_OLD_LSN'
UPDATE #temp_dbcc_opentran
SET RowName = 'Oldest non-distributed LSN' WHERE RowName = 'REPL_NONDIST_OLD_LSN'

-- 3- selecting DBCC OPENTRAN data
SELECT *
FROM #temp_dbcc_opentran

-- 4- getting active transaction duration & replication latency "if exists"
-- active transaction duration "in case of no replication latency"
IF EXISTS (SELECT [Value]
FROM #temp_dbcc_opentran
WHERE RowName = 'LSN')
BEGIN
  DECLARE @LSN NVARCHAR(25= (SELECT CAST(Value as NVARCHAR(128))
  FROM #temp_dbcc_opentran
  WHERE RowName = 'LSN')
  SELECT @LSN AS ActiveTransactionLSN ,
    [Begin Time] AS TransactionBeginTime, GETDATE() AS CurrentTime,
    DATEDIFF(MINUTE, [Begin Time], GETDATE()) AS TransactionLatencyInMinutes
  FROM ::fn_dblog(REPLACE(REPLACE(@LSN,'(',''),')',''),REPLACE(REPLACE(@LSN,'(',''),')',''))
  WHERE [Begin Time] IS NOT NULL
END
-- active transaction duration "in case of replication latency"
IF EXISTS (SELECT [Value]
FROM #temp_dbcc_opentran
WHERE RowName = 'Oldest non-distributed LSN'
  AND [Value] <> '(0:0:0)')
  BEGIN
  DECLARE @RepLSN NVARCHAR(25= (SELECT CAST(Value as NVARCHAR(128))
  FROM #temp_dbcc_opentran
  WHERE RowName = 'Oldest non-distributed LSN')
  SELECT @RepLSN AS ActiveTransactionLSN ,
    [Begin Time] AS TransactionBeginTime, GETDATE() AS CurrentTime,
    DATEDIFF(MINUTE, [Begin Time], GETDATE()) AS LogReaderLatencyINMinutes
  FROM ::fn_dblog(REPLACE(REPLACE(@RepLSN,'(',''),')',''),REPLACE(REPLACE(@RepLSN,'(',''),')',''))
  WHERE [Begin Time] IS NOT NULL
END
-- 5- getting SPID details "if exists" & replication latency "if exists"
IF EXISTS (SELECT [Value]
FROM #temp_dbcc_opentran
WHERE RowName = 'SPID')
BEGIN
  DECLARE @SPID INT = (SELECT REPLACE(CAST(Value as NVARCHAR(128)),'s','')
  FROM #temp_dbcc_opentran
  WHERE RowName = 'SPID')
  IF EXISTS (SELECT [name]
  FROM master.sys.objects
  WHERE [name] = 'sp_WhoIsActive')
  EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command=1
    @get_plans = 1,@get_full_inner_text = 0,@get_task_info=1
    @show_sleeping_spids=2, @filter_type='session', @filter= @SPID
  ELSE
  EXEC sp_who2 @SPID
END
ELSE
SELECT 'There is no SPID information to present!!' AS SPID_Information
-- 6- drop table #temp_dbcc_opentran
IF EXISTS(SELECT 1
FROM tempdb.sys.tables
WHERE NAME LIKE '#temp_dbcc_opentran%')
DROP TABLE #temp_dbcc_opentran
GO

We can have different output based on the type of oldest active transaction "whether it is a normal transaction or a replication transaction that being processed by log reader", also in case we have no active transaction then the result set will be empty.


Example for opened active transactions



The using sp_who2 or sp_WhoIsActive we can get more details for the SPID itself.

So, in summary, this is how we can get more insights about the oldest active transaction on our database, its type and also how much latency we have and some details about the SPID for this transaction.

Saturday, May 23, 2020

Welcome to the second post in Git for DBAs series which is Discovering Version Control Systems.

In this post we will start talking about the definition of version control and why it is important, also we will give a quick brief about types of version control systems and the advantages/disadvantages of each type.

What is Version Control?
As a definition and as we can see from any online resources interested in version control, it is simply a system that records changes to a file or group of files over time and based on that we can recall any specific version of these files at any point of time.

What can Version Control do for us?
It is a paradigm shift !!, let us imagine what we can do to manage changes we are doing in a project with set of code files and by time we keep changing the code inside those files, what is the traditional approach we will use? ... the answer is easy: keep creating sub-files with different timestamp and names like: my_file_02022018.txt or my_file_last_update.txt ... etc.

One of the core added values of version control is that you can have all changes applied on this single file while having a single version of this file and avoid all confusion and mistakes that can happen based on having this big number of multi-versions of this file. 

Actually, there are much more actions against this file and any other files being tracked by version control:
  • Moving backward and forward in the changes "versions" of this file like a time machine.
  • Comparing code changes between those versions.
  • Reverting files or project "set of files" into a previous state, for example to a specific stable version.
  • Check who modified files and when.
  • Check who introduced new code and new issues "this will be explained into more details when we start discussing code collaboration with Git".

Types of Version Control Systems
Version control systems can fall into three main types:
  1. Local Version Control Systems.
  2. Centralized or Client-server Version Control Systems (CVCSs).
  3. Distributed Version Control Systems (DVCSs).

Local Version Control System
As illustrated above, the idea of having multiple versions of the same single file is the core idea of version control which is a local version control which means that it is being applied only on local computer.
However, with local version control we still miss an important benefit, which is collaboration, this is under the fact that we are limited to the scope of local machine for managing our file's changes, then it is still impossible to share our code files with others, and here the client-server version control or centralized version control step in.

Centralized Version Control System (CVCS)
In order to achieve the collaboration goal, then there is a need to have a single location to have all files accessible and maintainable among all collaborators like software engineers, this location is a centralized version control server that contains all these files along with its versions and client machines can connect to this centralized server and get/modify those files.


















With centralized version control systems, we are now able to share our code among all collaborators who copy required files into their machines, apply required modifications then push these modifications back to the centralized server.

This is a good approach and can be managed by set of permissions that CVCS system administrators can easily apply, however there some downsizes for such systems:
  • Single point of failure: so in case the CVCS server down then no one can access the different project's code files.
  • Connectivity: developers or collaborators have to be always connected to this centralized server, which means no offline work can be done at anytime.
  • Losing history: in case of HW or disk failure for the centralized server with no proper up-to-date backup, then all the projects history will be lost forever.

Distributed Version Control System (DVCS)
Here comes the idea of making the code files with its versions distributed instead of being centralized.
In DVCS clients clone a full copy of all code files with versions for the master server into their local machines and then can apply all required modifications locally and when needed they can push those modifications back, by applying this approach we managed to solve all the above downsizes:
  • Single point of failure: in case the master server dies, collaborators can still work normally on their local copy of code "local repository".
  • Connectivity: there is no need now to be connected to the master server, as changes can be applied on collaborators local machines, connectivity will be required only when code need to be cloned from master or pushed back to master.
  • Losing history: by having a full copy of code files cloned on all collaborator's machines, it is not possible to lose our code even there is no backup or the backup is outdated "please note that this is not considered as a backup replacement, backup is always important".





















It is also important to mention that distributed version control systems can act as local version control ones too, this will be explained into more details when we start demonstrating how to work with Git as an example for DVCS.

Starting from the next post, we will start our journey with Git and explain its structure, how it works and provide many practical examples and cases.

Saturday, May 9, 2020

Version control becomes a required skill for most of IT professionals not only software engineers, and DBAs are not the exception, version control can make the life of DBAs much easier and can be used to accomplish many goals like:
  1. Having a unified single of truth of organization's database scripts.
  2. Keeping track with all versions of administrative SQL scripts being used by organization's DBAs.
  3. Organizing the process of application's database scripts deployment being developed by software/database developers, and keeping track with all those scripts.
And the examples have no limit, based on the needs of every organization.

During this post and the upcoming ones, we will talk about the version control concept and use of it in the database world, since it is a relatively new concept for DBAs compared by software engineers or system administrators/engineers, however it becomes an essential skill for DBAs too.

During the upcoming posts, we will be covering the following points:
  1. The concept and history of version control.
  2. The types of version control and the distributed version control systems "DVCS".
  3. What is Git, GitHub and GitLab?
  4. Using Git as a local version control system.
  5. Basic Git operations for databases and database SQL scripts.
  6. Using GitHub and GitHub enterprise for collaboration.
By the end of this series of Git and version control for DBAs, you as a DBA will be able to use the concept of version control to manage your database environment and scripts and implement this concept using Git.

Friday, June 7, 2019

This is a simple PowerShell function that gets all services related to SQL Server services on a computer with its status (running or stopped).

1
2
3
4
5
6
function Get-SQLServices
{
Get-Service | Select-Object Name, Status, DisplayName | 
Where-Object Name -like *sql* | Sort-Object Status -Descending | 
Format-Table -AutoSize
}

Name Status DisplayName ---- ------ ----------- MSSQLServerOLAPService Running SQL Server Analysis Services (MSSQLSERVER) SQLWriter Running SQL Server VSS Writer SQLTELEMETRY Running SQL Server CEIP service (MSSQLSERVER) MSSQLFDLauncher Running SQL Full-text Filter Daemon Launcher (MSSQLSERVER) MSSQLSERVER Running SQL Server (MSSQLSERVER) SQLPBENGINE Stopped SQL Server PolyBase Engine (MSSQLSERVER) SQLSERVERAGENT Stopped SQL Server Agent (MSSQLSERVER) SQL Server Distributed Replay Controller Stopped SQL Server Distributed Replay Controller MSSQLLaunchpad Stopped SQL Server Launchpad (MSSQLSERVER) SQL Server Distributed Replay Client Stopped SQL Server Distributed Replay Client SQLPBDMS Stopped SQL Server PolyBase Data Movement (MSSQLSERVER) SQLBrowser Stopped SQL Server Browser

Tuesday, January 26, 2016

We addressed in the last post the difference between the memory on the server level and the buffer pool on the database engine, in this post we will address how to calculate the buffer pool consumption for the whole SQL Server instance.
In order to calculate this figure we need to get some information for different factors like:

1-   Total number of data pages that buffer pool section can hold inside the server memory.
This value can be calculated using the following query by querying sys.configurations catalog view  
select convert(int,value_in_use)*1024/8 as [TotalNumofPages] from sys.configurations where name='max server memory (MB)'

         2- Number of pages for every database in the SQL instance, we can get that
             from DMV sys.dm_os_buffer_descriptors
              SELECT
db_name(database_id) as [DBName],COUNT_BIG(*) as [NumofPages]
  FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

After getting this data then we have to make some calculations to get the following values:
Current Buffer Pool usage (GB)
Current Buffer Pool usage (number of pages)
Current Buffer Pool usage (percentage)
And to add date/time column for every record we collect.

The complete script:
DECLARE @TotalNumofPages INT;

SELECT @TotalNumofPages=convert(INT,value_in_use)*1024/8 FROM sys.configurations WHERE name='max server memory (MB)'

SELECT db_name(database_id) as [DBName],COUNT_BIG(*) as [NumofPages]
INTO #DBBPUSG
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

SELECT sum(convert(DECIMAL (10,3),[NumofPages] / 128)/1024) AS [Current used memory by BP (GB)],
sum([NumofPages]) AS [Current used pages in BP],SUM(CONVERT(DECIMAL(6,3),
[NumofPages] * 100.0 / @TotalNumofPages)) AS [Current used percentage of BP],convert(SMALLDATETIME,GETDATE()) AS [Date\Time] FROM #DBBPUSG
GO

DROP TABLE #DBBPUSG
GO

Tuesday, December 29, 2015

Memory is one of the important resources for any database management system and for any IT service in general and controlling this critical resource is one of any DBA major challenges.

Most of database management systems has a parameter or more to define the its share from the total amount of server installed memory, this maintain a hard limit to the DBMS that should not exceed and the rest should be used for the operating system itself “in case that there is no other software installed like web services for example and this–by default–the best practice for a production environment.”

This parameter is very useful for defining a hard limit for the memory the database case use and assign the rest for the OS operations in order to maintain the server performance but it is useless for measuring the amount of memory utilized, this is because the simple fact that this is a static value and in order to measure the memory and its peaks we need another figure to be considered which it the buffer pool utilization.

The buffer pool is the area inside SQL Server memory that used for hosting data and indexes pages for both reading and writing operations and represent about 99% of the total memory of SQL Server, by measuring the buffer pool consumption we can then measure the actual memory utilization for SQL Server instance.
 The below snapshot shows the difference between the SQL Server memory utilization “as a whole” and the buffer pool utilization.

















While the red line represent the memory utilization the blue one represents the buffer pool utilization, as we can see the total memory utilization is always the same because it is a static value, while the buffer pool utilization changes by the reading/writing operations activities on the system and that is why we find the utilization during some points reached the total assigned memory and sometimes else it decreased below.

 The next step will be how to collect the memory and buffer pool utilization figures and this will be explained deeply in the next post.

Sunday, December 13, 2015

This is one of the critical and bad situations that any DBA can face, to find yourself unable to start your SQL instance after disabling NT AUTHORITY login “for any reason”.

In my case it was disabled by the Network administrator guy by mistake and I did not realize that till restarting my SQL production instance for a planned maintenance operation!!

So, the situation is very critical that you find your production instance failed to be started with error message:
“Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: The account is disabled”.

The first positive point here is that the error message is clear enough and then we can start troubleshooting in the right track.

What we need to do in this situation is simply grant “sysadmin” permission for NT AUTHORITY system account again but the question is how can we connect to the instance while it is stopped?, the answer is to start it in single user mode and to do that we need to go through the following steps:

1- Open command prompt in administrator mode.

2- Navigate in SQL Server installation media till “Binn” folder.

3- Start SQL Server DB Engine service in single user mode using command “sqlservr.exe -s SQL2012 –m” while SQL2012 is the instance name, here is a complete example: 
“C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s SQL2012 –m” 
while "–s  SQL2012" is the instance name and "–m" is the option for starting the instance in single user mode.

The next step then is to grant the NT AUTHORITY system user "sysadmin" permission and to do that we need to connect to the instance after starting it in single user mode via command prompt, the command to do that is as below:

SQL Authentication:
SQLCMD -S instance IP,port -U SQL account -P password
ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
  
Windows Authentication:
SQLCMD -S instance IP,port –E
ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO

After that we can close the two opened command prompt and then restarting SQL Server instance in the normal mode.

Thursday, April 30, 2015

During my journey with SQL Server, I used to query the SQL Server agent jobs metadata, by default it is very important to get some information related to the jobs defined on your SQL instance, the current running ones, some details like steps defined per job, … etc.

Due to all the above situations, I used to design a lot of complex queries that related to SQL jobs and thought that I am doing the right job … but unfortunately I am not.

The secret behind this is simply the system stored procedure sp_help_job which literally save a lot of time and effort of creating such complex long queries, the sp_help_job is a system stored procedure located in msdb system database and has a set of parameters that cover most of the information required for SQL agent jobs.

We can use sp_help_job stored procedure in the below scenarios:

1- Getting all jobs on a SQL instance with some metadata like server, job name, owner, notification methods, create date and last run date … this can be done by executing the stored procedure without specifying any parameters.
exec msdb.dbo.sp_help_job;

2- Getting all current running jobs, this can be done by specifying the parameter @execution_status with value 1.
exec msdb.dbo.sp_help_job @execution_status=1;

3- Getting details for a specific job, by using parameter @job_name and specifying the job name we can get information about the job itself, the jobs steps, the job schedule and the job outcome.
exec msdb.dbo.sp_help_job @job_name=[Job Name];

4- Getting the jobs owned by a specific login, by specifying parameter @owner_login_name.
exec msdb.dbo.sp_help_job @owner_login_name=[Login Name];

5- Getting the enabled or disabled jobs on the instance by specifying parameter @enabled with 0 value for disabled jobs and 1 for the enabled ones.
exec msdb.dbo.sp_help_job @enabled=0;
exec msdb.dbo.sp_help_job @enabled=1;

There are some other parameters that can be specified like the subsystem name, the category name and date created/ modified.

The below snapshot shows all available parameters for sp_help_job stored procedure.

Sunday, April 12, 2015

This post is one of the "Quick Tips" posts series that gives a quick guide about how to deal with simple tasks in your DBA's day-to-day life, and in this post I will show how to add a new clustered disk to an existing SQL Server cluster.

Adding new disks to the current running SQL Server is a task any DBA may need to do many times during his\her career, the task of adding disk to a local SQL instance is a straight forward one by default, but what about adding disks to clustered instance?

It is still a straight forward task even it may take some extra steps, let us go through it...

The first step is to add the clustered disk to the “Windows Cluster“, this can be done by opening the Failover Cluster Manager, expanding the cluster name then right click on “Storage” folder, select add disk and then select the disk to be added into the windows cluster.














Second step is adding the clustered disk into the targeted SQL Server instance’s resource group and then add it in the dependencies resource list for SQL Server database engine.































Third and final step is to make sure that the SQL Server is currently controlling the disk and can access it normally, this can be done by querying the DMV sys.dm_io_cluster_shared_drives which shows a list of the clustered disks attached to SQL Server or by trying to create a testing database on the new attached disk.

-- Queries
-- Checking with DMV
select * from sys.dm_io_cluster_shared_drives

-- Checking with creating a testing database
-- Assuming that the clustered disk letter is "I"
CREATE DATABASE [TestDB] ON  PRIMARY
( NAME = N'TestDB', FILENAME = N'I:\TestDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestDB_log', FILENAME = N'I:\TestDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO