Category Archives: SQL Server

Moving SQL Server Reporting Services

Over the last year, I have had the opportunity to migrate Reporting Services several times.  :)    Unlike the Microsoft documented backup and restore operation or the move procedure , the process has been … er… not so straightforward for me.

The problem I have run into revolves around the key in the ReportServer database in table dbo.Keys and the InstallationID in my rsreportserver.config file

If I match the key in this table to the file I can solve the problem.

In the event you have to migrate Reporting Services from one server to another and get an error message that there is a mismatch between the rsreportserver.config file and the ID in ReportServer.dbo.Keys , then you may try the procedure outlined below.  Keep in mind, this process steps you through the entire migration procedure of moving your Reporting Services databases from your source server to the destination server.


When navigating to //<Reporting Services Instance Name>/ReportServer you get the Reporting Services Error Message:

The Installation ID in the rsreportserver.config file does not match the ID in ReportServer.dbo.Keys.


To correct this problem,

1. Stop Reporting Services on your destination server.

2. Take your ReportServer database backup and restore it to your destination server.

3. Find the source server’s InstallationId in the Keys table with this query.  Copy the results:

SELECT * FROM ReportServer.dbo.Keys

4. On your destination server, find and open your rsreportserver.config file.  It is located in a path like:

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

5. Copy the InstallationId from the Keys table in step 3 and replace the InstallationID between the curly brackets {} in the rsreportserver.confi g file.  Save and close the file on the destination server.

6. Start Reporting Services on your destination server.

7. Browse to Reporting Services: //<Reporting Services Instance Name>/ReportServer to confirm that the Keys match and that Reporting Services is running properly.

Database AutoGrowth

SQL Server Database AutoGrowth

Leaving the default settings for SQL Server AutoGrowth is an unhealthy practice.  As many will agree, the default growth rates should be changed .

OK.  So using percentage settings is a bad practice.  I don’t want to grow the database in 1 MB increments, and not touching it is bad too.  What am I supposed to do?  How can I best configure my SQL Server AutoGrowth settings?  Are there any best practices or recommendations for database autogrowth?   Aggghhhhhh!

Before I pull out my hair, let me digress…

Over the course of time, colleagues and experience have helped me draw up some general guidelines:

  • If the file size is less than 1 GB , then set the File Growth Rate for the data file to 200 MB .
  • If the file size is between 1 GB and 200 GB , then set the File Growth Rate for the data file to 1 GB .
  • If the file size is greater than 200 GB , then set the File Growth Rate for the data file to 10 GB .

The conditions in your environment may vary, but this is a good place to start.  Consider whether your database is a data warehouse, has a transactional focus, or very large database (VLDB).  Your goal is to minimize the number of times your database grows, but when it does, take the amount of storage you need without having to return to claim more too soon.

The final piece of advice, as any good DBA will tell you, is interview your users, measure your workload, monitor your database growth, and adjust settings for your specific situation.

Maintaining Your SQL Server

I was working on a SQL Server installation this week when I was reminded of an excellent maintenance solution for the database server.  Ola Hallengren’s database maintenance solution is a package of scripts based on Microsoft’s best practices for backing up, checking integrity, and optimizing indexes for databases.

It is easy to implement for the savvy SQL user.  The scripts are also flexible enough to allow for more granular configuration of the maintenance job.

If you haven’t tried it, I strongly recommend that you review it at a minimum.  You will be glad you did.

Shrink tempdb database in SQL Server

Yesterday I had to shrink the tempdb database on SQL Server.  I follow SQL Server tempdb best practices , but rarely have I shrunk tempdb before.  Despite the discussion on whether or not, or how many files to separate tempdb into, I expanded upon the instructions on how to shrink the tempdb database in SQL Server.

Of the options available, Method 1 Use Transact-SQL commands, has always been my preferred method. This is due to the fact that all three ways require restarting SQL Server, so I favor the one that allows me complete control over the size of the tempdb files.  Nevermind I cannot guarantee tempdb is not in use in my environment like I should have for methods 2 and 3.

When, I follow Method 1 for shrinking tempdb, I:

1.Stop SQL Server.  You can do this from SQL Server Management Studio (SSMS) by right-clicking on your server instance and selecting Stop .

2.  Start SQL Server in minimal configuration mode.  At a command prompt, type the following command to start SQL Server:

sqlservr -c -f

In minimum configuration mode tempdb has a size of 1 MB for the data file and 0.5 MB for the log file.

Note If you are unable to start SQL Server, you may need to navigate to “Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\”

If you use a SQL Server named instance, you must change to the appropriate folder (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) and use the -s switch (-s%instance_name%).

3.Connect to SQL Server by using Query Analyzer, and run the following Transact-SQL commands:

(NAME = ‘tempdev’, SIZE = target_size_in_MB)
— Target size for the data file

(NAME = ‘templog’, SIZE = target_size_in_MB)
— Target size for the log file

(NAME = ‘tempdev’, SIZE = 2048)
— Set the data file size to 2 GB.

(NAME = ‘templog’, SIZE = 1024)
— Set the log file size to 1 GB.

4. Stop SQL Server by pressing Ctrl+C at the Command line.

5. Start your SQL Server service in SSMS by right-clicking your SQL Server and clicking Start .

6. Log into SQL Server and verify the size of the tempdb.mdf and templog.ldf files.  You can do this in SSMS by expanding the System Databases folder, Right-click tempdb , choose Properties , and click Files in the left pane.  The tempdb file sizes will be in the right pane.