Database script not executing in SQL Server 2008

Question

In my SQL Server 2008 R2, I have a database script which generates successfully, but when I am trying to execute that script it only shows Executing query message and nothing happen. I had waited at-least 10 minutes for result but force fully I have to stop executing that query.

Note: All other queries are working normally, but only database script is not executing as explained above

I don't know what's going on...

More details: This thing is not happening on particular DataBase, it is a problem on all the database of my SQL Server.

lets see it by example.
In SQL Server 2008 R2, I have following type of script.

USE [master]
GO
/****** Object:  Database [BillingApplication]    Script Date: 01/22/2013 17:42:04 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'BillingApplication')
BEGIN
CREATE DATABASE [BillingApplication] ON  PRIMARY 
( NAME = N'BillingApplication', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BillingApplication.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'BillingApplication_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BillingApplication_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO
ALTER DATABASE [BillingApplication] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [BillingApplication].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [BillingApplication] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [BillingApplication] SET ANSI_NULLS OFF
GO
ALTER DATABASE [BillingApplication] SET ANSI_PADDING OFF
GO
ALTER DATABASE [BillingApplication] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [BillingApplication] SET ARITHABORT OFF
GO
ALTER DATABASE [BillingApplication] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [BillingApplication] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [BillingApplication] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [BillingApplication] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [BillingApplication] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [BillingApplication] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [BillingApplication] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [BillingApplication] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [BillingApplication] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [BillingApplication] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [BillingApplication] SET  DISABLE_BROKER
GO
ALTER DATABASE [BillingApplication] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [BillingApplication] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [BillingApplication] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [BillingApplication] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [BillingApplication] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [BillingApplication] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [BillingApplication] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [BillingApplication] SET  READ_WRITE
GO
ALTER DATABASE [BillingApplication] SET RECOVERY FULL
GO
ALTER DATABASE [BillingApplication] SET  MULTI_USER
GO
ALTER DATABASE [BillingApplication] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [BillingApplication] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'BillingApplication', N'ON'
GO
USE [BillingApplication]
GO
/****** Object:  Table [dbo].[tbCustBill]    Script Date: 01/22/2013 17:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbCustBill]') AND type in (N'U'))
BEGIN
                    -- And continue the entire script

Now as I had seen in SQL Server Profiler than the execution till following code work perfectly.

    ALTER DATABASE [BillingApplication] SET RECURSIVE_TRIGGERS OFF
        GO

And on the the next line executing become stop.
I don't know what's going on....
On force full stop of execution its generate error as below

Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

So, that's it may be some SQL Sever configuration problem...

Aaron Bertrand · Accepted Answer · 2013-01-22 14:50:59Z

4

I think you need to ensure you can place an exclusive lock on the database to change this setting. Even though you just created the database you may have established more than one connection to it. Make sure you disable IntelliSense in Management Studio, that you have no other windows connected to this database, and that you switch context to another database. Then set the database to single user, make your changes, and set it back:

USE master;
GO

ALTER DATABASE BillingApplication SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE BillingApplication SET RECURSIVE_TRIGGERS OFF;
GO

ALTER DATABASE BillingApplication SET DISABLE_BROKER;    
GO

... other changes ...

ALTER DATABASE BillingApplication SET MULTI_USER;
GO

If this still waits then you may be waiting on some very large transaction to roll back, and you can check in another window what you are waiting for by looking at sys.dm_exec_requests and/or sys.dm_os_waiting_tasks.

Though, if you are creating the database, why do you think you need to explicitly disable broker? It's not enabled by default...

answered Jan 22, 2013 at 14:50

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Aaron Bertrand Over a year ago

So did you try removing the SET DISABLE_BROKER line from your script? Did it work then? Did you try what I posted?

Sagar Upadhyay Over a year ago

I had tried both, I had removed Broker line and also made changes as u had said. but it still gives me error.....and show msg "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." Remember that I had scripted the DB, and threw that I had not make any changes...

Aaron Bertrand Over a year ago

So you set the database to SINGLE_USER and it still sat there executing query? Did you open another window and see what that spid is waiting on or who it is blocked by in sys.dm_exec_requests? Could you share that information with us?

Sagar Upadhyay Over a year ago

Which info u need from sys.dm_exec_requests?

Aaron Bertrand Over a year ago

You may find a number in blocking_session_id (so then go find out what that session is doing) and also you should see what wait_type and last_wait_type say.

|

Philip Kelley · Accepted Answer · 2013-01-22 14:43:02Z

0

You have

ALTER DATABASE [BillingApplication] SET DISABLE_BROKER

without ane ON or OFF. Set that and it should work.

Nope, that's not it. I'd guess it has something to do with the Service Broker sub-system, but that's not something I'm familiar with. Hopefully someone else who knows that system can answer this...\

answered Jan 22, 2013 at 14:43

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Collectives™ on Stack Overflow

Database script not executing in SQL Server 2008

2 Answers 2

13 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related