Tail Log Backup

Tail log backup is taken in below ways:

If the database is online follow below syntax:

BACKUP LOG [database name] TO [backup device] WITH NORECOVERY

If the database is offline (example a corrupted database which does not start]

BACKUP LOG [database name] TO [backup device]  WITH CONTINUE_AFTER_ERROR

SQL 2012 - The remote server does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.

Step 1: Check the Log Reader agent account
Step 2: Agent account must be owner, If not make him DB_OWNER
Step 3: This will resolve in the next synchronization

OR

Step 1:  Check SQL Agent is Local System
Step 2:  Log Reader agent running under Impersonate Account
Step 3:  Change the SQL Agent to Some other account which has access
Step 4:  The Step3 will restart Agent, Once Its UP in next synchronization data will be in sync.

Database Replication - Backup and Restore

It is important for you to regularly back up your replication databases and test to

make sure you can restore those backups. You need to regularly back up the following

replication databases: the publication database, the distribution database,

subscription databases, and the msdb and master databases at the Publisher, Distributor,

and all Subscribers. If you perform regular log backups, any replicationrelated

changes should be captured in the log backups. If you do not perform log

backups, make sure to perform a backup whenever you change a replicationrelated

setting.


You can restore replicated databases to the same server and database on which

you created the backup. If you want to restore a backup of a replicated database

to another server or database, note that replication settings will not be preserved.

In this case, you must re-create all publications and subscriptions after

you restore the backups.

Database Replication - Performance Improvement

You can use SSRM and System Monitor to determine baseline values for the following

key factors in replication performance:



■ Latency The amount of time it takes for a data change to be propagated

between nodes in a replication topology.



■ Throughput The amount of replication activity (measured in commands

delivered over a period of time) a system can sustain over time.



■ Concurrency The number of replication processes that can operate on a system

simultaneously.



■ Duration of synchronization How long it takes a given synchronization to

complete.



■ Resource consumption Hardware and network resources used in replication

processing.

Database Replication - Conflicts

CONFLICT TYPES




While the majority of conflicts you experience will be restricted to a particular update at one replica conflicting with a disparate update at another replica, or possibly with an update at one replica conflicting with a delete at another replica, this is not necessarily the case. While these are the most common conflict types, they are not the only ones.



Each type of conflict discussed in this section can occur during the upload phase or the download phase of merge processing. Upload processing is the first reconciliation of changes performed in a particular merge session, and is the phase during which the Merge Agent replicates changes from the subscriber up to the publisher. Conflicts detected during this processing are referred to as upload conflicts. Download processing involves moving changes from the publisher to the subscriber, and occurs after download processing. Conflicts during this phase of processing are referred to as download conflicts.





Update-Update Conflicts



These types of conflicts are probably the most common. In this case, an update to a row (or column) at one replica conflicts with another update to the same row somewhere else in the topology. The behavior of the default resolver in this case is to send the winning version of the row to the losing replica and log the losing row version in the article's conflict table.



Update-Delete Conflicts



The Merge Agent detects update-delete conflicts when an update of data at one server conflicts with a row deletion at another server. In this case, the Merge Agent updates a row; however, when the Merge Agent searches for that row at the destination, it cannot find that row because the row has been deleted. If the winner is the replica that updated the row, then the DELETE at the losing replica is discarded. Therefore, the Merge Agent sends the newly updated row to the conflict loser. The Merge Agent logs the losing version of the row, in this case, to the MSmerge_delete_conflicts table.



Failed Change Conflicts



These kinds of conflicts are not immediately recognizable as conventional conflicts because there is no actual change to conflict with the source change at the destination. The Merge Agent raises these conflicts when it cannot apply a particular change (INSERT, UPDATE, or DELETE) at the destination replica. This typically occurs because of constraint definitions between the publisher and subscriber, and the use of the NOT FOR REPLICATION (NFR) property on the constraint. Here are some examples of failed change conflict situations:





•A foreign key conflict at the subscriber can occur when the subscriber-side constraint is not marked as NOT FOR REPLICATION. The merge reconciler's behavior in this case is documented in the following Microsoft Knowledge Base article:

307482

