Sql Server Database goes into Recovery mode

Nilesh M Prajapati

I have a window based application that creates new database every month programmatically and in that database i have single table which consists of 50 columns which also . But the problem is after crating database programmatically it falls in recovery mode. So my question is Why database falls in recovery mode.

Andre Oosthuizen

Your question is very generalized and can have any of many reasons why it crashes, without proper info (remember that we cannot see your screen to guess the cause) - some causes might be Incomplete Transactions, Corrupted Log Files and so much more... Without specific details about your database creation process and the exact error messages you are encountering, it's challenging to provide a precise solution.

Mycroft Holmes

This design sound like a disaster waiting to happen - oh wait it has happened. What possible reason would you create a new database for every months data? I'd wager that the 50 column table is not normalised, leading to a myriad of problems down the track. As to why the database falls into recovery mode, the Great Ghu only knows but given the above issues it is the least of your problems.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

CHill60

As has already been pointed out to you, that design is a bad idea. See SQL Server: The Problems of Having Thousands of Databases on a Single Instance[^] It's bad for lots of other reasons too. Say you wanted to produce some MI for an entire year's worth of data for your application. You would have to query 12 separate databases ending up with something like

SELECT Col1, Col2 FROM DB1.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB2.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB3.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB5.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB6.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB7.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB8.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB9.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB10.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB11.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB12.dbo.MyTable;

And did you spot my deliberate mistake? Even if you have a new table in the same database each month it doesn't really get any better...

SELECT Col1, Col2 FROM MyTable01
UNION ALL
SELECT Col1, Col2 FROM MyTable02
UNION ALL
SELECT Col1, Col2 FROM MyTable03
UNION ALL
SELECT Col1, Col2 FROM MyTable04
UNION ALL
SELECT Col1, Col2 FROM MyTable06
UNION ALL
SELECT Col1, Col2 FROM MyTable08
UNION ALL
SELECT Col1, Col2 FROM MyTable09
UNION ALL
SELECT Col1, Col2 FROM MyTable10
UNION ALL
SELECT Col1, Col2 FROM MyTable11
UNION ALL
SELECT Col1, Col2 FROM MyTable12;

But introduce a Date column into your table and suddenly your code becomes so much clearer

SELECT Col1, Col2, YEAR(MyDate), MONTH(MyDate) FROM MyTable;

Next steps are to look at normalizing your single table - see Database normalization description - Microsoft 365 Apps | Microsoft Learn[^] for some starters. Address these sort of problems first, to take

jschell

Nilesh M. Prajapati wrote:

creates new database every month...Why database falls in recovery mode.

My guess is that it would be due to how you 'create' it. Certainly if you start with a clean install of SQL Server (just the normal install databases) and if you run you app and it impacts that database, then yes it is specifically how you are creating it.