Skip to main content

DMV (Dynamic Management Views)



Backup-Restore

msdb..sp_delete_backuphistory '1-Jan-2005'
msdb..logmarkhistory
msdb..backupset

msdb..backupfile
msdb..backupmediaset
msdb..backupmediafamily
msdb..restorefile
msdb..restorefilegroup
msdb..restorehistory


Index Related

sys.indexes
sys.dm_db_index_usag
sys.Indexes
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.index_columns



SQL Server Operating System

sys.dm_os_sys_info
sys.dm_os_performance_counters
sys.dm_exec_requests

sys.dm_exec_sessions
Master..sysprocesses
sys.dm_exec_connections

sys.dm_os_waiting_tasks
sys.dm_os_wait_stats
sys.dm_os_nodes


   
SQL Server Agent Tables

dbo.sysjobactivity
dbo.sysjobhistory
dbo.sysjobs
dbo.sysjobschedules
dbo.sysjobservers
dbo.sysjobsteps
dbo.sysjobstepslogs
dbo.sysoperators
dbo.sysproxies
dbo.sysschedules
dbo.syssessions


Database Development, Management & Maintenance Related

sys.columns
sys.check_constraints
sys.filegroups
sys.linked_logins
sys.master_files
sys.backup_devices
sys.configurations
sys.database_files
sys.databases
sys.default_constraints
sys.dm_tran_locks
sys.dm_tran_locks
sys.foreign_key_columns
sys.foreign_keys
sys.foreign_keys
sys.indexes
sys.key_constraints
sys.objects
sys.partitions
sys.remote_logins
sys.server_principals
sys.servers
sys.sql_logins
sys.syscharsets
sys.syslanguages
sys.types
sys.dm_db_file_space_usage

Security Related DMVs

sys.certificates
sys.credentials
sys.database_permissions
sys.database_principals
sys.securable_classes
sys.server_permissions
sys.server_principals
sys.server_role_members
sys.sql_logins
sys.symmetric_keys
sys.user_token


Log shipping Related DMVs
use msdb
log_shipping_monitor_alert
log_shipping_monitor_error_detail
log_shipping_monitor_history_detail
log_shipping_monitor_primary
log_shipping_monitor_secondary


Mirroring - Working with Mirroring Related DMVs

sys.database_mirroring
sys.database_mirroring_endpoints
sys.database_mirroring_witnesses
sys.dm_db_mirroring_connections
Sys.databases



Replication Tables in the Distribution Database

MSarticles
MSreplication_objects
MScached_peer_lsns
MSreplication_subscriptions
MSdistpublishers
MSrepl_commands
MSdistribution_agents
MSrepl_errors
MSdistribution_history
MSrepl_originators
MSdistributiondbs
MSrepl_transactions
MSdistributor
MSrepl_version
MSlogreader_agents
MSsnapshot_agents
MSlogreader_history
MSsnapshot_history
MSmerge_agents
MSsubscriber_info
MSmerge_history
MSsubscriber_schedule
MSmerge_sessions
MSsubscriptions
MSpublications
MStracer_tokens



Replication Tables in the Publication Database

MSpeer_response
MSmerge_contents
MSpub_identity_range
MSmerge_current_partition_mappings
sysarticlecolumns
MSmerge_dynamic_snapshots
sysarticles
MSmerge_errorlineage
sysarticleupdates
MSmerge_generation_partition_mappings
sysmergearticlecolumns
MSmerge_genhistory
sysmergepublications
MSmerge_partition_groups
sysmergeschemaarticles
MSmerge_past_partition_mappings
sysschemaarticles
MSpeer_conflictdetectionconfigresponse
syssubscriptions
MSpeer_lsns
systranschemas




Replication Tables in the Subscription Database

MSdynamicsnapshotjobs
MSmerge_settingshistory
MSdynamicsnapshotviews
MSsnapshotdeliveryprogress
MSmerge_current_partition_mappings
MSsubscription_properties
sysmergearticles
MSmerge_generation_partition_mappings
sysmergepartitioninfo
MSmerge_genhistory
sysmergepublications
MSmerge_identity_range
sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo




Comments

Popular posts from this blog

mongoDB error : aborting after fassert() failure

What to do when facing errors on mongoDB “aborting after fassert() failure”

I like errors, in mongoDB this is the first error I faced and luckily many times. This error i faced during restoring name-space on local and restarting db system. I am still searching the exact root cause of this issue but i am able to resolve the current problem through below steps.

Remove all relevant namespace files from data-file route path..Now repair mongo instance using mongod process.mongod --repair ////////// execute command from bin folder path Then start server using mongd process, if started server successfully then ..mongod  ////////// execute command from bin folder path Restore last backups as normal process.Now check database by connecting mongo shell. Thanks for reading, 
Please comment your experience if you faced and also share knowledge if you have better steps to resolve...


https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

SQL71562: external references are not supported when creating a package from this platform

Last week I got this error from one of developer who was trying to deploy his project from Testing server to SQL Azure QA server. He was using “Deploy Database to SQL Azure” option from SSMS Tool-Task option.

After connecting to SQL Azure portal when operation started to deployment below errors occurs.

Validation of the schema model for data package failed. Error SQL71562: Error validating element xx.xxx.xx:function .dbo.xxx has an unresolved refrence to object xx.dbo.xxxx external refrences are not supported when creating a package from this platform.



Reason: The reason of the this error was; some functions of project was dependent on master database and only single database was being deploy to SQL Azure. DACFx must block Export when object definitions (views, procedures, etc.) contain external references, as Azure SQL Database does not allow cross-database external references So, this error was coming.

Solution : I suggested him to create those function to locally on local database what…

How to recover msdb database from suspect mode

It was Monday 9th Jun 47 degr. temperature of Delhi-NCR. Temperature was like boiling me and database. When I reached my office( @ 8.45 am) got an alert from one of Server.
“MSDB is in suspected mode” At the same time comes in my mind, this issue will boil me today.. I just tried to cool my self through cold drink then connected server from my local system using windows authentication mode..