SQL Server Database Administration

SQL Server Job Failed [SQLSTATE 42000] (Error 3013) and (Error 3202)

2018-05-17T08:33:00.000-07:00

My SQL Server job is failing throwing errors as shown below

[SQLSTATE 01000] (Message 3211) Write on
"\\backup-location\folder\database_name.BAK"
failed: 2(The system cannot find the file specified.) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

The sql jobs are failing due to network dis-connectivity to the network location where my backups will be written to.
So I made use to retry_attempts and retry_interval parameter options available in job properties. as show below pic.

But I have 102 steps, I don't want to waste my time in updating all the job steps, instead I want to update all steps at once. Here is the query that I used to update all steps of a particular job that I wanted to update retry_interval and retry_attempts of a job.

TO SET RETRY ATTEMPTS to the job
Update sysjobsteps
Set retry_attempts=3 (--set as you prefer)
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name='JOB NAME'

TO SET RETRY INTERVALS to the job

Update sysjobsteps
Set retry_interval=1 (--set as you prefer)
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name='JOB NAME'

Note: Run at your own risk. It worked for me. This may be not the ideal solution since it is more related to the Network packets being dropped, and in-stable Network. I spent several months on resolution for network issues. But I used this, hope this helps others.

Identity Specification (Is Identity) SQL Server Table

2017-05-08T08:24:00.000-07:00

Saving Changes is not Permitted. Tables need to be dropped and re-created. Message is appeared while you try to change the (Is Identity) Column Property of a table.

How to make a change without dropping the table.
Follow the below steps.

Go to Tools>Options>Designers> Un-check Prevent Saving changes that requires table re-creation.

Message 15063, Login Already Exists

2016-08-10T10:22:00.000-07:00

Recently, I received an error from a Share-Point 2013 database creation from the Farm Account

As you all know, while triggering a new database from SharePoint Site its uses a farm account to create a new database on SQL Server, and during the creation as a DBA its our responsibility to grant necessary permissions, in my case I have granted DB_Creator server role as per MS recommendations.
after the database has created by the farm and is the owner of the database, but still its not able to access within the database. Very strange huh..
and then I got involved in to the issue to have it fixed.

I tried to add users, since it does not exist in the database, but the user has db_owner rights when I tired to add Farm account to the database from the login in properties users mappings in the GUI very strange. and I received an error while tried to add and hit OK.

The login already has an account under a different user name. 15063

I verified the database login and user info by running the below script.

SELECT l.loginname , u.name AS username

FROM sysusers u INNER JOIN master..syslogins l ON u.sid = l.sid

I found that the users is mapped to [DBO]. So I changed the database owner to 'sa'

by running the below script

sp_changedbowner 'sa'

and then I ran the below statements to grant access to the existing farm account.

CREATE USER [domain\FARMacocunt] FOR LOGIN [domain\FARMacocunt]

ALTER ROLE [db_owner] ADD MEMBER [domain\FARMacocunt]

That's how I resolved the issue, but I do not know why it happened to behave like this during the time of new database creation.

Lost SQL Server access, forgot 'sa' password no clue how to gain access?

2015-09-18T08:29:00.000-07:00

I recently encountered an issue where I didn't have access to SQL Server and I was asked for help to debug and gain back control on the system and the server hosting sql server database.

Here is what to do.

Step1) Login to the server as administrator.

Step2) Download PSExec tool from

https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Its windows sysinternals utility.

Why?

PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account

Step3) Find the location of your SSMS.( SQL Server Management Studio)

Right click on the properties of SSMS and click on shortcut tab.

Copy the location to a notepad.

Step4) Open Command Prompt run as administrator

Step5) Navigate to the folder where you have downloaded and extracted PSexec tool.

Step6) Run the following command to start your SSMS.

PsExec -s -i "F:\SQLServer\Installs\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"

Please refer to https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

s- Runsthe remote process in the System account.

i-	Runs the program so that it interacts with the desktop of the specified session on the remote system. If no session is specified the process runs in the console session.

Step7 ) go to Security and gain access or add user as login and add them to the sysadmin server role.

Database in Recovery Pending State

2014-10-15T13:56:00.000-07:00

Database 'db_name' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Looks like Log file got corrupted and not allowing the database to come online.

