SQL SERVER – FIX – The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run

The best way one can learn SQL Server is by trying out things on their own and I am no different. I constantly am trying to explore the various options one can use when working with SQL Server. In the same context, when I was playing around with backup restore commands, I made a mistake and unfortunately restarted SQL Server. After that I was unable to start SQL Service.  If I start the service, it doesn’t give any error but gets stop automatically.

Whenever I have any weird problems with SQL, I always look at ERRORLOG files for that instance. If you don’t know the location of Errorlog, you should refer Balmukund’s blog (Help : Where is SQL Server ErrorLog?)

This is what I found in ERROLROG just before the stop.

2014-10-28 002039.02 spid9s      Starting up database 'model'.
2014-10-28 002040.01 spid9s      The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2014-10-28 002040.04 spid9s      Error 927, Severity 14, State 2.
2014-10-28 002040.04 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

The error and behavior which I am seeing makes sense because to start SQL Server, we need master, model and tempdb database. You might think that MSDB is also a system database and would be needed for SQL Engine? Well, you might have been tricked. MSDB is needed for SQL Server Agent Service, not SQL Server Service. So, my master is fine, model has some problem. Every new database is created using model, including TempDB so SQL Service is refusing to start. Since the model database is not recovered successfully, SQL Server cannot create the tempdb database, and the instance of SQL Server does not start understandably.

So I called up Balmukund – these are the perks of having a good friend to rely. He never says “no” but he also doesn’t give complete solution to the problem. He gives hint and asks me to research further. This time also magical words were – “use trace flag 3608 and restore model with recovery”.

I followed his advice and performed below steps.

1. Start SQL Server with trace flag 3608 using net start command

Net Start MSSQL$SQL2014 /T3608

In my case SQL2014 is the name of the instance. If you have default instance then service name would be MSSQLServer. For named instance, it is MSSQL$InstanceNameHere

2. After starting with trace flag 3608, I verified the same from Errorlog as well.

Further, I also found below message in ERRORLOG.

Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

3. Connected to SQL Instance using SQLCMD by below command.

SQLCMD -S .\SQL2014 -E

You can read parameter of SQLCMD at Books online here

“1>” means we are connected to SQL Instance and then Executed below command (hit enter at end of each line)

RESTORE DATABASE Model WITH RECOVERY
GO

4. Once the command is executed successfully, we will come back to “1>” again. We can type exit to come out of SQLCMD

5. Now stop SQL Service

Net Stop MSSQL$SQL2014

6. And start again without trace flag.

Net Start MSSQL$SQL2014

Now my SQL instance came up happily and I was unblocked.  After sometime I got call from Balmukund asking if SQL is started and I told that my next blog is ready on the same topic.  He finally asked, how did that happen? And my answer was – I ran wrong command. My command was

BACKUP DATABASE model TO DISK = 'Full.bak'
GO
BACKUP LOG model TO DISK = 'Log.trn' WITH NORECOVERY
GO

My request to my reader is that please DONOT run the above command in your SQL instance and restart SQL else you need to follow the steps in production server. Learning never stops when working with SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database – SSMS

This is a follow up of the blog post I have posted on error 3154 few years ago. I have received quite a few emails that how we can fix the same error with the help of SQL Server Management Studio. In this blog post, I will explain how we can do the same with SSMS. Here is the error code:

Error 3154: The backup set holds a backup of a database other than the existing database.

You can fix that with the help of T-SQL with the following command. 

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE

If you want to do the same with SSMS, you can follow the steps here:

Step 1: Create a database with your preferred name. (In our case AdventureWorks)

Step 2: Write click on the database and click on Tasks >> Restore >> Database…

Step 3: On the restore screen go to third selection of Options. Now select the checkbox “Overwrite the existing database (WITH REPLACE)”

Step 4: Click OK. It should successfully restore the database.

Note: When you restore a database WITH REPLACE it will overwrite the old database.

Relevant Blog Post:

FIX : Error 3154: The backup set holds a backup of a database other than the existing database

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)

Recently I formatted my computer and installed fresh SQL Server in it. I installed AdventureWorks database in my database. Once done I wanted to run few test scripts on my database. Just like every DBA, I decided to take backup of my database – this way I can restore it back to attain original database state. As soon as I ran the backup command I ended up with following error. This error is due to permissions issue on the local disk and user account which is running SQL Server.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\AdventureWorks.bak’. Operating system error 5(Access is denied.).

Fix/Resolution/Workaround:

I checked which user account my SQL Server services are running.

I gave full permission to that account to get read of the error.

NOTE: This whole experience was done on the my personal machine and not on production server. Please check with your network administrator for permission on production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 32 Bit – 64 Bit – HTML5 – Database Backup Restore

During TechEd India I was attending HTML5 session along with regular Database sessions. Couple of attendees were discussing database there and I find the incidence very interesting.

Note: Please take the whole discussion keeping humor as primary. There is no incident to disrespect anybody.

Attendee1: I wonder if this HTML5 works on 64-bit machine – hopefully they support it?
Attendee2: I think they will create HTML5 such a way that it will work on 32-bit and 64-bit machine.
Attendee1: Yeah they should.
Attendee2: I wonder how much work the product team developer have to do – they have to create 32-bit browser which supports 32-bit and 64-bit HTML5 and on 64-bit browser support for 32-bit and 64-bit HTML5.

