Privileges needed to run SQL Server Profiler without sysadmin privileges

 

In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.

It can be accomplished by,

GRANT ALTER TRACE to [Login]

BOL explanation:

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission

References: Permissions Required to Run SQL Server Profiler

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;

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

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

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

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.

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

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

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.

Learn SQL Server 2008 for free

 

Microsoft makes it easier than ever to attend SQL Server 2008 training, at little to no cost, without any travel necessary. 

It just needed a email account on hotmail or msn.

Subject matter includes:

· Database Administration

· Database architecture

· Database, programming and security. 

Click the courses below to see full descriptions or visit Microsoft Learning to browse the course catalog

DBA Track

Course 6339: Database Fundamentals in Microsoft SQL Server 2008

Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform

Collection 6187: What’s New in Microsoft SQL Server 2008

Developer Track

Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

Clinic 10164: Essential SQL Server 2008 for Developers

Clinic 10258: SQL Server 2008: Upgrade and Application Compatibility

Reporting Service

Clinic 6258: New Features Of Microsoft SQL Server 2008 Reporting Services

Business Intelligence

Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence

DAC – Dedicated Administrator Connection

 

Use Command below to connect as DAC

SQLCMD -A

 

It is on by Default for local server.

For remote(via network) enable it by running query below

sp_configure 'remote admin connections'

 

For Clusters, recommended to enable remote admin connections as no local DAC available

 

---- Query to Find if DAC - Dedicated Admin Connection used -----   select s1.session_id, s1.program_name,s1.host_name,t1.name from sys.tcp_endpoints t1 join sys.dm_exec_sessions s1 on t1.endpoint_id = s1.endpoint_id where t1.name = 'Dedicated Admin Connection' --- End of the Query -----

--- Query to check the DAC ---- --- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) --- select * from sys.dm_os_schedulers

 

Usage:

when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection

• Use DAC sparingly for monitoring and Troubleshooting
  
• Connect to master database first
  
• Use lightweight queries.
  
• Not advised for long running queries like ( DBCC CHECKDB, DBREINDEX, INDEXDEFRAG )
  

SQL Server Default Collation

Technorati Tags: SQL Server Setup

 

SQL Server Default Collation

SQL Server Latest Service Pack and Security Hot fixes

Applies to	Version Range	Type	Description	Target Version	Release Date	Security Bulletins	KB	Download
SQL Server 2000	Below 8.00.2039	Service Pack	SQL Server 2000 SP4	8.00.2039	6-Jun-05	MS03-031	KB884525	http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
SQL Server 2000	8.00.2039-8.00.2054	GDR	SQL Server 2000 SP4 GDR	8.00.2055	10-Feb-09	MS09-004	KB960082	http://www.microsoft.com/downloads/details.aspx?familyid=d5bb816a-6e1a-47cb-92be-51c565ee184c&displaylang=en
SQL Server 2000	8.00.2148-8.00.2281	QFE	SQL Server 2000 SP4 QFE	8.00.2282	10-Feb-09	MS09-004	KB960083	http://www.microsoft.com/downloads/details.aspx?familyid=A93F3CFE-18C9-4218-A551-13BF415E418A&displaylang=en
SQL Server 2005	Below 9.00.4035	Service Pack	SQL Server 2005 SP3	9.00.4035	15-Dec-08	N/A	KB955706	http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en
SQL Server 2005	9.00.4035-9.00.4052	GDR	SQL Server 2005 SP3 GDR	9.00.4053	12-Oct-09	MS09-062	KB970892	http://www.microsoft.com/downloads/details.aspx?familyid=0d878f4b-71e8-4170-9a14-1bce684811ce&displaylang=en
SQL Server 2005	9.00.4205-9.00.4261	QFE	SQL Server 2005 SP3 QFE	9.00.4262	12-Oct-09	MS09-062	KB970894	http://www.microsoft.com/downloads/details.aspx?familyid=e6f307c1-8b21-406e-9c6f-b1a3a1e9a98f&displaylang=en
SQL Server 2008	Below 10.00.2531	Service Pack	SQL Server 2008 SP1	10.00.2531.00	27-Aug-09	N/A		http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en
	It lists only the latest SQL Server Service Pack and Security Hot fixes as on 22nd April 2010

Software Asset Management - SAM

Software Inventory Assistance

Whether we have just a few computers or a large network, a software inventory tool can make it easier to quickly know what we have and how it's being used.

The Microsoft Software Inventory Analyzer (MSIA) is a free tool available for download that can help us with our software inventory. It is built specifically to be a starting point to working with SAM.

We can use the MSIA to scan and inventory the Microsoft software that is installed on a single computer, or on multiple computers throughout a network. It generates a report that provides details of all installed Microsoft products, including the type and the number of licenses. The MSIA will work with networks that have 250 or fewer computers. The latest MSIA 5.1 features enable you to:

• Identify and report all installed Microsoft products listed under Add or Remove Programs.
  
• Consolidate the MSIA report with the Microsoft Licensing Statement (MLS) report (.xls only).
  

This information is useful for you, if you are taking care of the system administration, licensing and inventory of Microsoft software products in your organization.