(http://support.microsoft.com/kb/307482/EN-US/ )

PRB: Foreign Key Conflicts at Merge Subscriber Result in DELETE During Next Upload



•Conflicts can occur when the publication's join filtering logic does not match its referential integrity definitions, the primary key (PK) or foreign key (FK) constraints. This can occur when the SQL Server relational engine tries to honor a certain constraint (as defined by the PK-FK relationship between the tables), but the Merge Agent is honoring the join filter definition between the articles. The Merge Agent cannot apply the change at the destination replica because of the table-level constraints, which results in a conflict.



•A lookup table that serves as the primary key data for a PK-FK relationship is not published. Generally, from a performance perspective, you ought not publish lookup tables in a merge publication if their data does not frequently change. This prevents the Merge Agent from having to consider those tables in the change-enumeration phase of processing. However, the Merge Agent does detect conflicts if changes to the parent table are not replicated to the subscriber; however, replicated changes to the child table are dependent on those parent-table changes.



•If constraints are not marked as NOT FOR REPLICATION, and are not defined the same at the publisher and the subscriber, the Merge Agent can detect conflicts during the application of changes. Because of the mismatched constraint definitions, the Merge Agent may not be able to apply the destination change. If you have defined constraints at your subscribers, and the Merge Agent is going to evaluate the defined constraints, make sure that they are the same as the constraints defined at the publisher.



•Conflicts because of unique index or unique constraint violations or primary key violations can occur if IDENTITY columns are defined for the article, and automated identity management is not used. This can be a problem if two subscribers were to use the same identity value for a newly inserted row.



•Program logic can also result in failed change conflicts if trigger logic prevents the Merge Agent from inserting a row from in the destination table. For example, a "trgUpdate" trigger is defined at the subscriber (and not marked as NFR) that performs some business logic on the incoming update. If a failure occurs, the trigger's business logic issues a ROLLBACK of the transaction, which results in the Merge Agent detecting a failed change conflict.



For all types of conflicts, the conflict_type is defined as one of ten (10) possible values. These conflict types map directly to the conflict_type in the conflict table:



Description                           conflict_type


----------------------------              -------------



Update Conflict                             1

Column Update Conflict                2

Update Delete Wins Conflict          3

Update Wins Delete Conflict          4

Upload Insert Failed                       5

Download Insert Failed                   6

Upload Delete Failed                       7

Download Delete Failed                   8

Upload Update Failed                       9

Download Update Failed                   10



In the case of UPDATE-UPDATE and UPDATE-DELETE conflicts, the reason_code used in the conflict table is the same as the conflict_type. However, for failed change conflicts, the reason code is the actual server-side error that prevented the Merge Agent from being able to apply the change. For example, if the Merge Agent cannot apply a publisher-initiated INSERT at the subscriber because of a primary key violation, it logs a conflict_type of 6 ("download insert failed") and a reason_code of 2627, which is the SQL Server internal error message for a primary key violation:



Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.

Database Replication - Agents

■ Snapshot Agent (snapshot.exe) This agent prepares the schemas and the initial


copy of the data files. All replication types use the Snapshot Agent as the starting

point for the synchronization process. Generally, the Snapshot Agent is run on a

regular basis to keep the data files updated. These files are required if the replication

synchronization process finds anomalies in the data that cannot be fixed.

The Snapshot Agent runs in the Distributor server.



■ Log Reader Agent (logread.exe) This agent monitors the database’s transaction

log and copies each transaction that affects the publication to the distribution

database, in which the transactions are stored until applied to the Subscribers. It

is important to note that multiple publications of the same database share the

same Log Reader Agent. Log Reader Agents are used only in transactional replication

configurations and run in the Publisher server.



■ Distribution Agent (distrib.exe) This agent performs two tasks: it delivers the initial

snapshot to Subscribers and applies transactions stored in the distribution

database to Subscribers. The Distribution Agent is used in snapshot and transactional

replication. The Distribution Agent runs in the Distributor when you

configure the publication as a push subscription and in the Subscriber when you

configure the publication as a pull subscription.





■ Merge Agent (replmerg.exe) This agent delivers the initial snapshot from the Distributor

to the Subscribers. It also merges data changes that occur in the Publisher

to the Subscribers, and vice versa. When two servers modify the same information

at the same time, a conflict occurs; the Merge Agent reconciles the conflict by using

a set of rules that you define during replication configuration. The Merge Agent

runs in the Distributor when you configure the publication as a push subscription

and in the Subscriber when you configure the publication as a pull subscription.



■ Queue Reader Agent (replmerg.exe) This agent reads messages stored in queues


(SQL Server queues or Microsoft Message Queues) and applies transactions sent

to the queue to the Publisher database. The Queue Reader Agent is used only

when snapshot or transactional replication is set with the option for queued

updating subscriptions.

Database Mirroring - Endpoints

Endpoints that support database mirroring are a special implementation of a TCP endpoint and have the following characteristics:



■ Endpoint type of TCP

■ Payload of DATABASE_MIRRORING

■ Only one endpoint supporting database mirroring allowed per SQL Serverinstance



Database mirroring endpoints establish a seventh layer of security through the use of

the ROLE option. You can specify that an endpoint be a PARTNER, WITNESS, or ALL.

An endpoint specified as PARTNER can participate only as the principal or the mirror.

An endpoint specified as WITNESS can participate only as a witness. An endpoint

specified as ALL can function in any role.

Database Mirroring - Operating Modes

Database Mirroring Operating Modes




We can configure database mirroring for three different operating modes:

High-Safety Mode Or High Availability (synchronous + Automatic Fail Over + Witness Server)

High-Safety Mode OR High Protection (synchronous + Manual Fail Over)

And

High Performance (Asynchronous)


The operating mode governs the way transactions are transferred between the principal and the mirror databases as well as

the failover processes that are available in the database mirroring session.





High Availability Operating Mode



Database mirroring’s High Availability operating mode provides durable, synchronous

transfer between the principal and mirror databases in addition to automatic

failure detection and failover.



SQL Server first writes all transactions into memory buffers within the SQL Server memory

space. The system writes out these memory buffers to the transaction log and then

flushes the log to the data files.



When SQL Server writes the transaction to the transaction log, the system triggers database mirroring to begin transferring the transaction log rows to the mirror. The transaction rows continue to flow to the mirror. When the application issues a commit for the transaction, the transaction is first committed on the mirror database.



An acknowledgement of the commit is sent back to the principal, which then allows the commit to be issued. After the commit is issued on the principal, the acknowledgment is sent back to the application, allowing it to continue processing.



This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.



High Availability operating mode requires a witness server along with the principal and mirror databases for database mirroring to automatically detect a failure at the principal and fail over to the mirror.



A quorum is necessary to prevent the database from being served on more than one instance within the database mirroring session.

If the principal were to fail, and the mirror could not connect to the witness, it would be impossible to reach a quorum, and SQL Server would then not promote the mirror to the principal.





High Performance Operating Mode



Database mirroring’s High Performance operating mode uses a principal and a mirror

database but does not need a witness server. This operating mode provides a warm

standby configuration that does not support automatic failure detection or failover.



High Performance operating mode does not automatically fail over because the application’s

transactions are sent to the mirror asynchronously. Transactions are committed

to the principal database and acknowledged to the application.



A separate process constantly sends those transactions to the mirror, which introduces latency into the

process. This latency prevents a database mirroring session from automatically failing

over because the process cannot guarantee that SQL Server has received all transactions

at the mirror when a failure occurs.





High Protection Operating Mode



Database mirroring’s High Protection operating mode is the same as High Availability

operating mode, except that you do not configure a witness server.



SQL Server transfers transactions synchronously between principal and mirror, but because a two-outof-three quorum cannot be achieved without a witness, failover is manual.



If the principal fails in High Protection operating mode, you must manually promote the mirror to serve the database.

An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.

Work Around:-




Go to the Binaries Location,

>> Go to D:\SQL Server\SQL-2005\Servers\Setup
>> Search sqlncli.msi
>> Double click choose Repair
>> Once Done Try to install, It will succed.


*This might work, only when you installed and uninstalled couple of time with different sql servers editions

Solution:-

If it failed to work as mentioned above

Please visit http://support.microsoft.com/kb/929667
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/16fd90cf-0dbc-4012-9970-6ca29b6f4aef

Upgrading to SQL Server 2012

http://blogs.extremeexperts.com/2012/05/17/upgrading-to-sql-server-2012/
By Vinod Kumar

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

DBCC ERROR :-The page may be invalid or may have an incorrect alloc unit ID in its header

BELOW IS THE DBCC ERR:-

 

Table error: page (1:735428) allocated to object ID 2025058250, index ID 0, partition ID 132714217473000, alloc unit ID 132724217472000 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

There are 75408262 rows in 744739 pages for object "tblancellationHistory".

CHECKDB found 0 allocation errors and 1 consistency errors in table 'tblCancellationHistory' (object ID 2025058250).

 TO KNOW THE OBJECT, ANYWAY ITS ALREADY MENTIONED IN THE ERROR MSG :

USE

go

SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name,
au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id
WHERE au.allocation_unit_id = (allocation_unit_id) -- in above case 132724217472000
ORDER BY au.allocation_unit_id

Once you get the Object name try the following ,

DBCC CHECKTABLE(TABLENAME, REPAIR_REBUILD)

The above dont have any impact on data, so there is no data loss.

If that dint solve the purpose, we have to opt for DATA LOSS

DBCC CHECKTABLE(TABLENAME, REPAIR_ALLOW_DATA_LOSS)

Make sure we have enough permission to run the above.

 If it dint solve we need to contact microsoft, hopefully it will resolve.

A program running on this computer is trying to display a message.

To disable Interactive Services Detection

 

1. Click Start > Control Panel and then double-click Administrative Tools.
2. Double-click Services.
3. Scroll down and double-click Interactive Services Detection.
4. On the General tab, change the Startup type to Manual or Disabled.
5. Click OK and restart the computer.

Enrolling the instance. Step failed. An exception occurred while executing a Transact-SQL statement or batch.

Failed Operation to Enroll an Instance of SQL Server into a SQL Server Utility

---

If you connect to the instance of SQL Server to enroll using SQL Server Authentication, and you specify a proxy account that belongs to a different Active Directory domain than the domain where the UCP is located, instance validation succeeds, but the enrollment operation fails with the following error message:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information: Could not obtain information about Windows NT group/user '', error code 0x5. (Microsoft SQL Server, Error: 15404)
This issue occurs in the following example scenario:

1. The UCP is a member of "Domain_1."
   
2. A one-way domain trust relationship is in place: that is, "Domain_1" is not trusted by "Domain_2" but "Domain_2" is trusted by "Domain_1."
   
3. The instance of SQL Server to enroll into the SQL Server Utility is also a member of "Domain_1."
   
4. During the enroll operation, connect to the instance of SQL Server to enroll using “sa”. Specify a proxy account from "Domain_2."
   
5. Validation succeeds but enrollment fails.
   

The workaround for this issue, using the example above, is to connect to the instance of SQL Server to enroll into the SQL Server Utility using “sa” and provide a proxy account from "Domain_1."

1. Connect utility using SA
2. For enrolling use Domain 1 usr and pwd



More On  :- http://msdn.microsoft.com/en-us/library/ee210592.aspx

Error 952. Database 'YYYY' is in transition. Try the statement later.

This can happen sometimes if you try to take a DB offline or perform certain other operations and they fail.

Sometimes the lock can be cleared if you close the SSMS instance that attempted the operation, then reopen it. Close and reopen any SSMS instances attached to the server.

It can also occur if you try to take the DB offline while a long query is running. Check the activity monitor and try killing any long-running queries, if applicable and safe.


If neither of the above works, close all SSMS instances, then restart SQL through the SQL Server Configuration Manager. Usually that will cure it, although the DB may be in recovery mode at first.

Check the database is offline or what, check the corresponding MDF and LDF files it may be missing too.
In that case need to restore the DB from Latest Backup.

ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE

...

ALTER DATABASE SET ONLINE

Backup detected log corruption in database Context is Bad Middle Sector.

Follow the following steps,


1. Stop all user activity in the database
2. Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up)
3. Switch to the FULL recovery model
4. Take a full database backup (thus starting a new log backup chain)
5. Start taking log backups

