Showing posts with label Log Shipping. Show all posts
Showing posts with label Log Shipping. Show all posts

Tuesday 20 February 2018

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.

Friday 3 June 2016

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!.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE