RSS

Category Archives: SQL Server 2012

SQl Server 2012 RC0

Top 5 new features in SQL Server 2012 for developers

Introduction:

Microsoft SQL Server 2012 introduces many features that help database administrators, database developers, and BI developers.

In this article, I will cover some of the new features for database developers in these main points:

  • Database Engine Improvements
  • Improvements to SQL Server Management Studio Debugging
  • Changes to the Scope of Objects
  • Conclusion
  • References

Database Engine Improvements:

  1. File Tables:When we open up SQL Server Management Studio, one of the first changes we notice for SQL Server 2012 is the addition of a new type of table called a File Table.

    File table allows us to make a connection between windows share and a database table such that any file that appears in the share will become a row item in the table.

    It allows us to run queries that tell us how many files we have in that shared location, what type of files, what size the files are, etc….

    Setting this up is a multiple step process:

    • Enable file stream: is The first step we have to do at the instance level.We will do that with the configuration manager tool, open properties of the instance we are interested in, and there is a tab for file stream, in there we should click on all of the checkboxes(enabling all features) as below

word-image-51

 

To complete the article check it HERE

 
Leave a comment

Posted by on February 18, 2017 in New Feature

 

Tags: , , , ,

Troubleshooting “SQL Server Database Is In Recovery Pending State”

Overview

Users of SQL Server Database must be aware of Recovery states, that occurs when there is unintentional dropped SQL table or any other components that needs to be restored. The SQL Server Database is in recovery pending state at situations like restart of SQL Server, offline & online state of database or while restoring database from a backup. However, if there is any issue during this recovery process, error can be Read the rest of this entry »

 
Leave a comment

Posted by on August 30, 2016 in backups, problems

 

Tags:

Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group

One of customers changed the value returned from @@SERVERNAME.  SQL Server works no problem, however an unexpected behavior appeared.  Changing the value for @@SERVERNAME, caused the backups to fail.

Looking at the maintenance jobs, found all jobs completed successfully and without issues.  However, upon looking at the database’s statics it states no backups completed.

image

Because the database in question is part of AlwaysOn Availability Group (AG); SQL Server executes sys.fn_hadr_backup_is_preferred_replica to determine if the backup should take place on the current node.  However, it returns value of 0 for all databases, if the preferred replica is set.  Because, the script makes a check that is running on the server that is preferred.  It does this by comparing the value to @@SERVERNAME to value of replica_server_name in sys.availability_replicas.  Because value will never match, it skips the database on both primary and secondary replica.

I have created a Microsoft Connect article (link); asking this little bit of information to be added to Books Online article (link).  There was a request submitted by Ola Hallengren (Blog | Twitter), which was closed as Won’t Fix (link).   Please vote!

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
 

Tags:

SQL Server Transaction Log Backup Failing (Error 22029)

A Transaction Log is a file that contains all the records of transactions and the database modifications made by each transaction in SQL Server database. The log file plays a very important part of SQL Server database when it comes to disaster recovery and it should not be in corrupted state. There are several types of errors we came across while using SQL Server and one of the most common error is Failure in SQL Server Transaction Log or Error 22029. This page will be discussing about the same error in details.

SQL Server Transaction Log Backup Failing

Why It Occurs?

SQL Server Error 22029

This error in associated with the maintenance plan of the transaction log back up or maintenance plan options for the database in SQL Server.

There are many reasons why the above error in SQL Server occurs-

  1. The SQL Server transaction log backup error 22029 may be seen if your database maintenance plan is trying to make a transaction log backup of a database that is offline.
  2. It may occur when you try to create transaction log backup and when the database is using the simple recovery model.
  3. It may occur if you select the master database and you choose backup the transaction log as part of the maintenance plan option.
  4. This error may also be seen if the string for the target folder (where the database backup will be stored or the one used for transaction log backup or one used for reports) in the maintenance plan contains “-S”
  5. It may happen if you have less disk space for the transaction log backup.
  6. If the multiple databases are backed up in a single maintenance plan, this error might show up.
  7. In addition, it may occur if you have no right access permissions or authority. Example- the SQL agent does not have write access to the file storing the database backups.

How It Can Be Resolved?

One thing that should be kept in mind while creating maintenance tasks is to check the “write report to a text file in directory” so that all the details of whatever happening during the maintenance task is seen, especially when there is any error.

