Need to repair corrupt SQL server database

If you are working as a SQL server DBA from a long time then you will have encountered the SQL database corruption once in a while of your DBA life. Often corruption occurs due to power failure, sudden shutdown to the SQL server, hardware malfunction, virus attack, and many more. You can't access the database until repaired the database files.

Additionally, there are several methods available to protect your database file from being corrupt. And it is equally related to your job. In the event of database corruption, the first and foremost question, have you maintained the backup of database? If you do not have backup of database then it is like a soldier had gone to the country's border to protect the country without arms. Soldier may die at the border but your database can't die if you have not the backup of your database. You have chance to recover your database by repairing it.

How to repair?

You can repair corrupt SQL server database by following methods:

1. Repair the database using DBCC CHECKDB command

DBCC CHECKDB is the best command to test and fix the consistency errors in the SQL server database. DBCC CHECKDB is a combination of three commands: DBCC CHECKCATALOG, DBCC CHECKTABLE, and DBCC CHECKALLOC. If you run DBCC CHECKDB against the database then no need to run all other three commands separately. This command has three options to repair a corrupt SQL server database and the commands are repair_fast, repair_rebuild, and repair_allow_database. There is no any data loss, when you run DBCC CHECKDB with repair_fast & repair_rebuild but you may loss some amount of data in repair_allow_data_loss option.

Here is the syntax of DBCC CHECKDB

DBCC CHECKDB

