Tag Archives: sql server

CREATE DATABASE failed on SQL Server FCI cluster: Only formatted files on which the cluster resource of the server has a dependency can be used

Problem

Today, I have a request to create a new database on two-node SQL Server 2016 failover cluster instance, which is in an active/passive configuration. I connected to the clustered SQL Server instance and ran the CREATE DATABASE command similar to the one below:

USE [master]
GO

CREATE DATABASE [MyDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MyDatabase_001_Data', FILENAME = N'E:\DATA_MyServer\MyDatabase.mdf' , SIZE = 2097152KB , MAXSIZE = 2097152KB , FILEGROWTH = 262144KB )
 LOG ON
( NAME = N'MyDatabase_001_Log', FILENAME = N'E:\LOG_MyServer\MyDatabase_log.ldf' , SIZE = 4390912KB , MAXSIZE = 2048GB , FILEGROWTH = 262144KB )
GO

However, when I executed the command, I received the following error:

Msg 5184, Level 16, State 2, Line 1
Cannot use file 'E:\DATA_MyServer\MyDatabase.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Reason for error

This error is self-explanatory. This error occurs when the disks resources containing SQL Server database data and log files are not added as a dependency of the SQL Server cluster resource.

Solution

  • Open the Failover Cluster Manager snap-in.
  • Select the SQL Server resource, open the Properties dialog box, and use the Dependencies tab to add the disk to the set of SQL Server dependencies.
  • Next, click OK to save the setting and to close the Properties dialog box.
Advertisement

Database Issue: The transaction log for database ‘db_name’ is full due to ‘ACTIVE_BACKUP_OR_RESTORE’.

Today, I was called in the early hours because the transaction log of one of the SQL Server 2016 database was full. I logged onto the SQL server instance via SQL Server Management Studio, and saw the following errors in the SQL Server error log:

The transaction log for database 'OLTPDB' is full
due to 'ACTIVE_BACKUP_OR_RESTORE'.

Reason for failure

This occurs because the full backup for the ‘OLTPDB’ database was still running, and as a result, the transaction log of this database was not freed up for reuse. This caused the transactions that were running against this database to fail.

Issue resolution

As we know, the portion of the transaction log file that is used during the full backup operation must be maintained while the full backup operation is running, which will also be backed up at the end of the full backup operation. That’s because the full database backup contains all the database data at the start of the full backup operation plus the transition log used until the end of the full backup operation. Therefore, to fix the issue, I had to manually increase the size of the transaction log file, to accommodate the high load against the database.

My articles published on SSWUG.org from March 2014 to July 2014

The following is the list of my articles published on SSWUG.org from March 2014 to July 2014. To read these articles, you must have SSWUG.org standard-level membership.

SSWUG.org logo

SQL Server 2014 Replication, Published on July 29, 2014

You use replication to move data between servers. Replication is important in distributed environments, when archiving data, and as an inexpensive way to support high availability.

This article explores the different types of replication, the components involved in replication, and the steps necessary to configure replication.

Microsoft SQL Server 2014: Database Engine key new features, Published on July 15, 2014

Earlier this year, Microsoft released SQL Server 2014. Like previous releases of SQL Server, Microsoft further enhances the SQL Server Database Engine. Microsoft not only improved existing SQL Server Database Engine features, but also introduces many new Database Engine features, including new in-memory OLTP engine (aka Hekaton), Buffer Pool Extension (BPE), Updateable Columnstore indexes, encryption for backups, Resource Governor enhancements for physical IO control, AlwaysOn enhancements, SQL Server Data Files in Windows Azure, Windows Azure Integrated Backups, new design for Cardinality Estimation, Incremental Statistics, Security enhancements, Transact-SQL enhancements, and so on. With the help of these new features of SQL Server 2014 Database Engine we can design, build and deploy mission-critical database applications.

This three part article series discusses in-detail the key new features of the Microsoft SQL Server 2014 Database Engine.

Developing Report Models, Published on July 8, 2014

A report model is a metadata description of a data source. It contains a business model of a data, a physical model of the underlying database, and a mapping between the two. The business model, also known as a semantic model, describes the data by using familiar business names, usually recorded in a business lexicon.

This article reviews the benefit of using report models.

Guidelines and Best Practices for developing and implementing a Reporting Solution, Published on June 6, 2014

Your reporting solution design should match technologies provided by Microsoft SQL Server™ 2014 Reporting Services to the requirements and abilities of users, and the requirements of the business. The documents that make up your reporting specification should also describe the purpose, data content, layout, and user interaction that are required for each report.

After you have an appropriate design in place, the next step is to develop the reports as effectively as possible by using appropriate tools that Microsoft SQL Server 2014 Data Tools (SSDT 2014) provides. SQL Server 2014 Data Tools (SSDT 2014) is a Microsoft Visual Studio environment with enhancements that are specific to business intelligence solutions. SQL Server 2014 Data Tools (also known as SSDT 2014) is included with Microsoft SQL Server™ 2014. You use SQL Server 2014 Data Tools for creating and managing solutions and projects for Reporting Services reports and report-related items. SQL Server 2014 Data Tools provides the Report Designer authoring environment. In Report Designer, you can open, modify, preview, save, and deploy report definitions, shared data sources, shared datasets, and report parts. For more information about SQL Server 2014 Data Tools solutions, projects, project templates, and configurations used for Reporting Services, and the views, menus, toolbars, and shortcuts that you can use in Report Designer, refer to SQL Server 2014 Books Online article “Reporting Services in SQL Server 2014 Data Tools (SSDT 2014)”.

This two part article series offers up some basic high-level guidelines, best practices and considerations for developing and implementing reporting solutions with SQL Server 2014 Data Tools (SSDT).

Handling blocks and deadlock in SQL Server, Published on May 15, 2014

A database server should be able to service requests from a large number of concurrent users. When a database server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it.

This article offers up several options for troubleshooting and resolving blocks in SQL Server.

Understanding the purpose of SQL Server Agent, Published on April 21, 2014

The SQL Server Agent is a Windows service that runs scheduled jobs. A job is a set of one or more management tasks. The SQL Server Agent service depends on the SQL Server service.

This two part article series shows you how you can configure and manage SQL Server Agent service and jobs.

SQL Server Integration Service (SSIS): Import and Export wizards, Published on April 14, 2014

SQL Server includes SQL Server Integration Services (SSIS). SSIS is primarily a data transform and load utility, but its functionality goes beyond that. SSIS provides a flexible development environment that you can use to identify sources and destinations, processing actions, and decision logic. Several SQL Server data utilities, such as the Database Copy Wizard, are based on SSIS.

This article shows technique of importing and exporting data to and from SQL Server database using SQL Server Import and Export Wizard.

Diving deep into SQL Server Integration Services Transactions, Published on March 31, 2014

A transaction is a logical unit of work made up of one or more tasks. The concept of transaction usually applies to a relational database such as those in Microsoft SQL Server. However, in Integration Services, you can create a transaction that includes any task or container. In general, a transaction is considered to have four primary characteristics: atomicity, consistency, isolation, and durability (ACID).

This article provides an overview of transactions and there characteristics. It also shows how you can implement transactions in an SQL Server Integration Services packages.

I hope you will find these articles useful. 🙂

Have a good weekend!

Basit

Book: SQL Server 2014 Development Essentials

I am very happy to announce that my first book “SQL Server 2014 Development Essentials” has now been published by Packt Publishing.

SQL Server 2014 Development Essentials, ISBN: 9781782172550

SQL Server 2014 Development Essentials, ISBN: 9781782172550

SQL Server 2014 Development Essentials is an easy-to-follow yet comprehensive guide that is full of hands-on examples, which you can follow to successfully design, build, and deploy mission-critical database applications with SQL Server 2014. You can view and download the table of contents, sample chapter, code files and the preface of this title on Packt Publishing website here.

Who this book is for?

If you are a database developer, architect, or administrator who wants to learn how to design, implement, and deliver a successful database solution with SQL Server 2014, then this book is for you. This book will provide you with all the skills you need to successfully create, design, and deploy databases using SQL Server 2014. You will also learn how to add, modify, and delete data stored within a database. You will use Transact-SQL statements to create and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers. Finally, you will learn about how SQL Server works, how indexes and statistics improve query performance, and the new SQL Server 2014 in-memory technologies.

From this book, you will learn:

  • Get introduced to SQL Server 2014’s new in-memory database engine
  • Understand SQL Server database architecture and relational database design
  • Use joins, subqueries, CTEs, and windowing functions to write advanced Transact-SQL queries
  • Learn about tools that let you monitor SQL Server database performance
  • Identify and troubleshoot blocks or deadlocks that might slow down a system
  • Design, create, and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers
  • Use SQL Server 2014’s structured error handling blocks to handle errors that occur in the Transact-SQL batches and programmable objects

You can order it at Packt Publishing, Amazon UK, Amazon.com and Barnes & Noble. It will also be available at O’Reilly Media and Safari Books Online.

Please feel free to contact me via twitter (@BasitAali) or via email, if you have any questions or feedback about this book.

SQL Server monitoring and optimization

To optimize the performance of your database, you need to monitor and tune. You determine the performance baseline, how SQL Server is currently performing, through monitoring. Your optimization process includes making changes and then monitoring their effect. This means adjusting two overlapping performance areas, that is, database server performance and query performance.

Click here to read full article on SSWUG.org…

Bulk Transfers: Some guidelines

Bulk transfers are a common way of importing large amounts of data into, or exporting large amounts of data out of, SQL Server databases.  SQL Server supports bulk transfers run from an operating system command line or the SQL Server command processor. When performing bulk transfers, you must consider the implications regarding the impact on server performance, file sizes (especially for transaction log file), and data access.

Click here to read full article on SSWUG.org…

Up or out? Deciding on SQL Server scalability

Scalability is the ability of a system to support an increased workload by adding incremental system resources without fundamentally changing the system design or architecture. We must distinguish the scalability from reliability, because reliability is the ability of a system to manage the workload without experiencing failures or stopping altogether.

Checkout my article here, in which I discussed the considerations for SQL Server scalability.

This article is published on SearchSQLServer.techtarget.com.

SQL Server upgrade paths: What to do before, during, after upgrading

Creating SQL Server upgrade paths from one version to another is a task that every database administrator is eager to undertake. However, upgrading SQL Server is not an easy task, and improper planning increases the chance of derailment and delays.

Checkout my article here, in which I discussed what to do before, during, after upgrading SQL Server upgrade projects.

This article is published on SearchSQLServer.techtarget.com.