SSRS Scripting Tool

 Download Here : 


http://weblogs.asp.net/akjoshi/archive/2009/01/29/using-rs-scripter-to-create-deployment-script-for-reporting-services.aspx

Data Source Updation  can be done using batch file provided by this tool.

Package 'Microsoft SQL Management Studio Package' failed to load

"Reinstalling .Net Framework X.X solved the problem" found at:

"http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/75e2f725-338f-41a7-b66b-5fb4be2d339c"

Posted by Duncan Smart on 04/01/2012 at 14:43

I found that renaming the "HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server" registry key solved the issue for me. More info: http://blog.dotsmart.net/2012/01/04/solution-for-package-microsoft-sql-management-studio-package-failed-to-load/ 

 

 

SQL Server Utility Dashboard

The SQL Server Utility dashboard includes the following information:

■ Utility Summary Found in the center of the top row of the Utility Explorer Content
pane, this section is the first place to look. It displays the number of managed instances
of SQL Server and the number of deployed data-tier applications managed by the SQL
Server Utility. Use the Utility Summary section to gain quick insight into the number of
objects being managed by the SQL Server Utility. In Figure 5-6, there are 14 managed instances
and nine deployed data-tier applications displayed in the Utility Summary section.
NOTE After you have reviewed the summary information, it is recommended that
you analyze either the managed instances or deployed data-tier application section
in its entirety to gain a comprehensive understanding of its overall health status. For
example, the first set of the following bullets interpret the health of managed instances.
After managed instances are analyzed and explained, then the health of data-tier
applications is reviewed from beginning to end.

■ Managed Instance Health This section is located in the top-left corner of the Utility
Explorer Content pane and summarizes the health status of all managed instances
of SQL Server in the SQL Server Utility. Health status is illustrated in a pie chart and has
four possible designations:
● Well Utilized The number of managed instances of SQL Server that are not violating
resource utilization policies is displayed.
● Overutilized A SQL Server instance is marked as overutilized if any of the following
conditions are true:

■ CPU resources for the instance of SQL Server are overutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
overutilized.

■ The instance contains data or log files with overutilized storage space.

■ The instance contains data or log files that reside on volumes with overutilized
storage space.
● Underutilized A SQL Server instance is marked as underutilized if it is not
marked as overutilized and any of the following conditions are true:

■ CPU resources allocated to the instance of SQL Server are underutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
underutilized.

■ The instance contains data or log files with underutilized storage space.

■ The instance contains data or log files that reside on volumes with underutilized
storage space.
92 CHAPTER 5 Consolidation and Monitoring
● No Data Available Either data has not been uploaded from a managed instance
or there is a problem with the collection and upload process.
By viewing the Managed Instance Health section, DBAs are able to quickly obtain an
overview of resource utilization across all managed instances within the utility. The
example in Figure 5-6 shows that five managed instances are well utilized, six are overutilized,
none are underutilized, and data is unavailable for three managed instances in
the Managed Instance Health section.

■ Managed Instances With Overutilized Resources This section is found directly
under the Managed Instance Health section. It displays overutilization data for managed
instances of SQL Server based on the following categories:
● Overutilized Instance CPU This represents the number of managed instances
of SQL Server that are violating instance CPU overutilization policies.
● Overutilized Database Files This represents the number of managed instances
of SQL Server with database files that are violating file space overutilization policies.
● Overutilized Storage Volumes This represents the number of managed instances
of SQL Server with database files on storage volumes that are violating file
space overutilization policies.
● Overutilized Computer CPU This represents the number of managed instances
of SQL Server running on computers that are violating computer CPU overutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.

■ Managed Instances With Underutilized Resources This section is located under
the Managed Instances With Overutilized Resources section and displays underutilization
data for managed instances of SQL Server based on the following categories:
● Underutilized Instance CPU This represents the number of managed instances
of SQL Server that are violating instance CPU underutilization policies.
● Underutilized Database Files This represents the number of managed instances
of SQL Server with database files that are violating volume space underutilization
policies.
● Underutilized Storage Volumes This represents the number of managed
instances of SQL Server with database files on storage volumes that are violating file
space underutilization policies.
● Underutilized Computer CPU This represents the number of managed instances
of SQL Server running on computers that are violating computer CPU underutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.
Using the SQL Server Utility Dashboard CHAPTER 5 93

■ Data-Tier Application Health This section is located in the top-right corner of the
Utility Explorer Content pane. Health status is illustrated in a pie chart and has four
possible designations:
● Well Utilized The number of deployed data-tier applications that are not violating
resource utilization policies is displayed.
● Overutilized The number of deployed data-tier applications that are violating
resource overutilization policies is displayed. A deployed data-tier application is
marked as overutilized if any of the following conditions are true:

■ CPU resources for the deployed data-tier application are overutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
overutilized.

■ Storage volumes associated with the deployed data-tier application are
overutilized.

■ The deployed data-tier application contains data or log files that reside on volumes
with overutilized storage space.
● Underutilized The number of deployed data-tier applications that are violating
resource underutilization policies is displayed. A deployed data-tier application is
marked as underutilized if any of the following conditions are true:

