Tuesday 30 October 2012

Error while installing SQL Server 2005 – Native Client cannot be found August 26, 2008

Recently while installing SQL Server 2005 Standard Edition on a “fresh” server I found that crazy thing just wouldn’t install!
I was getting the following error:

“An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package ‘sqlncli.msi’”

Most cryptic… Considering my installation package is the disc given to me by Microsoft and I’m positive that the sqlncli.msi file is right there.
However, the actual issue appears to be as simple as an inability to upgrade an old Native Client edition.

To resolve, use the Add / Remove Programs panel to uninstall an existing SQL Server Native Client installation and you should be good to roll.
Note that this appears to be common for other editions of SQL Server 2005 too.

Monday 29 October 2012

Cannot detach a suspect or recovery pending database

Database in 'Recovery Pending' State -Error 945

After losing connectivity to the data and log volume for one of our SQL servers, we received the following error:

LogWriter: Operating system error 2
(failed to retrieve text for this error. Reason: 15100) encountered.

Which resulted in the database on that volume showing as being suspect.
After gaining connection back to the volume, I tried to detach the database, but received the following error:

Cannot detach a suspect or recovery pending database. 
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707)

To resolve i did the following:

ALTER DATABASE emergencydemo SET EMERGENCY;
GO

EMERGENCY mode marks the database as READ_ONLY, disabled logging, and access is limited to sysadmins. Marking the database in this mode is a first step for resolving log corruption.

ALTER DATABASE emergencydemo set single_user
GO
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE emergencydemo set multi_user
GO

This should resolve any corruption and bring the database online. The database will come out of EMEREGENCY mode automatically.

Sunday 21 October 2012

Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005

If you want to use OpenDataSource or OpenRowSet query than you must have to enable “Ad Hoc Distributed queries”. It is disabled by default as a part of security configuration as per Microsoft.

You can set this option by two ways.

1.) sp_configure command

2.) Surface area configuration.

Since I am a script bee, I will explain steps for sp_configure.

You can run sp_configure to see current status of “run_value” field. If you don’t able to see “Ad Hoc Distributed queries” in the results set. You have to enable “Show advanced option” by following command.

sp_configure ‘show advanced options’, 1

reconfigure
GO

You may get message

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you are done with that, you can see “Ad Hoc Distributed queries” in list of sp_configure. You have to set “Run_value” to “1”. If it is “0” than run following command.

sp_configure ‘Ad Hoc Distributed Queries’, 1

reconfigure
GO

Which will show you following message.

Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Using sp_configure

sp_configure is a system stored procedure designed to show and change server-level configuration settings on SQL Server. When executed without parameters, this procedure will enumerate the basic configuration settings on the SQL Server.

EXEC sp_configure

Results from sp_configure

These results show us 16 configuration options, however these are only the basic configuration options. There are also advanced options this procedure does not show us unless we change an additional configuration setting, named, ‘show advanced options’. Let’s do that now.

EXEC sp_configure 'show advanced options', 1 -- Changing to 1 turns it on

GO

RECONFIGURE WITH OVERRIDE

Now that we have changed this option, let’s execute sp_configure again and look at the result.

EXEC sp_configure

Now we should see upwards of 70 configuration options we are able to set on SQL Server.
In order to set them, we only need to copy the exact name listed in the ‘name’ column, and pass it as the first parameter for sp_configure, then for the second parameter, pass our desired value.

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE WITH OVERRIDE

Sunday 14 October 2012

DBCC CHeckdb in one shot for all user databases

EXEC sp_msforeachdb 'use ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'',
''ReportServer'',''ReportServerTempDB'')
DBCC CheckDB'

How to find failed and successul jobs on sql server for particuler date?

                Daily Monitoring For Failed Jobs in Sql Server
select sj.[name],sh.step_id,sh.step_name,sh.run_date,sh.run_time,sh.sql_severity ,sh.message,
sh.server from msdb.dbo.sysjobs sj inner join msdb.dbo.sysjobhistory sh
on sh.job_id=sj.job_id
inner join msdb.dbo.sysjobsteps ss
on sj.job_id=ss.job_id and
sh.step_id=ss.step_id
where sh.run_status=0
and sh.run_date='20101115'
and sh.message like '%failed%'

          Daily Monitoring for successful jobs in Sql Server

     select sj.[name],sh.step_id,sh.step_name,sh.run_date,sh.run_time,sh.sql_severity ,sh.message,
sh.server from msdb.dbo.sysjobs sj inner join msdb.dbo.sysjobhistory sh
on sh.job_id=sj.job_id
inner join msdb.dbo.sysjobsteps ss
on sj.job_id=ss.job_id and
sh.step_id=ss.step_id
where sh.run_status=1
sh.run_date='20101115'

How to Add data file to database using T-SQL COMMEND

Creating database: T-SQL Command

1. Open Query Analyzer

2. SELECT master database from the database drop down or issue USE MASTER command and execute

3. Type the following command and press F5 to execute:

ALTER DATABASE MyDB
ADD FILE

(

NAME = MyDB_dat2,

FILENAME = 'E:\DATA\MyDB_data2.mdf',
SIZE = 100MB,

FILEGROWTH = 25MB

),
(

   NAME = MyDB_dat3,
   FILENAME = 'E: \DATA\MyDB_data3.mdf',
   SIZE = 100MB,
   MAXSIZE = 150MB,

FILEGROWTH = 25MB

);

how to add primary key on existing table in sql server ?

Please refer the below example syntex :

table my_table( m_col1 int ,m_col2 varchar(20),m_col3
alter
alter table my_table add constraint pk_m_no primary key (m_col1); table my_table alter column m_col1 int not null;datetime);
create

How to check DATABASE GROWTH and HARD DRIVE FREE SPACE on SQl Server

--FOR DATABASE GROWTH REPORT
use master
go
create table #grow1(database_name varchar(250),
database_size float,remarks varchar(150));
insert #grow1 exec('exec sp_databases');

select database_name,round(database_size/1024,2) as 'MB',
round((database_size/1024)/1024,2) as 'GB',
remarks from #grow1;
drop table #grow1
GO
--FOR HARD DRIVE FREE SPACE
create table #space1(drive varchar(10),free_space float);
insert #space1 exec('exec.master.dbo.xp_fixeddrives');
select Drive ,free_space as 'MB ',round (free_space/1024,2) as 'GB' from #space1;
drop table #space1

how to take copy only backup in sql server

copy_only backup is used to take copy of full database backup with out breaking the squence of backup and restoed in msdb database.

syntex for copy_only backup

backup database my_db to disk 'd:\backup\my_db.bak ' with copy_only

above commend made a copy of full database with out stored information in msdb databse .

Sql Server DBA Interview questions

Please complete all below questions series after that attend any interview for Sql DBA

1).what is the default Recovery model of a newly created database ?

2).How many types of recovery model in Sql Server ?

3.)How many system databases in Sql Server 2005 ?

4). How you can say that Tempdb Database is different from other system and user databases ?

5).What is different between simple and full recovery model ?

6.)How to create a Database with 1 mdf,1ndf and 1 log file by T-Sql syntax ?

7).How to Add one more log file into a database by T-Sql syntax ?

8).How to add a file group to a Database by T-Sql syntax?

9).How to add a log file with a test file group by T-Sql syntax ?

10).How to find out database size , physical file location and database status as well ?

11) How to rename a Database by using T-Sql syntax?

12)How many types of backup in sql Server 2008/2005/2000 ?

13) What is the usage of mssqlsystemresource database in sql server ?

14) How many types of challenges you face in your project ?

15 ) what is the different between differential and log backup ?

16) How to shrink the log file of database ?

17) what is the different between shrink commend with truncate_only or no_truncate clause ?

18) backup log DB name with truncate_only is taking any backup or not if not ? why ?

19 ) How to take filegroup file backup of database ?

20) how to perform point in time recovery ?

21) How many types of replication in sql server 2008/2005/2000 ?

22) Which backup strategy (policy ) implement in our current project ?

23)How you configured Log Shipping or Database mirroring in current project if yes then please justify and how many types of issues you faced regarding log shipping and Database mirroring ?

24 ) Have u configured clustering on current project if yes then what king of clustering you configured and how ?

25) Tell me about your daily , weekly and monthly activities ?

26 ) How you enable log file in memory for sql server ?

27) how to find out blocking process and deadlock on sql server ?

28 )How to move Master database from one location to another location ?

29)How to restore master database backup on sql server ?

30)what is different between windows authentication and sql server authentication ?which one better then other ? why ?

31)How to find out currently running process on sql server and which one process taking long time to execute ?

32)Which process or job taking high C.P.U. Utilization ?

33)IF Sql server going hang again and again what are steps to follow to resolved problem ?

R S R KARAN SQL-ORACLE-DBA HANDS ON

CLOCK