Database Maintenance Plan

  1. The reason why the first error occurs can be solved by connecting the database to the server. So that, the log of that database can be backed up successfully with the maintenance plan as the database is online.
  2. While creating the maintenance plan for transaction log backup under simple recovery model, we may find error. Why is it so?
    The simple recovery gives the simplest form of backup and restore. It supports both database and file backups but does not support log backups. The absence of log backups simplifies managing backup and restore.

    Tips: The simple recovery model is not appropriate for systems where loss of recent changes is unacceptable. In such cases, full recovery model is preferred.

    We should never run a transaction log backup on a database in the Simple Recovery model. Even though there is option to select all databases, we can select either database that are not in Simple Recovery model or change the database to the Full or Bulk-logged Recovery models to avoid this error. This problem has been fixed in SQL Server 2005 as the databases in simple recovery are ignored automatically.

  3. The third error says we cannot select master database and choose backup of transaction log because only full database backups of master database are allowed. Hence, we can create a separate maintenance plan for master database and not backup the transaction log or set master database to Full recovery and do transaction log backup.
  4. To solve the fourth error, we will not use strings that contain “-S” for a directory path. It only occurs on named instances of SQL Server 2000.
  5. The problem with less storage can be solved by allocating more disk space.
  6. If the error occurred due to sixth reason, then we can select a single or some databases while back up and keep in mind the type of recovery models with respect to transaction log backup.
  7. To perform transaction log backup of the database, we need to configure MS SQL Server to run under a user account that has administrator privileges to the machine.
 
Leave a comment

Posted by on December 21, 2015 in backups

 

Tags: ,

sys.foreign_keys does not have matching row in sys.indexes

Running DBCC CHECKDB you are getting following error message:

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes

This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK constraint (FK object_id 2040565179).  This should not happen, SQL Server will not allow you to drop a constraint that is referenced by FK.  If attempted should get following error message:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘dbo.a.NonClusteredIndex-20151119-085219’. It is being used for FOREIGN KEY constraint enforcement.

So if we are suppose to get errors? Why do we have corruption; simple answer, someone be making updates to system tables directly, which is not allowed or supported!

Actually we are not able to update system tables in SQL Server 2005+ (ref), however in SQL 2000 days, we had setting called allow updates in sp_configure options.  Also supported by the KB2787112.

So question is how do you fix it?

First, identify the child table name from sys.foreign_keys:

SELECT object_name(parent_object_id) AS TableName
  FROM sys.foreign_keys
WHERE name = ‘FK_b_a’

Second, script our constraint definition:

  1. Find the table, we got in SQL Statement above.
  2. Go to Keys.
  3. Right click on FK constraint name.
  4. Script Key As.
  5. Create To.
  6. New Query Window.

Third, drop the FK constraint:

ALTER TABLE [schema].[tablename] DROP CONSTRAINT [fk_constraint_name]

Fourth, Re-create the constraint, with script generated in Step 2.

If it was issue of someone playing around in system tables, this should resolve it.  However, if you get error similar to below:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.a’ that match the referencing column list in the foreign key ‘FK_b_a’.

This means, that the key is missing in parent table and appropriate index needs be created before FK constraint can be created.  Since SQL doesn’t allow the index to be dropped there most likely are other corruption issues that have gone unnoticed.  If that is an issue, you will have to rely on your backups for recovery.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on November 20, 2015 in Administration

 

Tags: , , , , ,

Cumulative update package 9 for SQL Server 2012 SP2 is available now

HI Followers Microsoft Announced about new cumulative update for SQL Server 2012 SP2 (Cumulative update package 9) and it come to fix around 13 Point in SQL Server (Master Data Services , Reporting Services , High Availability , SQL security , SQL service )

the most interested fixed for me is :

5285719 321185 This update introduces a new global property that exposes the latest CU number that is currently installed on the SQL Server. After you install this update, you can obtain the CU number information by executing either of the following queries:

SELECT @@VERSIONSELECT SERVERPROPERTY(‘PRODUCTUPDATELEVEL’)Additionally, this update also adds the CU number information to the first line of the SQL Server error log.

5646993 3097636 FIX: Performance decrease when an application that has connection pooling frequently connects or disconnects in SQL Server
5699533 3051145 FIX: SQL Server may shut down when you use Service Broker in SQL Server 2012 or SQL Server 2014
4272469 2682488 FIX: Backup operation fails in a SQL Server database after you enable change tracking

For more information about the Hotfixes go here

To know More about new feature in SQL Server 2016 you can check it from HERE

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS ,Facebook WHO WE ARE

 

 
Leave a comment

Posted by on November 19, 2015 in General topics, New Feature

 

Tags: , , , , , ,

Change DB Owner on all Databases one time

this one of wonderful scripts and very important 

—Change DB Owner on All Databases
EXEC sp_MSforeachdb ‘IF ”?” NOT IN (”Master”,”tempDB”,”model”,”msdb”)
BEGIN
EXEC [?]..sp_changedbowner ”User Name”
END

To know More about new feature in SQL Server 2016 you can check it from HERE

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS ,Facebook WHO WE ARE

 
Leave a comment

Posted by on November 16, 2015 in Administration

 

Tags: , ,

 
%d bloggers like this: