SQL Server Data Management Life Cycle

This is the second article in the “Backup and Restore (or Recovery) in SQL Server” stairway series (see the full TOC below). This article deals with the different phases of data management life cycle and it encompasses the following topics:

  1. Introduction to data corruption
  2. Defining data corruption and its causes
  3. Discussion on the impact of data corruption
  4. Explaining data prevention mechanisms
  5. Data protection
  6. And more…

Introduction to Data Corruption

Data is said to be corrupted when it attains an undesirable state different from the original data. Data corruption also refers to data errors that can occur during any of the stages of data writing, data processing or even reading, at the storage, or during transmission.

We all know that at the very core, data is nothing but a series of 1s and 0s. When data is written, what actually happens is that the 1s and 0s are written to the destination. This sequence of 1s and 0s is interpreted during the data reading operation, giving it some meaning. If these bits don’t get written in the intended sequence, the data is said to have been corrupted.

Small data means smaller sequences and relatively lower chances of corruption. When the amount of data increases, the chances of corruption also increases. The chances are particularly high when data generation is high, and the subsequent storage operations hit unprecedented rates.

Some of the most common reasons for data corruption include hardware or software issues with regard to I/O subsystems.

Causes of Data Corruption

  • Hardware issues or failures
  • Memory issues
  • Power failure or outages
  • I/O subsystem
    • SAN controllers
    • RAID controllers
    • Disk drivers
    • Bad sectors on the disks
  • Operating System errors
  • Virus attacks
  • Antivirus, defraggers, even data encryption,
  • SQL Server bugs
  • Human errors
  • Improper shutdown
  • Hard reset

Further reading SQL Server Data Management Life Cycle

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!




Posted in SQL | Tagged , , , , , , | Leave a comment

Experiment with SQL Server and DBFS on Linux

Given that Microsoft now considers Linux a ‘first-class citizen’, SQL Server 2017 offers native support for Linux. It is engineered to be cross-platform, and that’s good news for Linux administrators and open source developers. Microsoft’s support for Linux (and open source in general), has come into clearer focus, and the company’s mission now seems to be all about bringing its tools to wherever its users are.

Microsoft has been known for proprietary software, and this openness to the open source community came as a surprise; the development is quite recent, and one in the right direction. SQL Server has had a myriad of GUI and CLI tools. These tools have also, along with SQL Server itself, have become cross-platform. These tools are almost equally as good on Linux as Windows, and they help effectively manage, develop, and administer SQL Server. Some of the tools are:

  • SSMS
  • Sqlcmd
  • Bcp
  • Sqlpackage
  • Mssql-conf
  • Mssql-scripter
  • DMV tool
  • SQL Operations Studio
  • MSSQL-Cli
  • SQL Extension for VS code

At first, it seems like a myriad of them. Taking one piece at a time helps with understanding the tools. Let us try to classify these tools based on what they are useful for:

Further reading

SQL Server and DBFS


  • Support for Custom Query execution
  • Data can be viewed as JSON
  • Bash supports utilities such as cut, grep, sed, and awk, etc.
  • Projection of the live/real-time DMV data
  • Cross-platform tools which work on both SQL Server on Windows as well as SQL Server on Linux
  • DBFS is available for Ubuntu, RedHat Enterprise Linux, and CentOS as of writing this article.

Happy Learning!


Posted in SQL | Tagged , , , | Leave a comment

Importance of Memory Setting in SQL Server

Memory is one among the resources forming the performance triangle—CPU and storage being the other two. If one is hit, the other two take the load to try to bring the performance to acceptable levels, but there’s always the trade-off. Whatever transactions cannot be committed to the memory they would be forwarded to the disk subsystem by SQL Server. This causes a performance bottleneck. Therefore, the wait statistics can help identify performance issues on a SQL Server.

In this article, the following topics are discussed:

  1. Understanding internals of SQL Server memory setting and configuration
  2. The SQL Server memory and its impact on the database and application performance
  3. Discuss various SQL Server components that contribute to the memory usage
  4. Best practices and recommendation for memory sizing
  5. Multi-server memory report
  6. And more…

Memory management internals

SQL Server has a Memory Management Unit that performs automated dynamic memory management based on the workload of the system. This memory is the volatile space that’s critical to today’s Business – Tech needs, the right-sizing of which is vital to the optimal performance of the applications.

