How to shrink tempdb

Imparted from here

There may come a time when you might want to shrink tempdb because it has become too large.
There are a few ways you can do this and I have listed them below but please read to the end of the post before making a decision on which way you want to approach this. There is an important note at the end of the post.
So first, we’ll look at the configuration on my server
1
SELECT name, size
2
FROM sys.master_files
3
WHERE database_id = DB_ID(N'tempdb');
name                 size
-------------------- -----------
tempdev              1280
templog              640

(2 row(s) affected)

Note that the size column is listing the size of the file in 8Kb pages. In this instance my “tempdev” file is 10Mb (( 1280 * 8 ) = 10240 kb)

How to shrink tempdb using DBCC SHRINKFILE


The syntax is as follows and the operation does not require a restart of the sql server service.
DBCC SHRINKFILE(logical_filename, size_in_MB)
So I will shrink the file to 5Mb
1
DBCC SHRINKFILE(tempdev, 5);
Which produces the following output and I can see that CurrentSize is now 50% smaller than previously
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
2      1           640         288         176         176

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
name                 size
-------------------- -----------
tempdev              640
templog              640

(2 row(s) affected)

Don’t try and increase filesizes in tempdb using this command because you will see an error. In this example, the attempt was to increase to 50Mb.
Cannot shrink file '1' in database 'tempdb' to 6400 pages as it only contains 640 pages.

How to shrink tempdb using DBCC SHRINKDATABASE


The syntax is as follows and does not require a restart of the SQL Server service:
DBCC SHRINKDATABASE(tempdb, ‘target_percentage_of_free_space’);
So if the data files in tempdb had enough free space, you could shrink tempdb by running this command to leave 10% of free space at the end of the files:
1
DBCC SHRINKDATABASE(tempdb, 10);

How to shrink tempdb using ALTER DATABASE


As in my post about moving tempdb, you can use the ALTER DATABASE command to perform a tempdb resize. A restart of the SQL Server service will shrink tempdb to its original predefined size but you can also resize tempdb using ALTER DATABASE.
The following script will resize both the log and data file to be 100Mb.
1
USE master;
2
GO
3
ALTER DATABASE tempdb
4
MODIFY FILE (NAME = tempdev, SIZE=100Mb);
5
GO
6
ALTER DATABASE tempdb
7
MODIFY FILE (NAME = templog, SIZE=100Mb);
8
GO
How to shrink tempdb using Management Studio

You can also use Management Studio to resize Tempdb and perform shrink operations by right mouse clicking the tempdb database and choosing Tasks->Shrink.
To resize tempdb, you can set the file sizes by right mouse clicking the tempdb and choosing Properties->Files and setting the sizes there.
Note that with both the ALTER DATABASE and management studio methods, you cannot resize a tempdb file to a particular size if the data contained in the file exceed the size that you are trying to size to.
Should you shrink TempDB?
It is documented in this Microsoft article that it can cause consistency errors in the TempDB database if you perform a shrink operation while the database is in use so please read this carefully and consider whether you can shrink the database by other means, i.e restarting the SQL Server instance which will create a brand new copy of TempDB releasing the disk space.






























Walkthrough: Displaying KPIs in a Role Center [AX 2012]

You can display the KPIs that you created in a web part on your role center page in Microsoft Dynamics AX or Enterprise Portal.

The following procedure explains how to add the Sales Analysis KPI to a web part in the CEO Role Center page.

To display the KPIs in a Business Overview Web part
  1. Navigate to the CEO role center that is located at http://<server>/sites/DynamicsAx/Enterprise%20Portal/RoleCenterCEO.aspx.

  2. On the Site Actions menu, click Edit Page. Locate the Middle Column section, and then click Add Web Part

  3. In the Categories list, click Microsoft Dynamics AX. In the Web Parts list, select Business overview, and then click Add.

  4. On the drop-down menu for the web part, click Edit Web Part. The properties for the web part are displayed.

  5. For the Select mode property, select KPI List.

  6. Expand the Business Overview Setup node.

    NoteNote

    The Business Overview web part points to the default Dynamics AX Analysis Services database. You can use any KPIs that ship with Microsoft Dynamics AX and any KPIs that you add to that database. If you create another database, you must create an Office Data Connection (ODC) file that points to that database. You then must specify the location of the ODC file in Data Connection.

  7. For the Title property, type Sales KPIs. Click OK to save the changes.

  8. In the Sales KPIs web part, click Add KPIs.

  9. In the Business Overview - Add KPI dialog box, select the Sales Analysis cube, select the Customer sales KPI, select Amount for the Display value as field, and then click OK.

    NoteNote

    If you created a new Analysis Services database that has only the SalesAnalysis cube, you do not need to select a cube in the Business Overview - Add KPI dialog box.

  10. Click Stop Editing to save the changes made to the page.

Setting Time-out Values for Report and Shared Dataset Processing (SSRS)

 

You can specify time-out values to set limits on how system resources are used. Report server supports two time-out values:

  • An embedded dataset query time-out value is the number of seconds that the report server waits for a response from the database. This value is defined in a report.

  • A shared dataset query time-out value is the number of seconds that the report server waits for a response from the database. This value is part of the shared dataset definition and can be changed when you manage the shared dataset on the report server.

  • A report execution time-out value is the maximum number of seconds that report processing can continue before it is stopped. This value is defined at the system level. You can vary this setting for individual reports.

Most time-out errors occur during query processing. If you are encountering time-out errors, try increasing the query time-out value. Make sure to adjust the report execution time-out value so that it is larger than the query time-out. The time period should be sufficient to complete both query and report processing.

Setting a Query Time-Out for an Embedded Dataset in a Report

Query time-out values are specified during report authoring when you define an embedded dataset. The time-out value is stored with the report, in theTimeout element of the report definition. By default, this value is set to 30 seconds.

Users who have permission to modify the properties of a published report can reset this value by editing the report definition file.

You can also specify a query time-out value for data-driven subscriptions. The query time-out value is specified in the Data-Driven Subscription pages. The value you specify determines how long the report server waits for query processing to complete when retrieving data from the subscriber data source.

Setting a Query Time-Out for a Shared Dataset

Query time-out values are specified in seconds on the report server when you create or manage a shared dataset. By default, this value is set to 0 seconds, which is the equivalent of no time-out value.

Setting Report Processing Time-Out

You can set the report processing time-out value to limit the amount of time that a report server uses to process a report. Report processing time-out values can be changed using two different procedures:

  • Use Report Manager. You can set a default value for all reports in the Site Settings page, and you can override that value in the Execution properties page for a specific report. By default, the value is set to 1800 seconds.

  • Use SQL Server Management Studio (SSMS).You can set the execution time-out for all reports. In SSMS, Right-click the name of a report server, then click Properties. On the Server Properties window, click the Execution page and change the value for Limit report execution to the following number of seconds.

How Report Execution Time-Out Values are Evaluated

The report server evaluates running jobs at 60 second intervals. At each 60 second interval, the report server compares actual process time against the report execution time-out value. If the processing time for a report exceeds the report execution time-out value, report processing will stop.

Note that if you specify a time-out value that is smaller than 60 seconds, the report may execute in full if processing starts and completes during the quiet part of the cycle when the report server is not evaluating running jobs. For example, if you set a time-out value of 10 seconds for a report that takes 20 seconds to run, the report will process in full if report execution starts early in the 60 second cycle.

NoteNote

You can set the RunningRequestsDbCycle setting in the RSReportServer.config file to change the frequency of how often running jobs are evaluated.