May 22, 2016

SQL Database Backup Restore History

To get SQL Database Backup Restore History you can execute below code using msdb database. This table resides in msdb database.

 SELECT [rs].[destination_db],  
 [rs].[restore_date],  
 [bs].[backup_start_date],  
 [bs].[backup_finish_date],  
 [bs].[database_name] as [source_db],  
 [bmf].[physical_device_name] as [backup_file_used_for_restore]  
 FROM msdb.restorehistory rs  
 INNER JOIN msdb.backupset bs  
 ON [rs].[backup_set_id] = [bs].[backup_set_id]  
 INNER JOIN msdb.backupmediafamily bmf  
 ON [bs].[media_set_id] = [bmf].[media_set_id]  
 ORDER BY [rs].[restore_date] DESC  

May 4, 2016

Access denied Error on SQL Database Backup failure

Problem:

I have faced one issue which was access denied type error . I am able to take Backup manually but when I am trying to set it through maintenance job then its show below Error.

Executing the query "EXECUTE testDB.dbo.xp_create_subdir N'D:\DBBackup..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Solution:.I resolved above error by providing full right for user everyone to folder DBBackup. 

To give rights to folder you can follow steps as below.

1.Select folder Properties and select the Tab Security
2.Now click Add buton then click on Advanced button.
3.Then click Find Now and select Everyone then click ok.

April 28, 2016

Error during setting SQL Maintenance plan wizard setting

Problem

Getting Error during setting SQL Maintenance plan wizard setting.

"Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)"

Solution:

This type of error comes due to t the unregistered COM in dts.dll which is located in

in command line change directory and path of dll file is  C:\Program Files\Microsoft SQL Server\100\DTS\Bin

You can run the following from the command line to resolve this error:

REGSVR32.EXE dts.dll

Then you need to disconnect from management studio and reconnect, it again then set SQL Maintenance plan wizard again it should work.

I hope this will solve to come out from your troubleshooting.

April 7, 2016

How to handle disk space issue on C drive

Mostly we face space issue in C drive . How to handle disk space issue on C drive?

Following are the steps you can use to free some space in c drive.

1. Go to event viewer and clean all logs.
2. Use disk cleanup properties on c drive to clean unwanted logs.
3. Clean CBS log file at C:\Windows\Logs\CBS folder.
4. Go to %userprofile%\desktop folder and transfer all files to other drives.
5. Set virtual memory to other drive instead of more on c drive.
6. Uninstall unwanted program from control panel ->Program and features.
7. Clean temporary folder from internet explorer.
8. You can use ccleaner software to clean logs.
9. Empty Recycle bin folder.
10. Find duplicate files and delete it.

March 27, 2016

What is difference between btree indexes and bitmap indexes?

1)Btree index:when ever we are requesting data using btree indexes columns using where clause then oracle server searching for indexes in database,if btree indexes columns are available in database then oracle server automatically creates btree structure,through btree structure only oracle server retrieve data very fast from data base .

2)Bitmap index:when ever we are using bitmap index then oracle server automatically creates bitmap table,it is mostly used in data-ware house applications.bitmap index is high performance compare to btree index because bitmap index internally creates bitmap table.

3)By default indexes are btree indexes. When ever we are creating the primary key ,unique key constraints automatically btree indexes are created.

If you want to create bitmap index then explicitly you have to mention the bitmap keyword at the time of creation of index. Bitmap index mostly created on lo cardinality columns.the table having wide range values.