I could not stop myself from speaking as I was listening this loud conversation sitting behind them.

Pinal: Hey, I think HTML5 is markup language and it will work on 32-bit and 64-bit browser without any special requirement. Just like earlier version of HTML there is no different coding for 32-bit or 64-bit browser.

Attendee1: Really!
Attednee2: Are you web developer too?
Pinal: Yes, I am web developer but I love to work on database technology.
Attendee1: Are you sure regarding there is no specific HTML for 64-bit machine?
Attendee2: Yeah. Tell me. Does your database back up from 64-bit work on 32-bit machine?
Pinal: Yes. You can restore database backup from 64-bit to 32-bit machine without issue, or vice-a-versa. Database backup is just a file and it can work independent of the version. Just like TXT file or JPG file.

Both of the attendees got very quite. Finally they whispered.

Attendee1: (Whispering) Let us try out what he is suggesting.
Attendee2: (Nodding Yes)
Pinal: (Looking at them with Wide Open Eyes).

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

I am really enjoying writing about computed column and its effect in terms of storage. Before I go on with this topic, I suggest you read the earlier articles about computed column to get the complete context.

This is the list of the all the articles in the series of computed column.

SQL SERVER – Computed Column – PERSISTED and Storage

This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance

This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2

This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3

This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

This article talks about how creating index on computed column does not grow the row length of table.

SQL SERVER – Computed Columns – Index and Performance

This article summarized all the articles related to computed columns.

This article actually originates from the questions asked by one of the smartest attendees I have ever met in training a few weeks ago. He really impressed me with these good questions. He asked me this: “if we create the index on computed column, does it increase the data size of the original table? In other words, does the computed column become persisted if we create an index on it?

This is a great question and the answer is rather very simple: No. It does not increase the data size of the original table as well as it does not turn the column into persisted. When we create an index on the column of the table, there is an additional space occupied by that index. Let us run the following code and understand this behavior.

USE tempdb
GO
-- Create Table
CREATE TABLE CompCol (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
-- Insert One Hundred Thousand Records
INSERT INTO CompCol (ID,FirstName,LastName)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Add Computed Column
ALTER TABLE dbo.CompCol ADD
FullName AS (FirstName+' '+LastName)
GO
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Create non clustered index on Computed Column
CREATE NONCLUSTERED INDEX IX_CompCol_FullName
ON dbo.CompCol (FullName)
GO
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.CompCol ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Clean up Database
DROP TABLE CompCol
GO

Here we have done the following efforts:

  1. Basic table and measured the used space
  2. Computed column and measured the used space
  3. Index on computed column and measured the used space
  4. Table and measured the used space

Now let us observe the resultset.

Taking a look at the result, it is very clear that columns made persisted only takes additional spaces. On the other hand, when an index is created, it does not increase the data size of the table; rather, the index created just uses up the unused space in the table.

Let me know your opinion about this series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Computed Column – PERSISTED and Storage

This is the list of the all the articles in the series of computed column.

SQL SERVER – Computed Column – PERSISTED and Storage

This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance

This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2

This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3

This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

This article talks about how creating index on computed column does not grow the row length of table.

SQL SERVER – Computed Columns – Index and Performance

This article summarized all the articles related to computed columns.

Every time I go for SQL Server Training or Consultation, I always learn something from my attendees. Sometime I even learn that a simple concept which we think is easy and whole world knows, many times very few knows about the same. In recent training I was surprised to know that many people think that as soon as computed columns are created the column is materialized and the data is now stored in the column just like usual. In fact this is not true. If computed column is not marked as persisted, it is not created when the column is created, in fact it is still computed at run time. Once you mark column as persisted, it is computed right away and stored in the data table.

Let us see quickly following example of the creating computed column.

USE tempdb
GO
-- Create Table
CREATE TABLE UDFEffect (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
-- Insert One Hundred Thousand Records
INSERT INTO UDFEffect (ID,FirstName,LastName)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Add Computed Column
ALTER TABLE dbo.UDFEffect ADD
FullName AS (FirstName+' '+LastName)
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.UDFEffect ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Clean up Database
DROP TABLE UDFEffect
GO

I have used the system stored procedure sp_spaceused to find out the space used in the query.

From the resultset it is very clear that when I created the computed column, it did not take any additional space in the database. However, when I created computed column marked as PERSISTED it indeed took more space in the data table and the size of the table is grown larger.

I hope this clear it up. In future article I will write performance and efficiency of the computed columns.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Storage and SQL Server Capacity Planning and configuration – SharePoint Server 2010

Just a day ago, I was asked how do you plan SQL Server Storage Capacity. Here is the excellent article published by Microsoft regarding SQL Server capacity planning for SharePoint 2010. This article touches all the vital areas of this subject. Here are the bullet points for the same.

  • Gather storage and SQL Server space and I/O requirements
  • Choose SQL Server version and edition
  • Design storage architecture based on capacity and IO requirements
  • Determine memory requirements
  • Understand network topology requirements
  • Configure SQL Server
  • Validate storage performance and reliability

Read the original article published by Microsoft here: Storage and SQL Server Capacity Planning and configuration – SharePoint Server 2010.

The question to all the SharePoint developers and administrator that if they use the whitepapers and articles to decide the capacity or they just start with application and as they progress they plan the storage? Please let me know your opinion.

Reference: Pinal Dave (http://blog.SQLAuthority.com)