Tag Archive for SQL Server 2005

Installing SQL 2005 (X64) on Windows 7 RC

I was forced to install SQL Server 2005 X64 in my system, because my development DB was X64. Since my machine runs Win 7 X64, I was tempted to install on top of it. Anyway, I don’t have a spare machine, and VPC also does not support X64 OS :).

I was thinking that everything should be fine because I did it on my Vista X64 before. But apparently not, after tweak the setting here and there, I was ended up with the following error screen:

Capture

I was sure that all pre-requisites has been installed properly, but it was still failed. My first attempt was checking IIS 7 configuration, but everything was fine. The machine passed IIS requirement checking at the beginning of the steps.

I tried the last resort, just checking my luck. Since I saw that SQL Native Client was failed to install, I tried to download the latest SQLNCLI and voila! It was installed successfully after I updated the native client. Moral of the story: Windows 7 X64 RC does not come with the latest SQLNCLI, and the one bundled in SQL 2005 CD is not compatible with Mr. 7.

Capture3

To sum up, this is the steps to install SQL 05 X64 on Win 7 x64 RC:

Download and install the latest SQL Native client, it’s bundled under SQL 2005 Feature Pack family. I use the one from February 2007 and worked like a magic.

Capture2

Second, make sure all pre-requisites are installed such as IIS 7 components (ASP.NET, CGI, IIS6 compatibility, etc). This is the case if we want to configure Reporting Services as well.

Once installation finished, update to SQL Server 2005 SP2. Without update, it won’t work because the security policy kicked out Mr. Administrator from SQL sysadmin role :).

That’s all, happy testing …

Shrink and manage huge SQL log file

Updated on January 10, 2011:  This post is outdated

You should not shrink database file too often because It creates more fragmentation. Quote from MSDN documentation:

Best Practices

Consider the following information when you plan to shrink a database or file:

  • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
  • Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
  • Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

http://msdn.microsoft.com/en-us/library/ms189080(v=SQL.105).aspx

========================================================================================================

I had a bad experience with transaction log file, it was 8 years ago. But thanks to Mr.Bad, I learnt very important lesson as newbie DBA at that time. This is the story: my ERP application which sitting on top of SQL Server 2000 was fine at Friday afternoon. I went home for a weekend, only to find everybody was starred at me at Monday morning. What had happened? The ERP was crashed, actually it couldn’t start at all.

I went to my SQL Server box, and the guy was stopped. It’s not started. I checked everything from security permission to possibility of virus, nil. Then I found that the disk was out of space because of transaction log explosion. The accounting guy was doing quarterly stock journal in the system, and it caused transaction log growth exponentially. There was an audit table which recording all journal operation, and obviously the INSERT operation was the source of problem.

Moral of the story: make sure you have automated way to manage transaction log growth.

There are 2 way to shrink the log:

  • Detach database, delete the big log, and re-attach without log file. SQL Server will create a new, fresh log file with minimum initial size. It is 2MB by default. Disadvantage: the database will not be available during the process. Offline duration depends on how fast is the detach – attach operation takes time.
  • Better approach: use SHRINKFILE of DBCC command. This is better because I do not need to bring the system OFF.

Regardless of the approach, make sure to do full database backup. Shrinking the log deletes historical transaction that may be needed to restore database to specific point of time. Just in case, make sure to do full backup. You have been warned right :).

These are the steps to shrink a huge log file:

First, obtain the log filename, simply use sp_helpdb as follow:

sp_helpdb 'AdventureWorks'

It returns all files in AdventureWorks:

log

Then shrink AdventureWorks log file to 2 MB size:

USE AdventureWorks
GO
DBCC SHRINKFILE (AdventureWorks_Log, 2)
GO

The outcome is 20 GB log file has been shrunken to 2 MB:

 

shrinked

I usually run DBCC SHRINKFILE on scheduled basis, by attaching SQL script to SQL Agent job. This is easier and painless :).

Configuring SSRS 2005 on Vista and Win Server 2008

I never think that this is a difficult or exceptional. I never configure it before, because my job does not really require to deploy such configuration. Someday in the morning, one student asked my this question: Why I cannot run Reporting Service 2005 on my Vista? This guy used Vista as his development machine and SQL Server was running fine except the SSRS.

Well, I saw some documentation so I’m sure there is away to do it. The problem is not Vista specific. This is IIS 7 matter instead of Vista. Vista runs IIS 7, and so does Win Server 2008. IIS 7 has several breaking changes compare with IIS 6, and some developers do not really know how to deal with some changes.

I decided to configure it on my Vista 64 bit desktop and worked like a charm. Nothing special, just couple of IIS 7 configurations and security setting. Here are the steps:

Step one: Make sure the Vista machine already upgraded to SP1. In Win Server 2008, It comes with SP1 by default.

Step two: Configure role services on IIS 7. This menu is accessible from IIS 7 setting or Control Panel > Program and Features. Make sure that the following features are activated:

IIS 6 Management Compatibility:
IIS 6 WMI Compatibility
IIS Metabase and IIS 6 configuration compatibility

Application Development Feature:
ASP.NET
ISAPI Extensions
ISAPI Filters

HTTP Features:
Default Document
Directory Browsing
HTTP Errors
HTTP Redirection
Static Content

These are the screen capture in my Windows Server 2008 machine:

ssrsiis71ssrsiis72

The next step is to install SQL Server 2005, including Reporting Services. proceed with SQL Server SP2 after that. After the installation finished, browse http://localhost/reports. The reporting services should run properly.

However, I noticed there are several small issues that may varies from machine to machine. If you got trouble to load SSRS after the above procedure, do some checking as follows:

Make sure the Reporting Services web sites (both reports and report server) run on top of the classic mode application pool. IIS 7 comes with 2 mode to run the pipeline: classic and integrated. The application pool for SSRS has to be in classic mode.

 iis7ssrs3

ssrsiis74

ssrsiis75

The complete step by step guide from Microsoft is here.