Read Dirty Records in SQL Server

Sometimes we need to read Dirty / Uncommitted records to confirm the changes made by other users.

To read uncommitted records we have SET the Isolation level to read uncommitted. check below example.

Formate to write the SQL

set transaction isolation level read uncommitted select * from dbo.code where  id ='123'

look at the "isolation level read uncommitted" part, which makes you SQL to read dirty records/data.

Now go for a full example:

Let there are two users,

USER-1 will make the changes and rollback at the end

USER-2 will read the uncommitted data from other sessions

Before start making changes, data looks like 

ID	Name
123	Saiful

USER-1: started the transaction...

begin transaction      update dbo.code    set name = 'azam'   where id='123'

USER-2: Read this Dirty/ Uncommitted/ Unrollbacked record

set transaction isolation level read uncommitted select * from dbo.code where  id ='123'

S/he will get below data BUT the transaction has not been committed OR Rollbacked

USER-1: now rollback this transaction 

rollback

USER-2: if s/he runs the SQL then s/he will get the actual data!

select * from dbo.code where  id ='123'

will get below data

ID	Name
123	Saiful

Grant Solarwinds to access and monitor DB performance

If we use Solarwinds DPA to monitor the DB health and performance and want to get notified about the data file space and long-running sql queries, then first of all we have to grant access on all dbs to SW by below script..


---------------- Solarwinds DPA grant access script -------------
-- Change the DOMAINNAME\User

USE master GO IF NOT EXISTS(SELECT * FROM sys.syslogins WHERE name = 'MED\swadmin') BEGIN           print 'CREATE LOGIN'        CREATE LOGIN "MED\swadmin" FROM WINDOWS END GO GRANT VIEW SERVER STATE TO "MED\swadmin" GO GRANT VIEW ANY DEFINITION TO "MED\swadmin" GO print 'sp_adduser' EXEC sp_adduser @loginame = 'MED\swadmin', @name_in_db = 'MED\swadmin' GO GRANT EXECUTE ON xp_readerrorlog TO "MED\swadmin" GO USE msdb GO print 'sp_adduser' EXEC sp_adduser @loginame = 'MED\swadmin', @name_in_db = 'MED\swadmin' GO EXEC sp_addrolemember N'db_datareader', N'MED\swadmin' GO print 'Loop Start' go EXEC sp_MSforeachdb 'USE [?]; EXEC sp_adduser @loginame  = ''MED\swadmin''' GO

Grant Read & Write access on all user databases using tSQL, SQL Server

You can use below tSQL to grant any permission to a user.
Just modify it as your requirements.

Below script can be used to grant Read & Write access to all user databases...

NOTE: you have to run the printed output separately!
-------------------------

DECLARE @user_name    SYSNAME = 'ENET\azam' DECLARE @SQL varchar(MAX)='' SELECT @SQL = @SQL +char(10)+char(10)        +'USE ' + QUOTENAME(NAME) + char(10)+'GO'+ char(10)        +'CREATE USER '+QUOTENAME(@user_name)+' FOR LOGIN '+QUOTENAME(@user_name)+ char(10)+'GO'+ char(10)        +'ALTER ROLE [db_datareader] ADD MEMBER '+QUOTENAME(@user_name)+ char(10)+'GO'+ char(10)        +'ALTER ROLE [db_datawriter] ADD MEMBER '+QUOTENAME(@user_name)+ char(10)+'GO'+ char(10) FROM   sys.databases WHERE  database_id > 4        AND state_desc = 'ONLINE'        --and name like 'DEV_%' print @SQL

Once you run this, you will get below code to run in another editor...

----  OUTPUT --- 

:: need to run below code separately...

USE [dev_test_01] GO CREATE USER [ENET\azam] FOR LOGIN [ENET\azam] GO ALTER ROLE [db_datareader] ADD MEMBER [ENET\azam] GO ALTER ROLE [db_datawriter] ADD MEMBER [ENET\azam] GO USE [dev_test_02] GO CREATE USER [ENET\azam] FOR LOGIN [ENET\azam] GO ALTER ROLE [db_datareader] ADD MEMBER [ENET\azam] GO ALTER ROLE [db_datawriter] ADD MEMBER [ENET\azam] GO

Check Job Status [Running/Not] using tSQL, SQL Server

Sometimes SQL Server job history doesn't show that a job is running currently or not.

For what we need to check this using SQL.

Check my other post to "View Job History using tSQL, SQL Server".

I always use below to check "Is my job running now?"

--CREATE PROCEDURE sp_IsJobRunning --@JobName as varchar(100) = Null --AS --BEGIN declare @JobName as varchar(100) = Null CREATE TABLE #Job_sazam (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, -- BOOL request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, -- BOOL current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) -- Get Job Information -- you need "sysadmin" role or sql_agent related role access to execute  xp_sqlagent_enum_jobs. INSERT INTO #Job_sazam EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo SELECT Running, sj.name FROM #Job_sazam J INNER JOIN msdb.dbo.sysjobs SJ ON J.job_ID = SJ.job_ID --WHERE [name] = @JobName order by 1 desc, 2 asc drop table #Job_sazam --END

View Job History using tSQL, SQL Server

We can view job history using SSMS very easily. But when the amount of log is too high in count, SSMS got timeout exception to view the job history.

For what, I always use below SQL to check the job history, which is almost like to GUI view :)

NOTE: you can comment the WHERE condition to see the full history of all jobs!

t-SQL
------------
SELECT top 100
b.instance_id,
  msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
b.step_id,b.step_name,
b.message,
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
          as 'RunDurationMinutes'
FROM msdb.dbo.sysjobhistory  b
INNER JOIN msdb.dbo.sysjobs a
ON a.job_id = b.job_id 
WHERE a.name = 'your job name'
ORDER BY b.instance_id DESC

Get Database Size (MB) using tSQL, SQL Server

Using below tSQL we can check the database's data/row size , log size & total size in MB,

this sql will list all databases (system & user)

------------------------------

SELECT DATABASE_NAME = s.name     , LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(10,2))     , ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(10,2))     , TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2)) FROM sys.databases s JOIN sys.master_files m ON s.database_id = m.database_id GROUP BY s.name ORDER BY s.name --SUM(size) desc

---------------

DB Size.png