■ CPU resources for the deployed data-tier application are underutilized.

■ CPU resources of the computer that hosts the SQL Server instance are
underutilized.

■ Storage volumes associated with the deployed data-tier application are
underutilized.

■ The deployed data-tier application contains data or log files that reside on
volumes with underutilized storage space.
● No Data Available Either data affiliated with deployed data-tier applications has
not been uploaded to the Utility Control Point or there is a problem with the collection
and upload process.
By viewing the Data-Tier Application Health section, DBAs can quickly obtain a holistic
view of resource utilization for all deployed data-tier applications managed by the SQL
Server Utility. In Figure 5-6, there are seven well-utilized and two overutilized data-tier
applications.

■ Data-Tier Applications With Overutilized Resources This section is found
directly under the Data-Tier Application Health section. It displays overutilization data
for deployed data-tier applications based on the following categories:
● Overutilized Data-Tier Application CPU This represents the number of
deployed data-tier applications that are violating data-tier application CPU
overutilization policies.
94 CHAPTER 5 Consolidation and Monitoring
● Overutilized Database Files This represents the number of deployed data-tier
applications with database files that are violating file space overutilization policies.
● Overutilized Storage Volumes This represents the number of deployed datatier
applications with database files on storage volumes that are violating file space
overutilization policies.
● Overutilized Computer CPU This represents the number of deployed data-tier
applications running on computers that are violating computer CPU overutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.

■ Data-Tier Applications With Underutilized Resources This section is located
directly under the Data-Tier Applications With Overutilized Resources section. This
section displays underutilization data of individual instances based on the following
categories:
● Underutilized Data-Tier Application CPU This represents the number of
deployed data-tier applications that are violating data-tier application CPU underutilization
policies.
● Underutilized Database Files This represents the number of deployed data-tier
applications with database files that are violating file space underutilization policies.
● Underutilized Storage Volumes This represents the number of deployed datatier
applications with database files on storage volumes that are violating file space
underutilization policies.
● Underutilized Computer CPU This represents the number of deployed datatier
applications running on computers that are violating computer CPU underutilization
policies.
Detailed status for each health parameter is listed in a sliding indicator to the right of
each element in this section.

■ Utility Storage Utilization History Located at the bottom-left corner of the Utility
Explorer Content pane, this section uses a time graph to display the storage utilization
history for the amount of storage the SQL Server Utility is consuming in gigabytes.
By using the buttons under the Interval heading , you can view data in the graph by
the following intervals:
● 1 Day Displays data in 15-minute intervals
● 1 Week Displays data in one-day intervals
● 1 Month Displays data in one-week intervals
● 1 Year Displays data in one-month intervals

■ Utility Storage Utilization The bottom-right corner shows a pie chart that displays
the amount of space used and the amount of free space available on the volume hosting
the SQL Server Utility. It is worth noting that the data is refreshed every 15 minutes.
Using the Managed Instances Viewpoint CHAPTER 5 95
This section explained how to obtain summary information for all managed instances of
SQL Server. DBAs seeking more information might be interested in the Managed Instances
node in the tree view of Utility Explorer. This node helps database administers gain deeper
knowledge of health status and resource utilization data for each managed instances of SQL
Server.

Enhancements to High Availability with Windows Server 2008 R2

■ Hot add CPU and memory When using SQL Server 2008 R2 in conjunction
with Windows Server 2008 R2, database administrators can upgrade hardware
online by dynamically adding processors and memory to a system that supports
dynamic hardware partitioning. This is a very convenient feature for organizations
that cannot endure downtime for SQL Server systems running in mission-critical
environments.

■ Failover clustering Greater high availability is achievable for SQL Server R2 with
failover clustering on Windows Server 2008 R2. Windows Server 2008 R2 enhances
the failover cluster installation experience by increasing the number of validation
tests within the Cluster Validation Wizard. Moreover, Windows Server 2008 R2
introduces a Best Practices Analyzer tool to help database administrators reduce best
practice violations. Similar to its predecessor, Windows Server 2008 R2 continues to
supports up to 16 nodes within a failover cluster and organizations can also protect
their applications from site failures with SQL Server multi-site failover cluster support
by using stretched VLANs built on Windows Server support for multi-site clusters.
64 CHAPTER 4 High Availability and Virtualization Enhancements

■ Windows Server 2008 R2 Hyper-V The Hyper-V virtualization technology improvements
in Windows Server 2008 R2 were the most sought-after and anticipated
enhancements for Windows Server 2008 R2. It is now possible to virtualize heavy SQL
Server workloads because Windows Server 2008 R2 scales far beyond its predecessors.
In addition, database administrators can achieve increased virtualization availability by
leveraging new technologies, such as Clustered Shared Volumes (CSV) and Live Migration,
both of which are included in Windows Server 2008 R2. Guest clustering with SQL
Server 2008 R2 in Windows Server 2008 R2 Hyper-V is also supported.

■ Live Migration and Hyper-V By leveraging Live Migration and CSV—two new
technologies included with Hyper-V and failover clustering on Windows Server 2008
R2—it is possible to move virtual machines between Hyper-V hosts within a failover
cluster without downtime. It is worth noting that CSV and Live Migration are independent
technologies; CSV is not required for Live Migration.