However, we all know that when setting up the server, the sizing contains some default values. in some cases, we soon find out that SQL Server uses almost all of the memory on the server, even though there’s no visible activity on the databases, bringing in the questions: Are the default values incorrect? If so, what should be the right size?

Memory Management on SQL Server works on the Fill-and-Flush algorithm. The default values do not restrict the memory consumption from growing unless there’s a request from the Operating System.

The sizing depends on various components of the system—in many cases, setting it between 70% and 80% is a good starting point. Then, you should also monitor it to see what else you may be missing and if you should tweak the setting. If you have other services on the SQL Server (you really shouldn’t), you may need to leave behind more, especially if these services are memory hogs. Consider revisiting the memory setting of the SQL instance in any of the following scenarios:

  • Unresponsiveness of the Operating System
  • Application exhaustion
  • Backup operations that require large memory buffers
  • In-Memory Optimized objects
  • Column store indexes, since they require large volumes of memory to perform index maintenances.

The memory setting on SQL Server is pretty straightforward. You can change the value using sp_configure or SSMS GUI. This is an online option but remember that setting or resetting these values may cause some of the internal cache objects to reshuffle, which will leave the system running slightly slower.

further reading

Importance of memory setting in SQL Server

Happy Learning!



Posted in SQL | Tagged , , , , | Leave a comment

SQL Server RLS feature and GDPR

Of late, there’s been a lot of noise around the term, GDPR. Chances are, some of us even had to go through learning sessions targeted at IT professionals to learn about what this new standard of data protection means. GDPR is primarily a European privacy law which sets a new bar, globally, on privacy rights, compliance, and security. GDPR is mainly about protecting the rights of every individual, providing the individual with more control over his personal data. It dictates how data should be handled, managed and protected going forward, the individual’s choice being the prime focus.

Today, data is widespread; many corporations handle part of the data on the cloud and part of it on premises. Our focus being SQL Server, we shall talk about what capabilities Microsoft gives us in order to be compliant with these laws that come into effect on the 25th of May, 2018. We would have to modify our data handling procedures keeping the focus on the security of the data processing.

There are several built-in security capabilities in SQL Server to help in reducing risk and an overall improvement in managing data at the database level or otherwise.

Now that we have a basic idea on GDPR, let’s now dive a little deeper. Here are a few points to keep in mind:

  1. Discover: Identify which data is of personal nature, and technical details about it such as its location and the mode of storage.
  2. Manage: Classify the data access needs and decide the governance model accordingly.
  3. Protect: set up security controls to prevent vulnerabilities and also detect and respond to data breaches.
  4. Report: Document and manage data requests, and provide notifications in case of breaches.

Following are the features in SQL Server that support GDPR compliance:

  1. Row-Level Security (RLS)
  2. Dynamic Data Masking (DDM)
  3. Transparent Data Encryption (TDE)
  4. Transport Layer Encryption (TLS)
  5. SQL Server Audit
  6. Temporal Tables
  7. Always Encrypted (AE)
  8. Authentication
  9. Azure vault
  10. Azure Active Directory
  11. SQL Threat detection

GDPR can be further classified into several categories as follow:

  1. Encryption
  2. Pseudonymous data
  3. Data access, authorization and limitation
    • Row-Level Security (to be discussed in detail in this article)
    • TDE
    • Azure Active Directory
    • Always Encrypted
  4. Assessment, reporting and notification
    • SQL Server Audit
    • SQL Threat Detection

Further reading

SQL Server RLS  and GDPR

Wrapping up

In this article, we walked through the filter and block predicates. We went step by step to provide the required access to users and also, isolate the data operations from various users. This feature greatly simplifies the data security design and helps go closer to implementing GDPR, by enabling us to manage the application access model effectively.

Posted in GDPR, SQL Server 2017 | Tagged , , , , , , | Leave a comment

An overview of SQL Server backup-and-restore process

In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving data backup. In other words, it’s about how we ensure to retain the business data through any sort of situation that’s thrown at us.

Backup is probably the simplest and the most familiar process in most situations. A backup is a copy of the data derived from the production copy, stored at a location that’s different from where the production data is stored. A backup copy is used to recover data needed to restart an application correctly, after certain types of failures. Using various tools and techniques, the data is replicated to different geographies and is made available all the time. Some may even argue that having multiple working copies of the data somewhere else removes the need for having backups, but in a not-so-perfect world, backups are still required to make sure you can always recover from data mishaps.

