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

Different methods to write PowerShell output to a SQL table

PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.

I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.

When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.

Let’s get out and see the possible options to transform WMI data to SQL table. In this article, we will:

  1. discuss Invoke-Sqlcmd
  2. talk about the .NET class libraries
  3. talk about exporting data using various Export* cmdlets
  4. learn how to use Windows Management Instrumentation (WMI)
  5. discuss SQL Constructs to load data from file
  6. and more

This guide details the working example of checking disk space by querying WMI.

We discuss the transformation of the above data into a SQL Table using some direct as well as indirect methods in this post:

  1. using Invoke-Sqlcmd
  2. using ADO
  3. WMI Query
  4. using Export commands such as JSON,XML and CSV


Further reading

PoSH->Data Transformation -> SQL Table

Happy Learning!!


Posted in PowerShell, SQL, T-SQL | Tagged , , , , , | 2 Comments

Configure SQL Server 2017 on Linux with mssql-conf, the Linux way

The configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.

SQL Server on Linux provides a wide range of options for configuration, management, and administration.

  1. mssql-conf
  2. Transact-SQL
  3. SQL Server Management Studio
  4. PowerShell

This article outlines the use of mssql-conf in conjunction with other available tools, to manage SQL Server.

On Windows, we are familiar with SQL Server Configuration Manager, the configuration tool used to manage and administer SQL Server services, configure the network protocols, and to manage network connectivity between the SQL Server and the client computers.

On Linux, we have a configuration tool known as mssql-conf. The primary function of this tool remains the same as that of the SQL Server Configuration Manager, but makes it easy for Oracle and Linux admins to work with SQL Server 2017 on Linux. I’d like to visit some of the key concepts of the Oracle parameter file (PFILE and SPFILE) here, with which the server configuration can be set/unset by adding/modifying entries in the configuration file.

Further reading…


Happy learning!



Posted in SQL 2017 on Linux | Tagged , | Leave a comment

An introduction to a SQL Server 2017 Graph database

What is a graph database?

A graph is composed of two elements: a NODE (vertices) and an EDGE (relationship). Each node represents entities, and the nodes are connected to one another with edges; these provide details on the relationship between two nodes with their own set of attributes and properties.

The graph database can be defined as the data structure representation of an entity modeled as graphs. It is derived from the graph theory. The data structures are the Node and the Edge. The attributes are the properties of the node or the edge. The relationship defines the interconnection between the nodes.

Relationships are prioritized in graph databases, unlike other databases. Therefore, no data inference using foreign keys or out-of-band processing is needed. We can build sophisticated data models simply by assembling abstractions of nodes and edges into a structure. Given the priority for relationships over data, the development stack receives the biggest value here.

In today’s world, relationship modeling requires more sophisticated techniques. SQL Server 2017 offers graph database capabilities to model relationships. Graph DB has nodes and edges—two new table types NODE and EDGE. And a new TSQL function called MATCH(). The Node and Edge (relationships) represent entities of the graph database. And since this capability is built into SQL Server 2017, already-existing databases don’t have to be ported to another system, so to speak.

Further reading…

Graph database

Stay tuned for more updates on this topic….

Posted in Graph database, SQL 2017, Uncategorized | Tagged , , , | Leave a comment

SQL Server 2017 : Data Transformation and Interpolation using Python

As a continuation to my previous article, How to use Python in SQL Server 2017 to obtain advanced data analytics, a little bit of curiosity about Deep Learning with Python integration in SQL Server led me to write this latest article.

With Python running within SQL Server, you can bring the existing data and the code together. Data is accessible directly, so there’s no need to extract query data sets, moving data from storage to the application. It’s a useful approach, especially considering issues of data sovereignty and compliance, since the code runs within the SQL Server security boundaries, triggered by a single call from T-SQL stored procedures.

Continue reading…

Data Interpolation and Transformation using Python in SQL Server 2017

See Also,

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 2017, SQL and Python | Tagged , , , , | Leave a comment

SQL Server Log Shipping on Linux

Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS.

On a high level, Log Shipping consists of preparing the secondary database (NORECOVERY or STANDBY) using a backup of the primary instance database and then applying the transaction logs from the primary database onto the secondary database using SQL Server Agent Jobs and a shared file location. In other words, it’s an automated process of restoring transaction log backups from the primary database server on to the secondary database/database server. This way, the database is available for failover.

We’ve seen multiple models of implementation of log shipping: log shipping on the same server, on to a different server, across domains or workgroups, or two different versions of SQL Server. SQL Server, now being available for Linux, makes us think, “Can we set up log shipping across platforms?”

As it turns out, yes, we can. This article discusses the setup. We’ll use a network file share for the demonstration. Let’s jump right in!

Continue reading….

Log shipping on Linux

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 Logshipping on Linux, SQL 2017 | Tagged , , , , | Leave a comment