Discussing ways to easily recover SQL Server 2000 suspect database

-->

MS SQL Server 2000 is widely used all over the world as the data storage component of several large websites. It is a cross-platform application that supports various advanced and user-friendly features, such as Microsoft search service, federated servers, indexed views, etc. It can help you achieve the desired performance levels required for running large web applications. However, an SQL database may occasionally become corrupt. The potential reasons responsible for this corruption are virus infection, hardware errors, abnormal system shutdown, etc. As a consequence, all valuable objects in the database become inaccessible. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.

Suppose, you use MS SQL Server 2000. While trying to access one of your databases, you may receive the following error message on your screen:

“96/11/18 10:48:32.60 kernel udopen: Operating System Error 32 (The process cannot access the file because it is being used by another process.) during the creation/opening of physical device,
C:\DATA\SQL\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF)”

Cause: The above error occurs because you are trying to access a database that is marked as 'suspect'. There could be a number of reasons responsible for this, such as database corruption, missing device driver, database being moved to another location, etc.

This would render your SQL database inaccessible and lead to severe data loss. At this stage, you may use an updated backup to restore the database. However, if the backup is unavailable, you should follow the below mentioned resolution steps.  

Resolution: You can try to resolve the issue by following any of the given methods:

• Make sure that your system has all the necessary device driver files.
• If your SQL database is corrupt, you need to follow the given procedure.

• First, drop and recreate all indexes in your database.
• Create a new database table and copy all records from the original table to this newly created table.
• Now, update the statistics.
• Run the following command:

'DBCC UPDATEUSAGE'.

• Finally, run the 'sp_recompile' procedure

If the problem still persists, you should take help from a competent SQL server recovery tool. These third-party software are proficient in repairing corrupt SQL databases to recover valuable objects, including tables, views, indexes, constraints, triggers, stored procedures, etc. They allow you to take a sneak peek at the database objects prior to recovery.

Read More

How to troubleshoot SQL server Error 9002

-->

SQL server database mainly contains two files, one is data file & another is log file. Data file contains the information about data while log file contains the information about transactions in the database. In simple recovery model, log files are managed automatically but in case of bulk-logged & full recovery model database administrators are responsible to manage the size transaction log file. You can perform this by shrinking the transaction log file.

Sometimes, SQL server database generates an error message 9002 when it found insufficient log space. This error message will appear when SQL server database is online or in recovery mode. If database is online and struggling with log space problem then you can read the database but unable to do any update on it. If log fills during recovery then SQL server marks database in suspect mode. 

Space Full

-->

The log file for database '%.*ls' is full. Back up the transaction log for the database to free up some log space.

 

Work Around: As error message states, if the size of log file is full then immediately take the backup of database and issue some free space for the log file. Now the point comes in your mind, how to perform this. To do this follow the below given steps:

• Backup the transaction log.
• Issue some disk space.
• Move the log file to a disk drive (secondary storage media) with sufficient space.
• Enlarge the log file.

These are the possible user action required to perform the SQL server recovery. Detail description is given below:

Backup the transaction log: If you are using simple recovery model then backup of transaction log is not possible. Take the backup of transaction log file, if you are using bulk-logged or full recovery model.

Issue some disk space: Regardless of recovery models, freeing the disk space allow recovery system to enlarge the transaction log file automatically.

Move the log file to a disk drive: If you are unable to free some disk space to the transaction log file then move the log files to a disk drive with sufficient space.

Read More

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