Further reading

SQL Server backup-and-restore


As a general rule, the amount of time in between backups should be no more than the amount of time you are willing to spend redoing any lost work. For example, if spending a week recreating the lost data is too long for you, you should back up the data at least once a week.

The additional techniques that we mentioned for a zero-loss model are called High Availability in most situations. These may be very expensive for some organizations. On the other hand, backups are more economical to run. Therefore, because of our budget constraints, sometimes we have to live with the fact that we will lose a few minutes of data.

The recovery point objective and recovery time objective are metrics that are usually decided by the business. Several aspects of the business are taken into consideration when coming up with these metrics. Once these objectives are decided, these must be clearly communicated to the stakeholders (IT and otherwise) within the organization.


Posted in SQL | Tagged , , | Leave a comment

SQL Server – Internals of sp_spaceused

This article is an effort to dissect the output of the sp_spaceused stored procedure.


Understanding the database usage internals and the growth trends play a vital role in the defining the right sizing of the database. sp_spaceused is probably an administrator’s most widely-executed system stored procedure to find the disk space used by a database. This helps get a quick glimpse of the database usage. statistics. sp_spaceused is used to display the number of rows, the data size, index size, amount of used space, unused space by each object, and the unallocated size of the database. Although looking at the values given by sp_spaceused, one shouldn’t think of shrinking the database or data file or log file. Many a time, we are unaware of what we are doing. Many a time, we don’t know what would be the aftereffects of doing such resource intrinsic operations. The output of sp_spaceused tells us a lot about the current performance of the database. The unallocated column and the unused column tell us the free space left at the database and the table levels.

This article considers:

  1. A peek into sp_spaceused
  2. Impact of the auto-growth setting on the columns, unallocated and unused
  3. Finding the space usage details at the database and the instance levels
  4. Measuring the auto-growth events
  5. Finding the mdf and ldf file sizes
  6. Factors determining the performance of the database
  7. And more…

Further reading, click the following link sp_spaceused

Thanks for reading my space!!


Posted in SQL | Tagged , , , , , , | Leave a comment

Know How to Recover SQL Server Database Without Backup

Microsoft SQL Server has widely used database management system for storing and retrieving data. It stores its files in MDF, NDF and LDF format. If SQL database lost due to disasters then backup acts as a silver lining. That is why MS SQL administrators always suggested to take backup regularly. But, data loss can occur anytime due to multiple known and unknown causes. Under such circumstances, if a user has MDF and LDF file as the backup then it is easy to restore the deleted and lost database back to its original state. Otherwise, repairing corrupt SQL files, tables, and other components can be a tedious task if users do not follow the right technique. There are manual commands available that shows how to recover SQL server database without backup but an authentic third-party software is always the best approach to do it.

Method to Recover SQL Server database without backup

If you do not have backup and you need to recover SQL database without backup. Then, in order to recover corrupted tables in SQL database, there are some commands are mentioned below which helps you to recover database manually.

DBCC CHECKTABLE (‘table_name’)
To check entire database in MS SQL Server, this command will require to be used by command line or query manager.

DBCC CHECKDB (‘database_name’)
Both REPAIR_ALLOW_DATA_LOSS and REPAIR_FAST commands have the following repair options:

REPAIR_ALLOW_DATA_LOSS – This command performs allocation & deallocation of rows, pages for correcting allocation errors, structural row or page errors, deletion of damaged text objects etc.

But, these repair command can lose some data. So, this command should be performed only when all methods do not work. The recovery may be done under a user transaction to permit the user to roll back the changes made. If repairs are rolled back, and the database will still have errors, then it should be restored from the backup. Once, recovery process is completed, then you will get back up of the database.

REPAIR_FAST – This command performs minor and non-time-consuming repair actions like repairing extra keys in non-clustered indexes. These repairs can be done fast & without risk of any data loss.

Professional Solution to Recover SQL Database Without Backup

When the manual approach is not able to repair SQL Server Database, generally when the corruption level is severe and users do not have the backup file. In this situation, users are looking for a third-party solution which can smartly handle any level of corruption without backup file. Thus, SQL recovery software is the best tool to recover SQL Server database without backup. The software is extraordinarily programmed to recover all minor & major MDF file corruption issues with accuracy. Moreover, this utility has the ability to restore all SQL database objects such as Tables, Functions, Triggers, Stored Procedures, Keys, Views, Rules, Indexes etc. This software also ensures the quick results without any risk of data loss.

