Unable to Start MS SQL Server Database

Database Users or administrators can start Microsoft SQL server database from the task bar. To start, go to the task bar, open sq server service manager and then click on the start button. Sometimes MS SQL server is not start through this method and give some error message like

2011-11-20 10:15:39.87 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

Most possible reason for the above error message is “model is corrupt”. To resolve this, you can create a new copy of tempdb. This will fix the starting problem of SQL server database. If you still face the same problem then first of all make the backup of all MDF & LDF files.

How to Make Backup when Unable to Start SQL Server? As we have discussed, you are unable to start sql server then take a backup of data & log files by below steps:

• Set SQL server service manual by Windows control panel.
• Reboots the windows.
• Copy all the data and log file to the another computer or any removable media.

Attention: Don't try any methods until you have backup.

Have Backup? If Yes then use another copy of sql server with same version and level to restore model database using model.mdf & model.ldf. After restore, detach model database from new sql server and copy the data & log files to replace problem server. It is little bit messy to recover SQL server but not hard. In simple words, we can say that you will have to perform three steps:

        Restore-->Detach-->Copy (data & log files)

Doesn't Have Backup? If you don't have any backup of sql server database then it is recommended you to install a new copy of MS SQL server. When installation is complete the stop the service and copy the Model file to replace problem server.

Summary: SQL server database starting problem can be resolved by two method, one is restore-detach and another is new installation. Restore-detach method is hard in comparison of new installation method but it is effective.

Read More

Exploring the concept of Page Corruption in MS SQL Server databases

No matter how reliable your database seems to be, you always have issues on the performance grounds. More interestingly, I found out that many performance issues relate to page corruption in SQL Server. It would be good to understand here what exactly is meant by ‘BAD_PAGE_PROCESS’. It usually occurs when the background suspect page logger stops running after every five seconds because of a large number of suspect pages. If you want to checkout suspect pages, run the following T-SQL command:

“select * from msdb.dbo.suspect_pages”

I took the help of DBCC CHECKDB on a corrupt SQL database to find out the actual issue and whether it is able to resolve it. Ran the following command:

“DBCC CHECKDB () WITH ALL_ERRORMSGS, NO_INFOMSGS”

Got errors similar to this one:

“Table error: Allocation page (1:1002912) has invalid PFS_PAGE page header values. Type is 0. Check type, alloc unit ID and page ID on the page.”

The PFS pages can neither be deleted nor reconstructed as there is no easy way to examine which pages are allocated or not.

Now, I tried to view the contents of the page by using the ‘DBCC PAGE’ command as follows:

“dbcc traceon(3604)

go

dbcc page(,1,1002854,0)”

After looking at the content of the page, I found that there was some corruption in the next page pointers. This seemed to be a bit weird as initially the next page pointer was like:

“m_nextPage = (1: 1002855)”

When I decided to look at the next page and ran:

“dbcc traceon(3604)

go

dbcc page('GBLDataWarehouse',1,1002855,0)

go”

It was now pointing to page 0:

“m_nextPage = (0:0)” & “m_prevPage = (0:0)”

Finally, used a recent backup to restore the database and got it up and running once again. But again, it was wise to see where the issue occurred.

Read More

Transaction Log File & Recovery Models

SQL Server transaction log file is used to store recent database transactions. When you modify or alter any entity in SQL server database then it will be result in a write action in the transaction log file. Generally we do several activity on the SQL server database like add, delete, rename, update, insert and many more and this will also be the result of write action in the transaction log file but a read action on the SQL server database does not change the transaction log file.

Does recovery models affect transaction log? 

• The answer is NO. Recovery models (simple, full & bulk-logged) do not affect what has written into the transaction log but it effects the time period of entries that present in the transaction log, detail description is as follows:

1. Simple Recovery Model: In this recovery model, transaction log entries are keeping for the database integrity not for the database recovery purpose. When we run checkpoint operation on the transaction log files then all the inactive entries are deleted and space is ready for reuse. It is also the best way to manage the size of transaction log file but you can prefer another way also to manage size of transaction log file.
2. Full Recovery Model: In this recovery model, entries are key for the both integrity as well as for database recovery purpose. All the entries are remains present in the transaction log file until a log backup does not perform on the file. Sometimes it difficult to manage the size of transaction log files through this recovery model.  
3. Bulk-logged Recovery Model: It is almost same as full recovery model but in this recovery model bulk operations are minimally logged.

How to Monitor Size of Transaction log file?

• You can monitor the size of transaction log file by DBCC SQLPERF command. When will you run this command then it returns information about amount of log space used currently and it also indicates when transaction log is in need to truncate. Log truncation frees the used disk space but does not reduce the size of physical transaction log file. To reduce transaction log file size, you will have to shrink the log file.

How to shrink transaction log file in full recovery model?

• You can shrink the transaction log file manually by the using of DBCC SHRINKFILE command. This command may be differ for the different SQL server database.

Read More

How to Fix Error “Unable to attach database” In SQL server?

-->

Last Tuesday, we have faced a scary situation when my friend's hard drive has crashed and he has not maintain any backup for the database then he has used data recovery services offered by stellar data recovery and retrieve his database and log files. After retrieving database and log files, he came to me and ask how to fix error 5173 and 824.

After retrieving database and log files from the crashed server, he had tried to attach the SQL server database but got error messages 5173 & 824 and unable to access his database. He can't recover SQL database from backup because i have mentioned above he has not maintain any backup.

How to Fix: We have followed below steps to fix the errors.

• We have created a database with same name and same SQL data files (MDF & NDF).
• Make the file size identical as original SQL server database
• Stop the SQL server database services.
• Rename the old MDF to new one and copy the old MDF to new MDF location
• Delete the LDF files.
• Start SQL server database services
• Our database has gone in suspect mode.
• Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up Sp_configure “allow updates”, 1
  go
  Reconfigure with override
  go
  Update sysdatabases set status = 32768 where name = “corruptDBName”
  go
  Sp_configure “allow updates”, 0
  go
  Reconfigure with override
  go
• Now restart SQL server database.
• Our database is in emergency mode.
• Run DBCC CHECKDB on our database with repair_allow_data_loss, that will rebuild the log files and full repair.
• Again restart SQL server database and now our database is online.    

Read More

How to Move SQL server user Database?

-->

Microsoft SQL server has two types of database, one is system database and another is user database. A system database is used by the SQL server for its own maintenance and management while a user database is used by the database administrator for storing user data. In this article, we will discuss about to “How to move SQL server user database within same instance”.

A database administrator can move data and log files of user database to a new location by specifying the new location in a filename clause of ALTER database statement. To move a SQL server user database follow the below steps:

1. Set the database off-line through this command

ALTER DATABASE database_name SET OFFLINE;

2. Move the files to a designation location

Run this command for each move file.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = ' new_path\os_file_name' );

3. Now set the database online through this command

ALTER DATABASE database_name SET ONLINE;

4. Verify the changes

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'');

Note: The above method is applied to moving database within the same instance of SQL server database. To move database from one instance to another, you can try backup and restore method.

Example: Suppose we have a log file sqlserverdatabase and want to move in to a new location.

Use master;

Go

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'sqlserverdatabase')

AND type_desc = N'LOG';

Go

ALTER DATABASE sqlserverdatabase SET OFFLINE;

GO

ALTER DATABASE sqlserverdatabase MODIFY FILE ( NAME = sqlserverdatabase_log, FILENAME = 'C:\NewLoc\sqlserverdatabase_Log.ldf');

Go

ALTER DATABASE sqlserverdatabase SET ONLINE;

GO

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'sqlserverdatabase');

AND type_desc = N'LOG';

Read More