SQL SERVER – Identify and Filter In-Memory Optimized Tables – SQL in Sixty Seconds #079

Earlier I had written a blog about SQL SERVER – Beginning In-Memory OLTP with Sample Example which covers the basics of working with In-Memory OLTP. Though that post gets you started, one of my colleague asked me if there was an easier way to identify In-Memory Tables when working with SQL Server Management Studio. As a follow up I wrote another blog post over here where I demonstrate the same with images and query over here: SQL SERVER – Filter In-Memory OLTP Tables in SSMS.

I have converted the same blog post in SQL in Sixty Seconds video over here.

Let me know your opinion about it.

Here are few other blog posts related to this concept, which I have written earlier.

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

SQL SERVER – Schema Change Reports – SQL in Sixty Seconds #078

Earlier, I wrote a blog post about Schema Change Reports and Finding Tables Created Last Week – DBA Tip. I received quite a few emails about the same. It seems like a quite a popular topic. As per few requests I have created a very short 60 second video on this subject.

Let me know your opinion about it.

Here are few other blog posts related to this concepts, which I have written earlier.

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

SQL SERVER – Fix – Missing “Mirroring” and “Transaction Log Shipping” option in the Database Properties

If you are a SQL Server developer or DBA, the chances are you are quite familiar working with SQL Server Management Studio. The longer you have been in the industry, more likely are you to take these tools for granted. Sometimes, the tool or our basic mistakes in understanding the tool can baffle the best of the best.

So, how many times have you seen something unexpected in SQL Server or with SQL Server Management Studio? If you are seasonal DBA, you might say – “many times”. This blog would share one such experience which I also had. Recently I was trying to configure Database Mirroring on a freshly created database. I was surprised to see that database mirroring option was missing in database properties. If you right click on the System database (master, model, msdb, tempdb) the options of “Mirroring” and “Transaction Log Shipping” will not be available but for me it was a freshly created user database.

I was under the impression that the option might not be available for database in simple recovery model but that was not the case. Irrespective of recovery model, the option should be available. I connected to the same instance remotely and to my surprise the options were available.

Above troubleshooting proved that the issue was not due to SQL Server Engine installation, but something incorrect with client tools installation.

I looked further and launched discovery report from “SQL Server Installation Center”. This can be launched by clicking “Installed SQL Server features discovery report”.

Here is the same report from a server where this option was not visible.

And here is the report from working server.

To fix the issue, I have run the SQL installation again and Added below on the server which has the problem.

After that I was able to get the option in SSMS. There are other option which might not be available in “basic” version of SSMS. Balmukund has blogged about one of such missing option over here.

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

SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077

This error has to be one of the most famous error. I have installed SQL Server quite a many times, but I keep on getting this error once in a while. This is also one of the most searched error online. I have previously blogged about this but today, I planned to create a small video on the same subject. In this video I demonstrate how one can resolve the permissions issue with this error.

ere are few other blog posts related to this error, which I have written earlier.

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

SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.)

This has to be the most popular error in taking database backups. There are many reasons for this error and there are different ways to resolve it. I recently faced this error and for a while I was taking backup and found a very interesting solution to the problem. The error was as mentioned below:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘d:\Log\aw.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Personally, I know this error happens when

  1. There is no directory with Log
  2. There is not enough space on drive d:\
  3. There is not enough permissions on d:\Log drive

In my case all of the above were not the reason for above error. The error was there because of the file was read only. There was already another backup file which existed in the folder with the name aw.bak and it was marked as read only. Due to the same reason, my backup was failing. Once I removed the checkbox for reading only, the backup was successful.

Here are few other blog posts related to this error, which I have written earlier.

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

SQL SERVER – FIX: Error 5161 – An Unexpected file id was Encountered

Recently I was planning to give a demo about increasing tempdb files and its performance benefit. So while making the demo at home, I was doing multi-tasking. Talking to my daughter, having food and making demo. So, I ran the script and restarted SQL Server service but SQL Service didn’t start.

I looked into the ERRORLOG (and I would suggest you to look at that log in case of any SQL startup issues) and found below errors before SQL shutdown messages.

2014-12-02 17:03:24.42 spid18s     Error: 5161, Severity: 16, State: 1.

2014-12-02 17:03:24.42 spid18s     An unexpected file id was encountered. File id 3 was expected but 4 was read from “D:\TempDB\tempdev3.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.

2014-12-02 17:03:24.42 spid18s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

2014-12-02 17:03:24.93 spid18s     SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Since error message before shutdown were talking about tempDB database, I knew that I might have done something wrong while doing multi-tasking. I went back to query windows and read it completely. Initially I added files on C drive (first two commands) but later realized that I should not keep them on C as I already have space issues there, so I moved them using MODIFY FILE (last two commands)

USE [master]
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2',
FILENAME = N'C:\TempDB\tempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 0)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3',
FILENAME = N'C:\TempDB\tempdev3.ndf', SIZE = 1024MB, FILEGROWTH = 0)

-- Space issues on C, moving them to D
( NAME = tempdev2, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)
( NAME = tempdev3, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)

I called my daughter to check and she told me that she can see tempdev3 many times. Bummer! The second last command should have said tempdev2. mdf

I understood the problem but SQL is not getting started now, how can I modify it? Well, the easiest option is removed and add the file with a proper name

1. Start SQL via trace flag 3608

From the command prompt, we can use

net start mssqlserver /T3608

My machine has a default instance, that’s why I have used MSSQLServer. For named instance, we need to use MSSQL$<InstanceName>

If we open ERRORLOG, we should see below

Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

2. Run ALTER Database

Run ALTER DATABASE command as below to remove unwanted files. I have connected via SQLCMD and I am removing Tempdev2 because that has file as tempdev3. This can be done from Management Studio as well.

We can also run below query to find the current mapping. Database ID = 2 is always tempDB database.

SELECT name, physical_name FROM sys.master_files WHERE database_id = 2

In my case I got below output before removal.

NAME                PHYSICAL_NAME
-------------       -------------------------------
tempdev             E:\...\tempdb.mdf
templog             E:\...\templog.ldf
tempdev2            E:\TempDB\tempdev3.ndf
tempdev3            E:\TempDB\tempdev3.ndf

Based on the output you receive, you have to make appropriate changes.

3. Stop SQL Service and start normally.

Once above steps are performed, we should be able to connect to SQL and make changes to TempDB correctly this time.

Have you ever encountered similar tempdb errors? How did you fix them?

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

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

It’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.

This is the first error I got as soon as I wanted to run Invoke-SQLCMD

Error # 1 The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)

Once installation was done, I ran the command and I got a new error.

Error # 2The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.

Here is the complete error message

As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command

Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Here is the complete error message

Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.

Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once I followed above, I was able to use Invoke-SQLCMD as below

Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (http://blog.sqlauthority.com)