http://www.sqlskills.com/BLOGS/PAUL/post/How-can-a-log-backup-fail-but-a-full-backup-succeed.aspx

SQL Server® 2008 R2 Service Pack

http://www.microsoft.com/download/en/details.aspx?id=26727

latest SQL Server 2000 service pack

http://support.microsoft.com/kb/290211

latest service pack for SQL Server 2005

http://support.microsoft.com/kb/913089

SQL Server 2008 SP2

•SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550



•SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551


•SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815


•SQL Server 2008 SP1 CU’s Released: http://support.microsoft.com/kb/970365


•Knowledge Base Article For Microsoft SQL Server 2008 SP2: http://support.microsoft.com/kb/2285068

Windows 7 and Windows Server 2008 R2 Service Pack

http://www.microsoft.com/download/en/details.aspx?id=5842

New T-SQL Features in SQL Server 2012

SQL Server 2012 has several major enhancements including a new look for SSMS. SSMS is now similar to Visual Studio with greatly improved Intellisense support. The main new TSQL features in SQL Server 2012 are:



WITH RESULT SETS


OFFSET AND FETCH


THROW in Error handling


SEQUENCE

2012 - Licesensing

DB RECOVERY - PAGE LEVEL

STEP 1 - Check Database For corruption

We can check database integrity by using DBCC CHECKDB command, to see weather there is corruption in database of not.