First thing make sure you have .mdf and .ldf files in your drives.

next verify

select * from sys.sysdatabases where name ='db_name'

Verify the filename column sees the database .mdf file with full path.

Execute the following commands to bring your database to online.

alter database [db_databasename] set offline with rollback immediate

alter database [db_databasename] set online with rollback immediate

with this your database is online and accessible.

I would also recommend to run the following command to make sure no corruption.

DBCC CHECKDB ('db_databasename')

2014-09-05T10:50:00.002-07:00

High PLE high Performance.

Page Life Expectancy commonly called as PLE, is number of seconds a page will stay in the buffer pool.
The longer the pages stays in memory cache, the higher the PLE is, the high PLE gives high performance. Each time request comes, first SQL Server will check in the memory cache if it exists or not. If the results or data is found in memory cache (which is also called as buffer pool) decreasing the search time, then it will display the results that is read from cache. If sql server could not find the information in cache then it will look for the data in pages (that is stored in hard disk) now we are increasing the search time. So the high PLE leads to high performance. Below is the query to check the PLE value.

select
[object_name],
[counter_name],
[cntr_value] as Current_value
from
sys.dm_os_performance_counters
where
[object_name] LIKE '%Manager%'
and
[counter_name] = 'Page life expectancy'

Also always Buffer cache hit Ratio should be 100 or close to 100.
Below is the query to check the Buffer cache hit Ratio.

Both values can be obtained from Perfmon tool by adding respective counters.

select
[object_name],
counter_name,
cntr_value as current_Value
from
sys.dm_os_performance_counters
where
[object_name] LIKE '%Buffer Manager%'
and
[counter_name] = 'Buffer cache hit ratio'

SQL Server Error Log Recycle (ERRORLOG file growing large)

2014-09-04T06:31:00.000-07:00

SQL Server Error log is too large and has grown upto 24GB, it takes a lot of time to open and sometimes hangs and SSMS gets into not-responding state.

Go to the location of the error log and see what's the size as shown in the pics below.
If you don't know the location of file, open management studio new query and execute the below query

use master
go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', null, null, N'asc'
go

From the results set in the text column you can find the location of your error log file.

Recycle log means creating the new error log file to start recording events in the new file so that its easy to search something in a new file rather that has fewer events rather than searching large file takes time.

Open management studio connect to the Instance, open new query.
and run the following statement.

use master
go
exec sp_cycle_errorlog
go

New ERROLOG file will be created by renaming the older file (24Gb) to ERRORLOG.1. You can comfortably delete the file to save space if you don't need it.
and expand the management studio you will see two files.

1) Current being the new file and starts to record new events.

2 Archive being the old recorded events, if you don't need it you can delete the physical file from the location.

Where does SQL Server 2012 License Key gets stored?

2014-07-21T07:43:00.000-07:00

Many of my folks had a question about MicroSoft SQL Server License Key.

SQL Server license key can be found at the below location the registries.

launch REGEDIT(Registry Editor) from the command prompt type: regedit

Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup DIGITALPRODUCT ID

Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."

2014-07-21T07:23:00.000-07:00

TITLE: Connect to Server
------------------------------
Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."
By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.
------------------------------
Connecting to the Integration Services service on the computer "SERVER_NAME" failed with the following error: "Access is denied."
By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

This error occurred when I upgraded SQL Server 2008R2 to SQL Server 2012.
Everything was working until then, then I realized going back to old school its permission error, after integration services installed as an administrator needs to grant access to the users and groups who were in earlier version of SQL Server 2008R2, 2005 including me who actually installed\upgraded to sql 2012.

Here is the fix that I used it works after I granted access to the users or groups as below to DCOM.
First launch Component services.
from Search type component or from command prompt type Dcomcnfg.exe and hit enter.
And from the below figure expand DCOM Config, to the
MicroSoft SQL Server Integration Services 11.0
1) Right click and select Properties.
2) Select Security Tab
3) On Launch and Activation Permissions select Edit.
4) now Add the member or groups that needs access to Integration Services.
and Finally Restart your Integration Services and Restart SSMS.

Cannot see ODBC Driver after installation of Oracle 11g client.

2014-06-27T09:06:00.005-07:00

I installed the ORACLE client tool full Administrator option
Still not able to see the ODBC drivers for Oracle?