Recover database without backup


If a user has not the backup of SQL database and he or she wants to know how to recover SQL server database without backup. Then, there are both manual method and automatic solution discussed in this blog. But, in case of mass corruption, the manual command may fail to perform the recovery process. To overcome such situation, it is advised to take help from professional solution i.e SQL Recovery Tool. It is very easy and effortless utility.

Posted in Backup and Restore, SQL 2016 | Leave a comment

SQL Server 2017 DMVs and DMFs for DBAs

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

The new or enhanced DMV’s/DMF’s fall into the three categories:

  • Database related
    • sys.dm_db_log_stats
    • sys.dm_db_log_info
    • sys.dm_db_stats_histogram
    • sys.dm_db_file_space_usage
    • sys.sys.dm_db_tuning_recommendations
  • Transaction related
    • sys.dm_tran_version_store_space_usage
  • SQL Server Operating System related
    • sys.dm_os_host_info
    • sys.dm_os_sys_info

Further Reading

SQL Server 2017 new(or Enhanced) DMVs and DMFs

Wrapping up

The overview of new dynamic management views and dynamic management functions that ship with SQL Server 2017 have been explained in detail. They can be put to use to get more insight into the status of the system. It can be informative and useful for the administrators to understand the metadata pertaining to the SQL Server instance. To me, the sys.dm_db_file_space_usage and sys.dm_db_tuning_recommendations turns out to be something I was looking for, for a long time. How about you? Please feel free to comment!

Posted in SQL 2017, T-SQL | Tagged , , , , , , , | Leave a comment

How to fix the database consistency and integrity issues using DBCC CHECKDB and Third Party Tool


The database protection and recovery is a vital role of database administrators. While working on SQL Server, sometimes users get stuck in annoying situations when the database becomes inaccessible. After executing DBCC CHECKDB command to check the problem, the user gets database consistency errors

Many companies may have the budget to have a database recovery solutions in place to protect the system from various disasters.

I would also like to discuss the third party tool Stellar Phoenix SQL Database Repair software from Stellar Data Recovery as a recovery solution.

The DBCC CHECKDB Consistency Errors behind the doors

The reason behind the database consistency error can be varied from

  1. file system corruption
  2. corrupted pages in memory
  3. underlying the hardware system issues
  4. drive issues
  5. or some problem with SQL Server Engine

How to Fix Database Consistency Errors Reported By DBCC CHECKDB?

DBCC CHECKDB checks the physical as well as the logical consistency of the database pages, rows allocation pages, system table referential integrity, index relationship, and other structure. If any of these checks fail then, errors will be described as part of the command. To resolve the inconsistency errors, users first need to restore the data from the backup of data. However, if users are not able to restore from backup then CHECKDB gives a feature to repair errors. Many times, the problem arises due to file system or hardware issue so the user should correct these first before restoring and running repair. There are some resolutions that discussed below to help users to fix SQL database consistency errors.

Windows System Event Log

Windows System Event Log errors indicate possible I/O problems that can be associated with the inconsistency of database. It contains various Event IDs depend upon the inaccessibility of data. Every event ID has different resolution to fix the occurrence of an error.

Integrity of File System

Many times, the users face the inconsistency error due to the file system integration. To check the integrity of file system, the user can use the chdsk command. This command helps to create and displays a status report for the disk. It also lists all the errors on the disks. It is available with different parameters from the command prompt.

I/O Requirements

There are various counters, which are related to I/O and all are located in Logical and Physical Disk. The physical Disk performance object consists of counters that monitor hard or fixed disk on the system. The logical disk performance of an object consists of counters, which monitors the logical portion of fixed or hard disk drives.

SQLIOSim Utility

There is a utility known as SQLIOSim that also reports the consistency errors. SQLIOSim is a tool, which is independent of SQL Server Engine for testing the integrity of I/O for the disk system. It can be downloaded from the web for utilization.

Note: SQLIOSim ships on SQL Server 2008 does not require the separate download.

Verify Checksum Option