Looking at error message, we can clearly identify that there is corruption on page 223 as we can see this message. Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data): Page (1:223) could not be processed.



STEP 2 – Restore faulty page from a GOOD Backup – PAGE Level Database Restore



Now we need to restore faulty pages from a SQL Server backup, that means restore only faulty pages. This is a new feature in SQL Server 2008, where we can restore only some corrupted pages from a good database backup.



For example you have a 100 Gb database and only 1 page is corrupted than we can save recovery time by restoring a single page instead of a 100 GB database.



SQL Command to perform a page level restore



use master

go

RESTORE DATABASE DBA PAGE = '1:223' FROM DISK = 'C:\temp\DBA_before_curruption.bak';

goSTEP 3 – Backup and Restore Current TRANSACTION LOG Backup



If you read the restore informational messages, which we received in last step states that there is difference between the LSN number.



Processed 1 pages for database ‘DBA’, file ‘DBA’ on file 1.



The roll forward start point is now at log sequence number (LSN) 43000000055600001. Additional roll forward past LSN 43000000058400001 is required to complete the restore sequence.



RESTORE DATABASE … FILE= successfully processed 1 pages in 0.098 seconds (0.079 MB/sec).



To correct this LSN number, we need to backup the current log and restore in a current database, using the following syntax.



use DBA

