:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Mar 30 / 2009
  • 0
DBA Interview questions, dbDigger, Monitoring and Analysis, SQL Server tools

Purpose and use of profiler

Profiler is very useful tool for repeatable and insight analysis/monitoring of SQL Server database engine, SSAS and SSIS. Profiler is a GUI used to utilize SQL Trace. SQL tarce is a used to capture client-server communication. Profiler may server following major purposes for a SQL Server DBA

  • Query analysis of execution plans
  • Analyze errors and warnings of SQL Server
  • Analyze the user activities
  • Create traces to reuse later also
  • Save trace results for later analysis
  • Mar 26 / 2009
  • 2
dbDigger, Monitoring and Analysis, T-SQL Scripts, T-SQL Tips and Tricks

Get last modification date of a table in SQL Srever database

Some times i am required to get last modification date of some of my tables. This information is not there either in SSMS or through famous sp_help command. Before mentioning the example to get last modification date for a table it is better to mention that SQL Server 2000 does not keeps track of any such information. So you may get last modification date of a table in SQL Server 2005 and onwards. So to get last modification date of table in AdventureWorks

 
Use AdventureWorks  
 GO  
 SELECT Name, Modify_date  
 FROM sys.tables  
 WHERE name = 'ProductPhoto'  
 GO  

It is important to understand that modification date means modification in structure or properties of a table. Any operation on data of table does not have any link to modification date of that table that is being retrieved through this script. And to get this information for all of your tables in a database, just remove the where clause.

  • Mar 26 / 2009
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

get list of column meta data in a table from INFORMATION_SCHEMA

To day some one asked me that how to get columns count in a table. For information like list of columns of a table, i mostly used sysobjects, syscolumns and systypes by joining these. During a little googling i find that meta data about columns of a table can be efficiently and easily found in system views like INFORMATION_SCHEMA.Columns. For example following script may generate a list of columns in a table along with there relevanmt meta data

 
SELECT  
 table_name AS [TABLE Name],  
 column_name AS [COLUMN Name],  
 data_type AS [COLUMN DATA TYPE],  
 character_maximum_length AS [MAX length],  
 is_nullable as[Nullable],  
 ordinal_position AS [Postion IN TABLE]  
 FROM INFORMATION_SCHEMA.Columns  
 WHERE table_name = 'tableNamehere'  
 ORDER BY ordinal_position  

Columns aliases are self explanatory. Above script may be modified through altering parameters in select, where and order by clauses. Using such system views is far more easy and efficient than to use system tables.
And also to get count of columns in a table i just modified the script to simpler form as

 
SELECT count(column_name)  
 FROM INFORMATION_SCHEMA.Columns  
 WHERE table_name = 'tableNameHere'  
  • Mar 25 / 2009
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get list of all current connections to SQL Server instance

List of all current connections to SQL Server instance can be found in SSMS or Enterprise Manager. But these tools are not always accessible and also expert DBAs like to generate lists by code as it is flexible and easy to analyze. So to get list of all current connections to SQL Server instance use following script.

 
SELECT DB_NAME(dbid)AS ConnectedToDB,  
 hostname, program_name,loginame,  
 cpu, physical_io, memusage, login_time,  
 last_batch, [status]  
 FROM master.dbo.sysprocesses  
 ORDER BY dbid, login_time, last_batch  
 GO  

Also you can filter the results for a single user or a single database by providing filter criteria in where clause as required. For example to get just connections to database you may add following condition in where clause

 
WHERE dbid = DB_ID ('AdventureWorks')  

Similarly you may filter the results with relevance to any column as required. Above script can be used on all versions of SQL Server.

  • Mar 06 / 2009
  • 3
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Generate script for all permissions in a database

In many scenarios DBA is required to configure permissions of a user. If permissions are to be re configured for an existing user, due to user deletion or some other reason, then it may be a nightmare to exactly configure the same. A handy solution is that you may generate the permissions for your users regularly in a usable form and use it when ever required. Following script will serve the same purpose. T-SQL for Permissions may be generated by following script. This T-SQL may be used later with a single click whenever required. The script will be used on SQL Server 2005 and later

 
Use AdventureWorks  
 GO  
 SELECT  
 state_desc + ' ' + permission_name +  
 ' on ['+ SYS.SCHEMAS.name + '].[' + SYS.OBJECTS.name + ']  
 to [' + SYS.DATABASE_PRINCIPALS.name + ']'  
 COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]  
 FROM SYS.DATABASE_PERMISSIONS JOIN sys.objects  
 ON SYS.DATABASE_PERMISSIONS.major_id = sys.objects.OBJECT_ID  
 JOIN SYS.SCHEMAS  
 ON SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID  
 JOIN SYS.DATABASE_PRINCIPALS  
 ON SYS.DATABASE_PERMISSIONS.grantee_principal_id = SYS.DATABASE_PRINCIPALS.principal_id  
 order by [Permissions T-SQL]  
 GO  

each and every permission will be provided in T-SQL form for every user. Whole result may be saved to use later.

T-SQL for permissions

If you require to generate permissions for a single user or single object then conditions may be applied through where clause in our script.

  • For specific object put [SYS.SCHEMAS.name] in where clause
  • For specific user put [SYS.DATABASE_PRINCIPALS.name] in where clause

To get all permissions for a login in all databases please read my other post.

  • Mar 06 / 2009
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, Transaction log files

Check for any open transaction in transaction log

There may exist an open transaction within the transaction log. In that case if the BACKUP LOG statement is used, only the inactive part of the log can be truncated. An open transaction can prevent the log from truncating completely. To identify that open transaction use DBCC OPENTRAN in a database.
It will provide the transaction information if there is such present. If no such open transaction exists then an informative message will be displayed.

Consult us to explore the Databases. Contact us