July 10, 2016

Difference between view and Materialized view and Define Long Column

Difference between view and Materialized view 

(i) View will not store any data,whereas Materialized View will store data.
(ii)View is used for security purpose and Materialized View for performance.
(iii) view is not accessible if base table is dropped, whereas in Materialized View it can still be accessible.
(iv) Can perform DML operation directly on view but it Materialized View its not possible.

Define Long Column

LONG columns cannot be in the SELECT clause when using the UNION set operators. There are some restrictions for long columns like. 
(i) It cannot appear in where clause.
(ii) Indexes cannot be created on them.
(iii) It cannot appear in Group by,order by, or connect by clauses or with the distinct operator in select statements.

June 27, 2016

Database space error on Sql server for ERP

If you are facing following error which Database space error on Sql server for ERP.

"The database reported (session 2 (-AOS-)): Database space problem - 'AX2009_Test' is out of space"

Then you can do following to resolve this error.

1. Clean unwanted file from c drive or other drive where database stored.
2. Shrinking database log and data files can also help to free some space.
3. If you database size growing fast then increase hard drive volume or move database to other drive.
4. Clean log files which is bigger size.
5. Use disk cleanup tool.
6. You can Shrink other database which is not related to ERP.

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],  
 [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


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


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)"


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.