Management tools play a vital role in enterprise database management. This is because the well-integrated tools extend the administrator’s capabilities, whereas a random collection of tools can lead to confusion, operational mistakes, high training costs and poor database administrator (DBA) productivity.

To keep up with these new demands of complex… Read more

From time to time, I see the following question posted on various SQL Server forums by different users asking why the msdb database on their SQL Server instance is too big. As we all know, msdb is a system database that supports SQL Server Agent service, and stores all the… Read more

Checkout the fifth part of my XML support and SQL Server article series here.

In this article, I discussed the basic XML query methods for XML data manipulation. I also gave an brief introduction to the XQuery language, and demonstrated how to use the query(), value(), exist(), nodes(), and… Read more

Today, I’ve received an email from friend asking that that is there any way to find out the progress of following operation using T- SQL query. As we know, with the release of SQL Server 2005, Microsoft provides set of dynamic management views (DMVs) which helps ease the administration SQL… Read more

This dynamic management function (DMF) returns the detailed information about low level activities on indexes such as input/output (I/O) operations, locking and latching contention, and access methods.

You can also use sys.dm_db_index_operational_stats function to find information such as how long the users must wait to read or write to a… Read more

Today, we experienced performance issues with few databases that are hosted on one of our most critical production SQL Server. Upon reviewing the query execution plan and querying index related dynamic management views (DMVs), I noticed the problem is related with potential missing indexes on columns. The index related dynamic… Read more

Today, I received a call from friend asking how he can find out which database object belongs to which filegroup.

Well to find out this information can be achieved by writing a simple query using the following system catalogues: sys.filegroups, sys.indexes, sys.database_files and sys.data_spaces.

For example, here… Read more

Today, I am very happy because my first article (SQL Server Encryption) was published in SQL Server Pro. In this article, after providing an overview of the SQL Server encryption model, I described the encryption options available in SQL Server and how you can use them to… Read more

Virtualization is a hot trend in the computing world, offering businesses substantial cost and performance benefits that include server consolidation, reducing power consumption and the effortless creation of virtual development and test environments. As a result of this trend, many organizations are interested in virtualizing SQL Server, and for a… Read more

Checkout the fourth part of my XML support and SQL Server article series here.

In this part, I talked about XML data type (typed and untyped) and its limitations. I also demonstrated how you can create XML variables and XML columns, and compare between typed and untyped XML.

This… Read more

This is the third part of the three part article series on partitioning relational data. 

In the first part of this three-part article series on partitioning relation data, I started the article by giving an introduction to partition relational data along with the reasons why you might want to partition… Read more

Checkout the third part of my XML support and SQL Server article series here.

In this part, I talked about the fundamental problems that we face when integrating XML and SQL Server. Then I talked about the storage options that are available in SQL Server to store XML data.… Read more

Today, I received an email from the development team complaining why they have not received any of the SQL Agent job notification emails today. Upon checking the SQL Agent error log, I see the following error message:

[264] An attempt was made to send an email when no email session… Read more

This is the second part of the three part article series on partitioning relational data. 

In the first part of this three-part article series on partitioning relation data, I gave an introduction to partition relational data along with the reasons why someone might want to partition relational data. Then I… Read more

From time to time, I see the following question posted on various SQL forums asking how we can determine the (available / assigned) amount of memory for an SQL Server instance / machine. As we understand that there is no common script or procedure, which we can use in all… Read more

A common requirement for dealing with large datasets is the ability to split the data into smaller blocks to help improve performance. Performance degradation is evident once the tables reach larger sizes, and therefore, it is sometimes appropriate to break large tables using a process called partitioning or horizontal partitioning,… Read more

When creating XML documents, you must work from a design that defines the structure of the document. This strengthens the structure and design. If you need to send the document to others, especially from other companies, they need the document format. Without a formal design of the XML document format,… Read more

You can use the sp_spaceused system-stored procedure to return space usage information about a database or a table within a database. The syntax for this procedure is:

sp_spaceused [@objectname=['objectname']]
[,[@updateusage=['TRUE|FASLE']]]

You use the objectname parameter to enter the name of a table, indexed view, or Service… Read more

Database infrastructure security is extremely crucial for any organization, which is why Microsoft has invested heavily in SQL Server security features in recent years. Microsoft redesigned the SQL Server database security engine from scratch for SQL Server 2005, and since then, SQL Server security has improved with each update.

Microsoft… Read more

Today, I received a phone call from a friend who is thinking to downgrade the database hosted in SQL Server 2008 Enterprise Edition to SQL Server 2008 Standard Edition. He made me question whether this type of move is possible and is there any way for him to test the… Read more