Software Development,SQL Tips, Information Technology Technical tutorials

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

December 14, 2014

Career and opportunity in NGO'S

Working in NGO needs different sets of knowledge, attitude and skills. And obviously it offers a good salary also. But the initial days are very crucial as every moment a development worker has to deal with people.

In India different courses offered by various institutions offers an opportunity to enter into development sector (NGO sector is known as development sector for the professionals). The reputed institutes includes, TISS, Tata –Dhan Academy, XISS,KIIIT and IRMA. Etc. these institutes offers different courses in different names. Like MSW, Development Studies, Development Management, Rural Management etc.

The job opportunity is very good. Initially one can earn between Rs.15000-20000. In abroad one can earn more but in most of the cases it need at least three years of experience.

lease visit website of the few reputed NGOs. You could visit devnetjobs.com where you will find many opportunities.

There are many NGOs working in Bangalore. Please explore with them. In many of the cases the NGOs offer volunteering opportunity.

The professional NGOs organised stage shows for fundraising. You could be part of that. But before that you have to prove yourself.

December 1, 2014

Difference in CHAR and VARCHAR2 Data type in SQL

Char is fixed length Datatype. and Varchar is variable length datatype.

Suppose you have 2 columns, 1 with Char(40) another with Varchar2(40)  and both columns has values with only 10 characters in a record that time SQL server still occupy 40 character space in system for 1st column and 10 character space in system for 2nd column.

Simple pick char if length is going to remain same otherwise pick varchar as a speed both have its advantage . Choosing data type cannot be done by yes or no you have to choose data type as per your requirement both are good and bad. 

November 13, 2014

Suggestions to get rid of from recovery mode of SQL database

These are just some Suggestions to get rid of from recovery mode of SQL database. Mostly suspension of database made due to any long process and long suspended query which generating deadlock and infinite loop for your database.

Try these option.

A. Alter database dbname set emergency
Alter database dbname set single_user
dbcc checkdb(‘dbname’,repair_allow_Data_loss)
alter database dbname set multi_user


B. we have to run dbcc checkdb only in the third step,because User does not want to loose the data. If that don't works, then we have to use allow data loss. but first option is better to restore with available backup, if back up is not there then we have to use this method.


C.First find which file is corrupted, if Data file corruption immediately take Tail log backup then restore full backup followed by differential , log backups with no recovery finally restore tail log backup with recovery.

D.

1. Ensure that the device file is actually available.
2. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online. For SQL Server 6.0 and 6.5, if you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For SQL Server 7.0 and later, this procedure is created at installation by the inscat.sql script, found in the Mssql\Install directory.
3. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.
4. Execute sp_resetstatus in the master database for the suspect database:
use master
go
exec sp_resetstatus your_database_name

5. Stop and restart SQL Server.
6. Verify that the database was recovered and is available.
7. Run DBCC NEWALLOC, DBCC TEXTALL and DBCC CHECKDB.

use master
go
sp_configure 'allow updates', 1
reconfigure with override
go

select status from sysdatabases where name = 'Testdb'
4194329
update sysdatabases set status= 32768 where name = 'Testdb'

dbcc rebuild_log ('your database name', 'full path to a new transaction log file')

dbcc rebuild_log ('Testdb', 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\testdb.ldf')

use Testdb
go
sp_dboption 'Testdb, 'single_user', true
go
dbcc checkdb ('Testdb', repair_allow_data_loss)

update sysdatabases set status= 0 where name = 'Testdb'

DBCC CHECKALLOC ('Testdb')
and
DBCC CHECKDB ('Testdb')

sp_dboption 'Testdb', 'single_user', 'false'

use master
go
sp_configure 'allow updates', 0
go

use master
go
exec sp_resetstatus Testdb

DBCC NEWALLOC
DBCC TEXTALL
DBCC CHECKDB

November 7, 2014

Finding Reasons for Slow SQL Server database performance

If you found your SQL Database performance is very slow then you can check below points.

1.Check if any blocking.
2- Check execution plan for any costly operator.
3- We can run DTA and take help from that but keep in mind it's not necessary to implement all suggestions provided by DTA.
4. Index fragmentation .
5. Missing indexes.
6. Stale update stats
7. Blocking & Deadlocks of queries.
8. Excessive compilation & recompilation.
9.Check long queries using trace of suspended records on high working time of transactions.
10.Check RAM utilization at SQL Server.

What is VLF in SQL?

VLF  stand for virtual log file. Its the internal structure of log file.

Virtual log file means while running (DBCC loginfo)it will shows the status 0 indicates Recoverable 1 indicates Active 2 indicates unused 3.indicates reused.

The Tlog file internally divided into virtual log files. You can see how many vlfs you have in your log file can be find out by running dbcc loginfo. Too many vlfs will might slow down the db recovery. Each vlf will be having status 0 to 9. These status number indicates the current vlf's status i.e 0 reusable 1 waiting for log backup 2 Active and so on.

November 3, 2014

What is a minimal logged in SQL

1.Minimal information it writes in the log file, when databse in bulk model.

2.If transaction is minimally logged only enough information is logged in transaction log to rollback the transaction no information is logged to roll it forward. This is why you loose point in time recovery if operation is minimally logged.

November 1, 2014

What is difference between Single user mode and Offline mode om SQL

1.When database is in offline users cant connect we need to bring our database in to emergency mode to connect to db for this we will try to bring in single user mode so that we can able to connect with single user.

2.Single user mode means access only SA admin,offline mode means no one user can not access.

3.To bring the database online from offline,there is no need of putting it in emergency mode.

4.If 
database in single user mode it will allow only one connection, it is in offline we can't do any thing.

October 26, 2014

Total IPs Required for fail-over clustering in SQL

How many Total ip's required build win&SQL Failover clustering Active/Active & Active/Passive?

Ans.In a 2 node cluster each ip for each node and one ip for win cluster and one for msdtc and one for each cluster group .. If u use private network for heartbeat then 2 more ip .. So total of 5-7

In a two node cluster 7ip's are needed two private ip's for connecting to quarum. one for node A and other for node B, two public ip's one for node A and other one For node B and one for msdic and one for msclust and one ip for switch for connecting to san drives in which data is stored. 

Total 7ip's are needed.

Script to apply any user permission to the all databases of SQL

/* It is a new feature and it has ability to create user defined server roles
and assign server level/scope permissions. */
/*
- Following actions are performed to implement this new feature:
- Created Server role
- Created Login and made member of server role
- Granted Standard view permission
After completion above steps the login has ability to make connection any database with data reader
permission.
*/

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
CREATE LOGIN [DBREADER] WITH PASSWORD=N'123',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE ALLDBREADER ADD MEMBER [DBREADER]
GO
GRANT CONNECT SQL TO ALLDBREADER
GRANT VIEW ANY DATABASE TO ALLDBREADER
GRANT VIEW ANY DEFINITION TO ALLDBREADER
GRANT VIEW SERVER STATE to ALLDBREADER
GRANT CONTROL SERVER TO ALLDBREADER
DENY SHUTDOWN TO ALLDBREADER
Go

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
GRANT CONTROL SERVER TO ALLDBREADER
DENY ALTER ANY DATABASE TO ALLDBREADER
Go

October 7, 2014

Script or query for FAILED JOBS Details with error Messages in SQL

You can use following Script or query for FAILED JOBS Details with error Messages in SQL. I hope it will help you.

USE Msdb
Declare @lastRunDate int =replace(CAST(getdate()as date),'-','')
SELECT Sj.name as JobName ,CASE SJH.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
END AS JobStatus,SJH.message from sysjobhistory SJH inner join sysjobs SJ
ON SJH.job_id = SJ.job_id
where SJH.step_id = 0
and sjh.run_date =@lastRunDate
and SJH.run_status =0