BACKUP LOG DBA TO DISK = 'C:\DBA_log.bak' WITH INIT;

GO



use master

GO

RESTORE LOG DBA FROM DISK = 'C:\DBA_log.bak';This is going to be pretty quick as only page level transactions will be rolled back or rolled forward, you can see that in message where backup log size was in MB’s but restore was kind of ZERO only.



Processed 5 pages for database ‘DBA’, file ‘DBA_log’ on file 1.



BACKUP LOG successfully processed 5 pages in 0.020 seconds (1.684 MB/sec).



Processed 0 pages for database ‘DBA’, file ‘DBA’ on file 1.



RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).











STEP 4 – Verify corruption has been resolved and data is consistent



Re-execute DBCC CHECKDB to ensure and verify that corruption has been removed and database is health now.



OPTION 2 – The corruption example, which I took was of Index and I want to make you understand how page level restore works, if you are looking for a solution to a exact problem, which I demonstrated, can be resolved by rebuilding a non clustered index, as can afford to rebuild index which doesn’t;t result any data loss.

 

DB RECOVERY - PAGE LEVEL

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

http://msdn.microsoft.com/en-us/library/ff487856.aspx

http://www.cjvandyk.com/blog/Articles/How%20do%20I%20-%20Install%20PowerPivot%20into%20an%20EXISTING%20SharePoint%202010%20farm.aspx


NOTE :- This feature is not available on SQL Server Standard Edition.

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7609


How to install and configure please refer,

http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint

Usefull Queiries

 Following quick script demonstrates last ran queries along with the time it was executed.




SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC



-------------------------------------------------------------------------------------------------------------
 Gives the total number of tables available for a given schema.



SELECT count(*) TABLES, table_schema

FROM information_schema.TABLES

WHERE table_schema= 'dbo' and TABLE_TYPE ='BASE TABLE'

GROUP BY table_schema





Information_schema can used for getting many other information like column details, domain, privileges and many more



 Want to find a function or procedure you can use this query



SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'






 Tables that do not have clustured indexes

SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name

FROM sys.tables AS t

WHERE NOT EXISTS

(

SELECT * FROM sys.indexes AS i

WHERE i.object_id = t.object_id

AND type_desc = 'CLUSTERED'

)

ORDER BY schema_name, table_name;



 Find dependies of specified function



SELECT OBJECT_NAME(object_id) AS referencing_object_name

,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name,*

FROM sys.sql_dependencies

WHERE referenced_major_id = OBJECT_ID('o2_AccountUsers')

ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);

SSRS Maximum request length exceeded

Error : Maximum request length exceeded.

Change the values as mentioned below,

1. C:\Program Files\Microsoft SQL Server\MSRS__.SQL_\Reporting Services\ReportServer
2. Open web.config file 
3. Search
4. Change

Note :- This is max limit.


5. iisreset

The Windows Server 2008 failover cluster cannot start because of a deadlock condition in the Cluster service

RESOLUTION


Service pack information

To resolve this problem, obtain the latest service pack for Windows Server 2008 Service Pack 2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

968849 How to obtain the latest service pack for Windows Server 2008

Back to the top

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing the problem described in this article. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.



If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.



Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.



Important Windows Vista and Windows Server 2008 hotfixes are included in the same packages. However, only one of these products may be listed on the “Hotfix Request” page. To request the hotfix package that applies to both Windows Vista and Windows Server 2008, just select the product that is listed on the page.

Prerequisites

To apply this hotfix, you must have Windows Server 2008 installed.

Restart requirement

You must restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace a previously released hotfix.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Windows Vista and Windows Server 2008 file information notes

The .manifest files and the .mum files that are installed in each environment are listed separately in the "Additional file information for Windows Server 2008" section. These files and their associated .cat (security catalog) files are critical to maintaining the state of the updated component. The .cat files are signed with a Microsoft digital signature. The attributes of these security files are not listed.

For all supported 32-bit versions of Windows Server 2008

File name File version File size Date Time Platform

Srvnet.sys 6.0.6001.22178 98,816 14-May-2008 02:47 x86

For all supported 64-bit versions of Windows Server 2008