[

[ ( your_database_name | database_id | 0

[ , NOINDEX

| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

) ]

[ WITH

{

[ ALL_ERRORMSGS ]

[ , EXTENDED_LOGICAL_CHECKS ]

[ , NO_INFOMSGS ]

[ , TABLOCK ]

[ , ESTIMATEONLY ]

[ , { PHYSICAL_ONLY | DATA_PURITY } ]

}

]

]

Note: Don't run DBCC CHECKDB with repair_allow_data_loss against the database without knowing the effect of it.

2. Repair the database using 3^rd party SQL repair utility

There are so many data recovery companies available out there that offer SQL recovery utility to repair corrupt SQL server database. Let’s try any of the utility and see what they can do for you.

Note: First download the demo version of any software to measure the functionalities of the software.

Read More

FAQ: SQL Server Disaster Recovery

Q: Can I restore a SQL server database if I have data & log files?

A: Yes, you can restore a SQL server database if your data file & log file is not corrupt. You can do this by attaching data & log files to the SQL server.

Q: Can I restore a SQL server database if the data file is corrupt?

A: No, you cannot restore a SQL server database with a corrupt data file. You need to restore your database from a good known backup. If you don't have a good backup of your database, there is less change that you can do. There are some data recovery companies that claim they can recover data from the corrupt file by repairing the file. So you can go to the Internet and search the best SQL server mdf file recovery software and see what they can do for you.

Q: Can I attach a SQL server database without transaction log file if database was cleanly shutdown?

A: Yes, you can attach a SQL server database without transaction log file (.ldf) of the database. You can attach your database with the help of SQL Server Management Studio (SSMS). MS SQL server will create a transaction log file while attaching the database.

Q: Can I attach a SQL server database without transaction log file if database was not cleanly shutdown?

A: Yes, you can but method will be different. To do this, you have to perform below steps:

• Create a same size database as your previous database size
• Shutdown SQL server
• Change the old mdf file with new created mdf file
• Start SQL server, database may go in suspect mode
• Change the database mode from suspect to emergency by ALTER command
• Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS.

Q: Is there any In-built utility in SQL server to repair corrupt file like Compact & Repair in MS Access?

A: Yes, MS SQL server has DBCC CHECKDB that performs almost similar activities as Compact & Repair does. DBCC CHECKDB has three options; repair_fast, repair_rebuild & repair_allow_data_loss.

Q: Can I loss data after running DBCC CHECKDB with repair_allow_data_loss?

A: Yes, you may. As the name of command suggests you may loss the data from your database. In some cases it has been observed that database administrator has not lost the data after running DBCC CHECKDN with repair_allow_data_loss command against the database.

Q: Can I backup transaction log if my database is using Simple recovery model?

A: No, you can't backup transaction log file when you database is in simple recovery model.

Q: What can I do to avoid data loss from corruption?

A: Backup, Backup & Backup. It is the best solution to avoid any data loss from corruption. Additionally, running DBCC CHECKDB command against the database on regular interval is a good habit. You may know the corruption as soon as it occurs.

Q: What is the best time interval to run DBCC CHECKDB?

A: It depends upon your business need. If you can afford 2-3 days database downtime then run it on monthly basis but I don't think so a company can afford 2-3 days database downtime.

Read More

Methods to Resolve SQL server Backup Error 3007

After upgrading SQL server database from SQL server 2000 to SQL server 2005. When you perform a full database backup of a SQL server 2005 database, you may got following error message on your system screen.

Executing the query "Backup Database [ZZZ] TO [ZZZ]

WITH NOFORMAT, NOINIT, NAME = N'ZZZ_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10

"failed with the following error:

"The backup of the file or filegroup "sysft_ZZZ_FT" is not permitted because it is not online.

BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. BACKUP DATABASE is terminating abnormally.".

Cause: SQL server error 3007 may occur when fulltext catalog is offline for the database ZZZ.

Resolution: You can resolve this error message with the help of two methods: SQL server management studio and drop & rebuild catalog .

With SQL server management studio: It is the simplest action to resolve error 3007. Follow below steps to resolve it.

• Open SQL server management studio (SSMS).
• Now go-to the storage container.
• Check the state of fulltext catalog.
• If there is a problem then resolve it.
• Now you will able to backup your database.

With Drop & Rebuild Catalog: If you are still unable to backup your database then drop & rebuild the catalog if catalog is needed otherwise drop it.

Note: You can take backup of your database when rebuild start. There is no need to wait for rebuild catalog.

Read More

Available Methods for Sending a Mail from SQL Server

-->

Are you looking for a method that enables you to send a mail from your SQL server? For you Microsoft SQL server offers a special feature that will enable you to send a mail from it. Earlier to SQL server 2000, it is known as SQL mail but with release of SQL Server 2005 it has been changed & the new name is database mail. Database mail is very useful for sending a mail from SQL server to end users as well as to database administrator. It can contain data from files, queries, or many other resources available on the network.

What is SQL Mail? It is available in earlier version of SQL server 2005 & uses Message application programming interface (MAPI) to send mail. You can't use it without installation of outlook.

What is Database Mail? It is an enhanced version of SQL mail that is available in SQL server 2005 & upper versions. The best thing is that there is no any requirement to install outlook first to use it. It uses simple mail transfer protocol (SMTP) for sending a mail. In this article I would discuss about how to configure database mail in SQL server 2005 because SQL mail is a very old fashion.

How to Configure Database mail? You can configure database mail with the help of SQL server management studio. For exercise, we will learn how to setup a database mail using SQL Server Management Studio.

• To setup database mail in SQL server 2005, connect to the instances of SQL server database.
• Browse the management.
• Now right click from your mouse on database mail.
• Select configure database mail section.
• Choose one option from list for a particular task.

How to Check Database is enabled or not? If you want to check database mail is enabled or not on your database then connect to an instance of SQL server database by a query editor and execute bellow: 

 

sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO

Result: Go to the result pane and check “run_value” of the database. If run_value is 1 then database mail is enabled on your database otherwise not.

Read More

Best solution for you when your database is suspect mode due to insufficient space in filegroup?

SQL server uses filegroup for grouping all the database objects & files, there are two types of SQL server filegroup, one is primary & another is user-defined. Primary filegroup contains the primary data file and all the pages for system table while any other filegroups that are specified with Create or Alter statements are known as user-defined filegroups. No any file can be member of more than one filegroup and log file is not a part of any filegroup. It is managed from data space.

Note: One filegroup in each database is specified as a default filegroup. When you create a table or indexes in your database without specifying with any filegroup then it is assumed that all the pages are allocated from the default filegroup.

Consider a scenario wherein you are working on your SQL server 2000 database and suddenly unable to perform any operation on the database due to your database goes in suspect mode and got below error message:

Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full

Cause: As specified by the above error message, your specified filegroup has insufficient space.

Possible Solution: There are two possible solutions for the above error message and the solutions are:

1. Free Disk Space
2. Add a Data File

1. Free Disk Space: You can gain free disk space by increasing the size of any disk drive that contains files for filegroup and allow filegriup to grow or can gain space by adding a data file with the specified database. If you cannot increase the size of disk drive that contains filegroup then move the filegroup to another disk drive. Follow the steps to free disk space on another drive:

• Move the data file in filegroup with insufficient free disk space to another drive.
• Now detach the database by executing stored procedure sp_detach_db.

Systax for detach stored procedure

sp_detach_db [ @dbname= ] 'database_name'

[ , [ @skipchecks= ] 'skipchecks' ]

[ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

• And finally attach the database by stored procedure sp_attach_db

Systax for attach stored procedure

sp_attach_db [ @dbname= ] 'dbname'

, [ @filename1= ] 'filename_n' [ ,...16 ]

2. Add a Data File: You can add a file to specified database by using ADD file clause of ALTER database statement or enlarge the size of data file by using Modify clause of Alter database statement. 

Read More

Unraveled: Possible Resolution Methods to Work Around Database Corruption in SQL Server 2008

SQL Server 2008 has been specifically designed to work with large data sets and to handle multiple users. It facilitates ease of use and maintenance through its feature-laden application interface. It can easily interpret the bandwidth required by the users and considerably help to improve performance. However, it has a few downsides. Any unexpected server crash may leave your SQL database in a suspect state. This database corruption would thrive if no timely action is taken to counter the problem. In these circumstances, all your valuable database components become inaccessible. To overcome this problem, you should go for SQL server recovery.

Let us consider a real-world scenario. Suppose you use MS SQL Server 2008 on your Windows XP based computer. You may occasionally notice the below error message in your application log:

'Table error: Object ID O_ID, index ID I_ID, partition ID PN_ID, alloc unit ID A_ID (type TYPE). Duplicate keys on page P_ID1 slot SLOT1 and page P_ID2 slot SLOT2.'

The above error is generated by 'SQLengine' because there are two slots having the same or identical keys. In addition, the error may pop-up as a result of corruption in your SQL database.

To work around this problem, you need to isolate the cause of the error and accordingly, work out a solution. You can try the given steps in order to resolve the issue:

• Hardware failures and defects cause corruption in SQL databases more frequently than other sources. First off, you should view the SQL Server error log and the Windows system and application logs to check for hardware failures. If you encounter any hardware problem, run hardware diagnostics.
• If you continue to face problems, you should try swapping different hardware parts to examine the root cause. Additionally, ensure that the disk controller does not perform write caching.
• If the issue is not hardware-specific, you can try restoring the database from a recent clean backup.
• If you do not have a backup, run DBCC CHECKDB with an appropriate repair clause to fix database corruption.

If you receive the same error again, then take help of advanced third-party SQL database recovery software. These competent tools incorporate advanced, proprietary mechanisms to repair your damaged SQL database easily and recover all lost or inaccessible objects, such as tables, views, keys, indexes, constraints, and stored procedures. Furthermore, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.

Read More

A safe approach to database recovery in case of IAM chain corruption in SQL Server 2008

SQL Server 2008 is the best tool for database developers and administrators as it incorporates some of the major improvements over the earlier versions. It has support for large data sets and the ability to handle multiple users. The new 'Performance Studio' provides you with a collection of tools for easy monitoring and troubleshooting the system. It allows you to build up an efficient policy management system and help database administrators in carrying out various administrative tasks. Regardless of all these benefits, there are various contingencies that lead to corruption in the SQL databases. The potential reasons behind database corruption include virus infection, hardware failures, operating system bugs, etc. As a consequence, all the valuable objects in the SQL database become inaccessible. In order to recover your lost invaluable data, you should perform SQL recovery through proficient third-party software.


IAM chain corruption is a common occurrence on SQL Server 2008. This is usually accompanied by the below given error by DBCC CHECKDB:


"Msg 2576, Level 16, State 1, Line 1 
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:153) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594042384384 (type Unknown), but it was not detected in the scan."


The system table 'sysallocunits' usually has a reference to the first IAM page, the root page, and the first page. The above error says that metadata does not include a reference to the first IAM page in the IAM chain (page 1:153). This indicates corruption in the 'sysallocunits' system table.


If you try to use the 'repair_fast' and 'repair_rebuild' options in this case, you may get no significant results. In order to resolve the issue, you may try the following series of steps:

•     Start a transaction
•     Write the rowcounts for all system objects and tables in your database.
•     Now use CHECKDB for repairing the database.
•     Make sure that the problem is resolved and the error is not seen again
•     Rerun DBCC CHECKDB to verify the database.
•     Examine the rowcounts once again.
•     Commit the transaction

The 'repair_allow_data_loss' should be the minimum repair level for this case. If the problem persists, you may try professional SQL 2008 database recovery software. These third-party tools extensively scan SQL databases to recover damaged or inaccessible objects, including tables, views, indexes, constraints, triggers, etc. Moreover, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.

Read More

Repair Corrupt SQL Server Database

-->

In this blog post, I will try to explain all possible steps that you will have required to repair your corrupt SQL server database. Let's consider a case where you are working on SQL server database, trying to access some records from a table. During table access process you have got an error message that specifies, you can't access desired table due to database corruption. Corruption in SQL server tables can happen due to several reasons; some most prominent reasons are virus attack, power outage, improper system shutdown, hardware failure etc.

What to Do Now? It is the first question that will come in your mind. I will suggest some tips at this point.

• Don't Panic
• Keep Patience
• Take Backup of your database
• Check hardware configuration

How to Check Error Message?

Run DBCC CHECKDB command on the database, it will write a reported error message in SQL server error log. The reported error message will show you how many consistency errors are found.

For example: You may get below error message in your error log

2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.

Note: You can run DBCC CHECKDB command on the database any time no matter your database is online or offline but you will get information message only when DBCC CHECKDB will run on online database.

How to Fix Corruption: There are three methods that you can try to repair corrupt table. The methods are....

• Check Hardware Configuration
• Restore from Backup
• Run DBCC CHECK with repair_allow_data_loss

Check Hardware Configuration: First of all check your hardware configuration. If you are not a hardware geek then connect with your system support team and fix the issue.

Restore from Backup: If backup of corrupt table is available then restore from updated backup.

Run DBCC CHECKDB with repair_allow_data_loss: DBCC CHECKDB is a free utility offered by Microsoft to repair & recover SQL server database with minimum data loss.

ALTER Database Employee_Details Set Single_User

GO

DBCC CHECKDB (Employee_Details, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;

GO

ALTER Database Employee_Details set multi_user

GO

Read More

Causes & Resolutions for MS SQL Server Error 5120

-->

Are you getting SQL server error 5120, when you try to attach some database files to Microsoft SQL server? This may happen because you have selected a file that is in use by other SQL server instance or selected a wrong file for the attachment. To fix this, firstly you have to check the location and status of SQL server files that you want to attach.

The default location of data & log files are:

C:Program-files\MSSQL\Data\Database-Name_Data1.mdf

C:Program-files\MSSQL\Data\Database-Name_Log1.ldf

Note: Your data & log files may have different location. You can change it according to your need.

The error message that you may got is look like:

Unable to open the physical file "C:Program-files\MSSQL\Data\Database-Name_Data1.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

Possible Cause: As I have mentioned, this error message occurs due to many reasons but it may also occurs when you are not allowed to access the file directories. For the security purpose, database administrator does not allowed you to access the file directories. To access the file, you have to login as administrator or get permission to access the database files.

Resolution: As we know that solution is not rigid for sql database recovery. It may change according to the scenarios or causes. Here, we will discuss two scenarios and its possible solutions.

Solution 1: If you have found access permission problem with the database files then login as a administrator and fix the problem.

Solution 2: If you have found data file is present in C drive & log file is present in E drive then change the location of data or log and placed data and log files in a same drive. Now attach the database and fix it.

Point to Ponder: Generally, solution 1 is enough to solve Microsoft SQL error message 5129 because most of the database administrator (owner) does not allow every administrator to access the database files.

Read More

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

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

Restore SQL server Database in case of Corruption

MS SQL server database offers a high performance backup and restore facility to its database users for maintaining the backup of SQL server database and restore in case of corruption. SQL server database may be corrupt due to severely reasons like media failure, user errors, power failure, metadata structure corruption, virus attack and many more. High performance backup and restore facility enables database administrator to handle all above corruption issues in SQL server database. A well planed backup and restore strategy helps database administrator to protect their data in case of above mentioned failures. The point comes to describe backup.

Backup: A copied data that can be used in restoring the corrupted data is called backup.

How to make a backup of SQL server database? : A database administration can take a backup of SQL server database with the help of SQL server management studio. Below image is the screen-shot to make a backup in SQL server database.

Restore SQL server Database in case of Corruption?: A database administrator can restore corrupt SQL server database with the help of good backup but he/she will be also able to restore corrupt SQL database without any backup. In this article we will discuss about both two methods for restoring corrupt SQL server database.

Restore with Good Backup: A database administrator can use “backup & restore” method, if he has maintained a good backup for his database. Two machines are required in this method. One is source machine where corrupt database reside and another is designation machine where corrupted database will be restored. 

Note: Make sure following point before restoring the database

• The designation machine has sufficient space to restore.
• Directory structure on designation machine is must to exist.
• Don't have same file name on the designation machine.

Restore without Backup: A database administrator is still able to restore corrupt SQL server data files without any good backup. In this situation, you should take help from any Microsoft gold certified SQL server recovery software. Stellar Phoenix SQL recovery software is a Microsoft gold certified partner that repairs corrupt SQL server data files and its objects. It is the most recommended software by the database expert to repair corrupt SQL server database.

Read More

How to fix Error “Open Failed” in SQL server

When you are trying to start the services of SQL server database and found the database is unavailable. At this situation you are unable to do any operation on the SQL server database like insert, delete, rename, update, and many more or even unable to open the SQL server database. You can open and see SQL server error log to know the reasons for the problem. After knowing the reason, you can take appropriate method to recover sql server database. When you open the error log got an error message. The error message is:

"FCB::Open failed: Could not open file for file number . OS error: 5(access denied)"

Possible Cause: There are tons of reasons for the above problem but we will discuss here about hardware failure problem and account access problem.

Hardware Failure Problem: Whenever you will get the above error message. It is recommended you to check the hardware component of your system first before try any other action. If you found any faulty hardware component then replace it with new one and fix the problem.

Account Access Problem: The account in which SQL server is running does not has permission to access the folder that contains data and log files.   

Follow Steps to Fix: You can fix the above error message by following the below steps:

• Click on the Start button of your system
• Go to the programs and select SQL server
• Click on the folder that contains one the database files
• Right click on the folder
• Select sharing and security
• Now, select security button
• Click on the Add button
• A text box will be appear
• Write qualified user-name of the service account in the text box
• Check the allow column to ensure full control
• Repeat these all steps for all other database and log folder.
• Shutdown the SQL server database
• Now, restart the SQL server database from configuration manager.

Read More

How to repair MS SQL server 2005 Database?

In this article, I have described about how to repair a corrupt “MS SQL server 2005 (compact edition) database” with the help of engine object repair methods.

MS SQL server 2005 compact edition database has several files and all these files are divided into four kilobytes unit each, these files are known as pages. SQL server compact edition database stores a checksum for all pages. If a page is corrupted or damaged then checksum of the page does not match with stored checksum. There are so many reasons for the corruption in the database file like Meta data structure corruption, virus attack, sudden system download, hardware failure and many more. If you have verified that there is a corruption in the database file then you can repair sql server database file by the engine object repair method.

You can verify corruption is the database file by calling system.data.sqlserverce.sqlceengine command. If this command returns a true value then there is no corruption in the database file and if this command returns a false value then there may be some corruption in the database file. The syntax for verifying the corruption is given below:

Syntax for verify

if (false == engine.Verify()) {...}

If a database file has been corrupted then you can recover database file by engine object repair methods. This method scans and fixes the corrupt database file. Engine object repair method provides two repair methods:

1)  repairoption.deletecorruptedrows
2) repairoption.recovercorruptedrows

