Finding Last Backup Date in SQL Server 2005

I saw a question on the SO forums asking for a simple way to find the last backup date of a SQL Server 2005 database. Here's one approach.

During a backup operation, the following tables are updated in SQL Server 2005:

• msdb.dbo.backupfile,
• msdb.dbo.backupmediaset ,
• msdb.dbo.backupmediafamily and
• msdb.dbo.backupset.

Here we are doing a join between sys.sysdatabase and msdb.dbo.backupset which gets us the database name, the last backup date and who took the backup. sys.sysdatabase is queried as it contains one row for each database in an instance of Microsoft SQL Server.

OUTPUT

Feedback:

SQL Server NTILE Function

The SQL Server NTILE() function divides the result set into a specified number of even sized group (approximate division) and assigns a ranking value to these groups.

The NTILE() function is similar to other ranking functions like the RANK() and DENSE_RANK(), except that NTILE takes one parameter of type int/bigint that specifies the number of groups into which each partition must be divided.

Let us see an example. We will query the Products table of the Northwind database and divide it into 5 groups ordered by the UnitsInStock:

OUTPUT

On running the query, the results are divided into 5 groups, but because the total number of rows (48) is not divisible by the number of groups (5), NTILE puts 10 rows in the first three group and the remaining two groups have 9 rows each.

Feedback:

Activity Monitor in SQL Server

Activity Monitor is a handy tool in SQL Server to quickly see performance hot spots in the server, network and database activity. One of the most commonly performed tasks in Activity Monitor is to kill a stubborn connection that will not release its resources. In SQL Server 2008, this tool has undergone a major facelift from the 2005 version and helps in tracking your SQL server performance even better than before.

To view Activity Monitor in SQL Server 2008, right-click the instance name and choose Activity Monitor or click the Activity Monitor icon on the standard toolbar.

In SQL Server 2005, Activity Monitor can be viewed in SSMS by connecting to the server with Object Explorer, expanding ‘Management’, and then double-click ‘Activity Monitor’.

Once opened, the Activity Monitor Dashboard View contains four graphs which can help identity any abnormal activity in the Database. The graphs include % Processor Time (SQL Server), Waiting Tasks, Database I/O and Batch Requests. The default graph refresh rate is 10 seconds, but you change that easily by right-clicking any of the four graphs and selecting the appropriate refresh interval.

This snapshot is very helpful to get a quick performance snapshot without the need to use other monitoring tool for the same purpose.

This dashboard also contains expandable/collapsible panes to view detailed information about Processes, Resources, I/O and Expensive Queries. Just click on the expand button to the right of each pane to view the detailed information. Here’s a quick overview of the different graphs and what they show.

Processor Time - The percentage of elapsed time that the processor spends to execute non-idle threads for the instance across all CPUs. The Processes pane gives information on what processes are running, what resources are being utilized etc. You can even right-click a process and choose Trace Process in SQL Server Profiler. Presto!

 

Waiting Tasks - The number of tasks that are waiting for processor, I/O, or memory resources. The Resource Waits pane details the processes waiting for other resources on the server.  It shows the latest information from several DMVs like the sys.dm_os_wait_stats

 

Database I/O – Information on data and log files for system and user databases. Provides the transfer rate in MB/Sec, of data from memory to disk, disk to memory, or disk to disk. The pane contains information to quickly detect a contention in disk I/O.

 

Batch Requests/sec - The number of SQL Server batches that are received by the instance. The Expensive Query pane lets you find and tune expensive queries. You can even right-click any query and view its graphical execution plan. Also see Find the Most Time Consuming Code in your SQL Server Database

Note: To view the Activity Monitor in SQL Server, a user must have VIEW SERVER STATE permission. Also make sure you close the Activity Monitor tool in SSMS when it is not required.

Feedback:

SQL Server Management Objects 2008 (SMO) New Features

MSDN defines SMO as - SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. In this article we will practically explore some features of SQL Server Management Objects.