File name File version File size Date Time Platform

Srvnet.sys 6.0.6001.22178 141,312 14-May-2008 03:15 x64

For all supported Itanium-based versions of Windows Server 2008

File name File version File size Date Time Platform

Srvnet.sys 6.0.6001.22178 286,208 14-May-2008 02:57 IA-64

Quorum Resource Problems

Distinguishing Quorum Problems from Other Problems




It can be challenging to analyze a problem with starting the Cluster service and determine whether it is related in some way to the quorum resource. To try to distinguish different types of problems, review the following table:



Area Explanation



Cluster service account: On the nodes on which you are trying to start the Cluster service, are there problems with the Cluster service account?



Also, if you have Windows Server 2003 with the latest service pack, does Event ID 7041 appear in the system event log? This event was added in Windows Server 2003 Service Pack 1 and indicates that the Cluster service account does not have all the necessary user rights, such as the Log on as a service user right.





If no domain controller is available to authenticate the Cluster service account, the Cluster service cannot start. Other problems with the account can also prevent the Cluster service from starting, for example, if the password was allowed to expire or the account does not have the necessary rights, possibly as a result of a Group Policy setting.



To check for this type of problem, try to log on to the computer with the Cluster service account, or check the system event log for messages that indicate that the Cluster service account cannot log on. Also, review the Cluster service account to make sure that it has the permissions and rights described in Change the Account Under Which the Cluster Service Runs at the Microsoft Web site.



Cluster log: Is the cluster log read-only? Alternatively, is a policy preventing the cluster log from being modified?





If the cluster log cannot be modified, the Cluster service cannot start.



By default, the cluster log is called Cluster.log and is located in systemroot\Cluster. The default name of this log can be changed by changing the system environment variable called ClusterLog.



Events in event log: Do event log messages appear to be consistent with a quorum resource problem?





Specific events can indicate that a problem with the quorum is preventing cluster startup. However, an event can indicate a symptom rather than a root cause, so all events should be interpreted in context.



See the next section for information about some events that can indicate problems with the quorum resource.



For more information about troubleshooting problems with starting the Cluster service, see article 266274, "How to Troubleshoot Cluster Service Startup Issues" in the Microsoft Knowledge Base.



If your startup issue appears to be related in some way to the quorum resource, see Flowchart for Troubleshooting the Quorum Resource.

Event Messages That are Consistent with Quorum Resource Problems



The following list describes some common event log messages that are consistent with quorum resource problems.

ImportantImportant

If your event log contains any of the messages in the following list, be sure to check that the cables for the storage are not damaged or disconnected. Also, follow the other recommendations in Verifying Permissions, Hardware, and Software Before Troubleshooting the Quorum Resource.



Event Messages That Are Consistent with Quorum Resource Problems



1034: The disk associated with cluster disk resource DriveLetter could not be found. The expected signature of the disk was DiskSignature.



This error can result when the disk signature of the quorum disk has been inadvertently changed. This can happen when you make changes through a disk or storage utility, or when you recreate the LUN containing the quorum resource. Note that the cluster identifies the quorum resource and other disk resources by disk signature, not just drive letter.



Follow the flowchart in this topic, and see if The Quorum Resource is on an Inaccessible or Nonfunctioning Disk applies to your situation. Consider whether Clusterrecovery.exe, which is described in Configuring a Computer for Troubleshooting the Quorum Resource in a Server Cluster, would be useful for your situation.



1035: Cluster disk resource DriveLetter could not be mounted.



Follow the flowchart in this topic, and see if The Quorum Resource is on an Inaccessible or Nonfunctioning Disk applies to your situation.



1066: Cluster disk resource DriveLetter is corrupt. Run ChkDsk /F to repair problems.



This error might indicate that the volume used for the quorum resource has a file system corruption problem, possibly a transient problem. Other events in the logs might help with diagnosis.



Follow the flowchart in this topic, and see if The Quorum Resource is on an Inaccessible or Nonfunctioning Disk applies to your situation.



1069: Cluster disk resource DriveLetter failed.



This error might indicate that the disk used for the quorum resource has a problem. Other events in the logs might help with diagnosis.



Follow the flowchart in this topic, and see if The Quorum Resource is on an Inaccessible or Nonfunctioning Disk applies to your situation.



1147 or 1148:



The Microsoft Clustering Service encountered a fatal error. The vital quorum log file 'q:\MSCS\quolog.log' could not be found. (Additional text is provided in this event.)



If you have Windows Server 2003 with the latest service pack, you might see this event and yet find that the Cluster service started. When you apply the latest service pack, if the quorum log and cluster configuration files are missing from the quorum resource, the operating system can replace these files with information from a node.



