SQL Server

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.



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:


Then shrink AdventureWorks log file to 2 MB size:

USE AdventureWorks
DBCC SHRINKFILE (AdventureWorks_Log, 2)

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



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

Presentation Slide at Singapore CTU 2008

I presented at Community Technology Update 2008, organized by Microsoft Singapore DPE on November 22, 2008. It was a fantastic community event, a place when community members gathered together to share their passion on latest Microsoft Technologies.


My first session at the morning was Web 2.0 Programming with Visual Studio 2008. I presented Web 2.0 term, and what kind of tools available in Visual Studio 2008 to achieve Web 2.0 effects. I was much inspired by DropThings project  by fellow ASP.NET MVP Omar Al Zabir.

The second session was about SQL Server Performance Tools. No, this is not yet another Microsoft product come out. It is a collection of free tools, scripts, and tips to identify server bottleneck and resolve performance problems. My idea was to share hidden tools and script to reveal performance bottleneck. I was presenting about PAL, SQLDiag, server side script and database tuning advisor. I have to give a big credit to Itzik Ben Gan book for collection of server side trace script instead of using graphical Profiler. Both PPTs are available for download from my Skydrive:

Web 2.0 with VS 2008

SQL Server Performance Tools

I am not able to publish my demo code at the moment, because it was running on my real machine at that time, so I need to fine tune and modify some configuration to avoid any problem. Once I finished with the changes, I will update share my demo code here.

Easy Server Monitoring with PAL

The first step in SQL Server performance tuning usually starts with finding bottleneck at the server side. It includes monitoring to some suspicious symptoms on CPU, Memory, Disk, and various SQL Server counter. Yes, Performance Monitor tool is already available on Windows machine. It is a very valuable tool for detecting the server bottleneck, and it is free. Just type perfmon and we are ready to start to do monitoring.

Usually I create counter log and let the monitoring runs at the background based on specific schedule and sampling interval. With this way, I can minimize the performance impact by running Perfmon inside the box. Once I got the log data, then I am ready to start the analysis. There are thousands or even millions rows of logs inside. How do I analyze and finding the system behavior, detect the pattern and plan a possible actions? Obviously I can import the CSV log file to SQL table or Excel and then use my T-SQL knowledge to summarize the data. But actually there is easier way to do it, Clint Huffman of Microsoft already created PAL.

The PAL does query to the counter log data using Log Parser, and display it in a nice HTML format. It is a graphical chart data analysis, so that’s why PAL requires Office Web Component installed to view the report. We can download PAL from CodePlex, and don’t forget to install Log Parser and OWC11 as pre-requisites. So the steps to do analysis with PAL are:

  • Collect counter log data with Perfmon
  • Use counter log as input to PAL
  • Analyze the report with PAL

The first screen of PAL will ask a location of counter log file that was created by Perfmon.


At the next screen, there are several options of system monitoring template. It depends of what kind of servers installed in the system.


The good things is PAL already comes with predefined template and threshold, so we do not need to think about the threshold for various counter. We con modify this template as well to suit our needs.

Here are several reports generated by PAL, very nice analysis with graphical trends.



Extract and Transfer RDL files from SSRS

I was in the situation when I had to modify some reports, but I did not have the source code (RDL) files on hand. I tried to go to SSRS server, and looking for download or save-as menu. I was expecting there are some ways to download the report definition so I can modify it under Visual Studio.

Unfortunately not, there is no built-in features in SSRS that for download or extract report from the server. Once deployed, it is there forever. I just thought to create small application using SSRS web services or API to extract the definition. But I was lucky enough that Jasper Smith created an amazing tool for this purpose.

I downloaded the RSScripter and install it on my machine. It can connect to both SQL 2000 and 2005, and generate the RDL, user, roles, and also the data source. This free tool is really fantastic.


I can also use this stuff to transfer the SSRS content from one server to another. A very good toolkit for server migration and deployment. The configuration is under option button, specify to transfer and as well as destination server.


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:
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:


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.




The complete step by step guide from Microsoft is here.

I passed SQL 2008 BI Beta Exam

I took Beta exam no 70-448 last July, because I was curious how’s difficult it is. It is a Business Intelligence exam, a foundation for MCITP for SQL 2008 BI. I’ve touched SQL 2008 since it’s CTP 5 release so I’m quite familiar with the stuff.

I sat almost 3 hours in the testing room, clicked the finish button to end the exam. There was no on the spot scoring for beta. So I did not really know if I passed or fail. But I felt that I pass even with no flying marks. After more than 4 months, I just received email from MS learning. They said that I passed the Beta. Wow, It seems magic to me.

