Interview Question of the Week #053 – What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Some questions are so theoretical that I believe they really do not add too much value if users know that question or not. Here is one such question I am very confident that you agree with my point of view.

Questions: What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Answer: 

Deterministic functions always return the same output result all the time it is executed for same input values. i.e. ABS, DATEDIFF, ISNULL etc.

Nondeterministic functions may return different results each time they are executed. i.e. NEWID, RAND, @@CPU_BUSY etc. Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.

Now you have read the answer – I have a question back to you.

Did you the difference between deterministic and nondeterministic function before this blog? If no, has it ever impacted your performance in your daily job?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: Msg 3169, Level 16, State 1 – The database was backed up on a server running version. That version is incompatible with this server.

At this rate, I think I am going to exhaust the whole error set available inside SQL Server. I am glad that I am able to get into unique situations and then resolve them too. And this is more of a diary of the error messages I am getting into. I was playing with my Demo database on SQL Server 2016. Once done, I started restore from the previous copy.  But is failed with below error:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 13.00.0801. That version is incompatible with this server, which is running version 12.00.4213. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Then I realized that I have taken back-up from 13.00.0801 which is SQL Server 2016 and I was trying to restore on 12.00.4213 which is SQL Server 2014. Error message shows both versions, source and destination.

So, it’s is clear that there is no direct way to downgrade from a higher version to a lower version and it would fail with error message. Why? When an instance of SQL is upgraded, not only do the binaries for the database engine change, the schema level for the databases also changes. So it would not be possible to attach a database with a higher schema level to an instance on a lower version of SQL.

What can be done? One possible approach is to manually export and import the data. You can follow the below steps:

Create empty database

  • In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate Scripts. Follow the steps in the wizard to script the database objects.
  • On the Choose Objects page, select “Script entire database and database objects”.
  • On the Set Scripting Options page, select Save scripts to a specific location.
  • select the Advanced button; under “Types of data to script” select “Schema Only” and under “Script for server version” select “SQL Server 2008” (or appropriate version)
  • Click on Next and finish to complete the process.
  • Now you can use the generated script file and execute this against the SQL server 2008 instance to create the complete database schema without data.

Move the data

To move the data, you have a couple of options but all of these are a bit tricky.

  1. Script out the data exactly the way described in the above steps, just by selecting “Types of data to script” as “Data Only”.

Or

  1. Script out the data for every object individually one by one instead of complete database if you have a large database.

Or

  1. BCP to export the data into files and then again use BCP to import the data back into the tables on to the new server.

In short, moving back-up from higher to lower version is not possible. Only option we have is to move the data using the ways described above. Do you know any more ways?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSMA Error: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

I work majorly on SQL Server but rarely do I get a chance to work on Oracle in general. When some of the error messages reach my inbox, I get intrigued to why some occur. This error message was an outcome of that exploration. When I got this error message, I said I have no clue to why this is happening and I asked if it was anyway related to SQL Server?

The gentleman wrote back stating this was coming from a SQL Server tool and since they were doing a migration using SQL Server Migration Assistant Tool (SSMA). Now that statement got me interested because it was coming from a SQL Server migration.

I went ahead to asked about the environment to get a better hang of things. I asked the Oracle client version they were running. They responded saying, though we are running Oracle Client 9.2.0.8 we are getting an Oracle 8.1.7 or higher error strangely.

Following message is being displayed on the console:

Migrating data…
Analyzing metadata…
Preparing table SYSTBL.CMN_TBL_CUST_…
Preparing data migration package…
Starting data migration agent…
Starting data migration…
Exception during processing: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
Data migration complete.

0 table(s) successfully migrated.
0 table(s) partially migrated.
1 table(s) failed to migrate.

After a going through the complete problem statement. I saw that it was working on a local server, but was failing when it was called from remote server.

Reason After Investigation

The Oracle client provider is not there on the remote SQL Server. If working with remote machines, the Oracle Client provider should be present on the remote SQL Server.

I sent a mail to – Install the Oracle Client provider on the remote SQL Server and verify the data migration. As SSMA doesn’t have any limitation to work with remote SQL Server. Also as part of the installation, asked to check if the extension packs were installed on the remote servers.

After this exercise, I felt there was some good learning for me too and worth a share here. I am sure you are much more experienced in working with SSMA but feel free to let me know if I missed anything.

