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

I finally got around to posting the pictures from the Greater PA MSDN Code Camp 2005 event in Philadelphia. They are posted on the .NET Valley User Group website at http://www.dotnetvalley.com/Photo+Gallery/default.aspx. Hopefully next time I can make it to both days!

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/.

The .NET Valley User Group will participate with other User Groups in the Greater PA region in attending the Greater PA MSDN Code Camp in Philadelphia this Saturday and Sunday (April 23 and 24). We look forward to meeting the various attendees of this event. If you have not signed up as of yet, I believe there are still some spots available. Be sure to visit the .NET Valley website for more information.

This event will feature 15 SQL and Data Access talks from some of the top SQL professionals including some Microsoft MVPs. The event will also host many other talks including ASP.NET and Windows Forms presentations. It will be chilly this weekend so you might as well come on out!

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/

I thought I'd post and ask if anyone remembers those old ERwin diagrams. These days, there are numerous tools to perform data modeling. However, 5 years ago, there were only a few. I found a CD the other day with old ERwin documents that I needed restored. With the help of Computer Associates (ca.com), I was able to open my old ERwin 2.29 files in ERwin 3.52, a special download available by contacting CA support. They have to be contacted to obtain the proper key. Then I could download the new 4.14 version and update those docs. Now I've got my docs. So, if anyone has some old docs they need converted, hopefully CA can 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...  :-)