Suppose, you have Transactional Replication configured in your production environment. There is a business requirement to add a new article to the existing publication, and you would like to initialize only the newly added article instead of all articles in the publication. To achieve the above requirement, I will be using below-mentioned Publisher Server, Publication Database, and Subscriber […]
Capture Important Parameters of the SQL Server Replication
Read more
Whenever there is a business requirement to add a new article to an existing publication, you want to know how the publication is going to behave during the initialization. Basically, you start asking following questions to yourself; Will a new snapshot (running the snapshot agent of the publication) cause to initialize\reinitialize to all the articles […]
SQL SERVER DATABASE FILES WISE I/O LATENCY PART#2
Read more
Since the DMF sys.dm_io_virtual_file_stats shows cumulative I/O statistics value for the database files, you can’t just use the DMF to shoot a conclusion that the database files are experiencing high latency issue. The reason is the overall aggregation will mask spikes of poor performance. It is recommended to capture the latency data over a period […]
SQL Server Database Files Wise I/O Latency Details PART#1
Read more
One of the real-world I/O troubleshooting problems is – If you have your databases spread across multiple LUNs and you want to find out which database files are experiencing high latency issues and driving the most I/Os to storage subsystem using performance counter (Avg. Disk sec/Read & Avg. Disk sec/Write). The counter doesn’t help you to identify […]
How the Initial VLF sequence number gets decided of the default transaction log file
Read more
In last three years, I presented two times on the topic Transaction Log File Architecture. During the sessions, when I discussed the undocumented command DBCC LOGINFO, one of the common questions which I got – ‘How the Initial VLF sequence number gets decided of the default transaction log file’? In this tip, I will show you how the […]
Top Function – Performance Gain
Read more
Recently, I presented at Singapore SQL Pass Chapter on the topic T-SQL Fun, and the most of the attendees were a developer. During the session, I discussed TOP function, and I throw a question to the audience – “Not using Top function especially when you can use it – Can it cause performance problem or […]
Log File Space Issue Monitoring Using SQL Server DMV
Read more
As a continuation of “Database Monitoring using DMV” series, this blog will cover how quickly you can address the log file space related issue. Below are the previous blogs which you may find useful in your everyday job. Disk Space Monitoring Using SQL Server DMV SQL Database File Wise Disk Space Monitoring To address the […]
Setting Up a Subscription
Read more
In the last two blogs, we went through Configure Distribution Database and Publication Creation. If you haven’t read them, I will request you to go through before you start exploring this blog. If you don’t have the Subscription database, You must create your subscription database. Once you created the database, you’re ready to create your […]
Setting Up a Publication for Transaction Replication
Read more
In the previous blog, we discussed how to Configure Distribution Database. Once you setup your Distribution database successfully, you can create a publication. Here, we will learn how to create a publication for Transaction Replication and how many jobs get created after the publication setup. Create Publication I’ll walk you through step by step process […]
SQL Database File Wise Disk Space Monitoring
Read more
As a DBA, you get alerts many times regarding the database file space issue. To address the issue, you may be following the steps. Which is the particular database file in the database which is having less space? How much disk space available on the physical disk where the file resides? The below script will […]