BgInfo

BgInfo – Microsoft Windows SysInternal tool

How many times have you walked up to a system in your office and needed to click through several diagnostic windows to remind yourself of important aspects of its configuration, such as its name, IP address, or operating system version If you manage multiple computers you probably need BGInfo. It automatically displays relevant information about a Windows computer on the desktop's background, such as the computer name, IP address, service pack version, and more. You can edit any field as well as the font and background colors, and can place it in your startup folder so that it runs every boot, or even configure it to display as the background for the logon screen.

SQL Server 2005/ 2008 Query to find when the database last used

Below is the TSQL Query to find when the database last accessed for SQL Server 2005 / 2008

-- Query to find when the database last accessed on SQL Server 2005 / 2008 --- select d.name, x1 = (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

 

 

 

Differentiate your connections to SQL Instances by coloring it

What would be the case. have you ever connected to a server, thought it was a testing or development system, and only a split second after you pressed "F5" to run that command, realized that it was the production server? Yeah….me neither :).

To make sure that's never you, when you start SQL Server 2008 Management Studio (SSMS), stop for a moment on the connection dialog and press "Options". You'll see that you can select a color for the connection – I set mine to red for production servers, yellow for testing servers, and green for development servers.

Once inside SSMS, you can also set the default color for all connections – I do this and set them all to red, so that if I forget, at least I'll have that visual indicator to make sure the change I'm making is on the right server.

It helps on classifying the SQL Server Instances by Production, Development, Test or QA etc.

Format Date or Time without “/”

Format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Output
select replace(convert(varchar, getdate(),111),'/','')	20100413
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	20100413 004426
Select cast(int, convert(char,getdate(),111))	20100413

Remote Desktop Error - The terminal server has exceeded the maximum number of allowed connections

When trying to connect using a remote desktop session you may get this Login Failure Message, because there are too many remote sessions

To resolve the exceeded connection issue execute "mstsc /admin" ( mstsc /console )command from the command prompt as shown below

If a terminal server has exceed the maximum number of sessions an "/admin" session can still be created and this will allow you to still connect to the server.

The /admin sessions don't count towards the session limit that may be configured on a terminal server to limit the number of remote sessions, so this will give you a back door into the server if you get the message "The terminal server has exceeded the maximum number of allows connections".

SQL Server - Patching : An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

The SQL Server team is moving away from the current priority-driven hotfix release model to a scheduled delivery model. In the scheduled delivery model, a customer can receive a hotfix to address their most critical situations with a short turn-around time. Additionally, a customer can receive a fix that has undergone more testing and that is released on a schedule basis. Therefore, the SQL Server team has created the following delivery mechanisms.

Critical On Demand - COD	On Demand- OD	Cumulative Update - CU	General Distribution Release - GDR
The hotfix is released on or before a mutually agreed upon date based on the customer's need	The hotfix is released on or before a mutually agreed upon date based on the customer's need	The update is released every 2 months.	A GDR addresses an issue that has a broad customer impact, that has security implications, or that has both. A GDR is determined and issued by Microsoft as appropriate and when appropriate. GDRs are kept to a minimum.
		It contains all previous critical on-demand hotfixes to date.

 

ISM eliminates the need for hotfix rollup releases. The first CU after a new release contains the hotfix rollup, plus any additional QFEs.

A hotfix package does not replace a service pack. A hotfix package is optional. A hotfix package can be installed or uninstalled at any time. Additionally, hotfix packages are cumulative. Therefore, the latest OD hotfix package or CU hotfix package includes all previously released hotfixes.

Quick Fix Engineering - QFEs are used for the majority of fixes where the effects of the problem are not widespread or severe enough to warrant a GDR.

Can we have database on SIMPLE Recovery model for Transactional Replication?

Yes. We can have Publisher database on SIMPLE recovery model.

The reason is that;

• When executing, the Log Reader Agent first reads the publication transaction log and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data transactions that have been marked for replication. Next, the agent batch copies those transactions to the distribution database at the Distributor.
  
• The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
  
• Only committed transactions are sent to the distribution database by LogReader agent.
  
• After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed.
  
• Finally, the agent marks the rows in the transaction log that are ready to be truncated. Rows still waiting to be replicated are not truncated
  
• The transaction log on the Publisher can be dumped without interfering with replication, because only transactions not marked for replication are purged.
  

SQL Server 2005 Cluster: SQL Server Service not coming online, error on TCP port is already in use

 

SQL Server Error Log:

2010-06-04 15:19:37.520 Server Server is listening on [ 10.0.0.136 <ipv4> 2813].

2010-06-04 15:19:37.530 Server Error: 26023, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.

2010-06-04 15:19:37.530 Server Error: 17826, Severity: 18, State: 3.

2010-06-04 15:19:37.530 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2010-06-04 15:19:37.530 Server Error: 17120, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event Viewer Error

Event Type: Error

Event Source: MSSQL$PROD2K5ISD

Event Category: (2)

Event ID: 26023

Date: 4/06/2010

Time: 2:51:31 PM

User: N/A

Computer: PROD2K5ISD

Description:

Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

Resolution

The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid