HelpFull Querys -1

--- QUERY STATUS 

select T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
       , R.cpu_time, R.total_elapsed_time, R.percent_complete
from   sys.dm_exec_requests R
       cross apply sys.dm_exec_sql_text(R.sql_handle) T --where  db_name(R.database_id)='tempdb'
order by Command


---- TEMPDB SHRINK

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
dbcc shrinkfile (tempdev,5000)
go

--Troubleshoot if any opentran or locks or allocation

 

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

select * from sys.dm_tran_locks where resource_database_id= 2

select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0

 

SELECT * FROM tempdb.sys.all_objects where is_ms_shipped = 0

 --if the above Query has objects it will clears by running freeproccache

 

--Some time cache prevents it to shrink

 

DBCC FREESYSTEMCACHE ('ALL')

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

 

 

Find Nth Highest sal of emp

The following solution is for getting 5th highest sal from emp table ,

SELECT TOP 1 sal FROM (
SELECT DISTINCT TOP 5 sal
FROM emp ORDER BY sal DESC) a
ORDER BY sal

General Syntax to Find Nth Sal, N is the variable

SELECT TOP 1 sal FROM (
SELECT DISTINCT TOP n sal FROM emp
ORDER BY sal DESC) a
ORDER BY sal
where n > 1

Note :-n is always greater than one

Create user for all existing databases

USE master;

DECLARE
    DBNames CURSOR
FOR
    SELECT
        NAME
    FROM sysdatabases

OPEN DBNames

DECLARE @Name varchar(50)

FETCH NEXT FROM DBNames
INTO @Name

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    Declare @SQL varchar(255)
    Print 'Processing: ' + @Name
    BEGIN TRY
       Select @SQL = ' USE ' + @Name + ' CREATE USER [ldbamonitor] FOR LOGIN ["LOGINNAME HERE"] EXEC sp_addrolemember N''db_datareader'', N''ldbamonitor'''
       EXEC(@SQL)
    END TRY
    BEGIN CATCH
        select ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    FETCH NEXT FROM DBNames    INTO @Name
END

CLOSE DBNames
DEALLOCATE DBNames

Database status is shutdown, normal mode

SQL Database stats Shutdown,Normal

If you got your "database status is shutdown, normal mode".

Run the below query for reset the database status to Normal mode.

ALTER DATABASE dbname SET AUTO_CLOSE OFF

Now the database should be Normal mode.

Cannot connect to WMI provider.You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.

Step 1:- Verify which version of sql server 2005/2008
Step 2:- Go to Shared folder on C drive "C:\Program Files\Microsoft SQL Server\90\Shared"
Step 3:- Verify the following file present in the folder
sqlmgmproviderxpsp2up.mof

Step 4:- If missing copy from other instance folder
Step 5:- Register as shown below,

C:\>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared
\sqlmgmproviderxpsp2up.mof"


SOLVED Problem.

Check the network address name and that the ports for the local and remote endpoints are operational.(Microsoft SQL Server, Error: 1418)

 Change the SQL Server from local service to Service Sccount.

http://www.youtube.com/watch?v=9v0OW2WfoIo

RESTORE,BACKUP PERCENTAGE

SELECT command, s.text, start_time, percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','DbccFilesCompact')
OPTION (RECOMPILE);