:::: MENU ::::

Posts Categorized / SQL Server tools

  • Mar 30 / 2009
  • 0
DBA Interview questions, dbDigger, Monitoring and Analysis, SQL Server tools

Purpose and use of profiler

Profiler is very useful tool for repeatable and insight analysis/monitoring of SQL Server database engine, SSAS and SSIS. Profiler is a GUI used to utilize SQL Trace. SQL tarce is a used to capture client-server communication. Profiler may server following major purposes for a SQL Server DBA

  • Query analysis of execution plans
  • Analyze errors and warnings of SQL Server
  • Analyze the user activities
  • Create traces to reuse later also
  • Save trace results for later analysis
  • Jan 28 / 2009
  • 0
DBA Interview questions, dbDigger, SQL Server tools

SQL Server 2005 optional features kept off by default

Keeping in view the fact that more accessible features causes more security threats, Microsoft SQL Server 2005 came with optional features kept off by default. These features include

  • CLR Integration
  • OLE Automation sys procedures
  • Database Mail sys procedures
  • SQL Mail sys procedures
  • Ad Hoc remote queries
  • SQL Server web assistant
  • XP_cmdShell availability
  • Remote use of dedicated admin connection

It will be best that you keep off these features as long as you do not need. When you require any of these features, you may turn it on through Surface Area configuration tool or Surface Area configuration command line interface.

  • Dec 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, SQL Server tools

Duration value in SQL Server 2005 trace

Question: You make a SQL Trace in SQL Server 2005 and after you have loaded everything into a table you do a select and see the following results:

Duration Reads Writes CPU
1061610 400 0 66

When you look at the duration, what is the value here?

Answer: 1.06161 seconds

Explanation: The answer is 1.06161 seconds.

In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

  • Nov 12 / 2008
  • 0
DBA Interview questions, dbDigger, Logins and Users, SQL Server tools

Change password with -Z parameter through SQLSMD

Question: You are trying to run some scripts using SQLCMD but keep getting errors that your password has expired. SSMS is not installed on this machine. What should you do?

Answer: Use the -Z parameter to change your password.

Explanation: While many of us might feel like going home, that’s not a great solution. Either calling a DBA or finding another workstation will work, but that’s inefficient. With the -Z parameter, you can change your own password with SQLCMD.

Ref: SQLCMD – http://msdn.microsoft.com/en-us/library/ms162773(SQL.90).aspx

Note: The Question is taken from SQLServercentral.com.

  • Oct 31 / 2008
  • 0
dbDigger, SQL Server tools

Security requirements for running profiler in SQL Server 2008 for compatibility mode 80 database

Question: In SQL Server 2008, if you are running Profiler in a database set to compatibility mode 80, are the security requirements for showplan data determined?

Answer: The SQL 2008 interpretation is used.

Explanation: When you view showplan information through Profiler and the database compatibility mode is set to 80, the SQL Server 2008 permission interpretation is used. In this case, the compatibility mode has no effect.

Ref: Showplan Security –

Note: The Question is taken from SQLServercentral.com.

  • Jul 17 / 2008
  • 0
dbDigger, SQL Server tools

SQL Server 2005 tableDiff utility

TableDiff is one of many command line utilities in SQL Server 2005. It helps to determine differences between tow tables or views. One of these is taken as source and other as destination. This utility can report differences on schema and data. Although this utility is primarily designed for comparing replicated tables, but we can use it to compare tables.
To invoke the utility. Go to command prompt and access com folder of SQL Server 2005 installation there.

Then entered following command to compare tables through this utility.The command takes a few basic parameters to run.

  • sourceserver
  • sourcedatabase
  • sourcetable
  • destinationserver
  • destinationdatabase
  • destinationtable

Here tablediff is key word to invoke the functionality. All words starting with – are reserve words and are self explainatory. All words between the quotes are in your parameters and are linked to ancestor word starting with -. As you enter this whole command your parameters are echoed and result of comparison is generated.
Another powerfull feature is generation of SQL Scripts for the differences that it found between two tables. For that u just have to add -f as additional flag and also have to provide path and name for script.

Other options can be used in the TableDiff Utility. They can be found by executing the following command:
C:Program FilesMicrosoft SQL Server90COM>tablediff /?

Consult us to explore the Databases. Contact us