SQL Server – Take database offline hangs

With thanks to following and other web searches:

http://pianaro.wordpress.com/2012/11/30/sql-server-database-take-offline-hangs/

Scenario

New SQL Server on a new Azure VM, with a few new issues to learn from. I attempted to take a database offline but it hung.

Solution

USE master

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks

--Find troublesome session and kill it.
--You cannot kill your own process, so you may need to start another session
KILL [SPID]

To take the database offline without waiting try this:

alter database DOTNETNUKE_EBC set offline with rollback immediate

To bring database online:

alter database DOTNETNUKE_EBC set online

 

End

 

Advertisements

Error setting up SQL Server CDC – Change Data Capture

Error when using:
EXECUTE sys.sp_cdc_enable_db

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
Could not update the metadata that indicates database <dbname> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

Solution:

Database > Properties > Files >
Check that the databse has an owner

For me this went wrong because ‘dbo’ existed on the server (pc) where this database was created, but did not exist on this server (pc) (SQL Server Developer Edition)

SQL Linked Server Notes (including to an IP)

Create linked server when on the actual server, not as a user on another PC.
If there is a VPN connection required then the VPN connection needs to be up and running on the server, not the local PC.

USE [master]
GO
EXEC master.dbo.sp_dropserver @server=N'', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'',@useself=N'False',@locallogin=NULL,@rmtuser=N'RemoteServerLogin',@rmtpassword=''
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'',@useself=N'False',@locallogin='Domain\login',@rmtuser=N'RemoteServerLogin',@rmtpassword=''

.

Notes:
@locallogin=NULL means any local login
You cannot use Windows NT group logins, does not work. Individual window users only.

SQL Linked Server and SQL Server Agent

When using SQL Server Agent to do a job that has a view or other that connects to a remote server then you may get the following issues:

Error “Linked servers cannot be used under impersonation without a mapping for the impersonated login”

Solution 1:

  1. Create a login on the end server as:
    USE [master]
    GO
    CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
    GO
  2. Then give this login the permissions on the objects required.
  3. Back on the host right-click on the remote server > Properties > Security >
  4. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked on – No remote user or password
    Suggest use “Be made using the login’s current security context”

Solution 2:

  1. Create a new SQL server login on the end server and give it the required permissions
  2. Back on the host right-click on the remote server > Properties > Security >
  3. Add “Local server login to remote server login mappings:”
    Add “NT SERVICE\SQLSERVERAGENT” Impersonate = ticked off – Remote user = your new user, Password = new password
    Suggest use “Be made using the login’s current security context”

 

Cannot generate SSPI context when connecting to SQL Server when a VPN is connected

We setup a VPN connection to another site, but then programs would not connect to SQL Server using Windows Authentication. We spent a fair bit of time trying to resolve this. Then found the solution, thanks to the following post:


http://www.sevenforums.com/network-sharing/59414-win7-vpn-sql-server-cannot-generate-sspi-context.html

After a lot of searching i found a good solution to the problem when using Windows login to a SQL server while on Win VPN giving the error: Cannot generate SSPI context.
Wanted to share it:

  1. Locate the .pbk file that contains the entry that you dial. Is either: C:\Users\<USERNAME>\AppData\Roaming\Microsoft\Network\Connections\Pbk or if connection is visible to all users: C:\ProgramData\Microsoft\Network\Connections\Pbk (hidden files may need to be disabled first through Organize -> Folder and search options)
  2. Open the file in Notepad using Open with…
  3. Locate the following entry: UseRasCredentials=1 Each VPN connection you have has a section marked with [<VPN connection name>]
  4. Modify the entry to the following: UseRasCredentials=0 Needs to be done for each VPN section for the connections having problems.
  5. On the File menu, click Save, and the click Exit.

SSMS viewInfo error with no database list shown

PROBLEM

Opened SSMS 2012 and got error viewInfo. Unable to see database list.
On Refresh databases same error.

SOLUTION

For me it was to do a restore back 2 days

BACKGROUND

Others have had this.

Sysinternal Tools and filters to “Process Name is ssms.exe” and “Path contains AppData\Local\Temp”

It seemed to not be finding an Addin.

https://connect.microsoft.com/SQLServer/feedback/details/573771/value-cannot-be-null

https://github.com/TaoK/PoorMansTSqlFormatter/issues/48

SQL Agent Job – SSIS Integration package which uses VFPOLEDB and SQL 2012 fails

Various error messages received including:

Description: ADO NET Source has failed to acquire the connection DataReaderSrc with the following error message: “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”