Follow the flowchart in this topic, and see if Files on the Cluster Quorum Might be Missing, Inaccessible, or Corrupt applies to your situation.



If you want to find out more details about a specific event that relates to the cluster, one source of information is the cluster log. The cluster log may be easiest to use when you already have a general sense of a problem and are looking for details. By default, the cluster log is called Cluster.log and is located in systemroot\Cluster. The log might have a different name, because the default name can be changed by changing the system environment variable called ClusterLog.

Flowchart for Troubleshooting the Quorum Resource



Use the following flowchart and the topics to which it refers to troubleshoot quorum resource problems. For information about the /fixquorum option mentioned in the flowchart, see Understanding the Fixquorum Option for Starting the Cluster Service, later in this topic.

Flow chart for troubleshooting the quorum

Understanding the Fixquorum Option for Starting the Cluster Service



The troubleshooting techniques shown in the flowchart in this topic use the /fixquorum option for starting the Cluster service. It is useful to understand the following details about the /fixquorum option:



When the /fixquorum option is used on a particular node, the Cluster service starts on that node, but the resources in the group that contains the quorum disk (usually the Cluster Group) remain offline. This allows you to attempt bringing the quorum resource online manually so that you can more easily diagnose problems with the quorum. Note that if this attempt to bring the quorum resource online fails, the Cluster service will stop again, even though it was started with the /fixquorum option.



Only one node at a time can be started with the /fixquorum option. You cannot join any other nodes to the node that was started with this option. When you have corrected the problem that you were having with the quorum resource, stop the Cluster service and restart it without options.



You can start the Cluster service with the /fixquorum option by opening a command prompt and typing:



net start clussvc /fixquorum



Or by typing:



net start clussvc /fq



You can also start the Cluster service with the /fixquorum option when using Services in Computer Management. To open Services, click Start, click Control Panel, double-click Administrative Tools, and then double-click Services. On a cluster node, the Cluster service will appear in the list of services. Right-click the service, click Properties, type /fixquorum in Start parameters, and then click Start.

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)

Error 1 :-
The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) 

In SSRS 2005

WorkAround :-

1. Check ReportServer and ReportServerTempDB is created
2. If DB is missing, Take a Backup from server and restore, or need to uninstall SSRS and reinstall
3. Connect to report config Manager
4. Database Settings click update.
5. Make sure all goes Green in Report Configuration
6. Now we will be able to browse .


Error 2 :- "Create New Database Script for report server"

In SQL Server Reporting Services 2005, we can generate the scripts using Reporting Service Configuration Manager tool.

In SQL Server Reporting Services 2008, we can create the RSExecRole using the following script:

And then execute the scripts in Catalog.sql and CatalogTempDB.sql to create table, view, stored produce and so on.

USE [ReportServer]
GO
 
if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END
GO
 
USE msdb
GO
 
if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END
GO
 
USE master
GO
 
if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END
GO
 
USE [ReportServerTempDB]
GO
 
if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END
GO

QUIZ

https://docs.google.com/viewer?a=v&pid=explorer&chrome=true&srcid=0B3yagczqVQKlNGI3ZjUyMjUtMjY1Mi00YzU0LTlkY2YtMGIyNWJkMWY1ZWI0

What is SQL Server Disaster? What are the top SQL Server Disaster Recovery Solutions ?

http://www.sqlserver-training.com/10-sql-server-disaster-recovery-solutions/-?utm_source=feedburner&utm_medium=twitter&utm_campaign=Feed%3A+Sqlserver-training+%28SQL+Server+DBA+Training%29&utm_content=FaceBook

You receive an error message when you click a hyperlink in Outlook

http://support.microsoft.com/kb/310049

or

http://www.slipstick.com/problems/this-operation-has-been-cancelled-due-to-restrictions/

Unable to deallocate a kept page.

If you're seeing this problem, you need to install SQL Server 2005 SP2 CU13, SQL Server 2005 SP3 CU4, or SQL Server 2008/2008 R2.



More details are in KB article 919142

http://www.sqlskills.com/BLOGS/PAUL/category/CHECKDB-From-Every-Angle.aspx

Rebuild / Reorganize Based on Fragmentation Level

This Script Will  Rebuild / Reorganize Based on Fragmentation Level

 

 

--Rebuild and Reorganize Script
--Start of variables
use master
DECLARE @dbName sysname
DECLARE @tableName sysname
DECLARE @databaseID INT
DECLARE @objectID INT
DECLARE @indexID INT
DECLARE @fragPercent INT
DECLARE @cmd VARCHAR(5000)
DECLARE @objectName sysname
DECLARE @indexName sysname
-- End of Variables

-- The databaseCursor cursor selects the user databases

