Jason N. Gaylord ~ SQL Server Advice

Microsoft has released a SQL Server 2005 Business Intelligence webcast list. You can view the list of webcasts and other events here: http://www.microsoft.com/events/series/sqlserverbi.mspx

If you plan on keeping your SQL Express data when you deploy an ASP.NET 2.0 application that you add the applicationName attribute to each of your providers under each special ASP.NET configuration section (ie: Membership). If you do not, ASP.NET 2.0 will look to see if the app was run under a different application or not. If it was, it will create a new application entry in the database and none of your users and profiles will be associated.

I posted a question to the ASPAdvice.com forums regarding the limitations of SqlExpress 2005. I already knew that there was no limitation on connections. However, Scott Forsyth was able to provide me with additional info. Here you go directly from the post: The database size limit is 4GB. There is also a memory limit of 1GB of RAM and a limit of 1 CPU. You can install it on machines with more than 1 CPU, but it only utilizes a max of one. There is a tool to manage SQL Express that can be downloaded from http://lab.msdn.microsoft.com/express/sql/.

A little while ago, I blogged about some issues with uninstalling SQL 2005. Finally, Microsoft has published an uninstallation "script." Find out more by visiting http://lab.msdn.microsoft.com/vs2005/uninstall/.

Today I received an error message within one of our applications stating that "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space." I posted to the ASPAdvice.com lists and received more information than I bargained for. Mike Campbell and “Wonderful” Wally McClure provided some very valuable information regarding this issue. Here is Mike's post below:

Jason,

Let your log file grow a bit more in size and/or jump on query analyzer and run the following:

BACKUP LOG <database_name> WITH TRUNCATE_ONLY
GO

That will truncate your log (meaning recoverability won't allow point-in-time recovery... though I doubt you'll need it).

1 MB may not sound like much, and really isn't the issue. The issue is that SQL Server will only allow a certain amount of space for logging transactions/activity.. when that space is up it's game over (unless you allow the log file to grow -- and then if you allow it to grow, it can grow until there are only 5MB left on the disk, and when that fills up you'll get the same message). The best way to deal with this: make your log file 25% of the size of your .mdf and do nightly backups, which truncate the log after recording all of the changes to the backup. If you do LARGE transactional changes during the day you may run into this a bit.

Here's a quick overview of some other info (but the sql statement above will get your app working again... then you'll just need to solve the problem, not just the incident). http://blogs.sqladvice.com/aweiker/archive/2004/03/31/883.aspx

Turns out that the issue wasn't necessarily with the transaction log. In fact, the C: drive on the server dipped below 500 MB of free space. Once temp files were cleared out and the free space hit 2 GB, the web app started working again. Go figure!

I ran into an issue when installing a new version of Visual Studio 2005. I forgot to uninstall SQL Server 2005. So, the .NET Framework versions were different. This caused tons of issues leading to me not being able to have SQL Server 2005 at all on this particular machine. So, remember to uninstall everything first! ;)

I read a fairly decent article today about getting ready for SQL 2005. So, I thought I'd share...

http://www.ftponline.com/vsm/2004_en/magazine/features/rjennings/

Paul Litwin has posted some good points to help prevent SQL Injection attacks. View his article at http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/default.aspx.

I received an e-mail today with a SQL Server article repository on the FTPOnline website. I figured it would be useful to someone, so here it is:

http://www.ftponline.com/resources/spcollections/sql/

This past week, I had an issue where I needed to randomize the data coming from SQL to use in a SqlDataReader in an ASP.NET page. I posted an issue on the ASP.NET forums and soon received help from Scott Mitchell. He pointed out that in the stored procedure I can use the code:

Select *
From MyDatabase.MyTable
Order by NewID()

Of course, since Scott is the .NET Data Objects King, it worked like a charm. He received that information from http://sqlteam.com/item.asp?ItemID=8747. Thanks to Scott for the help.

Awhile ago I was discussing with a few other peers on the ASP.NET forums regarding relational and object-oriented databases. In my opinion, it seems as though a .NET programmer would find an object-oriented database more useful than a relational database because you would then be allowed to pass entire objects to the application. However, the only object-oriented database that I'm aware of is called Caché by InterSystems (www.InterSystems.com). I have not had the opportunity to fully test their system. However, I do know some valuable information about the database and .NET development. OO databases allow for objects to be created and then passed between applications. .NET doesn't directly support the Caché database nor any other OO databases. To get the OO database to work with the current framework, you can either use it as a relational database or pass the objects to web services; none of which uses the speed of an OO database. So, it will be interesting to see what OO databases will do in the future. I'm wondering if a future version of MS SQL will contain OO technology. Hmmm...  :-)