Make sure that the database, which is using PAGE_VERIFY CHECKSUM option. If the checksum error is reported then, it indicates that the consistency error has occurred. After writing the SQL Server, pages to disk the error occurred so that the disk system should be checked thoroughly. The user gets 824 error in SQL Server, which is caused due to some external conditions. Some external conditions have caused the modification on database page outside the SQL Server engine code. It can be resolved if the user running any hardware or system checks to determine if CPU, memory or other hardware related issues exist. The user can update all system drivers, Operating system, or hardware is required on the system.

Minimum Repair Option

When the user runs DBCC CHECKDB, a recommendation is used to indicate the minimum repair option, which is essential to repair all errors. Its message appears as mentioned below:
CHECKDB found 0 allocation errors and 15 consistency errors in database 'adventureworks'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (adventureworks

The repair recommendation is the minimum level of repair to attempt to resolve all the errors from CHECKDB. It does not mean that all the errors are fixed by repair option. It means that all the errors that are not reported by CHECKDB. When repair_allow_data_loss is recommended will result in loss of data. The repair must be run to determine if there is the resolution of an error that will result in data loss. It is a technique to help narrow down if the repair level for each table is to access DBCC CHECKTABLE for any table that is reporting an error. It will display the minimum level of repair for given table.

Third Party tools

To download the SQL recovery tool and test with the free Demo version of the software and then proceed.

I searched online for this product, conducted a thorough research on software and decided to download its free version.

This is how to proceed:

Check Software Specifications

Checked with software specification of Stellar Phoenix SQL Database Repair software:

Operating System
Windows Server 2012 / 2008 / 2003 and Windows 10 / 8 / 8.1 / 7 / Vista / XP
Memory 1 GB
Hard Disk 50 MB
Version Supports MS SQL Server 2016, 2014, 2012, 2008 R2, 2008, 2008*64, 2008 Express, 2005, 2005*64, Express, 2000, 2000*64, 7.0 and mixed format

Install Software on the system

  • Download the software here 
  • After the download, run the Setup Wizard
  • Accept the License agreement
  • Specify the target location for the installation
  • Click Next
  • Stellar Phoenix SQL Database Repair software was successfully installed and ready to use.

Test software on the system

Open the software Stellar Phoenix SQL Database Repair Software.The software GUI interface is simple and self-explanatory to perform the proceeding steps.

  • Click on Select Database
  • Locate the file using find database submenu. The MDF-extension related files opened and prompted for selection of the correct file.10
  • Next, the Stellar software scanned through MDF file and provided the scanned preview of repaired SQL Database for verification.


  • Proceeded with SQL database verification and saving file at preferred location.


  • Software provided various saving options and my preference rested on MDF format.

SQL Database was saved as MDF file in original format.

In a nutshell, the whole process of Database recovery and resolution of Suspect mode problem was completed with the click of three buttons –

Select file —- Repair File —– Save File



In the above discussion, the problem and cause of the consistency error are described. Along with this, solution on to how to troubleshoot database consistency errors reported by DBCC CHECKB is discussed. It makes easy for users the error occurrence.

The primary responsibility of the database administration team is to review all types of RDBMSs in the enterprise and to develop a comprehensive backup plan to conduct effective backup management by proactively monitoring backups, getting alerted for failed backups and rerunning these seamlessly, without loss of time. It is good practice to back up data to physical disk and to then archive the data to tape for disaster recovery purposes.

Stellar Phoenix SQL Database Repair software is a third party tool and dissolves any kind of Database related distress for SQL Administrator. I would recommend trying this tool.


Posted in Uncategorized | Leave a comment

Python and SQL Server Administration

Some of my previous articles on Python provided insight of the basics and the usage of Python in SQL Server 2017.

This article is an effort to collect all the missing pieces and try to showcase the importance of using Python programming in SQL Server.

Many say that PowerShell has the upper hand over Python in some aspects of the database administration. I too am under the same impression as many technical enthusiasts, but with very limited knowledge, we can also see the power of Python. Perhaps, instead of pitting PowerShell and Python against each other, we can look at them as complementing technologies.

In 2016, R, the statistical computing programming language was integrated with the SQL Server version, named for the same year. The other side of the coin was missing since Python is also a leading machine learning language and even having a large user base. In SQL Server 2017, Python is integrated. Now, R and Python are under the same umbrella of the feature called machine learning services.

Further reading

Python in SQL Administration

Happy Learning!!



Posted in Python, SQL 2017 | Tagged , , , , | Leave a comment