I know I have installed full administrator. Now next thing what I did was I went back and verified what version of Oracle Client I installed, I found it was 32 bit and My server is windows server 2008 r2 is 64 Bit.
So that explains we have a compatibility issues.

Without again re-installing 64 bit Oracle 11g client.
I went into
C:\Windows\SysWOW64\odbcad32.exe and launched the ODBC data source Administrator.
Now I see all the driver of 32 bit that I installed.

In my case I need 32 bit drivers, so If you still need 64 bit drivers Please install 64 bit client Oracle 11G drivers only.

Error: 9003, Severity: 20, State: 9 SQL Server 2012

2014-02-04T08:48:00.000-08:00

I have got this error message earlier this morning and all my user database backups failed due to this.

The log scan number (87817:27:0) passed to log scan in database 'AdventureWorks2012' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during Startup.

These are the steps that I took to resolve the issue.

I ran DBCC CHECKDB
and there are lot of allocations errors and database has been corrupted.

Fix1: Restore the Previous nights good backup can fix this.

Before you restore make sure you have good backup it can be verified by running the below.

RESTORE VERIFYONLY FROM DISK = H:\Backups\AdventureWorks2012.BAK
GO

and then restore to the earlier nights good backup.

================================================================

Fix2:
Rename your log file or move your log file to different location.
You can you do this by detaching the database first and then move the files or rename the files.

USE master;
GO
EXEC sp_detach_db @dbname = 'AdventureWorks2012';

Now rename the .ldf  file or move to a different location.

CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'D:\MSSQL\DATA\AdventureWorks2012_DATA01.mdf')
--(FILENAME = 'E:\MSSQL\LOGS\AdventureWorks2012_LOG01.ldf')
FOR ATTACH;

OR

EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2012', 
    @physname = 
N'MSSQL\DATA\AdventureWorks2012_DATA01.mdf';

Note: I commented .LDF file attachment, without it a NEW log file will be created in the default location.
and run the DBCC CHECKDB (Databasename)

Evaluation period has expired, SQL Server 2012

2012-12-17T10:25:00.002-08:00

How to upgrade Evaluation edition or use Licence key to the SQL Server Services.

Run SQL Server 2012 Setup file as administrator and select maintenance link as shown below.

Select Edition Upgrade; Edition Upgrade meaning you are actually Upgrading Evaluation edition to Enterprise edition by entering Licence key. Make sure you have Enterprise Edition KEY only other editions keys are not Accepted.

And finally restart services and start the Management Studio.

Role Management Tool to Install .NET FrameWork 3.5

2012-11-30T10:30:00.002-08:00

You get this error when Installing SQL Server 2012 Enterprise edition.

Because SQL Server 2012 ships with .NET 4.0 Framework.

And Windows 2008R2 already has 3.5 inbuilt you just need to enable the already existed feature within WINDOWS 2008R2 from server manager as shown below.

Server Manager Enable .NET FrameWork 3.5.1 Feature.

Right click on computer and select Manager and browse to Features as shown below.

Once selected this feature and continue SQL Server 2012 Installation it will be go smooth.

Shrinking Database Log Files.

2012-10-04T13:40:00.001-07:00

Often many get confused when the log grows and don't know what to do?

So here it goes.

It is recommended that you back up the log immediately before switching the recovery model of your database From FULL TO Simple.

backup log blogs_db_log with truncate_only;
You still have a point in time of recovery if your database goes down during this process.

Step1: Change recovery model of your database to SIMPLE. it will truncate the log
Step2: Shrinking the LOG file to bare minimum.
Step3: Changing the recovery model to FULL.
Step4: If you taking periodic LogsBackups from sql agnet job take a FULL Database backup of your database because your LOG backup will fail since we changed recovery model from
Full>Simple>Full.

Check your logspace on the server
dbcc sqlperf (logspace)
Check Available Free space on all drives
xp_fixeddrives

Example to Shrink your Logfiles.
Step:1
use blogs_db
go
alter database blogs_db
set recovery simple
go
I am shriking to 2MB You may even opt for 0
(It will shrink to the initial size of your Log file which you assigned while creating your Database log file)

Step:2
dbcc shrinkfile(glogs_db_log, 2)
go

Step:3
alter database blogs_db
set recovery full
go

