What's Your Favorite Feature of SQL Server 2012?

MSSQL : Bring database back to normal from Restoring State

If you are restoring a database using multiple backup files, you would use the WITH NORECOVERY option for each restore except the last. If your database is still in the restoring state and you want to recover it without restoring additional backups you can issue a RESTORE DATABASE .. WITH RECOVERY to bring the database online for users to use.

Here is the syntax.

RESTORE DATABASE <DATABASE_NAME>

WITH RECOVERY

SQL Server: Linked Server is not configured for RPC error:

Workaround for calling table-valued function remotely in SQL Server over Linked Server needs some fancy in the coding. Something like this


EXEC SERVER_LINK.DATABASE_NAME..sp_executesql N'SELECT value FROM fn_testexecute(''value1'', value2, value3, value3, value4);'

Now what if the SQL fails due to below error:

Msg 7411, Level 16, State 1, Line 2
Server 'TEST_LINK' is not configured for RPC.

This is a configuration thing with the Linked Server option called "RPC OUT". This is necessary to execute a procedure calls to go out to the linked Server. Where as, "RPC" option allows stored procedure call from the linked Server.

For this case, I had to enable "RPC Out". I ran sp_helpserver and it reported RPC OUT isn’t enabled.

That's it. Problem solved.

MSSQL Server to MSSQL Server Instance connectivity with Linked Server

In order to create LINKED Server the user needs to have SYSADMIN privileges. Here in this demonstration I’m going to show the preferred LINKED SERVER option called “Be made using the login’s current security context”. Condition is, LOGIN needs to stay on both the servers. This is the preferred and recommended method in terms of security as other users can’t use this link except for the Login I’ll be using.


Here are the steps:

1. I’m going to use a user called TEST for this demonstration, and going to provide SYSADMIN privilege to this user. Once the link created and tested, I’ll revoke SYSADMIN privilege from the TEST user.

2. Login as TEST user

3. Open SSMS, Go to Server Objects-->Right Click Linked Servers--> New Linked Server. The below form pops up

4. Give a name for “Linked Server” , choose the provider as shown on form. Product name here I mentioned ‘*’ to specify any SQL Server product. On the Data source I’m mentioning the instance name from where I’d be pulling data. It’s “MSSQLTEST” for my case.

5. On the security tab select “Be made using the login’s current security context”. This would use my used Loging ie, “TEST” for this case.

6. Click OK, now you can browse through the objects using SSMS-->Linked Servers--> TEST_LINK-->Catalogs or you could use below syntax to fetch any table data

select * from [TEST_LINK].[TEST_DB].[dbo].[TEST_TABLE];
  7. Revoke “Sysadmin” privilege from “TEST” user

My “Linked Servers” has been created!

SQL Server - Maintenace plan integrity checks fail with "Alter Failed for server xxx"

My maintenance plan integrity check task was failing with "Alter failed for server XXX". 

After tracing i found the maintenance plan executes sp_configure 'USER OPTIONS',xxxx followed by RECONFIGURE. The reconfigure statement then generates an error causing the plan to fail. This was because the server configuration option "Allow Updates" was set to 1. Changed the setting back to 0 and the reconfigure statement started to work again!

Following command changes "Allow Updates" parameter to "0"

SP_CONFIGURE 'ALLOW UPDATES',0
GO
RECONFIGURE
GO 

SQL Server Performance Dashboard Reports in SSMS

The step by step procedure has been published on Microsoft MSDN blogs both for 2005 & 2008.
Couple of tweaks required for 2008, but everything is documented.
Here's the link.

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx

This is how it looks like:

 

Move SQL Server transaction log files to a different location via TSQL

To optimize I/O performance of a database, it's always a good idea to keep LOG file & DATA file in separate physical drives. 

The transaction log file records every data change and DML transaction executed in the database.  Writing to the transaction log file is sequential in nature as compared to the database files which are typically random I/O.  As such, placing the log file on separate physical disk from database will allow the disk to work in sequential manner and perform optimally.  

I'm going to show a demonstration of moving LOG files to another drive.

1: Capture database and transaction log file information

USE AdventureWorks
GO

sp_helpfile
GO

2: Set database to single user mode and detach database

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO

-- Detach the database
sp_detach_db 'AdventureWorks'
GO

*** Now the database is detached.  Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer.  Once this is completed, we can attach the database with SQL Server database log file at new location with the following script:

3: Attach database with log file at new location

USE master
GO

-- Now Attach the database
sp_attach_DB 'AdventureWorks',
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
GO

4. Validate the LOG moving

After the final attach command  transaction log file has been moved to new location and database is operational with log file on new location.  Verifying the new database transaction log location can be accomplished by re-running 

USE AdventureWorks
GO

sp_helpfile
GO

Bringing a database out of Restoring state in SQL Server

What if I restored a database with “NO RECOVERY” option keeping in mind that I’d keep continuing with another Differential backup set / Transactional backup set next. But then I’ve decided not to proceed further?

At this moment, database is in “Restoring” state and inaccesible.