Reference: Pinal Dave (http://blog.sqlauthority.com)

It’s a New Year, Take Advantage of It – Notes from the Field #110

[Note from Pinal]: This is the first episode of Notes from the Field series in the year 2016 and 110th overall.

There is one place where I see the maximum crowd in the new year – fitness center (gym). It is indeed a good thing that we all want to stay fit and active. However, the reality unveils itself from the second week when the gyms are empty once again. We all start strong, but only few finish strong as well. Earlier last year when I was taking advice from Mike about staying healthy, I had narrated this scenario to Mike. I was expecting that Mike will stay that he understands my feeling we will soon start talking about something related to SQL – in reality, that did not happen. When I mentioned this to Mike, he said it is indeed a good thing and he is, suggested why not we take advantage of initial enthusiasm to set a strong base for the future and healthy lifestyle. Mike did make a great point. We kept on talking about various ways to stay healthy. When we ended our conversation, just as expected, we talked about SQL. Just like health, Mike had had a wonderful insight about the database.
mikewalsh Its a New Year, Take Advantage of It   Notes from the Field #110

In this episode of the Notes from the Field series database expert Mike Walsh presents his thoughts about why we should take advantage of the opportunity present at new year and build a stronger base for healthy databases. Trust me, you want to read it!


Us technologists, we are good at what we are good at. We excel at troubleshooting, solving complex challenges and rising to the occasion. We keep our systems up, we rush in to save the day (especially the DBAs). We ace tests, we love digging in and tuning queries. We’re good at our jobs for the most part.

Sometimes, though, some of us aren’t so great at getting things done. Maybe you suffer from that problem? I know I do. I am quick to pick up the next thing before the current thing is done. I am good at putting things on my to-do lists (in fact I actually have a lot of to-do lists in different forms in different places) but I’m not so good at checking things off of the to-do lists.

smile Its a New Year, Take Advantage of It   Notes from the Field #110I get the urgent things done, but the important and not so urgent things? They can take a back seat. Are you like that? It seems that many of us technologists are. Not all of course, but we live distraction full, device driven “NOW!” controlled lives lately.

This post is a departure from the great guest feature Pinal allows us at Linchpin People to offer. Normally we are answering a technical question, helping point you in the direction of an answer or a better way of doing something. Today, I want to slide back to Professional Development.

It’s a New Year, Take Advantage of It

Worried? Stop and do something about it :-)

On the Gregorian Calendar, this week signifies the start of a new year. You often will see New Year’s resolutions come out this time of year. People say this is finally the year they’ll start eating better, quit a habit, get in shape or do something they’ve been meaning to do.

I’m not asking you to make a resolution. But I am wondering if we can agree that there are things we all meant to do at work last year but never did. Are there services we never took care of? Improvements we left unchecked. If there are, and if you are like me there probably are, make a plan to do something about them. Not a plan that won’t get touched like my to-do lists all over the place. But start today, be persistent and let’s get it done.

This is a new year and we have an opportunity to have a reason and push to do something different for a change. Let’s take advantage of it.

 Its a New Year, Take Advantage of It   Notes from the Field #110

Some Ideas

There are a lot of categories we could look at here.

Learning –

