.MDF Corrupted

Question: I do not have full backup of my database. My .mdf and .ldf are corrupted. Is there any way I can restore database now?


Answer: Sorry. I do not think there is any way you can do it. Try attaching this files to database using db_attach but if that does not work, it will be very difficult make it work.
Make sure you have taken backup of your physical file and also this exercise you do at your own risk.
ALTER DATABASE test
SET emergency
GO
ALTER DATABASE test
SET single_user
GO
DBCC checkdb (test, repair_allow_data_loss)
ALTER DATABASE test
SET multi_user
GO
How to recover a corrupt master MSSQL database.


I am by no means a SQL expert but due to the life of a network engineer I get to see a little of everything. If anyone has any suggestions or comments on the steps I used feel free to make them. Recently we had an issue with a client that there master database was corrupt and MSSQL would not start. We had an old backup of the master database and the .mdf and .ldf of the client databases only. Below I have listed out the steps we took to recover the master database and the client databases.




- We started sqlservr.exe as an application using the trace flag -T3608 and it gave the error:


Error: 9003, Severity: 20, State: 1.


Cannot recover the master database. Exiting. 


- We then proceeded with reinstallation of sql server for a named intance:NEW. and also applied sp3.


- Started sqlservr.exe -c -m -snew


- Restore database master from disk = 'D:\SQLDATAold\mssql\BACKUP\master_db_200503130200.BAK' with replace


- Started sqlservr.exe -T3608 


- Checked the consistency of the Master database using: dbcc checkdb ('MASTER') - zero consistencies


- Next detached the Model database :sp_detach_db 'model'


- Renamed the new instance model db files


- Attached the Model database using: sp_attach_db 'model','D:\sqldata\MSSQL$NEW\Data\model.mdf','D:\sqldata\MSSQL$NEW\Data\modellog.ldf'


- Then took care of the Temp db by : Alter database tempdb modify file (name= 'tempdev', filename = 'D:\sqldata\MSSQL$NEW\Data\tempdb.mdf')


Alter database tempdb modify file (name= 'templog', filename = 'D:\sqldata\MSSQL$NEW\Data\templog.ldf')


- Detached the MSDB database: sp_detach_db 'msdb' and attached it back pointing to the right location: 


sp_attach_db 'msdb','D:\sqldata\MSSQL$NEW\Data\msdbdata.mdf', 'D:\sqldata\MSSQL$NEW\Data\msdblog.ldf'


- Then changed the server name by executing the following commands


- select @@servername


- sp_dropserver 'BTLAPP'


- sp_addserver 'BTLAPP\NEW', 'LOCAL'


- Attached the DOCUWARE database and checked its consistencies: dbcc checkdb ('DOCUWARE') - zero consistencies

DIRTYPAGE,CHECK POINT,BUFFERPOOL

Dirty Pages: Data that has been modified and Stored in the buffer cache and has not yet been written in to the hard disk. (Transaction Logs)
Any uncommitted data residing in buffer cache.
Dirty reads: Reading the data that is actually read by Transaction2 which was modified by Transaction 1 and Transaction1 was not yet committed and above that, if Transaction 1 is rolled back then transaction2 has read data that never was modified or exists... This is dirty read.
CHECK POINT
Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
BUFFER POOL

The buffer pool is basically all remaining memory. You can't really manage it at all beyond setting the min & max values. Other than that, you manage how data is accessed, how the procedure cache is used, etc. Is there something specific you're looking for in this area or just general guidelines?

SAN: Storage Area Networks

A SAN (Storage Area Network) is a network specifically dedicated to the task of transporting data for storage and retrieval. SAN architectures are alternatives to storing data on disks directly attached to servers or storing data on Network Attached Storage (NAS) devices which are connected through general purpose networks.

In order to meet the demands of the storage system, enterprises apply SAN to increase the system efficiency and capacity expansion. According to SNIA (Storage Networking Industry Association), SAN is:

1. The purpose of the SAN is transmitting data between storage systems and storage systems or storage systems and client servers. The SAN fabric contains physical connections from storage systems to client, and then storage management devices, servers, and network devices. However, SAN is usually defined as block I/O services provider.
2. The storage system contains storage components, devices, computer equipments, software applications, and network devices.