Solution:

  1. Create a SQL Credential
    1. SSMS > Security > Credentials
  2. Create a SQL Server Agent Proxy
    1. SSMS > SQL Server Agent > Proxies > SSIS Package Execution > New Proxy
  3. Return to job step in question > Run as > new proxy
  4. Execution options > Use 32-bit tick

Workaround for SQL Server 2012 64-bit connection to 32-bit VFPOLEDB – Stored Procedure and SQL Agent

In SQL Server 2005 32-bit > Linked Servers > Providers > VFPOLEDB was present, (with in process ticked)
This provider was not available in SQL Server 2012 64-bit

Workaround: Running a stored procedure from SQL 2012 back to to SQL 2005

  1. Create a linked server to the SQL Server 2005 32-bit. RPC = Remote Procedure Call. You need to set RPC Out to True. Possibly RPC as well.
  2. Then use/move/write queries in SQL 2005 and call them from SQL 2012 procedures using:
  3. EXECUTE [<SQL2005 server name>].[Database name].[Schema].[procedure]
  4. May need to add login to SQL 2005 and grant permission

Workaround: SQL Agent jobs that call procedures still not working

Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider “VFPOLEDB” for linked server “”. [SQLSTATE 42000] (Error 7303)  OLE DB provider “VFPOLEDB” for linked server “” returned message “Invalid path or file name.”. [SQLSTATE 01000] (Error 7412).  The step failed.

  1. Change the job type from Transact SQL to Operating System (CmdExec)
  2. Command = osql -E -Q “EXEC <sql 2012 db name>.dbo.<sp name>”

Maintenance Plan fails on Notify Operator – “No global profile …”

With thanks to

http://aartemiou.blogspot.co.uk/2011/02/no-global-profile-is-configured-specify.html


Even though the first three tasks were executed successfully, when I checked the history of the job that undertakes the execution of the maintenance plan, there was an error message on the last task that is the “Notify Operator” Task.
A part of the error message is the following:
“No global profile is configured. Specify a profile name in the @profile_name parameter.”
So, what does the above error message tell us? It actually says that upon the task’s execution, it cannot find a Database Mail profile in order to use it for sending the email notification.
To overcome this problem, you can set one of the available Database Mail profiles as the default one.
In SQL Server 2005 or 2012 you can do this in the following way through SSMS:

  1. Management — Database Mail (double-click or right-click – Configure Database Mail).
  2. Click on Next.
  3. Manage profile security.
  4. Click on Next.
  5. Then you will see a list with all the available database mail profiles. You have the option to select one of these profiles to be the Default Profile (be selecting ‘Yes’ in the combo box).
  6. Click on Next.
  7. Click on Finish.

Some SQL Server 2005 services are not listed in SQL Server Configuration Manager – Also Red Cross shown in IIS – Server 2003

PROBLEM

  1. Some SQL Server 2005 services are not listed in SQL Server Configuration Manager
  2. Red Cross shown in IIS – Server 2003

SOLVED QUICK

BITS – Background Intelligent Transfer Service may not be registered and may have a startup type of Manual and be Stopped.

regsvr32.exe qmgr.dll
regsvr32.exe qmgrprxy.dll

Then reboot.

SOLVED DETAIL

by external support company. Asked them how they did it. Answer copied here:

It looks like a windows update was responsible for the failure, Steve correctly spotted the fundamental issue which was that the default web site was stopped in IIS.  By attempting to start IIS, this registered an event in the windows logs and I could diagnose the issue from there.

The earlier versions of SQL reporting prior to 2008 relied on IIS to operate and the failure of IIS to start resulted in the missing items in SQL.

Windows update installed a hot fix which altered a file responsible for BITS (the service that enables Windows Update to function), the update applied an updated version of a file for IIS and the file failed to register when it was installed.  IIS was effectively looking for a file which wasn’t present and stopped itself.

By re-registering the file and rebooting, IIS was able to recognise the updated file and the default web site started successfully.  As the default website hosts information referred to by SQL, this caused SQL reporting to re-appear.

An odd one, but then Microsoft updates are probably responsible for a lot of strange behaviour!

Incidentally, while I was on the server, I spotted a string of Windows Desktop Search errors in the log – Windows Desktop Search was given as an update by Microsoft and it is my personal view that the product is poorly designed and disables perfectly good search facilities included within Windows, it looks like it has been clogging your event log with errors for some time now and with both of these things in mind,  I have disabled the Windows Desktop Search.

Anyway, glad SQL & IIS are now working, please let me know if you have any further issues.