Looking deeper into the physical & logical architecture - Transaction Log File

Beyond all doubt, it is essential to have a good understanding of the Transaction Log (T-Log) so that we can diagnose unforeseen performance issues related to it and I am sure that almost everyone had at least one. The T-Log is basically a record of all transactions happening to the database. All these transactions are actually first written to the physical T-Log file, and then after a CHECKPOINT, is written to the Data File via the Lazy Writer process. Some of the uses of T-Log are: as a point in time recovery (full recovery model), to record the Start and End of each transaction, every data modification (insert, update, delete) including system SP's, DDL statements to any table including system tables, every extent and page allocation and de-allocation operation, and creation or drop of tables and indexes.

SQL Mirroring Port could stop working unexpectedly

When it comes to diagnosing and fixing some SQL Mirroring issues we may have to face with bizarre causes that could stop working a database mirroring and as a result the state of it will be "disconnected". For instance, one of the most common problems is that the SQL mirroring port may stop working unexpectedly. Consequently, the whole database mirroring functionality will stop working too. We basically configure SQL Server mirroring to use the port 5022 by default which works good enough for the vast majority of cases. To be perfectly honest, there is not an urgent need to change it since the issue is not about the port number (or mirroring configuration) but the port itself.

While working on fixing the issue, we may think the database mirroring port may have stopped working or blocked for some security reason which we do not know accurately at the beginning of the situation. Nevertheless, the reason should be investigated further by the Network team so as to prevent from getting the same issue in the future again. Having clear the situation, I am going to show one way to have the database mirroring working again. To begin with, we need to figure out what is the current number port of the mirroring being used.



We can see that the mirroring port is 5022, so we can opt to change it.

ALTER ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5023)

Only after successfully completing that will we have to restart the database engine. Finally, we need to restore the original port and then restart again the database engine.

ALTER ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5022)

Having done it, we will see the database mirroring working properly. That's is all for now. I hope this tip also works for you. Let me know any remarks you may have. Thanks for reading.

How to move the files of database which has Replication, Mirroring, Log Shipping or AlwaysOn Settings

One of the challenging tasks in the life of a DBA is definitely moving all or some of the files of a database from one physical location to another one because of performance issues, maintenance requirements, disk space issues, etc. We usually move database files to another location by using Backup/Restore or Detach/Attach procedures. They are the most proper methods for most of the business cases but not for all. Let me expand on what I mean, for instance, those methods will not work with databases which have Replication, Mirroring, Log Shipping or AlwaysOn Settings because you will have to remove these settings before move them and then you should set up every setting again which could waste your time and have your database service stopped further than necessary. In this situation Backup/Restore or Detach/Attach simply is NOT an option because we need to make the database available as soon as possible. So, what we must do in order to move files of this type of database is by modifying the physical name of each database file we want to move. For instance, in the following code I will move 4 files (3 Data Files and 1 Log File):

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data01', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data01.mdf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data02', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data02.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data03', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data03.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Log', FILENAME= N'E:\SQLLog\SalesDB\SalesDB_Log.ldf')

It is very important to verify that new database file folders already exist, if so, this should be the output results:

The file “SalesDB_Data01” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data02” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data03” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Log” has been modified in the system catalog. The new path will be used the next time the database is started.

What’s next? We must stop the SQL Engine Service and then manually move every database file to the new location we indicated in the code above. Finally, we have to start the SQL Engine service which will load the files from the new location. With this method you do not need to remove any setting mentioned before. This is extremely effective and there is no doubt that it will work. Having these files moved to the new location, the database will start without any problem. If not, you should make sure that the SQL Service account has Full Control permission on database files from the new location.

I hope this tip helps you to save time and it will ensure that your database will be available quickly. I will be pleased to answer any question you may have. Thanks for reading!.