Msg 3154, Level 16, State 4

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'AdventureWorks' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Some of these messages related to backups and restores in MSQL Server are misleading.
This error occurs due to following reason

The instance where you trying to restore the backup file already has a database with the same name. So you need to use the option 'WITH REPLACE' option in the restore command. Else drop the existing database first and then restore the backup file.

Request large buffers failure on backup device

SQL error 3013: BACKUP DATABASE is terminating abnormally.

SQL error 18210: BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_687FEF8A-0BE6-485A-AE9C-7BABDA668AF8'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).

BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'D:/SQLBakups/LOG/xxxxxxx. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).

I was in the process of setting log shipping and started receiving the above mentioned error while I was restoring the database backup on the secondary server.

I checked the memory consumption and found that the server had enough memory and all the buffers had enough memory.

Then found the following link that talks more about this issue while backing up and restoring. It seems that this is related to the low size of MemToLeave area for SQL Server.
http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/

Ran the following query and found that it was very low.

WITH VAS_Summary AS

(

    SELECT Size = VAS_Dump.Size,

    Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 0 ELSE 1 END),

    Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 1 ELSE 0 END)

    FROM

    (

        SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],

            region_allocation_base_address [Base]

            FROM sys.dm_os_virtual_address_dump

        WHERE region_allocation_base_address <> 0

        GROUP BY region_allocation_base_address

        UNION

        SELECT

            CONVERT(VARBINARY, region_size_in_bytes) [Size],

            region_allocation_base_address [Base]

        FROM sys.dm_os_virtual_address_dump

        WHERE region_allocation_base_address = 0x0 ) AS VAS_Dump

        GROUP BY Size

    )

SELECT

    SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],

    CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]

FROM VAS_Summary WHERE Free <> 0

First I restarted the SQL Server instance and that seemed to do the trick. The restores went fine and the Log shipping was configured. But the next day the restores started to fail with the same error.

So this time I modified the startup parameters for SQL Server and increase the MemToLeave to 512 MB and that seems to have fixed the problem and haven't seen any issue since then.
Then found the
 

could not register the SPN Kerberos

The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

There was another problem that I faced today for a different client. The solution to this one was similar to the SSPI context error.

We installed a SQL Server instance and when I tried to start it, it wouldn't start. Saw the above mentioned error in the event viewer and SQL Server error logs.

The solution was

1) Change the SQL Server Service to run under 'LOCALSYSTEM' account and restart SQL Server.
2) Once this was complete I was able to connect to the instance.
3) Next change the service to run under whatever Active directory account you want to run it under.
4) Restart SQL Server

can not generate sspi context

Cannot generate SSPI context.

Today I was getting the above error while trying to connect to SQL Server instance.





 After some research found that this is related to the registration of the Service Principal Name (SPN) for SQLServer.

The solution to this problem was

1) Change the SQL Server Service to run under 'LOCALSYSTEM' account and restart SQL Server.
2) Once this was complete I was able to connect to the instance.
3) Next change the service to run under whatever Active directory account you want to run it under.
4) Restart SQL Server

This seems to have fixed the problem.

Error: 5123 Error: 5120 TEMPDB

SQL Server : How to start SQL Server if you lost TEMPDB Data files?

Recently we migrated one of our SQL Server instances to a new server. During the migration we had moved the storage to the new server so the old server didn't have some of the Disk drives that were previously mounted on the old server.
After the migration we realized that there were couple of server objects that were not scripted out from the old server. So we had to start the SQL Server instance on the old server. Unfortunately, the disk drive that was used to store the data files for TEMPDB was now mounted on the new server. So I could not start the SQL Server instance because of the following errors 
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'C:\Data\tempdb\datatempdb.mdf'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file C:\Data\tempdb\datatempdb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "C:\Data\tempdb\datatempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 1802, Severity: 16, State: 4.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Error: 5123, Severity: 16, State: 1.
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'C:\Data\tempdb\datatempdb.mdf'.
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.
So we could not start the TEMPDB and there was no option to mount the drive back.
There is a '-f' startup option that you can use to start SQL Server with minimal configuration. This does put the SQL Server instance in single user mode. So start SQL Server using the command line.

Following are the steps needed to add new file to TEMPDB and then restart SQL Server.

1) Open command prompt window #1 as ADMIN and go to the BINN directory where SQL Server is installed. This is typically in

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

 OR

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

2) Then execute command like

            Sqlservr.exe /f /c 

 

3) Then open one more command window #2 and if this is a default instance then open SQLCMD using following command

SQLCMD –S localhost –E

4) This will open SQL command prompt there type the following command

Next execute the following command

1> USE MASTER

2> GO

3> ALTER DATABASE tempdb MODIFY FILE

4> (NAME = tempdev, FILENAME = 'C:\NEWPATH\datatempdb.mdf')

5> GO 

6> quit

5) Go back to Command window #1 and hit CTRL C.

It will ask if you want to stop the instance. Y/N.
Enter Y

6) Now start the SQL Server instance from configuration manager. You should see the new data file created and SQL Server started.