Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 some interview questions

Author  Topic 

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-03-29 : 13:37:21

Hi to all. Recently i attended an interview .I have so many doubts ,could any one tell me about these questions.


1.) How to identify a query which is used by end user with out profiler. (the exact query)
2.) my tempdb is full what to do.
Then i said i will restart the server
then he asked My company is not allowing to restart the server.then what we can do?
3.) Transaction log is full what to do ?
4..How can we improve the performance of database.
5..how to rebuild the master database if it is corrupted.
6.. what new features are introduced in log shipping in 2005.
7..how can we give a permission to a userto use only profiler. he is not allowed to do any operations
8..after finding long running queries the next stop what we can do?
9..what i can do if my secondary database is no in sync with primary database in log shipping.?.
10..if the quereader agent fails will the transactional replication completely fails or partially fails.
11..how to replicate identity column in merge replication?


Waiting for ur replies,
Sripathi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-29 : 14:58:51
1)Use activity monitor or DBCC inputbuffer(Spidid)
2)Shrink it .It is better to put TempDb in its own drive and put in simple recovery mode.
3)a) Shrink it with DBCC shrinkfile. Backup log and try shrinking again. b)If you don't care about point in time recovery,Put the database in Simple recovery mode and shrink the file.Then Run full back up for safety.
4)a)Creating maintenance plan every week to Update stat,integrity check and rebuild index operation.
b)Put data files on seperate drives and spread accross different drives.
c) Put log files on seperate drives.
5)

Step 1. Put SQL Server into Single User Mode

The first thing you will want to do is stop the SQL Sever Sevicer (sqlservr.exe) and the associated services (Agent, Full Text, Etc). There are a few ways to do this, but the easiest way would be to use the SQL Configuration Manager (Start->All Programs->Microsoft Sql Server 2005->Configuration Tools->Sql Server Configuration Manager). From this console you can manage the various SQL server services running on the machine. Right Click on each service listed and stop the service. The services are stopped, you can proceed to Step 2.
Step 2. Start the SQL server in Single User Mode

Open a command window and navigate to the folder where Sqlservr.exe resides (generally <install drive>:\Program Files\Microsoft Sql Server\MSSQL.1\MSSQL\Binn). Run “sqlserver.exe -m” to start the sql server from single user mode.
Step 3. Rebuild the system databases

In SQL 2005, the rebuildm.exe program is nto supported. To rebuild the master database you need to use the setup.exe found on the SQL 2005 installation media. To rebuild, use the following command “start /wait setup.exe /qn INSTANCENAME=<instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<New SA Password>”. Please refere to Books on Line for compelete details on how to use Setup.exe.
Step 4. Restart the SQL Server Services in regular mode

End the command shell you started earlier. Ctrl-C to stop SQL server in single user mode, then close the command window. Go back to the Sql Server Configuration Manager and restart the SQL services.
6)You can run log shipping in Workgroup and standard edition.

7)http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384

8) See the execution plan and Put it in Database tuning advisor so that you will recommendation on adding index or dropping and update stats too

9)check the log shipping jobs on both servers and see error logs for reason.If log shipping dies, need to again take full backups.
10) It depends on which transaction replication you choose .If with Queued updating ,then it causes a issue.
11)You have to put the column in NOT FOR REPLICATION CLAUSE .What the not for replication clause does is to tell SQL Server to disable the code that reseeds an identity column when a replication agent is inserting data into an identity column. This preserves all of the identity ranges that you have setup. The drawback to this option is you could not alter a table and add this option to it.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-29 : 22:11:19
look at this too:

http://www.mssqltips.com/tip.asp?tip=1274 id="Book Antiqua">
Go to Top of Page

jerryvn01
Starting Member

3 Posts

Posted - 2010-07-22 : 02:36:36
quote:
Originally posted by sodeep

1)Use activity monitor or DBCC inputbuffer(Spidid)
2)Shrink it .It is better to put TempDb in its own drive and put in simple recovery mode.
3)a) Shrink it with DBCC shrinkfile. Backup log and try shrinking again. b)If you don't care about point in time recovery,Put the database in Simple recovery mode and shrink the file.Then Run full back up for safety.
4)a)Creating maintenance plan every week to Update stat,integrity check and rebuild index operation.
b)Put data files on seperate drives and spread accross different drives.
c) Put log files on seperate drives.
5)

Step 1. Put SQL Server into Single User Mode

The first thing you will want to do is stop the SQL Sever Sevicer (sqlservr.exe) and the associated services (Agent, Full Text, Etc). There are a few ways to do this, but the easiest way would be to use the SQL Configuration Manager (Start->All Programs->Microsoft Sql Server 2005->Configuration Tools->Sql Server Configuration Manager). From this console you can manage the various SQL server services running on the machine. Right Click on each service listed and stop the service. The services are stopped, you can proceed to Step 2.
Step 2. Start the SQL server in Single User Mode

Open a command window and navigate to the folder where Sqlservr.exe resides (generally <install drive>:\Program Files\Microsoft Sql Server\MSSQL.1\MSSQL\Binn). Run “sqlserver.exe -m” to start the sql server from single user mode.
Step 3. Rebuild the system databases

In SQL 2005, the rebuildm.exe program is nto supported. To rebuild the master database you need to use the setup.exe found on the SQL 2005 installation media. To rebuild, use the following command “start /wait setup.exe /qn INSTANCENAME=<instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<New SA Password>”. Please refere to Books on Line for compelete details on how to use Setup.exe.
Step 4. Restart the SQL Server Services in regular mode

End the command shell you started earlier. Ctrl-C to stop SQL server in single user mode, then close the command window. Go back to the Sql Server Configuration Manager and restart the SQL services.
6)You can run log shipping in Workgroup and standard edition.

7)http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384

8) See the execution plan and Put it in Database tuning advisor so that you will recommendation on adding index or dropping and update stats too

9)check the log shipping jobs on both servers and see error logs for reason.If log shipping dies, need to again take full backups.
10) It depends on which transaction replication you choose .If with Queued updating ,then it causes a issue.
11)You have to put the column in NOT FOR REPLICATION CLAUSE .What the not for replication clause does is to tell SQL Server to disable the code that reseeds an identity column when a replication agent is inserting data into an identity column. This preserves all of the identity ranges that you have setup. The drawback to this option is you could not alter a table and add this option to it.




Hi,

I agreed with you. Any way, your ideal make me thinking about some thing for my project.

Please try to keep posting. Tks and best regards
Go to Top of Page
   

- Advertisement -