How to Attach a database without a transaction log file (.ldf)

sqlprof.com — If you ever lose the drive that contains your log file, your database will become suspect and will stop working. You do not need to restore the backup in such a crash. If you do, you will lose everything since your last BACKUP. All you have to do is to detach the database and reattach it without the log. SQL Server 2005 does allow you to do this. You can reattach the database by following these simple steps... read more...

add a comment | category: Database | Views: 1

How to Tune a Database Using the Database Tuning Advisor (DTA)

sqlprof.com — The Database Engine Tuning Advisor (DTA) is a new tool in SQL Server 2005 that enables you to tune databases and improve the performances of your queries. DTA examines how queries are processed in the databases you specify and then it recommends how you can improve performance by suggesting the creation of indexes and statistics. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000. read more...

add a comment | category: Database | Views: 0

How to Use Aggregate Functions with NULL Values

sqlprof.com — NULL values may cause aggregate functions to produce unexpected or incorrect results because these functions ignore NULL values for calculations. For example, if the Weight column of a table has 504 records, of which 299 records have NULL values, the average of this column calculated by using the AVG function may return incorrect values... read more...

add a comment | category: Database | Views: 0

Understanding SET STATISTICS IO and SET STATISTICS TIME

sqlprof.com — Today, I want to talk about two commands that are often overlooked. SET STATISTICS IO and SET STATISTICS TIME these two commands are very useful when comes time to tune a query. Tuning a query seems simple enough. In essence, we want our searches to run faster. When I teach my class on “Optimizing SQL Server” I realize that many people find it difficult to accomplish this task. While there are many reasons why query tuning is difficult, this article will concentrate on one aspect. And that is that query tuning takes place in an environment that is often changing from second to second, making it hard to really know what exactly is going on... read more...

add a comment | category: Database | Views: 2

Preventing Microsoft Excel or any other application from connecting to

sqlprof.com — Today, I was asked a question on how to prevent Microsoft Excel from connecting to SQL Server. At first I wasn’t sure how to respond to this, but I finally found a way. It is a simple trick that uses the FOR LOGON option now available since the SP2 release of SQL Server 2005. read more...

add a comment | category: Database | Views: 0

Auditing Logins in SQL Server 2005

sqlprof.com — I'm often ask if SQL Server provides a mechanism to log user logins. Before SQL Server 2005 SP2, this task was possible but not reliable. In SQL Server 2005 this task is easy to implement using DDL triggers (Data Definition Language). One reason that people ask me this question, is because they need to comply with Sarbanes-Oxley. read more...

add a comment | category: Database | Views: 1