:::: MENU ::::

Posts Categorized / DBA Interview questions

  • Apr 02 / 2009
  • 0
Backup and Restore, DBA Interview questions, dbDigger, System Databases, Troubleshooting

Cannot back up the log of the master database. Use BACKUP DATABASE instead.

The error message
Msg 4212, Level 16, State 1, Line 2
Cannot back up the log of the master database. Use BACKUP DATABASE instead.
is error of severity level 16 and can occur on all versions of SQL Server. It appears when you try to backup log of master database. SQL Server does not allow to backup log of master database. To avoid the error you should take full backup of master database.

  • 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
  • Mar 21 / 2009
  • 0
DBA best practices, DBA Interview questions, DBCC Commands, dbDigger, Transaction log files

How to Prevent the Unexpected Growth of transactional log files in SQL Server

To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:

  • Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
  • Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
  • Change the recovery model. By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups. By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database. You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.
  • Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
  • Design the transactions to be small.
  • Make sure that no uncommitted transactions continue to run for an indefinite time.
  • Schedule the Update Statistics option to occur daily.
  • To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
  • 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 03 / 2008
  • 0
DBA Interview questions, dbDigger, Performance Tunning and Optimization

Statistics cannot be created on object ‘%.*ls’ because the object is not a user table or view.

The following error message
Msg 1929, Level 16, State 1, Line 1
Statistics cannot be created on object ‘%.*ls’ because the object is not a user table or view.

is a run time error message of severity level 16. It can occur on all versions of SQL Server. This error message appears when you try to create a statistic for an object that is not a table or a view. As errors of Severity Level 16 are generated and can be corrected by the user. To remove the error make sure that statistics can only be created for table and view objects.

Consult us to explore the Databases. Contact us