SAN is able to attach with various kinds of storage devices, such as disk-array subsystems, CD towers, magnetic tape drivers and libraries, and provides data I/O services via hub or switches through network connections.

SAN (Storage Area Network) Protocols

Storage Area Networks are traditionally connected over Fibre Channel networks. Storage Area Networks have also been built using SCSI (Small Computer System Interface)technology. An Ethernet network which was dedicated solely to storage purposes would also quality as a SAN.

Internet Small Computer Systems Interface (iSCSI) is a SCSI variant which encapsulates SCSI data in TCP packets and transits them over IP networks.

Fibre Channel over TCP/IP (FCIP) tunnels Fibre Channel over IP-based networks.

The Internet Fibre Channel Protocol (iFCP) transports Fibre Channel Layer 4 FCP on IP networks.

Advantages of SAN

By integrating storage devices, SAN increases the storage space usability and cost efficiency.

• SAN is the high-speed storage sharing system.
• SAN increases the network bandwidth and reliability of data I/O.
• SAN is separated from the regular network system, and has an ability to expand the storage capacity.
• SAN reduces the cost of the storage management since it simplifies the system fabric and devices management.

Suspectmode DB Recovery

Introduction

Sometimes, you may have experienced that your Microsoft SQL database is marked as SUSPECT. Database may go into SUSPECT mode because the primary filegroup is damaged and the database cannot be recovered during the startup of the SQL Server. Generally when the database is in SUSPECT mode, nobody can deal with the data.

Workaround

When the database is in SUSPECT mode, you can change the database status to the EMERGENCY mode. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

You can run the following SQL query to get the database to the EMERGENCY mode.

 Collapse

ALTER DATABASE  dbName  SET  EMERGENCY

After that, you set the database to the single-user mode. Single user mode allows you to recover the damaged database.

 Collapse

ALTER DATABASE  dbName   SET SINGLE_USER

Then you can run DBCC CheckDB command. This command checks the allocation, structural, logical integrity and errors of all the objects in the database. When you specify “REPAIR_ALLOW_DATA_LOSS” as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can cause some data to be lost.

 Collapse

DBCC CheckDB (dbName , REPAIR_ALLOW_DATA_LOSS)

If the above script runs without any problems, you can bring your database back to the multi user mode by running the following SQL command:

 Collapse

ALTER DATABASE  dbName  SET MULTI_USER

Recommendations

Using any DATA LOSS repair options can lead to other problems. This is not a recommended way to recover the database. The database should be restored from a backup made prior to the corruption, rather than repaired.

DBCC CheckDB command should be run on working databases at regular intervals to check for errors.

How to repair a SQL Server 2005 Suspect database

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

Rebuild and Re-Organize Indexes

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REORGANIZE
GO
Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

MSDB Corrupted

Recreate Corrupt MSDB database

If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only...

Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.

Now you'd hope that if you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss) in emergency mode). Nope, msdb can't be put into emergency mode.

So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the directory paths to suit your installation):

Detach the damaged msdb. You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. I did this by shutting down the server, navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608

Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)

Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory

Shutdown and restart the server without the 3608 trace flag

This works on SQL Server 2000 as well.

Rebulding MSDB in SQL Server 2005

Seems that because of some diskspace issures we had in April, my MSSQL configure got hosed and the server hasn’t been able to perform any backups nor has the sql server agent been running. This was due to the fact that the MSDB database has been corrupted.

The following are the steps that I took to get everything back up and running. I will warn you that by doing these steps, you will lose all you Maintenance Plans that you had and you will have to recreate them. Figure that this is a small price to pay because let’s face it, you got to have backups.

I would strongly suggest that you don’t be an idiot like me and make sure that you backup your msdb database. However if you are like me and didn’t do this, these steps should get everything back on track.

1. Put MSSQL into single user mode
   1. Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
      
   2. Right click on SQL Server and choose Properties
   3. Click on the Advanced tab. Under Startup Parameters you will be adding the following parameters to the beginning of the string: -m;-c;-T3608
2. Restart SQL Server
3. Connect to SQL server through the Management Console. From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.
4. Detach the MSDB database using the following commands:
   use master
   go
   sp_detach_db ‘msdb’
   go
   and click Execute
5. We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.
   1. Usually these files are located in the following directory:
      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataYour’s might differ.
   2. Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.
6. Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:
   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install
7. Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.
8. At this point you should have your MSDB database restored. The only thing left is cleanup.
9. Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:
   EXEC sp_configure ‘show advanced options’, 1
   GO
   RECONFIGURE WITH OVERRIDE
   GO
   EXEC sp_configure ‘xp_cmdshell’, 0
   GO
   RECONFIGURE WITH OVERRIDE
   GO
10. Shutdown SQL Server
11. Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.
12. Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.

TrobuleShooting

Problem:

In our cluster environment I was testing SQL failover, when I failed over to passive node I can no longer launch SQL Server configuration manager (SSCM). When I tried to open SSCM, MMC has waited for 30 seconds and gave me the error below.

“Connection to target machine could not be made in a timely fashion”

Resolution:

Upon researching on this I found that WMI Service (Windows Management Instrumentation Service) is in hanged state. After restarting the service on passive node, it worked for me. Steps to restart the service is below.

• Go to Run –> type services.msc and press enter
• Right click on the service “Windows Management Instrumentation” and click on restart option in the menu
• Now launch SQL Server configuration manager, it should work fine now.

SQL BACKUP ON NTFS Compressed Formate

I’m starting this topic since last week I had faced a backup issue with compressed drive. In one of our server we have used compressed folder to take backup of our SQL databases. This is working fine and the db is used for OLTP application and it had grown about 73GB. Now we have started facing backup failures only for that database alone all other databases (less than 10GB) are running fine in the maintenance plan. I’ve checked the machine performance and SQL Server is the only process using 50% of CPU and 3GB of memory, other than that the server is not under stress and in fact that box is dedicated to SQL Server.

Our backup has been failed with the below error message and the below event is written to errorlog too.

BackupMedium::ReportIoError: write failure on backup device 'D:\SQLBackup\MyDatabase.bak'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).

The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x0000xxxxxxxx in file with handle 0x000000000000xxxx. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

Upon running the tests I found IO is so busy with that backup folder. While checking it I found that the system is not able to handle backup and folder compression at the same time when the file size is large, this creates IO timeout for SQL Server and SQL Server in turn returns the general error mentioned above.

As we already know that taking SQL backup to a compressed drive \ folder is not recommended by Microsoft, I’ve removed the compression option for that folder and now the backup ran without errors and it completed within 15 min. So taking a large online SQL backup to a compressed drive is mere waste, where you will spend CPU and more IO for it. Some of my recommendations are below to compress your backup.

• Take a native SQL Backup to an uncompressed folder and then zip the backup file using any of the zipping (gzip, 7-zip, winzip, etc) utility and delete the SQL Backup file.
• Buy third party utilities to compress the SQL backup on the fly, i.e taking direct compressed backup, no need to create a separate SQL temporary backup file. You can try LiteSpeed, SQL Backup, Embarcadero etc.
• Upgrade to SQL Server 2008 Enterprise Edition which supports backup compression.
  
  
  Suppress Backup Information In Error Log
  
  
   This article is all about suppressing successful backup information into errorlog. Let me explain my scenario, In my working environment we have a production SQL Server with 60+ databases, all the databases are set to full recovery and data loss is accepted only for 15 min. Yeah you got me, we are taking Full backup daily, differential every 4 hours and transaction log backup  every 15 min, there is no problem with the backup. We have one thing that is messing our error log, whenever a backup is taken by default SQL Server will write the detail to the errorlog as shown below.

  

  In our case SQL Server is writing the successful backup information for 60+ databases every 15 min to error log. So what happens, error log grows 30~50 MB daily which makes us hard to scan the error log. We have a maintenance job to scan the errorlog for errors, if it grows 30 MB our errorlog monitoring jobs runs for long time. To overcome this we can suppress this backup information writing in error log because we have this information in backupset table on MSDB database. We can achieve this by using the trace flag 3226. Follow the steps below to add this trace flag to sql server startup.

  • Open SQL Server Configuration Manager
  • Go to SQL Server properties and click on Advanced Tab
  • Add Trace flag 3226 as shown below

  

  • Restart SQL Server service

  That’s it you are good to go. From now no backup information will be written to errorlog however you can get the backup details from backupset table on MSDB database. We have done this and saved lot of space and time scanning errorlog.

  Applies to :

  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008 &
  • SQL Server 2008 R2