Optional
Step4: backup database BLogs_DB to DISK
'C:\backups\BLogs_DB.BAK'

Restore table from Backup file SQL Server 2005

2012-08-29T11:15:00.000-07:00

Few days before I was asked to restore a Table from a backup file? Can you really attain this in sql server 2005? Which is currently under LogShipping.

And then I suggested easiest way to achieve your goal is

1) Restore backup to another server and apply all the transactions logs with NO RECOVERY and last one with RECOVERY until where you want the data to be recovered.

2) STOP LogShipping.

3) Rename the Table which is corrupted or which you want to restore from Backup file.

4) Export the Table from the RESTORED Database which is in another server to the CURRENT production server.

5) Enable Log Shipping.

You should be good to go.

How to Remove LogShipping SSMS

Ghost Process or Ghost Cleanup Tasks in SQL Server Blocking

2012-08-29T06:21:00.000-07:00

Yesterday I got a call from a user about Database not being connected or getting timed out, well actually there are many different solutions for resolving performance issues.
But the issues what I had was not actually realated to performance there is something else I noticed behind the scene when I ran sp_who2 I had no clue.

Then I ran select * from sysprocesses where blocked<>0

to give me the list of blocked processed.

Then I noticed GHOST CLEANUP process was constantly running and blocking the other SIPD and using most of the resources.

First lets understand what is Ghost Process or what are Ghost records.

Ghost records are the records which are logically deleted but still they exist physically deleted in a page. Just to give a brief overview, having ghost records simplfies key-range locking and transaction rollback.

