:::: MENU ::::

Posts Categorized / DBA Interview questions

  • Jan 07 / 2010
  • 0
DBA Interview questions, dbDigger, SQL Server Agent scheduled Jobs, T-SQL Interview Questions

SQLAgent status through T-SQL

Question:

Using Transact-SQL, how can you find out whether SQLAgent is running?

Answer:

  • sysprocesses table for program_name like SQLAgent%
  • Use xp_servicecontrol querystate SQLSERVERAGENT

Explanation: Querying the sysprocesses table will return a row if sql agent is running, else it will not return any rows. The xp_servicecontrol procedure will return the status of the service (running/stopped) on 2005 / 2008 editions except express. On a SQL Server Express instance this command will return an error. So you need to check for errors as well as the status to determine whether the agent is running.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
Data Modeling and Database Design, Data Types, DBA Interview questions, dbDigger, T-SQL Interview Questions

Find columns with identity property

Question:

In SQL Server 2005, how can you easily determine which columns have the identity property set?

Answer:

  • Query sys.identity_columns for the rows.
  • Query sys.columns.is_identity for a value of 1

Explanation: There is a table, sys.identity_columns that contains a row for each column in your database that has the identity property set. There is also a column called is_identity in the sys.columns view that contains a 1 if the column has the identity property set.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
DBA Interview questions, dbDigger, Triggers

Ordering the triggers in SQL Server database

I have 3 UPDATE triggers on a individual table, TRA, TRB and TRC. Can I fire them in the sequence TRA, TRB and TRC?

Answer: Yes

Explanation:Using sp_settriggerorder Set TRA as first, TRC as LAST and TRB will fire between TRA and TRC firing which is what is desired.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Oct 06 / 2009
  • 0
DBA best practices, DBA Interview questions, dbDigger, Publications of Atif Shehzad on MSSQLTips.com, Triggers

Prevent accidental update or delete commands of all rows in a SQL Server table

My article related to Prevent accidental update or delete commands of all rows in a SQL Server table is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Prevent accidental update or delete commands of all rows in a SQL Server table. It would provide efficient way to make sure that if you forget to include where clause in update or delete scripts then all rows should not be updated or deleted.

Consult us to explore the Databases. Contact us