Software Development,SQL Tips, Information Technology Technical tutorials

Asp.net,SQL tips-Tricks for error resolution version 2014,2012,2008,2005,Procedural language,Stored Procedure, computer , excel macro programming,Film,Musical notations,Indian ,Western Music.

July 28, 2014

Clustered index and non clustered index in SQL

Clustered index which reordered the table and saved separately as index table(data pages stored as B-tree) there can be one clustered index per db & Non clustered index is typically a index(data pages stored as heap)

A table can have only one clustered index with many non clustered indexes.

The leaf nodes of the cluster index stores data pages and non clustered index stores key values(where as table includes clustered index) or rid's(table contains non cluster index only).

July 22, 2014

Advantage and disadvantage of Team Viewer , Ammy Admin and show my PC etc

Advantage of Team Viewer , Ammy Admin and show my PC etc

You can share your desktop to other user or pc to take live support help from your friend or colleague. You can also help to other friends also.

If you want to use limited hours in a month use of Team Viewer , Ammy Admin and show my PC then you can use trial version but if you want to use lot of time in a single month then you need to buy license. You can buy license online.

Disadvantage of Team Viewer , Ammy Admin and show my PC

Mostly these type of connections registers all clicks and keypress, that means they can know about all your logins and passwords and you PC could be hacked or misused(Its possibility but mostly they do not do it).



What is Full quorum in mirroring in SQL Server?

What is Full quorum in mirroring in SQL Server?

Ans:Full quorum forms when both the partners with witness are connected and performing their roles.

“full quorum” means that all three roles are available and all is going swimmingly.

What is system configuration of your cluster servers in SQL?

What is system configuration of your cluster servers in SQL?

Ans:If I have around 250gb physical and 30 gb allocated forsql , win 2008 sp2 ,they are physical.

You should know about this,System configuration means RAM Size and How many core/processor are in CPU.Apart from this what is the OS version

How will you upgrade for Active and Active cluster server

To upgrade for Active & Active cluster server
Active to active means you will install SQL server on each node so they will be active to active.

First transfer both sql server service on node A and upgrade Node B then transfer both services on node B and upgrade node A and then split the service on separate nodes(Node A & B)

July 18, 2014

Datebase-Unavailable database:testdb is unavailable(suspect,offline,recovering etc) in SQL

SQL Error: Database-Unavailable database  :testdb is unavailable(suspect,offline,recovering etc)

1.If the database is offline, the approach should be to bring the database online by either from GUI or from query : ALTER DATABASE <database_name> SET ONLINE and if it still doesn't come up..we should check the logs for the reasons..and based on the outcome we have to proceed accordingly.

2.If the Database is offline, then try to make the database online using the script ALTER DATABASE <database name> SET ONLINE.
If it is not recover the database to online, then check in event viewer for possible error, if error is 17204--then data file has damaged,if error is 17207--- then log file has damaged,If you see this error then the database is there in suspect state.
To Recover the suspect state database u need to follow steps as below:
If data file damaged:
1. Take the tile log back up.
2.Restore Full back up with no recovery.
3.Restore Differential & Log Backups with no recovery(If you have).
4.Restore tail log backup with recovery, then database will become online.
If Log file damaged:
1.Put the database in single user mode.Using the following command.ALTER DATABASE <database_name> SET SINGLE_USER
2.Put the data base in emergency mode using ALTER DATABASE <database_name> SET EMERGENCY
3.Run the DBCC CHECKDB Command with require repair level as follows
DBCC CHECKDB('<Database_name>',REPAIR_ALLOW_DATA_LOSS)
4.Then put the data base into multi user mode.
ALTER Database <database_name> SET MULTI_USER
Then database will become online.

July 16, 2014

How to Shrink TempDB in Sql Server?

To Shrink TempDB in Sql Server below ideas can help you to handle the issue.

If it is data file then Instead of shrinking you should check the open transactions causing tempdb to get full..if it is not critical you can kill the process or else should add secondary data files. Shrinking log whereas is safe witch you can do by dbcc shrinkfile command.

1)If mdf file is full we can shrink that but sometimes we can’t shrink mdf file the reasons are:

(i)check if any locks are there through (sys.dm_tran_locks)or sp_locks

(ii)check for sys.dm_db_session_space_usage which will allows to check for page allocation and deallocation for each session on that instance

(iii)check for temporary objects in tempdb using (select * from sys.all_objects where is_ms_shipped=0)-ser created temp objects. These are called as cached objects which are residing in procedure cache which helps stored procedure to make use of the query plan. But now it is not getting shrinked we have to perform DBCC FREEPROCCACHE which will remove all the cached objects from Proc cache. But from development perspective this will lead to spaces to get recompiled.


Then perform DBCC shrink file finally which will allows us to shrink it.

How to Convert time format in SQL

How to change time to be 1:30:00 am/pm Instead of 13:30:00 ?

You can try below queries.

SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5)
+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

declare @time time = '13:30:00' SELECT convert(varchar, @time, 22)

July 14, 2014

How to set single and multiple use mode in SQL

If the database is in single user mode then You can bring that database to normal state following way.

ALTER DATABASE Database_Name SET MULTI_USER

Go to ssms ->right click on database properties--> select options then scroll down to "Restrict Access" then change it MULTI_USER

What is the difference between cummilative update and hot fix in SQL

Service Pack, Cumulative Update and Hot Fix are all patches to the bugs faced by Users/Vendors. Severity and No. Of Users facing problem will flow down from Higher to Lower i.e, Service Pack (Higher) --> CU (Medium) --> Hot Fix (Lower).