■ Cluster Shared Volumes (CSV) CSV enables multiple Windows servers running
Hyper-V to access Storage Area Network (SAN) storage using a single consistent
namespace for all volumes on all hosts. This provides the foundation for Live Migration
and allows for the movement of virtual machines between Hyper-V hosts.

■ Dynamic virtual machine (VM) storage It is possible to add or remove virtual
hard disk (VHD) files and pass-through disks while a VM is running. Support for hot
plugging and hot removal of storage is based on Hyper-V. This is very handy when you
are working with dynamic SQL Server 2008 R2 storage workloads, which are continuously
evolving.

■ Second Level Address Translation (SLAT) Enhanced processor support and
memory management can be achieved with SLAT, which is a new feature supported
with Hyper-V in Windows Server 2008 R2. SLAT leverages Intel Virtualization Technology
(VT) Extended Page Tables (EPT) and AMD-V Rapid Virtualization Indexing (RVI)
technology in an effort to reduce the overhead incurred during mapping of a guest
virtual address to a physical address for virtual machines. This significantly reduces
hypervisor CPU time and saves memory for each VM, allowing the physical computer
to do more work while utilizing fewer system resources.

LogFile Size Location

SELECT  name  AS FileName, Filename as Location,    (size * 8) / 1024  AS AllocatedMb
  ,   ( (CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int) * 8 ) / 1024)  AS SpaceUsedMb
  from sys.sysaltfiles where fileid=2 or filename like '%log'
 
  go
  exec xp_fixeddrives
  go
 

SSRS, Report Past Date TIME Executed

Use below query to get Last Execution Date and TIme of the Report.

=================================================================

use ReportServer
go

Set transaction isolation level read uncommitted
select        x.itemID,
        x.name,
        x.path,
        x.lastRunDate,
        x.avgTimeDataRetrieval,
        x.avgTimeProcessing,
        x.avgTimeRendering,
        x.avgTimeDataRetrieval + x.avgTimeProcessing + x.avgTimeRendering as avgTimeTotal,
        x.avgRowCount,
        lastStat.status,
        x.rsSuccessY,
        x.rsSuccessN,
        (x.rsSuccessY*1.0) / (x.totalEntries*1.0) as rsSuccessYpct,
                                x.lastNonSuccess,
        x.totalEntries
from        (           

        select        c.itemID,
                c.name,
                c.path,
                max(e.timeStart) as lastRunDate,
                avg(e.timeDataRetrieval) as avgTimeDataRetrieval,
                avg(e.timeProcessing) as avgTimeProcessing,
                avg(e.timeRendering) as avgTimeRendering,
                avg(e.[rowCount]) as avgRowCount,
                sum(case when e.status = 'rsSuccess' then 1 else 0 end) as rsSuccessY,
                sum(case when e.status = 'rsSuccess' then 0 else 1 end) as rsSuccessN,
                                                               (select max(timeStart) from executionLog where reportID = c.itemID and status != 'rsSuccess' and timeStart >=                                                                            dateAdd(mm,-6,getDate())) as lastNonSuccess,
                count(c.itemID) as totalEntries
        from        executionLog e
                inner join catalog c on e.reportID = c.itemID
        where        e.timeStart >= dateAdd(mm,-6,getDate())
        group by                    c.itemID,
                c.name,
                c.path
        ) x
        left join (       

                                select        reportID,
                max(timeStart) lastRunDate,
                [status]
        from        executionLog
        group by                    reportID, [status]           
        ) lastStat on x.itemID = lastStat.reportID and x.lastRunDate = lastStat.lastRunDate
       
       =================================================================

MSDB Restored, Version of SQL is same, But failes to run Maintenace plan

Please set the sys subsytem values to proper values.

Example :-

use msdb
go
select * from msdb.dbo.syssubsystems


update syssubsystems set subsystem_dll ='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\binn\SQLCMDSS.DLL'
where subsystem='CmdExec'


Restart SQL Agent once Done.

Table with values you can find here :- http://support.microsoft.com/?kbid=903205

Map the dll with your DLL file location, It must work

RESTORE requires MAXTRANSFERSIZE

MAXTRANSFERSIZE

Specifies the maximum size of each block of memory to be used when SQL Backup stores backup data. You may want to specify this argument if a SQL Server reports that it has insufficient memory to service requests from SQL Backup.

Valid values are integers in multiples of 65536, up to a maximum value of 1048576.

For example:

MAXTRANSFERSIZE = 262144

If not specified, defaults to 1048576. However, if you have created the following DWORD registry key, SQL Backup uses the defined value as the default value:

HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\\MAXTRANSFERSIZE (32-bit)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\\MAXTRANSFERSIZE (64-bit)

Data Compression

Data compression can be configured for the following database objects:

• A whole table that is stored as a heap.
  
• A whole table that is stored as a clustered index.
  
• A whole nonclustered index.
  
• A whole indexed view.
  
• For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.
  

More On :- More On :-