Tanner’s Space

Let me tell you a story about a guy, his file processing service, and the transaction log.

The file comes in, it has 65,000 entries.  Guy starts file processing service on a p4 dual core with about 2gb ram.  Surprisingly, 5 minutes later Guy wanders up and checks his log file to see that “file processor” is still processing!! Guy screams “WTF mate, these blimey new boxes are still slow as the afternoon sun”.  A bit later, the processsing is complete and Guy gets to go home. Lucky Guy.

Guy is not happy about the wasted time at work that he could use to leave before the rest of traffic, so he Googles “slow stupid sql server big file” and stumbles across information about the Transaction Log. After much reading Guy reads that a small log size for this type of log results in slow performance.  The next day Guy looks at the size of this log and notices it is very small compared to the log for the primary database.

Guy knows that lots of free hard drive space exists so he decides to try and increase it. He downloads Sql Server Express and Management Studio Express so that he can figure out how to adjust the size.   At least Guy is smart enough to not “learn” how to do it on the main machine.

After Guy learns how to fix the transaction log size at home he fixes it on the machine at work and anxiously starts the file processing service. To his surprise the entire 65,000 entry daily file is completely processed in 15 seconds, instead of the 5 minutes as before.

Moral of the story: Don’t forget about the transaction log, make sure you set it up so that it can grow proportionally. A fixed size just won’t cut it.

sql server bulk insert sql, slow insert, sql, sql server, sql2000, sql2005, sql2008, transaction log

One of the most difficult things that I encountered with my installs of Sql Server 2005 was trying to capture all the install configuration settings so that I could duplicate the install without worry.

In Sql Server 2000 you could choose to capture all the options during a GUI install and have them written to a .ini file. You could then create a batch file to open the ini file and set all the parameters during a install from the command line. This nice feature was left out of 2005 Sql Server but could partially be duplicated by some hacky steps.

After doing some research for 2008 Sql Server I am happy to report that the first time through the installer GUI it will build you a configuration file, and in subsequent installations from the GUI you can choose to load this file of parameters. Or, you can still create your batch file and load the parameters in that way.

The configuration.ini file will be built to

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\yyyymmdd_hhmmss

http://sqlblog.com/blogs/john_paul_cook/archive/2008/08/15/sql-2008-configurationfile-ini-file-not-just-for-unattended-installs.aspx

edit: 2/23/2009 - Because of increased amounts of spam comments tied to this article I have turned off commenting. If you have a question about something in the article please go through the contact page to reach me for the time being. Sorry for the inconvenience.

Tech 2008, sql, sql server, sql2008, unattended install, unattended installs