Repairoption.Deletecorruptedrows: This method discards all the corrupted pages and recovers the database files but you may lose some data if corrupt database file contains the database schema.

Syntax for this method
engine.Repair(null, RepairOption.DeleteCorruptedRows);

Pros: Guaranteed that recovered database is free from logical corruption.
Cons: You may lose some data, if you will use this method.

Repairoption.recovercorruptedrows: This method tries to read more data from corrupted database files and recover more data as can possible.

Syntax for this method
engine.Repair(null, RepairOption.RecoverCorruptedRows);

Pros: Recover more data in comparison with repairoption.deletecorruptedrows.
Cons: Does not guarantee that recovered database is free from logical corruption.

Read More

Analyzing DBCC CHECKDB failure in severe corruption cases and MDF Database Recovery

The DBCC CHECKDB utility is used more often than not by almost all SQL database users. This eminent utility makes use of an internal database snapshot to obtain transactional consistency of the database. It uses this information to check and repair SQL database following a three stage process. In the first stage, it performs an allocation check. Then it checks all the critical system tables for consistency errors in the second stage. In the final stage, a consistency check of the whole database is performed. Sometimes due to severe corruption, the process cannot be completed. If the CHECKDB utility fails to repair corruption, you should go for SQL recovery through a reliable third-party software.

For instance, you use MS SQL Server 2000. While running CHECKDB on one of your important database files, you notice that the process terminates unexpectedly giving you the following error message:

“Database 'DBNAME' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKNAME processing.”

Cause:
The CHECKDB utility encountered some errors in the second stage that it cannot repair. So, it simply aborts the process before reaching the third stage and throws the above specified error message. The completion of the second stage is necessary for executing the third stage.

This could have happened due to metadata corruption in the database system tables. Corruption may make all the components in your database inaccessible. You may use a backup to restore all the lost or inaccessible data. If the backup is missing, you should follow the below mentioned resolution steps to perform MDF file recovery.

Resolution:

Try the following methods to resolve the problem-

Check the SQL Server error log and the Windows application and system log to determine the cause of the problem. If hardware failure caused the problem, run the hardware diagnostics tool.
Run DBCC CHECKDB with proper repair clause to fix corruption.

If the problem still persists, you should take help of a SQL database recovery software. These software are capable of recovering the damaged SQL database components in their original form restoring all their properties and relationships with other components. They retrieve tables, views, queries, indexes, stored procedures, keys, constraints etc. from the corrupt database.

Read More