SQL Server Databases in Recovery Pending state

If you encounter an error database state “Recovery Pending” don’t panic you’re not aloneJ.  This is because SQL server has encountered improper shutdown or not mounted properly. Possible reason insufficient permission to the DB file folder may prevent the recovery. Follow the steps mentioned below it might help…

 

 

You can see the various state definitions from following link

 

http://msdn.microsoft.com/en-IN/library/ms190442(v=sql.100).aspx

 

 

If you got this error don’t try to delete the database. Following steps may help you to recover.

 

1.      Restart your sql server instance and check the database state. Still if it is not recovered then continue with next step

2.      Wait for some time (5 or 10 mines) probability it will get online, if not it will go to suspended mode.

3.      If it is in suspended state following link will help you to recover

http://www.codeproject.com/Articles/42736/Recover-SQL-Server-2005-Database-from-SUSPECT-Mode

 

4.      If still it continue with pending state run the following command

 

ALTER DATABASE <> SET OFFLINE WITH ROLLBACK IMMEDIATE

Go

ALTER DATABASE <> SET ONLINE WITH ROLLBACK IMMEDIATE

Go

       This command will help you identify the issue

      

       Ex: Sometime you may receive following error

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file "Abc.mdf". Operating system error 5: "5(Access is denied.)".

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file "Abc_log.ldf". Operating system error 5: "5(Access is denied.)".

Msg 5181, Level 16, State 5, Line 1

Could not restart database "Abc". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

5.       Try to give proper access rights to the database file folder and restart the SQL Server.

a.       Check if files are in read-only mode or not. 

b.      Check the SQL server service account it is has access to database file folder.

 

Reliability

Reliability is the ability of a system to perform and maintain its routine circumstances as well as unexpected circumstances.  In other word we can say ability to perform system without any failure.

The Reliability defined by Institute of Electrical and Electronic Engineers (IEEE) as

“The probability that software will not cause a system failure for a specified time under specified conditions.  The probability is a function of the inputs to, and use of, the system as well as function of the existence of faults in the software. The input to the system determines whether existing faults, if any, are encountered. “

John Musa of AT&T Bell Laboratories defines as follows

“ The probability that a given software system operates for some time without software error, on the machine for  which it was designed, given that it is used with in design limit”

Now a day’s software is become driving point in, most of the business, so reliability of software is more important.

Few objectives for software reliability as follows

• To meet requirement
• To perform software under a verity of the input and  the environment
• The exception to be handled and the details should be maintained for audit purpose.
• The application should be proceeding even the exceptions occurs.
•  Response time are not adequate due to I/O  and memory resources are not optimized

Few functional reliability well known errors

•  In ecommerce   credit card traction will be completed before you submit an order
•  Bills are calculated wrongly or send to wrong person.
•  In banking amount are credited from the wrong account

Few non- functional reliability well known errors 

•  Due to improper access management, getting an access to unauthorized area.
• User is not informed when error is occurred

Software reliability needs to address in all our SDLC process.   In the design phase we should have a proper architecture to address the reliability issues like security, exception management and auditing etc.  Proper coding standard document should be available for developer like how to do string concatenation, where to use session etc,.  In the coding phase code has to reviewed and make sure it follows all the best practice and architectural principals. In the testing phase application has to test completely with various scenarios and supported environments to increase the reliability.

Scalability

Scalability is nothing but how the entire system performs for number of user, session and transaction. In other word scalability is the capability to increase the resources based on the usage. The key consideration for the scalable application is based on the additional load adding additional resource will not require any application change.

Scalability something you have to think from day one while designing your application.  For example to increase the scalability adding a load balanced server is not meaning full if you designed your application for single system. In other case you designed a high scalable application and deployed in the low configured system will not help. So scalability should require a balance between software and hardware

To increase the scalability of the application we can either follow scale up or Scale out depends upon application architecture

Scale Up

Scale Up means increase the memory / better processor / migrate to powerful system ideally in single system increasing more capacity to perform better.  This approach doesn't require any addition development effort only it required system configuration.

Scale Out

Scale Out means distribute the processing load across multiple servers. The advantage of scale out is we can dedicate the several machine for commonly accessed task, it increases the applications fault tolerant. It required more administrative effort to manage more system, but we can move to cloud servers for better management. This will create a more design effort for example if you not considered location transparency in mind scaling out is not possible.

Good design is required for Scale out.  Few design consideration for better scalability

•  Identify the transaction which may take longer time to process, and analyze how we make those task make we make it asynchronous.  
• Use transaction where ever required.  For example inserting values into single tables doesn’t required any transaction because if it failed no need to rollback any other transaction.
• Open database connection as late as possible and release it as early as possible.
• Remove all single point bottlenecks while designing your architecture to support scale out.  Example Make your application to be stateless.
• Identify proper Database Scale out approach while designing you architecture.  ( Mater – Slave replication/  Database Clustering / Database Shading)
• The architecture should allow horizontal growth to add more servers.
• When designing a distributed application, logical separation should also be considered
• While using Dependency Injection/ Reflection/Annotations will have marginal performance issue, so make a careful choice when using these.
• Leverage Cache feature
• Using ORM some time may result performance issue, because of non-optimized quires.

Identify Scalability bottlenecks

To identify scalability bottlenecks it important to test and identify the major workloads and mitigate in the early stage. Scalability testing is the extension of performance testing.  Use performance test to arrive a baseline for application performance. Perform scalability testing systematic manner to ensure that all relevant data is collected.  Some of the scalability testing attributes needs to analyze

• Response Time
• Throughput
• Number users
• CPU memory usage
• Network Usage
• Web server

Work around to pass spam filter with PHP mail

Good artical to Pass Spam Filters with PHP mail . I haven't tried but seems to be good work around

Click here to read more