Tag Archive for SQL tips

Practical SQL Performance Troubleshooting

DBA daily life is always hectic. They receive many issues ranging from network, performance, security, or application. I had collected many scripts and tools since couple of years ago. They are very useful for ad-hoc troubleshooting and diagnose the problem on the spot.

When I mentioned ad-hoc means the ability to locate and diagnose the problem quickly, without collecting data with SQL Profiler or Perfmon. I’m not saying that ad-hoc approach is better, but there are many situations when we just do not have enough time to collect the data while my users are screaming. The DMVs become handy tools for this purpose. It shipped since SQL Server 2005, and has been improved in 2008. I always has those DMVs in my shortcut folder, or in most cases in my thumb drive.

I had the opportunity to do demo on this topic at Singapore SQL User Group meeting last month. The response was fantastic, knowing that we have many free scripts to solve our daily problem. I promised to upload all presentation and sample codes, so here they are:

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

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.