The Ghost record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits or until the transaction is committed. As soon as it is committed, records are deleted by an asynchronous background process (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.

To Resolve this issue quickly, I disabled the Auto Create Statistics and Auto Update Statistics options on the database. As soon as I hit the apply button the GHOST CLEANUP process stopped.

To me it looks like when SQL tries to create stats or update them, it will run the Ghost Cleanup process first to cleanup the indexes and tables, before getting the stats.

But don't forget to create a job when you are disabling these tasks on the database. Create a job during non-business hours or when the load on the database is minimum.

Query to Find out Orphaned Users and Fixing them

2012-08-21T08:47:00.000-07:00

When a restore of any database has been performed for example; SERVER A to SERVER B, Users cannot access the database on the SERVER B because they become Orphans even if they have Login ID on the SERVER B, to fix this.

Run this to find out the list of Orphaned Users

EXEC sp_change_users_login 'Report'

After finding the orphaned users report, run the following command to Fix the Orphaned Users.

EXEC sp_change_users_login 'Auto_Fix', 'user'

To create a new login with password for user it can be achieved by running the below command.

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Monitoring SQL Server Database Backups

2012-08-08T12:43:00.005-07:00

This scripts will give you when was the last time database has been backed up.

select    sdb.name as databasename,
        coalesce(convert(varchar(12), max(bus.backup_finish_date), 101),'-') as lastbackuptime
    from
        sys.sysdatabases sdb
left outer join
        msdb.dbo.backupset bus
    on
        bus.database_name = sdb.name

    group by sdb.name

This Script will give you backup file location of a particular Database
** ENTER THE DATABASE NAME **

select physical_device_name,
        backup_start_date,
        backup_finish_date,
        backup_size/1024.0 as backupsizekb
    from
        msdb.dbo.backupset b
join
        msdb.dbo.backupmediafamily m
    on    b.media_set_id = m.media_set_id

where
        database_name = 'yourdb_name'
    order by backup_finish_date desc


Check to see if your database has been backed up from the past 2days or not

select
        databasename = d.name,
        latetstbackupdate = isnull(convert(varchar(30),max(b.backup_finish_date),120),'no backup'),
        'days' = min(datediff(hh,b.backup_finish_date,getdate())) / 24,
        'hours' = min(datediff(hh,b.backup_finish_date,getdate())) % 24
from
        master.sys.databases d
    left outer join
        msdb.dbo.backupset b
    on    d.name = b.database_name
where
        d.name not in ('tempdb','model')
    group by d.name
    having isnull(min(datediff(hh,b.backup_finish_date,getdate())),999) >= 24 * 2-- change the 2 when you want to use a different amount of days
    order by d.name

WAIT Stats

2011-07-21T22:27:00.001-07:00

Query to check WAIT Stats

    select
            db_name(exec_re.database_id) databasename,
            wait_tsk.wait_duration_ms,
            wait_tsk.wait_type,
            exec_txt.text,
            exec_qpln.query_plan,
            wait_tsk.session_id,
            exec_ssion.cpu_time,
            exec_ssion.memory_usage,
            exec_ssion.logical_reads,
            exec_ssion.total_elapsed_time,
            exec_ssion.program_name,
            exec_ssion.status
    from
            sys.dm_os_waiting_tasks wait_tsk

            inner join sys.dm_exec_requests exec_re
                        on
                        wait_tsk.session_id = exec_re.session_id

            inner join sys.dm_exec_sessions exec_ssion
                        on
                        exec_ssion.session_id = exec_re.session_id

            cross apply sys.dm_exec_sql_text (exec_re.sql_handle) exec_txt

            cross apply sys.dm_exec_query_plan (exec_re.plan_handle) exec_qpln

    where
            exec_ssion.is_user_process = 1

TempDB size

2011-07-21T22:25:00.001-07:00

Query to check Temp Db size immediately.

\
SELECT
      [name]                  AS [Logical FILE Name],
      CASE type_desc
            WHEN 'ROWS' THEN 'Data'
            WHEN 'LOG' THEN 'Log'
      END                     AS [FILE Type],
      physical_name           AS [FILE PATH],
      [SIZE]                  AS [FILE SIZE],
      CASE growth
            WHEN 0 THEN 'Enabled'
            ELSE 'Disabled'
      END                     AS [Auto Growth]
FROM tempdb.sys.database_files

Temp DB Full and not able to Shrink? Restart SQL Server is a good option..

2011-06-14T22:12:00.000-07:00

I know there are many different blogs about tempdb, but I just wanted to share what I have experienced in my environment. TempDB was growing very fast and it already grew ~ 120GB but the used space is 100MB, general idea would be to restart the SQL Server services that way new TempDB is re-created.

So I tried first shrinking the files.

DBCC SHRINKFILE ('tempdev', 1024)

DBCC SHRINKFILE ('tempdev2', 1024)
Still not able to shrink
Command(s) completed. It is of no use hmmm interesting. 

tempdb_ID 
SELECT database_id,*,session_id FROM sys.dm_exec_requests it will give you session ID and by default I believe database_id for tempDB is 2. 
Now we will see if there are any locks or open transactions by running (DBCC OPENTRAN) on tempBD

SELECT * FROM sys.dm_tran_locks where resource_database_id= 2
and to check open transactions you can also write query 
SELECT * FROM sys.dm_exec_requests WHERE database_id = 2
I verified no locks and no open transactions. 

Now I have seen all the tables in tempDB
SELECT * FROM tempdb..sysobjects 
This may be due to many reasons, may be a developer or user has run a query or 
Stored Proc that caused tempDB grow and ran out of space, by creating cached objects on tempDB. When a table is created in Cache they are TRUNCATED instead DELETE because these tables can be reused when the stored procedure is run again. 

So now what do you do? 
Clean Procedure Cache or Restart SQL Server Services? And of-course stored procedures have to be recompiled if you clean up Procedure cache, that is going to be a different topic.  But in my case I simply Cleared Procedure cache in SQL Server 2005 by executing 
DBCC FREEPROCCACHE WITH NO_INFOMSGS (this is used to prevent information messages from begin displayed) there by allowing my tempdb to shrink it to bare minimum. 

In sql server 2008 
Gives you plan_handle and cached entries that are reused
SELECT
             plan_handle, 
             objtype, 
             usecounts,
             cacheobjtype, 
             objtype, 
             text 

FROM 
            sys.dm_exec_cached_plans 

CROSS APPLY 
             sys.dm_exec_sql_text(plan_handle) 

WHERE
             usecounts > 1 

ORDER BY
              usecounts DESC;
Gives you memory breakdown of all cached compiled plansSELECT 

        plan_handle, 

        ecp.memory_object_address AS CompiledPlan_MemoryObject, 

        omo.memory_object_address, 

        pages_allocated_count, 

        type, 

        page_size_in_bytes 

FROM 

        sys.dm_exec_cached_plans AS ecp 

JOIN    sys.dm_os_memory_objects AS omo 

    ON 

        ecp.memory_object_address = omo.memory_object_address 

    OR    ecp.memory_object_address = omo.parent_address

WHERE 

        cacheobjtype = 'Compiled Plan';

So I took the plan_handle and cleared only that particular cache there by allowing me shrink TempDB
You can clear any specific plan from cache there by allowing tempdb to shrink and gain back space and avoid ing SQL Server services restart.
DBCC FREEPROCCACHE (0x0600040057AFE42740A14D80000000000000000000000000);

Cannot connect to WMI provider.Invalid namespace[0x80041010]

2011-06-12T21:53:00.000-07:00

Few days before I had experienced issue with the WMI not able to connect as shown in the below figure.

"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with the SQL Server Configuration Manager. Invalid namespace[0x80041010]") when you install a 32-bit version of Microsoft SQL Server 2008 on 64-bit machine and you install an 64-bit version of SQL Server 2008 on the same machine. If you uninstall any instances you will receive the error message when you open SQL Server Configuration Manager.

