:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Dec 04 / 2008
  • 0
dbDigger, Monitoring and Analysis, T-SQL Enhancements

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views

As we know that system tables of SQL Server 2000 have been evolved to system views in SQL Server 2005. Although many system tables tables have been migrated to SQL Server 2005, but using the system view is more recommended because these will be preserved in coming versions of SQL Server. And also system views provided in SQL Server 2005 provide much more information about internals of SQL Server and meta data.
Visit following link to get mapping of SQL Server 2000 tables to SQL Server 2005 system views.

http://msdn.microsoft.com/en-us/library/ms187997(SQL.90).aspx

  • Nov 24 / 2008
  • 1
dbDigger, Monitoring and Analysis, System Administration

How to view virtual memory consumed by processes

Usually task manager is used to view the memory and CPU usage by different running processes. By default physical memory and CPU usage is shown under processes task in task manager.

So how to get virtual memory usage statistics for any process of windows OS? To get view of virtual memory statistics along with physical memory usage for processes.
Go to ‘view’ menu
click ‘select columns…’ option
Select ‘Virtual Memory Size’
and click ‘OK’ button

Then you also get virtual memory statistics for each process.

  • Nov 12 / 2008
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures

Read SQL Server Error log info through T-SQL

SQL Server Management studio (SSMS) and SQL Server Enterprise Manager provide a good interface for listing error log file archives and reading each error log archive individually. But I have noticed that when log file entries grow in number. SSMS/SSEM take some time to load the log file content. So how to get list of error log archives through T-SQL and how to list log contents of an error log file through T-SQL. Following two commands will help you in this regard.

exec sp_enumerrorlogs

Error log Archives

And to read log content of log archive file # 1, I have used following command.

exec sp_readerrorlog 1

The parameter 1 is to point the log archive file number. You may give file number from 0 to 6 as provided in list.

Error log Archives contents

  • Nov 12 / 2008
  • 0
dbDigger, Mirroring, Monitoring and Analysis, System Stored Procedures

Monitor DB mirroring session in SQL Server 2008

Question: In SQL Server 2008, if you want to use a stored procedure to monitor the status of your database mirroring session, which one would you use?

Answer: sp_dbmmonitorresults

Explanation: The sp_dbmmonitorresults procedure will return the current status of your database that is being mirrored.

Ref: sp_dbmmonitorresults

Note: The Question is taken from SQLServercentral.com.

  • Nov 11 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions

List permissions for logins in SQL Server database

It is very important information that which user has which rights and permissions in your SQL Server databases. By this way you may periodically monitor the permissions and chances for any accidental hidden loop hole will be minimized. Following script will provide you the permissions of logins in a database.

 
USE DBName  
 GO  
 SELECT  
 dp.class_desc, dp.permission_name, dp.state_desc,  
 ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id  
 JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id  
 GO  
Consult us to explore the Databases. Contact us