How to find the amount of space used by data (.mdf, .ndf) and log (.ldf) files using TSQL

Well, this is a pretty simple question, and many of you out there already know how to do it. SQL Server 2005 introduced a number of system views that gives the size occupied by each data/log file in the database. So it is a matter of just querying one of those system views and read the "size" column.

We were trying to configure an alert when the file size goes above a given limit and realized that the "size" column actually stores the number of pages. Each page is 8 KB. So to get the actual size of the file in KB, you need to multiply the value in "size" column with 8.

Read the article: http://blog.sqlserver.me/2008/10/how-to-find-space-used-by-data-mdf-ndf.html

Continue reading “How to find the amount of space used by data (.mdf, .ndf) and log (.ldf) files using TSQL”  »»

SQL Server 2005/2008 - Cryptography in SQL Server

Summary: Although cryptography provides SQL Server with powerful tools for encryption and verification, these are often not well understood. This can lead to poor or incomplete implementations. This white paper presents an overview of cryptographic functionality and discusses how this applies to authentication, signed procedures, permissions, and encryption. Because the target audience is the database professional and not necessarily security experts, the focus is on practical ways to use cryptography in SQL Server.

http://msdn.microsoft.com/en-us/library/cc837966.aspx

Continue reading “SQL Server 2005/2008 - Cryptography in SQL Server”  »»

SQL Server 2008 SSMS Enhancements

I have written a few articles at www.sqlservercentral.com on the new features and enhancements added to SSMS in SQL Server 2008.

Read at:

http://blog.sqlserver.me/2008/10/sql-server-2008-ssms-enhancements.html

Continue reading “SQL Server 2008 SSMS Enhancements”  »»

SQL Server SET Options: Do not depend on the DB level settings

We have examined some of the SET options of SQL Server in the previous posts. We saw how these SET options controls the behavior of SQL Server in different cases.

We saw that some of the settings can be done at the database level. When an option is configured at the database level, all queries that you run on that database will inherit the setting, unless you change the option explicitly.

However, be aware that your data access library or the tool that you use to run your queries (SSMS, Query Analyzer etc) might change the SET options while establishing a connection to the server. They might change the SET options for the current session and when you run your queries, you might find a different behavior than expected.

Read the article here: http://blog.sqlserver.me/2008/10/sql-server-set-options-do-not-depend-on.html

Continue reading “SQL Server SET Options: Do not depend on the DB level settings”  »»

Understanding SQL Server SET Options 4 - SET ARITHABORT ON | OFF

Part 4 of 'Understanding SQL Server SET Options' explains SET ARITABORT. ARITHABORT, along with ANSI_WARNINGS controls the behavior of SQL Server when it encounters an 'arithmetic overflow error' or 'division by zero error'.

You can find the post here: http://blog.sqlserver.me/2008/10/understanding-sql-server-set-options-4.html

Continue reading “Understanding SQL Server SET Options 4 - SET ARITHABORT ON | OFF”  »»

Understanding SQL Server SET Options 3 - SET ANSI_PADDING ON | OFF

I started writing a series of posts on the SET options of SQL Server. I just posted a post on ANSI_PADDING. You can find the post at the link given below:

http://blog.sqlserver.me/2008/10/understanding-sql-server-set-options-3.html

Continue reading “Understanding SQL Server SET Options 3 - SET ANSI_PADDING ON | OFF”  »»

UNPIVOT Example 2

We saw an example of UNPIVOT in the previous post. Let us see one more UNPIVOT example in this post

http://blog.sqlserver.me/2008/10/unpivot-example-2.html

Continue reading “UNPIVOT Example 2”  »»

XQuery Lab 38 - Delete elements having date value older than 30 days

In some of the previous posts, we saw how to delete an element or attribute from an XML document (variable/column). In this post, let us examine one more example that deletes elements from an XML document matching a given criteria.

http://blog.sqlserver.me/2008/10/xquery-delete-compare-date.html

Continue reading “XQuery Lab 38 - Delete elements having date value older than 30 days”  »»

XQuery Lab 37 - Working with multiple namespaces

We have seen several XQuery examples in the previous posts in this series. We briefly discussed XML namespaces in a couple of posts earlier (XML Namespaces, SQL Server 2005 XML and Default Namespaces). Some times, reading values from an XML document having namespace declarations might seem little tricky.

This post demonstrates how to read values from XML documents having multiple namespace declarations. http://blog.sqlserver.me/2008/10/xquery-with-multiple-namespaces.html

Continue reading “XQuery Lab 37 - Working with multiple namespaces”  »»

Using TSQL Function: OBJECT_ID()

Many of you must have found the OBJECT_ID() function very handy. It is particularly helpful to check the existence of an object. SQL Server 2005 added a second optional parameter to specify the object type that helps you to check if the object is a table, procedure etc and can generate the drop statements accordingly. This function can also be used to check the existence of global and local temp tables. You can find a few interesting points about this function here: http://blog.sqlserver.me/2008/10/using-tsql-function-objectid.html

Continue reading “Using TSQL Function: OBJECT_ID()”  »»

SQL Server - Recursive CTE and Ordering of Hierarchical Result

WE discussed Recursive CTEs in many of the previous posts. Most of the times we use a recursive CTE to generate a tree/hierarchy and the order of rows are very important. Each child row should be placed right under the parent node. If this ordering is not maintained, the result may look incorrect. I have written a post that explains how to achieve this order. You can find the post here: http://blog.sqlserver.me/2008/10/ordering-hierarchical-data.html

Continue reading “SQL Server - Recursive CTE and Ordering of Hierarchical Result”  »»

SQL Server - Performing Bottom to Top recursion using CTE

You must have noticed that I kept posting about recursive updates several times in the last few weeks. This is the last post in the series that demonstrates bottom-to-top recursion using a CTE. In the earlier posts, we were discussing different ways to recursively update the count of items under each category/sub-category etc. This post presents two options using a bottom-to-top recursive CTE. One option presents how to do this by updating a column of the table and the other example demonstrates how to handle it in the SELECT query itself.

You can find the post here.http://blog.sqlserver.me/2008/10/bottom-to-top-recursion.html

Another post that might interests you is:
"How to find differences between two dates in HH:MM:SS format?"    (http://blog.sqlserver.me/2008/10/tsql-lab-19-how-to-find-differences.html)

Continue reading “SQL Server - Performing Bottom to Top recursion using CTE”  »»

Recursive updates in SQL Server (continued...)

I shared with you a few posts, last week, discussing recursive updates in SQL server. We discussed the example of an ecommerce application that needs to display the total count of items under each category. Each category may contain sub-categories and sub-sub-categories etc.

I have made one more post in the same series that uses a recursive CTE to simplify the code I presented in the previous post. http://blog.sqlserver.me/2008/10/sql-server-recursive-update.html

Continue reading “Recursive updates in SQL Server (continued...)”  »»