To start with, let’s create a ‘Windows Application’ using Visual Studio and name the application as ‘SMOExamples’ as shown below –

Now let’s add the following references to our project.

1. Microsoft.SqlServer.ConnectionInfo.dll
2. Microsoft.SqlServer.Smo.dll
3. Microsoft.SqlServer.SmoExtended.dll
4. Microsoft.SqlServer.SqlEnum.dll
5. Microsoft.SqlServer.Management.Sdk.Sfc.dll

All these references can be found in the path – ‘C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies’.

Now let’s design our windows form as shown below –

Declare an object which will make a connection to our SQL Server instance. For this, we will use a class called ‘Server’ as shown below –

Server srv = new Server(Environment.MachineName);

Now to test the connection to the SQL Server, let’s write the following code on the Form Load event as shown below –

Now let’s create a Database and Table with the name ‘SMOExample’ and ‘SMOTable’ respectively by writing the following code on the click event of ‘Create Database And Table’ button –

For creating a database, we have used a class called ‘Database’ which takes two parameters in the constructor. First one is the SQL Server instance in which we have to create a database. Second is the database name.

Now let’s create a script for all the tables available in our database. Write bee following code on the click event of ‘Generate Script’ button –

For generating the script files we are using a ‘Scripter’ class.

Now let’s write the code to take a backup of the complete database. Write the following code on the click event of ‘Backup Database’ button –

To take a backup of the database, we are using a ‘Backup’ class and to decide what will be the device of backup, we are using ‘BackupDeviceItem’ class.

Now the last thing we will explore is how to verify the backup, which is taken before we restore it to another server/same server. Write the following code on the ‘Verify Backup’ button –

Now run your project. This will show you the date and time created for ‘AdventureWorks’ database as shown below –

Now let’s click the button ‘Create Database and Table’ and observe the results in SQL Server Management
 Studio Object Explorer –



Now click on the button ‘Generate Script’. Go to ‘C:\’ and verify the script file –

Finally click the button ‘Backup Database’ and check your ‘C:\’ drive. Your backup file should have been created. Similarly click on ‘Verify Backup’ button and it will show you a success message.

Summary – In this article we saw few capabilities of SQL Server Management Objects (SMO).

Download the source code

Feedback:

List Empty Tables in SQL Server

I was recently doing a clean up of my website database. I remember creating some tables on my database but never adding any new rows to it.

Here’s a simple query to list all empty tables in your SQL Server database that uses a Dynamic Management View called dm_db_partition_stats which returns page and row-count information for every partition in the current database.

;WITH EmptyRows AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0

OUTPUT
  


Note that the results from the view is only approximate. If there are any active transactions that are inserting or deleting rows, the count may not include it.

Feedback:

Does my SQL Server Database Support Compression?

Some time back, Praveen Dabade had written a nice article on SQL Server Compressed Tables and Indexes in SQL Server where he explained how compression is now supported on ‘ROW and PAGE’ for tables and indexes. However did you know that compression is an enterprise-level feature?

How do determine what Enterprise Edition features are enabled on your database? Well you can use the sys.dm_persisted_sku_features DMV to find what Enterprise Edition features are
enabled on your database.

Learn more about Dynamic Management Views (DMV’s) here

Here’s the query for the same

SELECT feature_name,feature_id
FROM
sys.dm_db_persisted_sku_features;

Running this query will list all edition-specific features that are enabled in the current database. Some of the database changing features restricted to the SQL Server Enterprise or Developer editions are Compression, Partitioning, ChangeCapture etc.

This DMV is also useful in situations where you are planning to move a database from a higher to a lower edition. Eg: From Enterprise to Standard edition. Running the query will tell you if there are any Enterprise Edition features enabled that may not work when you move to a lower edition.

The DMV will return no rows if no features restricted to a particular edition are used by the database.

Feedback:

Order By Clause and Literal Values in SQL Server

The literal value in an ORDER BY Clause can be an ordinal position of the column or just literal values depending on the usage

Ordinal position in a table refers to the position of the column in a table. It can also point to the column number in a SELECT statement

