Friday, August 12, 2011

How to get .rdl file of the Reports on Report Server

 

Question:

Do you want to have copy of a report on the ReportServer?
What would be the case when, tt was not saved in SourceSafe. Is there a way to get the .rdl file for that report copied to local computer from the ReportServer?

Answer:

For SQL Server 2005 Reporting Services

  1. Go to Report on Report Manager
  2. Click properties of Report
  3. Under the report definition you will see Edit and Update link.
  4. if you click on Edit you can open or Save the RDL files

image

For SQL Server 2008 Reporting Services

  1. Go to Report on Report Manager
  2. Click properties of Report
  3. Select Download on the top right corner
  4. Now you can open or Save the RDL files

image

Monday, August 01, 2011

Can we backup reportservertempdb?


I have been asked by a DBA, if we can backup reportservertempdb, why not!

Here is the answer:

We should backup the database reportservertempdb as we backup the reportserver.

The reason to backup the reportservertempdb is to avoid having to recreate it if there is a hardware failure. In the event of hardware failure, it is not necessary to recover the data in reportservertempdb, but you do need the table structure.

If you lose reportservertempdb, the only way to get it back is to recreate the report server database. If you recreate the reportservertempdb, it is important that it have the same name as the primary report server database.

System database msdb size in huge! How to purge MSDB Backup and Restore History?

 

On one of my SQL Server 2008 Instance, the msdb database sizes around 4 GB.  I decided to find out the which table / index occupies major junk on this. I have used query below;

--- Query 1, if there is partition

SELECT TOP 10 SERVERNAME=@@SERVERNAME,DB_NAME=DB_NAME(),TABLE_NAME=OBJECT_NAME(I.ID),INDEX_NAME=I.NAME,INDID,USED, ROWS, SIZE_N_MB = ROUND((USED*8.0/1024.0),2),
ROWMODCTR,STATISTICDT=STATS_DATE(I.ID,INDID)
FROM SYSINDEXES I, SYSOBJECTS O
WHERE I.ID = O.ID
AND INDID IN ( 0,1)
AND XTYPE = 'U'
ORDER BY USED DESC

--- Query 2 If there are partitions

SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)

The output resulted;

The following 5 big tables takes up 88% of the total database size of 3.8 GB. These 5 tables occupy 3.3 GB on the msdb database.

Query 1: Output

TABLE_NAME INDEX_NAME INDID USED ROWS SIZE_N_MB ROWMODCTR STATISTICDT
backupfile PK__backupfi__57D1800A17C286CF 1 233057 4438738 1820.76 2646909 2/14/2011 15:57
backupset PK__backupse__21F79AAB0E391C95 1 115791 1489763 904.62 228112 6/19/2011 6:00
backupmediafamily PK__backupme__0C13C86F0880433F 1 50738 1489616 396.39 23386 7/26/2011 7:00
backupfilegroup PK__backupfi__760CD67A12FDD1B2 1 43284 2967333 338.16 1775145 2/14/2011 15:57
backupmediaset PK__backupme__DAC69E4D04AFB25B 1 29250 1489616 228.52 44462 7/22/2011 15:43
sysjobhistory clust 1 3553 33177 27.76 2428 7/25/2011 8:14

Query 2: Output

objectName indexName totalPages usedPages dataPages totalSpaceMB usedSpaceMB dataSpaceMB Rows
backupfile PK__backupfi__57D1800A17C286CF 233073 233043 231653 1820 1820 1809 4438738
backupset PK__backupse__21F79AAB0E391C95 100217 100200 99826 782 782 779 1489763
backupfilegroup PK__backupfi__760CD67A12FDD1B2 43289 43283 43077 338 338 336 1489616
backupmediafamily PK__backupme__0C13C86F0880433F 39473 39459 39287 308 308 306 2967333
backupmediaset PK__backupme__DAC69E4D04AFB25B 18737 18728 18654 146 146 145 1489616

Analyze and find out the minimum and maximum date range date available

SELECT COUNT(*) AS 'TotalRecords',
MIN(backup_start_date) AS 'MinDate',
MAX(backup_start_date) AS 'MaxDate'
FROM dbo.backupset

Resolution:
The solution is we have to purge these MSDB Backup and Restore history tables

1. The sp_delete_backuphistory system stored procedure takes a single parameter - a cutoff date. Any data older than the supplied date is purged from the msdb tables.

2. The sp_delete_database_backuphistory  system stored procedure allows you to delete historical backup data for a specific database. Unfortunately, this procedure does not offer the finer option of choosing a cutoff date. It's all or nothing.

3. sp_purge_jobhistory Removes the history records for a job.

4. Try considering creating indexes on the above said 5 tables, if no of rows are huge.
Source: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

use msdb
go
--backupset

Create index IX_backupset_backup_set_id on backupset(backup_set_id)
go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
go
Create index IX_backupset_media_set_id on backupset(media_set_id)
go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)
go
Create index IX_backupset_backup_start_date on backupset(backup_start_date)
go
--backupmediaset
Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)
go
--backupfile
Create index IX_backupfile_backup_set_id on backupfile(backup_set_id)
go
--backupmediafamily
Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
go
--restorehistory
Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
go
--restorefile
Create index IX_restorefile_restore_history_id on restorefile(restore_history_id)
go
--restorefilegroup
Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
go

5. Alternatively, check when the statistics of these tables are last updated. Just run sp_updatestats.  Just try sp_delete_backuphistory  after the statistics are updated. The result is awesome! Very quick response.

Permanent Resolution

6. Further restrict the Job history growth by setting in SQL Agent –> History tab.

image

7. Also on the SQL Server Maintenance Plan “History Cleanup Task” can be of helpful

image