When you install sql server 2008 WMI provider also gets installed and if 2005already existed on the machine then WMI provider is common for both versions, Soif you uninstall SQL Server 2008 it deletes WMI Provider. This file is located in the %programfiles(x86)% folder.
The WMI (Windows Management Instrumentation) provider is a published layer that is used with the SQL Server Configuration Manager snap-in for Microsoft Management Console (MMC) and the Microsoft SQL Server Configuration Manager. It provides a unified way for interfacing with the API calls that manage the registry operations requested by SQL Server Configuration Manager and provides enhanced control and manipulation over the selected SQL Server services. The SQL Server WMI Provider is a DLL and a MOF file, which are compiled automatically by SQL Server Setup.
The resolution is, open command prompt run as ADMINISTRATOR.
mofcomp "%programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"
Note: The location may be different as shown in the figure below.

DMV sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type

2011-06-07T21:54:00.000-07:00

This Query gives the current processes running in SQL Server. Also if you want to know what is stored in the Cache or what is running .

Once this is run you will get the following info.

wait_duration_ms – Means current wait of the query which is run at that time.
wait_type – Means the current wait type of the query
text – Has the Query in TEXT FORMAT
query_plan – Gives you the Query plan which could be very helpful to find some missing inxexes, etc and all that fun stuff.
Session_id, and all others(Columns) are pretty much self explanatory.

    select
            db_name(exec_re.database_id) databasename,
            wait_tsk.wait_duration_ms,
            wait_tsk.wait_type,
            exec_txt.text,
            exec_qpln.query_plan,
            wait_tsk.session_id,
            exec_ssion.cpu_time,
            exec_ssion.memory_usage,
            exec_ssion.logical_reads,
            exec_ssion.total_elapsed_time,
            exec_ssion.program_name,
            exec_ssion.status
    from
            sys.dm_os_waiting_tasks wait_tsk

            inner join sys.dm_exec_requests exec_re
                        on
                        wait_tsk.session_id = exec_re.session_id

            inner join sys.dm_exec_sessions exec_ssion
                        on
                        exec_ssion.session_id = exec_re.session_id

            cross apply sys.dm_exec_sql_text (exec_re.sql_handle) exec_txt

            cross apply sys.dm_exec_query_plan (exec_re.plan_handle) exec_qpln

    where
            exec_ssion.is_user_process = 1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED NO LOCK

2011-06-06T12:54:00.000-07:00

Everyone undergoes a blocking or locking issues when one forgets to write
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED NO LOCK or what ever...

In order to avoid such situation and which can also save a lot of time, you can setup READ UNCOMMITTED Option From SSMS>Tools>Options> Query Execution SQL Server> Advanced as shown below. also there is whole bunch of options available where you can play with ..

Setting up Isolation Level

Linked Server errors Microsoft SQL Server, Error:7411)

2011-06-05T16:06:00.000-07:00

Server 'ServerName' is not configured for DATA ACCESS (Microsoft SQL Server, Error: 7411)

This occurs if any of the following are not configured, even if you are trying to security.
Not be Made,be made without using a security context, login;s security context and using security context.
Data access, RPC, RPC Out, Use Remote collation has to be configured in-order to set up linked server without having 7411 error.