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]

Invalid Name Space SSRS 2005

Path for MOF File

C:\Program Files\Microsoft SQL Server\90\Shared

Change Name of RS Instance name,

1. Go to above mentioned location

2. Open the MOF file in Notepad

3. Search below code and replace string to SQL Server NAME.
#pragma namespace ("\\\\.\\root\\Microsoft\\SQLSERVER")
instance of __Namespace
{
    Name = "MSSQLSERVER";
};

4.Save the file, and then exit Notepad.


5.Open a command prompt, and then move to the same folder.

6.Type mofcomp reportingservice.mof, and then press ENTER.


This will solve invalid name space issue

Max Replication Text Size

When SQL Server is installed, the Max Replication Size value is set by default to 65536. If a record is inserted into a published table that exceeds that value, an error will occur at the application.

The following error will be returned to the calling application:

__________________________________________________________________________________

    Server: Msg 7139, Level 16, State 1, Line 1

Length of text, ntext, or image data (200) to be replicated exceeds configured maximum 10.

The statement has been terminated

__________________________________________________________________________________

In order to prevent this from occurring, Max Text Replication Size needs to be overridden to the maximum allowed, which is 2147483647

Solution :- 

exec sp_configure 'max text repl size', 2147483647

reconfigure with override

Tail Log Backup

Like any log backup, a tail-log backup is taken by using the BACKUP LOG statement. We recommend that you take a tail-log backup in the following situations:


If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

BACKUP LOG database_name TO WITH NORECOVERY

Note : To avoid an error, the NORECOVERY option is necessary.

If the database is offline and does not start.

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

BACKUP LOG database_name TO WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

The following table summarizes these options.


To create a transaction log backup when the database is damaged
 
To create a backup of the currently active transaction log

Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:
The name of the database to which the transaction log to back up belongs.
The backup device where the transaction log backup will be written.

The NO_TRUNCATE clause.

This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.

Optionally, specify:
The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.

The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.

The FORMAT clause, when you are using media for the first time, to initialize the backup media and rewrite any existing media header.

The INIT clause is not required if the FORMAT clause is specified.

Important noteImportant


Use extreme caution when you are using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media.



Example

Important noteImportant



The MyAdvWorks_FullRM database is a copy of AdventureWorks2008R2, which uses the simple recovery model. To permit log backups, before taking a full database backup, the database was set to use the full recovery model, using ALTER DATABASE MyAdvWorks_FullRM SET RECOVERY FULL.



This example backs up the currently active transaction log for the MyAdvWorks_FullRM database even though MyAdvWorks_FullRM has been damaged and is inaccessible. However, the transaction log is undamaged and accessible:



BACKUP LOG MyAdvWorks_FullRM


TO MyAdvWorks_FullRM_log1


WITH NO_TRUNCATE;


GO

DMF

Replication Related DMV

1) sys.dm_repl_articles	2) sys.dm_repl_schemas
3) sys.dm_repl_tranhash	4)sys.dm_repl_traninfo

Database Mirroring Related DMV

1. sys.dm_db_mirroring_connections (Transact-SQL)
2. sys.dm_db_mirroring_auto_page_repair (Transact-SQL)

I/O Related DMV

1. sys.dm_io_backup_tapes (Transact-SQL)
2. sys.dm_io_cluster_shared_drives (Transact-SQL)
3. sys.dm_io_pending_io_requests (Transact-SQL)
4. sys.dm_io_virtual_file_stats (Transact-SQL) 

Resource Governor Related DMV

1. sys.dm_resource_governor_configuration (Transact-SQL)
2. sys.dm_resource_governor_workload_groups (Transact-SQL)
3. sys.dm_resource_governor_resource_pools (Transact-SQL)

SQL Server Operating System Related DMV

1. sys.dm_os_buffer_descriptors (Transact-SQL)
2. sys.dm_os_memory_pools (Transact-SQL)
3. sys.dm_os_child_instances (Transact-SQL)
4. sys.dm_os_nodes (Transact-SQL)
5. sys.dm_os_cluster_nodes (Transact-SQL)
6. sys.dm_os_performance_counters (Transact-SQL)
7. sys.dm_os_dispatcher_pools (Transact-SQL)
8. sys.dm_os_process_memory (Transact-SQL)
9. sys.dm_os_hosts (Transact-SQL)
10. sys.dm_os_schedulers (Transact-SQL)
11. sys.dm_os_latch_stats (Transact-SQL)
12. sys.dm_os_stacks (Transact-SQL)
13. sys.dm_os_loaded_modules (Transact-SQL)
14. sys.dm_os_sys_info (Transact-SQL)
15. sys.dm_os_memory_brokers (Transact-SQL)
16. sys.dm_os_sys_memory (Transact-SQL)
17. sys.dm_os_memory_cache_clock_hands (Transact-SQL)
18. sys.dm_os_tasks (Transact-SQL)
19. sys.dm_os_memory_cache_counters (Transact-SQL)
20. sys.dm_os_threads (Transact-SQL)
21. sys.dm_os_memory_cache_entries (Transact-SQL)
22. sys.dm_os_virtual_address_dump (Transact-SQL)
23. sys.dm_os_memory_cache_hash_tables (Transact-SQL)
24. sys.dm_os_volume_stats (Transact-SQL)
25. sys.dm_os_memory_clerks (Transact-SQL)
26. sys.dm_os_wait_stats (Transact-SQL)
27. sys.dm_os_memory_nodes (Transact-SQL)
28. sys.dm_os_waiting_tasks (Transact-SQL)
29. sys.dm_os_memory_objects (Transact-SQL)
30. sys.dm_os_windows_info (Transact-SQL)
31. sys.dm_os_workers (Transact-SQL)

Service Broker Related DMV

1. sys.dm_broker_activated_tasks
2. sys.dm_broker_connections
3. sys.dm_broker_forwarded_messages
4. sys.dm_broker_queue_monitors

Index Related DMV

1. sys.dm_db_index_operational_stats (Transact-SQL)
2. sys.dm_db_index_physical_stats (Transact-SQL)
3. sys.dm_db_index_usage_stats (Transact-SQL)
4. sys.dm_db_missing_index_columns (Transact-SQL)
5. sys.dm_db_missing_index_details (Transact-SQL)
6. sys.dm_db_missing_index_group_stats (Transact-SQL)
7. sys.dm_db_missing_index_groups (Transact-SQL)

Database Related DMV

1. sys.dm_db_file_space_usage
2. sys.dm_db_session_space_usage
3. sys.dm_db_partition_stats
4. sys.dm_db_task_space_usagesys.dm_db_persisted_sku_features 

Change Data Capture Related DMV

1) sys.dm_cdc_log_scan_sessions (Transact-SQL)	2) sys.dm_cdc_errors (Transact-SQL)
3) sys.dm_repl_traninfo (Transact-SQL)