DECLARE databaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb','AdventureWorks2008','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','SQL2008SBS', 'SQL2008SBSFS')

OPEN databaseCursor

FETCH NEXT FROM databaseCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

/* Selecting the required columns in the statsInfoTable table
for storing the selected values for the database. The table will be dropped
and recreated for each database*/


SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
INTO statsInfoTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10 and index_id > 0

-- Declaring cursor for selecting each value from the statsInfoTable

DECLARE physicalStatsCursor CURSOR FOR
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
FROM statsInfoTable

OPEN physicalStatsCursor

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

WHILE @@FETCH_STATUS = 0

BEGIN

-- Select the Table name and Index name. The selected values will be
-- used for building the dynamic query for the Reorganize or Rebuild.
-- Reorganize is the online operation and Rebuild is the offline operation

SELECT @objectName = s.name + '.' + t.name FROM sys.objects t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.object_id = @objectID

SELECT @indexName = i.name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.object_id = @objectID AND i.index_id = @indexID

IF (@fragPercent > 10 AND @fragPercent < 30)

BEGIN

PRINT 'Rorganizing Index' + @indexName + 'ON' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REORGANIZE'
PRINT @cmd
EXECUTE (@cmd)

END

IF (@fragPercent = 30 OR @fragPercent > 30)

BEGIN

PRINT 'Rebuilding Index' + @indexName + ' ' + 'ON' + ' ' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REBUILD'
PRINT @cmd
EXECUTE (@cmd)

END

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

END

CLOSE physicalStatsCursor

DEALLOCATE physicalStatsCursor

FETCH NEXT FROM databaseCursor INTO @dbName

DROP TABLE statsInfoTable

END

CLOSE databaseCursor

DEALLOCATE databaseCursor

--select * from statsInfoTable

SQL Azure administration

http://beyondrelational.com/modules/12/tutorials/25/tutorials/7254/getting-started-with-sql-azure-part-7-sql-azure-administration-i.aspx


http://beyondrelational.com/modules/12/groups/25/tutorials/7263/getting-started-with-sql-azure-part-8-sql-azure-administration-ii.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Jan

Dirty Page, Checkpoint, Commit Tran

A dirty page is a page that has not yet been written to the disk. You can (and most often will) have many pages that are different in memory as opposed to the copy on disk. They are called dirty, because the application has "smudged" them with new data. Once they are written to disk and both the disk copy and the memory copy agree, then it is no longer dirty.




As for the Buffer cache, I thought that the procedure cache was separate from the buffer cache. At least in 6.5, it was the same pool, but different "sides". You had to define how much of your memory allocation was for the proc cache. SQL Server defaulted to 30%. Anyone who came into this business after SQL 7.0 doesn't remember a bit of that trivia ;-). Generally, I think of the buffer cahce as containing any data buffer, so that would be log as well as data. The log contains "data" of it's own. Usually page numbers, transaction id's, etc..



Checkpoints have to happen in full and simple modes. Otherwise, no one's database would ever come back from a reboot. Checkpoints are done asynchronously by one of the background processes (spids 1-15 or so). This is to keep the I/O of writing of data from interfering with your reading in other pages from disk. Checkpoints can be triggered by a number of things ranging from the manual issuing of the checkpoint command to a need for free buffers caused by a huge table scan. Oddly enough, a shutdown does not give a checkpoint. If you have long re-start times, you may want to consider issuing a checkpoint before a shutdown (assuming it is a planned shutdown).



A commit transaction will mark your transaction as "completed" in the trasnaction log. Remember: All data is written first to the transaction log, then to the data files. All data is updated in memory first, as it is faster, but the hope is that data makes it to disk eventually. If something does not make it into your transaction log, it has no business on your data file. On recovery (such as after reboot), SQL Server goes through the transaction log to make sure that the current copy of the data is consistent. If it is not consistent, then it is suspect.

SQL SERVER 2012 EDITIONS

SQL Server Enterprise edition

SQL Server BI edition (Business Intelligence edition)

SQL Server Standard edition

* The Web, Developer and Express editions will also be available in the SQL Server 2012 release.

 *Image Deleted...Shortly Uploading

 

Reboot from CMD

shutdown -r -t 00 -f /m \\computer_name or IP

Error 229: Execute Permission Denied on Object 'xp_availablemedia' Owner 'dbo'

To work around this behavior, assign the user profile to the sysadmin role. By doing so, the user profile has unlimited access to all SQL Server features.

More on http://support.microsoft.com/kb/323249

Cannot show requested dialog. (SqlMgmt) OR Cannot show requested dialog.

This property may not exist for this object, or may not be retrievable due to insufficient access rights

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somevalidlogin]