Yes, your own growth is important and something that we end up letting get neglected if it isn’t part of your official job description and your employer/manager doesn’t sort of force you along on that path. SQL Server 2016 comes out this year.  Here’s an action plan for you – Go to this site(https://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/), learn about what’s coming, download a Community Technology Preview and play with some of the features and get familiar.

Maintenance –

Especially if you are a DBA – how is your environment? When is the last time you’ve given it a health check? You don’t have to reach out to Linchpin People to have us do a WellDBA Exam – though we’d be happy to help you do that. Look at the free tools out there, spend time with your key SQL Servers – look at how they are configured and running. Are things good here? What can be done differently? What maintenance is missing? Make a list and start knocking things off as you get to them.

Plan Ahead –

How old are your servers? When’s the last time you had that conversation about data archival? What are the business’ plans this year and you can your systems handle those plans? Have a conversation with the technical teams and make sure you are ready for 2016 and beyond.

What Else?

I don’t know about you, but when I read a post like this or hear people talk about procrastination or missing to-do items – I get this feeling inside. This sort of angry, panicky voice that says “Oh yeah!! I have to do ______” are you getting that voice about anything? I am just typing about it. So what I’m going to do is stop writing this post and go get those things done. You should stop reading and start getting those things done, or delegate them, or realize they weren’t important and you should stop worrying about them. Happy 2016!

If you want to get started with performance analytics and triage of virtualized SQL Servers with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

Blogging has never been an easy task. With close to 9 years of non-stop learning and sharing has got me into a rhythm that I rarely miss a single day without writing one. Over these years, there is one topic area that gets most number of hits based on subject. It is invariably around the performance and troubleshooting area. Most hits on the blogs are always around these areas. Though I see this is proven and become mature over years, still people are searching for topics that are common and even proven over the years.

This always baffles me on the access pattern and I wondered what could be done to mitigate such queries from budding new age DBAs. At the recent SQLPass conference I met a number of them who came to me asking how to become an effective DBA. I generally try to understand their patterns and look for solutions. One of the DBAs told me he was under constant pressure because the SQL Server used to run fine start of the week and the end users complain that the servers are unresponsive or the performance is not acceptable over the week. He was getting clueless to why this behavior was happening. He wanted some help here and I was clueless what to suggest.

Getting started with SQL Diagnostic Manager 10.0

I was recently trying to play with a number of tools in the market and I bumped into IDERA’s SQL Diagnostic Manager tool. They were releasing the new version and I wanted to see how it was different. The first thing that struck me was the ability for DBAs and developers to create automated baselines. This was the first thing that got me by surprise because this requirement was fresh on my mind from the recent trip.

After installation, get to the properties page of the server and go to the “Baseline Configuration” tab. This brings the ability to set automated backups through the week. In the below case, I have gone ahead a created through the week from 8AM – 6PM. This is exactly the timeframe the production is at the maximum stress levels.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

What to monitor?

The next phase is to know what we are going to monitor. I saw the “Analysis Configuration” section has the settings already available with pre-set configuration. In the example below, I have used the “Online Transaction Processing” and selected some of the readily available “Categories”. I personally felt such jump start configurations can surely help and guide the new DBAs who are clueless about what is happening in the system.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

What I would have loved to see few tooltips while I select each of the categories. Because it would become easy before doing any selection. Having said that, it was pretty easy for me to make the selections as it was almost self explanatory.

While selecting the “Advanced settings”, I was able to see further settings that were interesting. I was able to see some of the rules that can be run by default or can be blocked for analysis. Some of these rules are pretty advanced and interesting because it can be easily missed out in the normal run. Here some of the tempdb rules caught my eyes and I have shown below for reference.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

If that was not enough, in “Filter Settings” we can remove unnecessary databases or add multiple databases of our choice. This is powerful because in servers that are consolidated, we don’t need to worry about analyzing smaller databases but the databases of interest based on application can be worked out.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

I just wish this had some way to customize some of the rules rather than being fixed. But the 100’s of rules already available are pretty exhaustive to start. I am sure an advanced user will piggyback on some of them. Cases like number of VLFs created are different between versions and sometimes obsolete in newer versions.

Web is the new SSMS

Though the desktop version is powerful and has tons of reports to show by default, I think the web interface has improved by leaps and bound in this release for SQL Diagnostic Manager. The web interface to the monitoring server can reveal insights remotely too without installation requirement. The initial install of 220+MB file and configuration can sometimes be daunting and once configured, we can use the friendly web interface to visualize what is happening on the server.

Below is a typical workload that you see that my server suddenly peaked in the late evening when a batch service got initiated. This is typically how people would see their server’s vital stats remotely to check if something is going wrong.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

When such anomalies are detected, we can get into the “Top N” queries to see what is going wrong. Which databases are causing heavy workload. Which queries are consuming lots of reads / writes in the system. I felt this snapshot was very useful and made a lot of sense.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

Attention to detail like – “When the last autogrowth happened” is something that I felt was quite useful. From the above, I can also see that a lot of tempdb activity is currently in the system that makes me understand how the developers have written their code and where I need to do the optimizations.

Self-Service the new silver-bullet

Though I was planning  to wrap up this blog, I found a unique customization feature that caught my eyes and needed a mention here. You can build your very own dashboards using the metrics that have been collected before. The usual performance counters are already available from the web version and building your own dashboard with widgets configuration. As you can see I am taking something simple as a dashboard, but these can show the health of multiple instances across the network based on the configuration.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

This ability to have a central web view to all the servers a DBA needs to monitor is critical in large installations and setups where DBAs need to monitor at least 8-10 servers at any moment of time.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

Above is a classic example of how I have created my own SQLAuthority dashboard based on the servers that are of interest to me. I am sure your views are going to be complex and complete.

As I conclude, I will let you try the analysis tool which gives some ready solutions to some of the most common problems. There are many more options that are getting added. As I started with the baseline, let me see how the baselines have been performing over the week using their “Baseline Visualizer”.

 SQL SERVER   Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

I am sure you will find some of these interesting and will share your experience as you play around with the tool. Do let me know via comments on what you found interesting.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: sp_rename error Msg 15225 – No item by the name of ‘%s’ could be found in the current database

Few days back I have written blog containing script for removing space in column name using sp_rename. In case you missed, I am listing them below for quick reference.

SQL SERVER – Script: Remove Spaces in Column Name in All Tables

SQL SERVER – How to Rename a Column Name or Table Name

While playing with sp_rename, I came across an interesting error message and I looked into the code of sp_rename to understand the issue. Here is the error message which I received.

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 387
No item by the name of ‘TableTwo’ could be found in the current database ‘SQLAuthority’, given that @itemtype was input as ‘(null)’.

For simplification, I have made up below script to explain the error.

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE SCHEMA HR
GO
CREATE TABLE dbo.TableOne (ID INT PRIMARY KEY)
GO
CREATE TABLE HR.TableTwo (ID INT, FName VARCHAR(100))
GO
sp_rename 'TableOne', 'TableOne_renamed' -- This works
GO
sp_rename 'TableTwo','TableTwo_renamed' -- This fails

If we look at sp_helptext sp_rename we would see below at line 387 (as shown in error)

-- was the original name valid given this type?
IF (@objtype IN ('object','userdatatype') AND @CountNumNodes > 3)
BEGIN
COMMIT TRANSACTION
RAISERROR
(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
RETURN 1
END

I looked at beginning of sp_rename the stored procedure and found some documentation there, there were some good rules listed.

  • To rename a table, the @objname (meaning OldName) parm can be passed in totally unqualified or fully qualified.
  • The SA or DBO can rename objects owned by lesser users, without the need for SetUser.
  • The Owner portion of a qualified name can usually be passed in in the omitted form (as in MyDb..MyTab or MyTab). The typical exception is when the SA/DBO is trying to rename a table where the @objname is present twice in sysobjects as a table owned only by two different lesser users; requiring an explicit owner qualifier in @objname.
  • An unspecified Owner qualifier will default to the current user if doing so will either resolve what would otherwise be an ambiguity within @objtype, or will result in exactly one match.
  • If Database is part of the qualified @objname, then it must match the current database. The @newname parm can never be qualified.
  • Here are the valid @objtype values. They correspond to system tables which track each type: ‘column’  ‘database’  ‘index’  ‘object’  ‘userdatatype’  ‘statistics’
  • The @objtype parm is sometimes required. It is always required for databases.  It is required whenever ambiguities would otherwise exist.  Explicit use of @objtype is always encouraged.

Parms can use quoted_identifiers.  For example: Execute sp_rename ‘amy.”his table”‘,'”her table”‘,’object’

So, we are getting error because our table TableTwo is not in the default schema which is dbo. The solution is very simple: we need to qualify the name of the table with the schema name as shown below.

sp_rename 'HR.TableTwo','TableTwo_renamed' -- Now This would work

While reading the ruled listed in stored procedure, I realized that same error can also come if there is a dot in table name. we need to use [] around such tables. Here is an example

USE SQLAuthority
GO
CREATE TABLE [Name.With.dot] ( i INT)
GO
sp_rename 'Name.With.dot', 'New_Name.With.dot' -- would fail
GO
sp_rename '[Name.With.dot]', 'New_Name.With.dot' -- would work
GO

Hope this would help. Do let me know if you have used something like this before in your environments. Feel free to share via comments below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Someone was trying to hack my SQL Server Logins in Azure!

When I started to work with SQL Server on Azure VMs, I was completely excited because now the need to build local VMs has become super easy. If I need something for testing, then I know I can get a SQL Server instance in a jiffy and it can be brought down as soon as my work is done. The more we start working with softwares on the cloud, it is important to also understand the nuances of security that we need to be aware. I recently noticed the ERRORLOG on my virtual machine sitting in Azure and was surprise to see below error in ERRORLOG multiple times every minute.

Error: 18456, Severity: 14, State: 5.
Login failed for user ‘KISAdmin’. Reason: Could not find a login matching the name provided. [CLIENT: xx.174.233.xx]
Error: 18456, Severity: 14, State: 7.
Login failed for user ‘sa’. Reason: An error occurred while evaluating the password. [CLIENT: xx.174.233.xx]
Error: 18456, Severity: 14, State: 5.
Login failed for user ‘KHB’. Reason: Could not find a login matching the name provided. [CLIENT: xx.174.233.xx]
Error: 18456, Severity: 14, State: 5.
Login failed for user ‘Chred1433’. Reason: Could not find a login matching the name provided. [CLIENT: xx.174.233.xx]

(I have masked the IPs in error messages above)

I realized that

  1. The IP address listed was not an IP which I know
  2. Account shown in login failed messages doesn’t exist on my SQL server instance.

Since this is a virtual machine in Microsoft Azure, I checked the endpoints for this VM as they are responsible for any entry to the sever from the outside world. Here is what I saw.

hack 01 SQL SERVER   Someone was trying to hack my SQL Server Logins in Azure!

As we can see above that I have created endpoint for SQL Server on port 1433 and it’s a very well-known port used by SQL Server. My theory about hacking became more firm when I attempted to do remote desktop on the IP given in the error message.

hack 02 SQL SERVER   Someone was trying to hack my SQL Server Logins in Azure!

Here are my suggestions:

  1. If Login failed message says <local machine> it is less likely to be hacker, but some process somewhere on the machine.
  2. Above is also true if the IP address is within your organization.
  3. If the IP Address is outside your organization, then answer is simple – don’t expose your SQL Server on the Internet. Else you may want to take below steps
    1. Change SA password and make sure its complex.
    2. If you don’t need SQL Authentication, don’t use it. Change the mode to Windows only.

In my case, since its and VM in Azure and I have to stay with SQL authentication so I changed the public port to something different and then there were no more Login failed attempts from that machine. When I searched, I also found article from Microsoft as below

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-sql-server-security-considerations/

Interesting my simple idea of using non-default endpoint of not listed there but they do ask to use ACL for endpoints for better security of SQL running on Azure virtual machine.

Do you check SQL ERRORLOG regularly to monitor the server’s security?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #052 – Print String in Reverse Order

Last week, I attended the last interview of the year. One of my co-interviewer asked a coding question to the candidate. In this type of question, the candidate is given one hour with SQL Server with a help file. The computer does not have internet candidate can use all the help available from MSDN. In this case, the candidate was able to resolve the problem very successfully.

Question: Write a User Defined Function which can generate a script in reverse order without using the reverse function?

Answer: Here is the script for the user defined function which can generate script in the reversed order, (however, he failed at one condition where he used REVERSE function).

CREATE FUNCTION UDF_ReverseString
( @StringToReverse VARCHAR(8000),
@StartPosition INT)
RETURNS VARCHAR(8000)
AS
BEGIN
IF
(@StartPosition <= 0)
OR (
@StartPosition > LEN(@StringToReverse))
RETURN (REVERSE(@StringToReverse))
RETURN (STUFF (@StringToReverse,
@StartPosition,
LEN(@StringToReverse) - @StartPosition + 1,
REVERSE(SUBSTRING (@StringToReverse,
@StartPosition
LEN(@StringToReverse) - @StartPosition + 1))))
END
GO

What really impressed me was that it also allowed to specify from which position the string has to be reversed.

Reversing the string from third position
SELECT dbo.UDF_ReverseString('forward string',3)

Results Set : forgnirts draw

Reversing the entire string passing 0 as beginning character
SELECT dbo.UDF_ReverseString('forward string',0)

Results Set : gnirts drawrof

Reversing the entire string passing negative number as beginning character
SELECT dbo.UDF_ReverseString('forward string',-9)

Results Set : gnirts drawrof

Reversing the entire string passing larger number than string length as beginning character
SELECT dbo.UDF_ReverseString('forward string',900)

Results Set : gnirts drawrof

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script: Finding Cumulative IO Per Database File

Sharing SQL Server related scripts is something I tend to do from time to time. At the SQLPass one of the attendees for my session around 42 tips asked, if I have any script that is handy to find the IO utilization per database file because they had couple of scenario’s where such information was useful. I got curious to know what are those scenarios. During the break while on the line I asked, and got the response.

“Pinal, I actually work for a financial institute and we have large SQL Server installations. For the question I asked, I thought you might have something handy that I can take a look at. There are actually 2 scenario’s that get enabled here:

  1. First is a classic place where I have an SQL Server where I have consolidated a number of databases and want to make sure the experience of users accessing different databases (aka different applications) is not compromised because I can go ahead and distribute IO hungry files on different files.
  2. The second scenario is wherein I have done heavy partitioning on my database and now I have far too many files. Though the thought process remains the same as 1st I want the IO stats per file so that I can again look at distributing them around.”

Trust me, this made complete sense and I came back to this blog to check if I had something similar for this requirement. Well, then I thought I must write something simple to get this sorted. Here is a simple script that I made and feel free to modify the same for your requirements:

SELECT f.database_id, DB_NAME(f.database_id) AS database_name, f.name AS logical_file_name, f.[file_id], f.type_desc,
  
CAST (CASE
      
-- Handle UNC paths (e.g. '\\fileserver\mydbs\sqlauthority_dw.ndf')
      
WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
          
THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
          
-- Handle local paths (e.g. 'C:\Program Files\...\master.mdf')
          
WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
          
THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
      
ELSE f.physical_name
  
END AS NVARCHAR(255)) AS logical_disk,
  
fs.size_on_disk_bytes/1024/1024 AS size_on_disk_Mbytes,
  
fs.num_of_reads, fs.num_of_writes,
  
fs.num_of_bytes_read/1024/1024 AS num_of_Mbytes_read,
  
fs.num_of_bytes_written/1024/1024 AS num_of_Mbytes_written,
  
fs.io_stall/1000/60 AS io_stall_min,
  
fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
  
fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
   ((
fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
   ((
fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
  
ABS((sample_ms/1000)/60/60) AS 'sample_Hours',
   ((
fs.io_stall/1000/60)*100)/(ABS((sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample' -- Number of milliseconds since the machine was started.
FROM sys.dm_io_virtual_file_stats (DEFAULT, DEFAULT) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]
ORDER BY 18 DESC
GO

The sample output (partially shown) is as:

 SQL SERVER   Script: Finding Cumulative IO Per Database File

Have you had similar requirements and have you used scripts similar to these before? I think necessity is the mother of all scripts in the SQL Server world. Hope to see some of your scripts via the comments section.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

Getting error and finding the cause is something which I like. And off late over the blogs I have shared a number of such incidents that have helped me figure out stuffs all by myself. Of course, I do my research and more often than not make things complicated because there are a number of resolutions that I find. I also make it a point to check with a few friends from Microsoft to a possible cause because it is easy to get help than walk the tough line of finding it myself.  One fine day I restarted my virtual machine and then I wanted to take a full backup of all databases before formatting the machine. As soon as I hit on execute button, as shown below, I was welcomed with an error message.

Agent Not Running 01 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

Here is the error which I received.

Agent Not Running 02 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

Complete text of the error message is below:

TITLE: Execute Maintenance Plan
——————————
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2480&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476
——————————
BUTTONS:
OK
——————————

Error message is very clear. SQL Server Agent service should be running before we can execute maintenance plan. If we notice the first screen shot, it’s clear that SQL Server Agent was not running. So solution looked very simple, start the SQL Server Agent Service. It was not started by its own because it was set to manual mode.

Agent Not Running 03 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

To avoid such error in the future, I change the “Start Mode” of the service, to automatic by going to the properties of the service.

Agent Not Running 04 SQL SERVER   FIX: SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

I also found that sometimes you would get this error even if the SQL Server Agent is running. In those cases, you need to check SQLAgent.out file to check if it’s started completely.

Something SQLAgent.out would show ONLY below message

Waiting for SQL Server to recover databases…

If you are running in that issue, please have a look at https://support.microsoft.com/en-us/kb/2640027 to get a fix for the issue.

Have you even fixed such simple issues? The error messages over the ages have become obvious and very helpful too.

Reference: Pinal Dave (http://blog.sqlauthority.com)