Consider the following set of data

create table testing(id int, names varchar(100))
insert into testing(id,names)
select 18,'Charles' union all
select 20,'Ashok' union all
select 19,'Birla' ;

The select statement

select * from testing order by 1

returns the following result



The number 1 in Order by clause refers to the first column listed in the SELECT statement so the result is ordered by column ID

The select statement

select * from testing order by 2

returns the following result


As you see the resultset is ordered by second column NAMES

But if you use a CASE expression and a number, it will behave differently. Suppose you want to keep the name Birla in first row and sort other rows by alphabetical order of remaining names, you can use the following statement

select * from testing order by case when names='Birla' then 1 else 2 end,names

The result is



In the above statement, number 1 and 2 are literal values and do not refer any columns. If the name is Birla, a value 1 is assigned otherwise 2 is assigned and ordered by these values first, then ordered by names.

Feedback:

Computed columns - Persisted Vs Non-persisted‏

Computed columns are derived columns based on other existing columns in the same table. Refer to this post http://www.sqlservercurry.com/2012/11/computed-columns-in-sql-server.html for more information:

There are two types of computed columns namely persisted and non-persisted.

There are some major differences between these two

1. Non-persisted columns are calculated on the fly (ie when the SELECT query is executed) whereas persisted columns are calculated as soon as data is stored in the table.

2. Non-persisted columns do not consume any space as they are calculated only when you SELECT the column. Persisted columns consume space for the data

3. When you SELECT data from these columns Non-persisted columns are slower than Persisted columns

Consider the following set of code



create table #t1(col1 int, col2 as col1*0.20)
insert into #t1 (col1)
select top 5000 row_number() over (order by s1.name) from sys.objects as s1 cross join sys.objects as s2

create table #t2(col1 int, col2 as col1*0.20 persisted)
insert into #t2 (col1)
select top 5000 row_number() over (order by s1.name) from sys.objects as s1 cross join sys.objects as s2

Run the following code to understand that table with persisted computed columns consumes more space when compared to a table with non-persisted computed columns. Refer the column named data

exec tempdb..spaceused #t1
exec tempdb..spaceused #t2

Feedback:

Parsename to Extract Precision and Scale values‏ From Decimal Numbers

The numeric datatype stores numbers with precision and scale. Suppose you want to extract only a precision or a scale, you can do it via many ways. One of the ways is to make use of the PARSENAME function.

Consider the following example

declare @amount decimal(12,2)
set @amount=87234.50
select parsename(@amount,2) as precision, parsename(@amount,1) as scale

The result is

precision            scale
---------            --------
87234                  50

Parsename is used to extract specified part of a name. In general, it is used to extract names from four part object names separated by a dot. Argument number 1 extracts the last part of a string, and the 2nd argument extracts the next last part.

This way we can effectively make use of parsename function to extract precision and scale values from the decimal numbers.

Feedback:

Error Handling in SQL Server with THROW

Continuing our series on SQL Server 2012, today we will talk about THROW. In versions prior to SQL Sever 2012, we used @@RAISE_ERROR to generate error messages dynamically or using the sys.messages catalog.

Consider the following example

SELECT ROUND(800.0, -3)

On executing this statement, you get the following error:



because the value does not fit into the decimal data type.

You can use @@RAISERROR to raise a message

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
DECLARE @ErrorMsg nvarchar(1000), @Severity int
SELECT @ErrorMsg = ERROR_MESSAGE(),
@Severity = ERROR_SEVERITY()
RAISERROR (@ErrorMsg, @Severity, 1)
END CATCH

Note: The old syntax of RAISERROR syntax specifying the error number and message number got deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead the new syntax RAISEERROR(50005, 10, 1) allowed you to specify the messageid, severity and state). For new applications use THROW.

However in SQL Server 2012, there’s a better way to this without much efforts – THROW. Consider the following code

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
THROW
END CATCH



As you can see, with just one word THROW, we were able to handle the error with grace and get a result too.

Feedback: