Saturday, May 29, 2010

Query to find when the table last accessed on SQL Server 2005 and above

 

------ Query to find when the User Table Last accessed ---------

WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
    MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

------ Query to find when the Database Last accessed ---------

SELECT name, last_access =(
                select X1= max(bb.xx)
                from ( select xx =
                        max(last_user_seek)
                        where max(last_user_seek)is not null
                        union all
                        select xx = max(last_user_scan)
                        where max(last_user_scan)is not null
                        union all
                        select xx = max(last_user_lookup)
                        where max(last_user_lookup) is not null
                        union all
                        select xx =max(last_user_update)
                        where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
group by d.name

Deny specific user from Remote Login to SQL Server

SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or 127.0.0.1).

This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger


 

CREATE TRIGGER Deny_Remote_Login_Login1_trigger

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'Login1' AND

(SELECT * FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'Login1' --- Checking the given login

and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely

and Session_ID = @@SPID )

BEGIN

PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally'

ROLLBACK;

END

END;


 

Tuesday, May 18, 2010

SQL Server 2005 Uninstall Error: The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error: 2147749896 (0x80041008).

 

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008).

clip_image002

clip_image004

Resolution: Copy and run the DOS batch file below

@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End
:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer
:SkipSrv
goto End
:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt
:End

Thanks to: http://social.msdn.microsoft.com/Forums/en/Vsexpressinstall/thread/632ca405-1c38-405b-9ed3-01785c9f99d1

Monday, May 17, 2010

How to remove the subscription from Publication in SQL Server Replication

 

1. On SQL Server 2000 connect to Enterprise Manager
On SQL Server 2005 / 2008 connect to SQL Server Management studio

2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication

3. Right click on the Publication

4. Select the Subscription tab

clip_image002

5. Select the Subscriber and click Delete button, that’s it

6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.
clip_image004

7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.

clip_image006

Tuesday, May 04, 2010

How to apply Service pack on SQL Server 2005 Cluster

 

1. Backup of all the System + User Database

2.Windows Login account with "Local Administrator" privileges on all the nodes involved

3. Ensure all the recourses part of the SQL Server Cluster resource group are online.
   I recommend to reboot both nodes before installation. (to avoid pending reboot, hanging installers, terminal sessions etc. problems.)

4. Cross check if the cluster failover on the nodes involved successfully.

5. shut down all applications that use SQLServer (because you know the instance(s) will get offline at some point(s) during the upgrade)

6. Disable if any DDL Triggers

7. Verify if following services online on the nodes involved.

  • Task Scheduler
  • Com+ System Application Service
  • Cryptographic Services
  • Remote Registry Service 

8. Pause Database Mirroring

Log on the active node, apply the Service pack. ( It is opposite in case of SQL Server 2008 ! ). You cannot install SP3 from any passive nodes in the failover cluster.

Please note SP will not update the client tools on the passive node. you need to do that manually; though it is cluster aware.

Reference: 

How to install SQL Server 2005 SP3

SQL Server 2005 SP3 Release Notes

SQL Server 2008 failover cluster rolling patch and service pack process

Saturday, May 01, 2010

SQL Server 2005 Service Pack 4

SQL Server 2005 Service Pack 4 ( SQL Server 2005 SP4 ) will be released by end of the year 2010 ( Quarter 4 of 2010 ). It will be the last Service pack for the SQL Server 2005.


SQL Server 2008 Service Pack 1 ( SQL Server 2008 SP1 ) will be released by third quarter of the year 2010 ( Quarter 3 of 2010 ). It will be the last Service pack for the SQL Server 2005.


Source: Shared by the Microsoft SQL Server release services group manager.