WAIT Stats

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

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..

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 plans SELECT         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]

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

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

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)

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.

Granting access to all objects in a database role.

This is my favorite script to grant access to database objects, specifically for development team. It would be tough to manage permission sometimes by creating individual accounts and granting access to database objects directly. This is much better way to grant permission hope this helps. Copy past the complete query and you have to edit few portions of the script as necessary.


DECLARE
        @LoginName VARCHAR(128),
        @DBUserRole VARCHAR(128),
        @DatabaseName VARCHAR(100),
        @SQLScript VARCHAR(6000)

SET @LoginName = 'Login\login'  -- Windows Login DOMAIN\Username
SET @DBUserRole = 'ROLE_for_developers' -- This role is for developer access to the objects

IF EXISTS (SELECT * FROM master..syslogins WHERE [name] = @LoginName)
BEGIN
        DECLARE DatabaseCursor CURSOR FOR
                SELECT [name] FROM master..sysdatabases
                WHERE  name in  ('database1','database2')  -- or you can just specify one database name = ''
ORDER BY [name]
        OPEN DatabaseCursor
        FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

        WHILE @@FETCH_STATUS = 0
                BEGIN

                        PRINT 'Updating Database:  ' + @DatabaseName + ''

                        SET @SQLScript = '
                                USE ' + @DatabaseName + '
                                IF NOT EXISTS (SELECT * FROM ' + @DatabaseName + '..sysusers WHERE [name] = ''' + @DBUserRole + ''')
                                        EXEC sp_addrolemember '''+ @DBUserRole + ''',''' + @LoginName + ''''
                                                     
                                                          
                        PRINT @SQLScript
                        --EXEC (@SQLScript)

                        -- Modify script below to change object types or type of permissions: U = User table, etc.
                        SET @SQLScript = '
                        USE ' + @DatabaseName + '
                        DECLARE
                                @TableName VARCHAR(100),
                                @SQLScript VARCHAR(4000),
                                                @type varchar(20)

                        DECLARE TableCursor CURSOR FOR
                                SELECT [name], type FROM ' + @DatabaseName + '..sysobjects WHERE type in (''U'',''V'',''P'',''FN'',''SN'',''TF'') ORDER BY [name]
                        OPEN TableCursor
                        FETCH NEXT FROM TableCursor INTO @TableName,@type
                        WHILE @@FETCH_STATUS = 0
                                BEGIN
                                                      set @SQLScript =  Case
                                                                                    when @type = ''U'' then ''GRANT SELECT,References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type =''V'' then ''GRANT SELECT,References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''P'' then ''GRANT Execute, References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''FN'' then ''GRANT Execute, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                    when @type = ''TF'' then ''GRANT Select, References, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''                           
                                                                                    when @type = ''SN'' then ''GRANT Select, Execute, View Definition ON '' + @TableName + '' TO ' + @DBUserRole + '''
                                                                                End
                                      PRINT @SQLScript
                                        --EXEC(@SQLScript)

                                        FETCH NEXT FROM TableCursor INTO @TableName,@type
                                END
                        CLOSE TableCursor
                        DEALLOCATE TableCursor'

                     --PRINT @SQLScript
                       EXEC (@SQLScript)

                        FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

                END

        CLOSE DatabaseCursor
        DEALLOCATE DatabaseCursor
END
ELSE PRINT 'Login ' + @LoginName + ' does not exist.'

-Pramod K

Installing Service Pack on Active/Pasive Cluster Windows 2008 SQL Server 2008

Hi all,

This is really interesting to learn how to apply service pack on Windows 2008 SQL Server 2008 Active/Pasive Clustered environment.

Here we are following the method of applying Service pack on Pasive node first and move the resources to the active node and once service pack is applied on Pasive node do the vice versa. Follow the below notes and link to watch the video. 

Please click the links to watch the videos.

SP on Active/Pasvie Cluster 1
SP on Active/Pasive cluster 2


I have the below notes from the video, it will be helpful.

Before you go further please take a note of the Version on the SQL Server it will change once you apply Service pack. So in-order to verify the installation make a note of the version.
node#1 active
node#2 passive(install on passive node first)

1-  In cluster manager - right click sql server network cluster name - properties - advanced - then uncheck the     passive node#2(this is because we dont want it to fail to this node during the install since it can corrupt binaries)
2- install SP on passive node#2 (make sure sql services stopped on this node, etc)
3- once installed  manual failover services from node 1 to node 2 and update SP on node 1
 steps: A- SQLserver network cluster name advanced and re-select node 2 to add it back in. 
        B- In Cluster Manager move services(current owner)over to node#2 (now that its been patched)   
        C- Now remove node#1 by right clicking  sql server network cluster name - properties - advanced then uncheck node#1
4- install SP 1 on node#1
5- After install complets on node#1 we need to set owners again. Right click sqlserver network cluster name properties  - advanced and re-select node#1 to add it back in.
6- Fail services back to primary node and start services.

Before moving the resources between the nodes open SSMS and verify that the service pack has been applied.

-- David M and Pramod K

Copy files by passing Variables in batch file.

For regular DBA activities, we should also be aware of some Windows commands as well.
We will be copying files from one location to another for which batch file would be easier way to do so without commiting mistakes.

Open notepad copy the below script and save it as a batch file (.BAT) extension.

@echo off
echo "Enter Source"
set /p source=
echo "Enter Destination"
set /p destination=
xcopy %Source% %destination%

run the batch file.

Also to extend this, if you want to copy only specific date of file or files that are new in any particular folder use

xcopy %Source% %destination% /D:00:01 by default its value is 1day older to the current date.
xcopy the command that is used to copy file.

Cannot create New Database Diagram?

Check the owner of the database and change it to 'sa' .
If you want to create or see database diagram, yes the owner of the database have to be 'sa'.

Cannot Rename Computer or Server ?

Tip from my at work dad :) , when I say work dad yes he is my BOSS.

If you have SQL installed on computer or on the server you cannot Rename it, because it is stored in the registries. And you cannot clean registires its going to mess up everyting.
Work Around is you have to unistall SQL and do the rename.

How to Clear drop down list of Recent Connection from SQL Server Management Studio ?

http://sqlserver-training.com/how-to-clear-recent-sql-server-connection-list-from-ssms/-

Most Recent User Server List in SSMS drop down menu is known as MRU list.
SQL Server 2005

1. Make sure that the "SQL Server management studio" is not opened
2. Go to "Run"
3. Type this command "%APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\" and click "OK". This will open up the folder where this list is stored.
4. Now search for the file "mru.dat" and rename this file.
5. After renaming, launch the SQL Server management studio and you will not see any of the most recently used servers in the list.

For SQL Server 2008

1. Make sure that the "SQL Server management studio" is not opened
2. Go to "Run"
3. Type this command "%APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\" and click "OK". This will open up the folder where this list is stored
4. Now search for the file "SqlStudio.bin" and delete / rename this file.
5. After deleting, launch the SQL Server management studio and you will not see any of the most recently used servers in the list.