Showing posts with label Transaction log. Show all posts
Showing posts with label Transaction log. Show all posts

Monday, March 30, 2020

Administration Basics: Point in time recovery

Nowadays, data is a precious asset for companies today. If you are a database administrator (by decision or by mistake) or simply you are the "IT guy," you have the mission of guarantee all the data is backed up and accessible for recovery.

Trust me, even when you could think you have the more reliable hardware on the planet, or you have multiple database replicas around the globe, anything can happen (a user deleting an entire schema by mistake, an application updating the wrong records, some process crashing, a lot of things can happen).
So trust me and don't question me, just backup all your databases regularly.

The Backup/restore strategy is a broad topic, so for this post, we will focus on the restore activity, so if you don't know how to back up a database yet, you can start here.


What do we need to know?

I like to think of the SQL backups like a pyramid, like this:

Pyramid of backups

For restoring your database to a particular time, you must perform a series of restores:
  • Full backup BEFORE the date you want. 
  • If available, the closest differential backup (this can be omitted if your diff backup is after the date you want or you don't have it).
  • Each T-log backup in order until the required date (we will see an example later).
It is essential to know that only the FULL database recovery model supports point-in-time recovery; with Bulk-logged, you are only allowed to restore the entire transaction log backup.

Set up our example

For this example, I have created a database named DB_RestoreDemo in SQL 2019. On it, I have a table called dates_demo with no relevant information, just a date and a varchar field for this example.

USE [DB_RestoreDemo]
GO

CREATE TABLE [dbo].[dates_demo](
 [id] [bigint] IDENTITY(1,1) NOT NULL,
 [date] [datetime2](7) NOT NULL,
 [text] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_dates_demo] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

GO

I populate the table with some records (save it as we will use the same insert later):

USE DB_RestoreDemo
GO

INSERT INTO dbo.dates_demo
VALUES(GETDATE(),replicate('|',rand()*100))

GO 50

Then take a full backup:

BACKUP DATABASE [DB_RestoreDemo] TO  
DISK = N'C:\SQLServer\DB_RestoreDemo_full.bak' 
WITH COMPRESSION
GO

After that, execute the INSERT statement above, but replace the number after the GO with a significant number, so it takes a lot of time to complete, in my case I will execute it 10 million times:



While it is running, open a new query window and perform some Differential and Transaction log backups as you prefer (in any order and as many times you want), just remember to change the filenames each time to have them quickly identified:


-- Differential backup
-- Remember to change the filename each time

BACKUP DATABASE [DB_RestoreDemo] 
TO  DISK = N'C:\SQLServer\DB_RestoreDemo_diff_1.bak' 
WITH  DIFFERENTIAL, COMPRESSION
GO

-- Transaction Log backup
-- Remember to change the filename each time

BACKUP LOG [DB_RestoreDemo] 
TO  DISK = N'C:\SQLServer\DB_RestoreDemo_1.trn' 
WITH COMPRESSION
GO

Once you are happy with the backups taken, you can stop the INSERT query or let it run until it finishes... depends on you.

We have what we need for our example now.


Let's do it!


Open the table and select a random record, we will try to restore the database as close as possible to the date of that record.

SELECT * 
FROM dbo.dates_demo
ORDER BY [date];

I have selected this random record; we must guarantee that the last record of the table is this one (or very close in case the millisecond resolution include other records):
Id: 613834, date: 2020-03-30 01:49:50.5366667



Then, go to the folder where your backups are located, and check the modified date of the files, you have three options depending on which record you have selected:


  • Option 1: The time you want to restore is before the full backup taken: Congratulations! You know what data loss is now, good thing this is a demo. You cannot go to that point in time if you don't have the required backups.
  • Option 2: The date you want to restore is after any backup was taken: You can make another transaction log backup.
  • Option 3: The date you want to restore is in range, just locate the file with the closest creation date after the date you want.

In my case, is this file:

 

Based on the pyramid we saw before, this must be my restore strategy:


  1. Restore 1 FULL backup - DB_RestoreDemo_full.bak
  2. Restore 1 DIFF backup - DB_RestoreDemo_diff_1.bak
  3. Restore 1 Transaction Log backup - DB_RestoreDemo_2.trn

We will restore these backups to a new database DB_RestoreDemo_NEW, so we don't overwrite existing.

Please note that except for the last one, all restore operations must be done with the NORECOVERY option.

For the last transaction log file restored, we will use the STOPAT option; this option allows us to restore a database to a specific date (our example), a particular LSN, or a transaction mark (we will see that on another post).

NOTE: for this example, we used only INSERT statements without explicit transactions, but in a real-life scenario, the STOPAT option with a date parameter will restore the database to the latest committed transaction to that point in time.

 Once we have our restore strategy in place, we create the restore scripts:

USE [master]
GO

RESTORE DATABASE [DB_RestoreDemo_new] 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_full.bak' 
WITH  FILE = 1,  
MOVE N'DB_RestoreDemo' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB_RestoreDemo_new.mdf',  
MOVE N'DB_RestoreDemo_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB_RestoreDemo_new_log.ldf',  
NORECOVERY;

GO

RESTORE DATABASE [DB_RestoreDemo_new] 
FILE = N'DB_RestoreDemo' 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_diff_1.bak' 
WITH  FILE = 1, 
NORECOVERY;

GO

-- Because of the resolution on the decimals in datetime type
-- the increments are .003, .007 and .000, so the closest one
-- to the date we want .537 is .540
RESTORE LOG [DB_RestoreDemo_new] 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_2.trn' 
WITH  FILE = 1, STOPAT = N'2020-03-30T01:49:50.540';

GO

 We navigate to the restored database, and if everything is ok, our selected record should be the last one (or very close to):



We have successfully restored a new database to the required point in time. A few things to have in mind:

  • You can specify the STOPAT option with a LSN or a transaction mark; this is useful if you have a critical process you can write your application to "mark" it for easy restore.
  • You just need the latest FULL and DIFF backup to the required date, but need all the TLog backups after that DIFF (or full if you don't have it).
  • In case the date you want to restore is included on a DIFF backup, you will need to ignore that DIFF backup and restore all the TLOG backups since the last FULL, including the first one after the DIFF backup.
  • For Bulk-logged recovery mode, you cannot perform point-in-time recovery, but you can perfectly restore log backups, but the entire content is restored.

And remember to always test your backups by restoring them regularly, with this you can achieve three things:
Confidence in the restore process, this will help you to remain calm under a stressful situation.
You can prepare the scripts you need to do it before anything bad happens.
You can estimate the time you need to do it, and check if that aligns with your RPO and RTO.

Thursday, February 1, 2018

Considerations for dealing with big databases

Stress everywhere, user wants everything "Para ayer"
Photo by Bernard Goldbach


If you are like me, and have SQL Server instances with databases of Terabytes of data, also known as very large database or VLDB, and you have to perform any kind of task on a reasonable time window, while minimizing the outage and keep the user happy with overall performance of the instance.

I want to share you some tips to help to make your life easier with a variety of common tasks you can encounter.

 

Optimize disk space


Maybe the most common task to deal with VLDs, a real headache if you have been handover the server with no further info and you have to optimize the disk space.

SQL Files location

Be aware of the location of each data and log file for your instance, find out how much space is taking each database file and if they are splitted across several files/folders.
Next step is to order the datafiles following best practices, separate datafiles in different drives as follows (if possible):
  • System databases (except for tempdb).
  • Tempdb database.
  • User datafiles (.mdf and .ndf files).
  • User logfiles (.ldf files) 

 

Use multiple filegroups

Most of the databases start with only one default filegroup. As you database becomes bigger and bigger, you should consider splitting your database into multiple filegroups, you can move big historical or log tables into a different filegroup and even mark them as read only, or separate them by business rules so that way, more critical data can be backed up more often using filegroup backups (more on this later on).

Monitor File growth

Put in place regular database growth monitoring (how you achieve that is up to you), but a proper alarm in time can avoid you a unpleasant outage or a critical ticket.
You can set an alarm (email, SMS, IM) when certain threshold is reached, for example a disk having 80% space used, so you have enough response time to check what is going on.

When creating new datafiles, set the expected size upon creation, don't rely on the auto-growth feature, as is recommended to have it enabled as a safeguard, always expand your datafiles manually with the expected size.
Remove the percent autogrowth feature, 10% of 1 GB is not the same as 10% of 1 TB, always prefer fixed sizes.

Data compression

Another feature of SQL Server is to row and page compression to reduce the disk space, the only drawback is that you have to consume more system resources to do the decompression when data is accessed.
Page compression usually saves more disk space, but also have a higher resource consumption for compress and decompress data.
If your database has high resource consumption, maybe this is not for you.


Backup/restore


If you don't have an adequate configuration for your VLDBs, the backup and restore windows can be also an issue, especially if you have tight RTOs for critical systems.

Backup compression

Out there since 2008, this feature is not commonly used, because it is not active by default, this can save you a lot of disk space for backups and reduce the backup time.

Filegroup backup

As stated above, consider separate your data into multiple datafiles and filegroups, this way using the backup filegroup option, you can use any business logic you want to achieve that, so more critical data can be backed up more frequently than the whole set, this also reduces your RTOs for your databases.

Differential backup is your friend

A good backup strategy must consider using differential backups for big databases, since these backups only store the changes from last full backup checkpoint, the size of the file will become larger as the time of the full backup increases, so you have to find a balance between full/diff backups that better suits your needs.



High Availability solutions


Mandatory for critical databases, even when the configuration of those can be a little more difficult due to the size of the databases, in case of a failure this can save your life, is always faster just to move to your secondary database copy than perform a full restore of the backups.
Which high availability solution you use depends a lot on your budget and the system configuration, but anything is better that nothing at all.


Transaction log


A couple of times in my personal experience, sudden high TLog growths have filled the disks and the alarms start to fire everywhere.  None these times was funny at all!


Backup your transaction log regularly

The more frequently you backup your log, the smaller the files will be, due the fact you need all the transaction log backups from the last full/diff, you have also to find the balance between backup frequency/file size.

Simple recovery mode

If your database is big due to a lot of historical data used mainly as read only, why bother having log backups? this can save up space you anyway will never use.
If your database have historical and high transactional data, consider split it into separate databases and configure the reporting ones as simple recovery mode.

Avoid shrinking whenever possible

Database shrinking can lead to fragmentation, always use it with caution and only in exceptional situations.
If your log grows  way often, then you have a issue with a query or queries that needs to be optimized.


T-SQL performance


For your application performance and object maintenance, especially if you are a hard-coded database administrator, you really cannot do much about, when another team is in charge of the database programming part, what you can do is to generate reports and advise user on improvements that could benefit all together.

Check for unused database objects

You will be surprised on the number of unused tables/indexes old databases have, check regularly for unused indexes and tables (we will do a post on how to check this in a near future), this can be helpful to free up some space.

Optimize your T-SQL code

Bad programming techniques, missing indexes, not using search arguments, can lead to poor performance queries, in a big database can lead to queries, reports, processes that take a long time to complete, use additional transaction log space and also use additional tempdb space, all of these thinks are bad for a high response database, so always identify your top resource consuming queries and try to optimize them.
Also as stated above, separating your tables across partitions/filegroups/datafiles/databases can be helpful to overall performance.

Reorganize and rebuild indexes

If your database is small, this is not an issue, but for big databases, think twice before reorganizing/rebuilding an index, this can be a high resource/high time consuming task, and transaction log of your database, as the tempdb size can significantly increase in matter of minutes.
Avoid using maintenance plans for big databases and implement specially tuned index and statistics maintenance jobs, that can determine if the maintenance is really needed for a particular object.


Please be aware that all of the described above are suggestions with no special order, and can drastically vary from one environment to another, so always keep in mind this!

Saturday, September 9, 2017

SQL Server 2017: Some changes in transaction log monitoring

If your database is configured with the Full recovery model, the transaction log (.ldf files) stores all the transactions done to the database for recovery purposes.
If you fire certain DML commands, the transaction log can grow in matter of minutes and occupy all your precious disk space, that can lead to new commands not able to be executed against the database.

As a new feature in SQL Server 2017 we will have 2 new commands that can help us to monitor the log usage: sys.dm_db_log_stats and sys.dm_db_log_info

sys.dm_db_log_stats


Usage:

sys.dm_db_log_stats ( database_id ) 

This DMV returns a summary of the information for transaction log files. if used without parameter this will return information of the current database.
This can be helpful in a lot of database administration tasks, for example:
  • Using column log_since_last_log_backup_mb you can fire a tlog backup when the log reaches certain size.
  • With log_backup_time you can know the date of the last log backup.
  • Obtain the VLF count with active_vlf_count.
  • Know the log hold reason with log_truncation_holdup_reason, among other things. 

For a full list of columns returned, please visit the official documentation here 


sys.dm_db_log_info


Usage:

sys.dm_db_log_info ( database_id ) 

This DMV returns the VLF information for the database id specified, or the current database if no parameter is used.
It replaces the DBCC LOGINFO command with a more robust and now, documented option to manage VLF information, besides you can now use in custom query build joining it with other tables or views.

For the full column output an definition, please visit the official documentation here

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...