From: Microsoft Certified Professional Programs
Sent: Thursday, October 09, 2008 11:22 AM
To: Muhammad Choirul Amri
Subject: Congratulations on Your Microsoft Certification! Access Your Benefits
Congratulations on earning your Microsoft Certified Technology Specialist: SQL Server 2008, Business Intelligence Development and Maintenance certification! We hope you enjoy the benefits of your certification and of membership in the Microsoft Certified Professional community.

In fact, my knowledge on SQL 2005 helped a lot to clear the exam. I did not do special preparation for it. It was a free offer, so nothing to loose at my side. I really relied on my prior knowledge on SQL 05, play around with SQL 08 and of course my real world experience in the past. This is also a prove that at least I know the stuff better. I was not cramming the book and practice test to shot the test.

I logged in to my MCP transcript, and MCTS SQL 2008 BI Charter member is already under my belt. Yes, I’m the charter member.


I joined the rank of first 200 people who cleared this paper. I can’t wait to receive the welcome kit. Hopefully there will be something special for Charter Member :)

Now I challenge my friend Kasim and Zeddy. If you dare to fail, try the beta exam.

Upgrade SQL 2000 to SQL 2008

Upgrade from SQL 2000 to SQL 2008 is now officially supported. In the early release of SQL 08 (currently is RC0), we are not sure whether there will be an upgrade path from SQL 2000 to SQL 08. This uncertainty was over when I was installing RC0 last night. The option to upgrade from SQL 2000 instance is there.


Ok dude, the menu is there. What about the guidance? We know that there is more than 300 pages whitepaper available for SQL 2005 upgrade. But the one for SQL 08 is not available as of this writing, please be patient to wait. The Microsoft Official Curriculum for this topic is under development now, and I am also involved in it. The alpha run of this training will be held around September 08, and is expected to hit the market 2-3 months after that.

My advice for you that still stick with SQL 2000 is: don’t upgrade to SQL 2005. Please wait until the release of SQL 08, and do direct upgrade to SQL 08 instead :)

Disable PDF Export in Reporting Services

One of my student came with a popup question: how do we disable the export to PDF feature in reporting services? Well, I should say that export to PDF is awesome. But some company don’t allow their employee to do that, or maybe they want to disable export to XML as well.

There are several option to do that. On the report level, we can utilize the query string to inject the parameter, and disable the parameter and export bar on top. Another elegant method is jump to the configuration file of the Reporting Services installation, and comment ot the PDF rendering extension.

Open the rsreportserver.config xml file with any editor of your choice. This file is located under ReportServer folder of the SSRS installation. Here is the capture:


If you’re not sure where is the file located, go to the IIS console and read the home directory of the ReportServer virtual directory.

Once the file is opened, locate the rendering section and disable the PDF extension. We can also disable or enable another extension that available.


As usual, we need to recycle the application pool or simply do IISRESET to apply the changes. The next step is to open a report under the http://yourservername/reports and make sure that PDF extension is not there anymore:


SQL 2005 Data Encryption

One of the cool feature of SQL Server 2005 is out of the box data encryption capability. Not like SQL Server 2000 that requires 3rd party support or creating your own custom script, SQL05 comes with built in function to do that.

It supports both symmetric and asymmetric encryption, as well as hashing. I created a sample script to do symmetric encryption. A slide deck presentation also available for a quick review about encryption architecture.

Actually I created this material for one day SQL Server security workshop at Avantus. This one day workshop covers encryption, SQL injection, SQL Lock down, and security administration. I decide to release the encryption module here along with the sample script, but not include the detail manual. Some of the material comes from Lara Rubbelke’s presentation.

For some of you that still stick on SQL 2000, Michael Coles already created an encryption toolkit with no cost.

Download the presentation.

Download the sample script.

Migrating to SQL Server 2005

I did SQL Server 2005 migration discussion last April with several engineers of Freeport Indonesia. I used white board, chalk talk style at that time. I did the presentation again yesterday but in more formalized format. It was an SQL Server 2005 Technical Upgrade Workshop held by Microsoft Singapore. There were 3 sessions in that half day workshop:

  • Transparent Benefits of Upgrading to SQL Server 2005
  • How to Upgrade to SQL Server 2005
  • Get The Most of SQL 2005 BI Tools, Benefits, and Upgrading

It’s not enough to cram the whole things in a half day, so I skipped some unimportant slides and did more live demos. Hopefully It gave better idea about the SQL Server 2005 benefits and migration process. Here is the presentation:

Microsoft already published a very good white paper for SQL Server 2005 upgrade and migration. It’s not only contain step by step wizard and screen shoot, but also how to design, prepare, and manage the migration process. Migration can become a risky job if we have a lot of DTS, AS, and heavy weight reporting services deployments. Also, did I mention the existing clustered environment 😉 ? This guide is definetely answer most of the uprgrade and migration questions. Download it, read it, and follow the migration path.