I need to run following script bringing a database back from restoring state to normal.

RESTORE DATABASE

WITH RECOVERY

MSSQL Server : The log or differential backup cannot be restored because no files are ready to rollforward

I was facing following error while restoring Differential backup.

Restore failed for Server ''. (Microsoft.SqlServer.Smo) Additional Information: System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo) 

The reason for this erro is: no database that was left in non-operational mode, and thus has not been cleaned up such that uncommitted transactions have not been rolled back. 

The easy way to reproduce this error is to 

-Backup the database using full recover mode 
- Do full and differential backups. 
- Restore the DB, First from Full backup then Differential

I got the above error while trying to restore the differential backup (after you just restored the full backup). 

Reason for the error:

I restored the Full backup with Recovery option. It must be restored with NORECOVERY option to allow rest of the backup sets to be restored (Differential/Transactional)

In the Microsoft SQL Server Management Studio there are three options on the Option "page" while restoring a database. 

Option 1 (the default): Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY) 

Option 2: Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY) 

To properly restore a database that is backup up using full recovery mode with full and differential backups, here are steps:

Restore Full Backup:

1. Open the Restore Database window in Microsoft SQL Server Management Studio
2. Ensure the To database field is filled in with the name you want.
3. Choose From device as the Source for restore.
4. Choose the full backup file you want to restore. In most cases this is the most recent full backup file.
5. Click the Options page on the left navigation.
6. Choose Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY). 

Restore Differential backup:

1. Open the Restore Database window in Microsoft SQL Server Management Studio
2. Ensure the To database field is filled in with the name you want. The same that you specified in step 2 for the Restore Full backup
3. Choose From device as the Source for restore.
4. Choose the differential backup file you want to restore. In most cases this is the most recent differential backup file.
5. Click the Options page on the left navigation.
6. Choose the default: Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY) 

If you want to restore Transactional log after Differential backup restore, then Choose "RESTORE WITH NORECOVERY" instead of "RESTORE WITH RECOVERY".

RESTORE WITH RECOVERY Needs to be the final step of a recovery process.

MSSQL Server: The tail of the log for the database "DATABASE" has not been backed up.

I was facing the following error error when attempting to restore a database in Microsoft SQL Server:

System.Data.SqlClient.SqlError: The tail of the log for the database "DATABASE" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Solution:

Select "Overwrite the existing database" from Restore option.

This will overwrite the tail of of database log , as my DB is corrupted and wanted to restore from last full backup so it's fine for my case.

MSSQL Linked Server error: The OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column

I was trying to pull data from Oracle to MSSqlserver database using Linked server.

select * from [LINK_NAME]..SCOTT.DESCRIPTION;

But it was failing with the below error:

Msg 7356, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "LINK_NAME" supplied inconsistent metadata for a column. The column "MANUFACTURER_NAME" (compile-time ordinal 6) of object ""SCOTT"."DESCRIPTION"" was reported to have a "LENGTH" of 100 at compile time and 200 at run time.

Not quite sure about the column metadata inconsistency the error reported. But got the following workaround using OPENQUERY option.

Here’s how it worked.

Select * from OPENQUERY(LINK_NAME,’SELECT * FROM SCOTT.DESCRIPTION’);

MS SQLSERVER to ORACLE connectivity with Linked Server configuration:

1. Install Oracle Client where MSSQLSERVER is running. I've used 11.2.0 client for my case.

2. Add oracle TNS Entries for the target Oracle Database. TNS location would be:

$ORACLE_HOME/network/admin/tnsnames.ora

Here is a sample TNS file. You need to copy this from the oracle server where you want to connect.

oraLab01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oralab01)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = lab.com)

    )

3. Now start configuring mssql server LINKED SERVER.

Server Objects --> Linked Servers -->Right click on Linked Servers --> New Linked Server

Here you need to mention the link name, provider name etc. As I’m connecting to Non MSSQL so I had to select "Other data Source" as server type. 

4. Select Oracle Provider for OLE DB from Provider drop down.

Type data source & provider string similar to the TNS file.

5. Click Security from left side. And select "Be made using security context" then Type username & password.

6. Click Provider --> OraOLEDB.Oracle --> Right click-->Properties --> check "Allow inprocess"-->OK

7. Right click on newly created connection ---> Test Connection ---> Success!!!

Now you can either browse the tables from Linked Server connection or you may run following to select your tables.

select * from [oralab01]..scott.tiger;

Shrink SQL Server Log File

Below are simple steps to shrink transaction log files in SQLSERVER.

sp_helpdb 'DATABASE_NAME'

Truncate the log by changing the database recovery model to SIMPLE

USE DATABASE_NAME;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE DATABASE_NAME

SET RECOVERY SIMPLE;

GO

Shrink the truncated log file to 1 MB:

DBCC SHRINKFILE (LOF_FILE_NAME, 1);

Reset the database recovery model.

GO

-- Reset the database recovery model.

ALTER DATABASE DATABASE_NAME

SET RECOVERY FULL;

GO