Skip to main content

Posts

Showing posts with the label TSQL

How to see current executing query

There are various way in sql server to see current executing query .. Using DMV          select * from sys . sysprocesses where status = 'Running' select * from sys.dm_exec_Requests where status=' Running select * from sys.dm_exec_sessions where status=' Running ' Using DBCC a.        DBCC opentran b.       DBCC inputbuffer ( SP_ID ) SSMS GUI using Activity Monitor. DBCC opentran DBCC inputbuffer ( SP_ID ) CREATE TABLE T1 ( Col1 int , Col2 char ( 3 )); GO BEGIN TRAN INSERT INTO T1 VALUES ( 101 , 'abc' ); GO DBCC OPENTRAN ; ROLLBACK TRAN ; GO DROP TABLE T1 ; GO

DATALENGTH for Text Data Type

Last Friday i was working on our one of legacy application. And we were required for finding length from text field but when i tried below sql.. select top 2 circuit_id , len ( prov_config ) from config sql server given error Msg 8116, Level 16, State 1, Line 4 Argument data type text is invalid for argument 1 of len function. When I tried to convert into varchar then result was completely different than actual. select top 2 circuit_id , len ( cast ( prov_config as varchar )), prov_config from config Finally I tried Datalength which I got from SQLAuthority. select top 2 circuit_id , Datalength ( prov_config ), prov_config from config I Solved my problem using Datalength but the question still running in my mind why there is two function for finding Length LEN for char and DATALENGTH for Text and why the errors comes Argument data type text is invalid for argument 1 of len function. . Thanks for reading  Suman

Data purging and Archival from VLDB

How to delete Records from a Very Large Tables ? If we have to delete a large records based on some conditions like year wise - month wise from very large table and our SLA is without degrading system performance then we will come into problem .We can't use TRUNCATE TABLE and we may run out of disk space and then DELETE query fails and nothing is deleted. The Real-Problem (from SQL Bible) A DELETE executes as a transaction where it will not commit until the last record is deleted. DELETE physically removes rows one at a time and records each deleted row in the transaction log. If the number of records in a table is small, a straight out DELETE is fine, however, with a large table the DELETE will cause the transaction log to grow. When this happens the system IO performance is degraded. Also, the table will be locked which will affect the application. The person executing the DELETE query will usually panic because what seems to be a simple task takes a long time an

Difference between Logins and Users

What is Difference between Logins and Users. Logins and Users are complete different things in  Microsoft SQL Server.  Many of us assumes that they are the same thing, it can get a little confusing.  The basic difference is login is created on instance level and when a login is given access to a particular database then it's call a user for that database. In simple every database user there should be a login. Logins are created at the database server instance level, while uses are created at the database level.  CREATE  LOGIN TestLogin  WITH  PASSWORD  =  'I4india@2012' select * from sys.syslogins where name='TestLogin' use company CREATE  USER  TestUser  FOR  LOGIN TestLogin select * from sysusers where name=' TestUser '   There are two main categories of logins: SQL Server authenticated logins and Windows authenticated logins. I will usually refer to these using the shorter names of SQL logins and Windows logins.

Connection and Sessions

What is the differences between Connection and Sessions in SQL-Server? The CONNECTION is the physical communication channel between SQL Server and the applications: the TCP socket, the named pipe, the shared memory . The connections show only external connections and each connection has an spid. select   a . spid   ,   a . nt_username   ,   a . loginame   ,   a . program_name   from   sys . sysprocesses   a   ,   sys . dm_exec_connections   b   where   a . spid = b . session_id A   SESSION   is a semi-permanent interactive information interchange, between two or more communicating devices  or between an application and users. We must open a connection in order to create a session. Normally there is one session on each connection, but there could be multiple session on a single connection. eg. If we connect to sql server using management studio then its one connection. once we connected we can  open 3 query windows then these are 3 sessions. Microsoft   SQL-

Database & Physical Location

Dear Friends, Use of DMVs instead of SSMS GUI is always good sign of DBAs i understood and also trying to remove my bad habits by remembering all  DMVs,Sys Table,sys Functions & sys procedure etc..  On my daily activity every morning i check all database,there status and physical location using below TSQL/DMV.  use master SELECT     db.name AS 'DB_NAME', mf.name AS 'FILE_NAME', mf.type_desc AS 'FILE_TYPE', mf.physical_name AS 'FILE_PATH', db.state_desc,                       db.recovery_model_desc, mf.size, mf.growth FROM         sys.databases AS db INNER JOIN                       sys.master_files AS mf ON db.database_id = mf.database_id Happy Reading SumanJha_Accidental_Dba

DB Change Tracking Alert on My Cell

Hello Friends,  Recently I have been shared My Post on Change Tracking Log  (winlinuxhelp.com) described how I am storing day to day Database schema change logs Of all server for Reporting &  System Analysis purpose. I want to continue writing about this Topic ..  So today I will share about Change Alert which I have been configured into  system  to alert on every schema changes into my cell. To Create run below Script Create TRIGGER safety1 ON DATABASE FOR CREATE_TABLE , ALTER_TABLE , DROP_TABLE , CREATE_PROCEDURE , ALTER_PROCEDURE , DROP_PROCEDURE , CREATE_VIEW , ALTER_VIEW , DROP_VIEW AS declare @object int                       declare @hr int       declare @rcnum varchar ( 200 ) declare @str varchar ( 200 )                SELECT @str = EVENTDATA (). value ( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]' , 'nvarchar(max)' ) --print @str exec @hr = sp_OACreate 'jmail.smtpmail', @object OU