MSSQLWIKI

Karthick P.K on SQL Server

Archive for January, 2009

How to find who altered my SQL Server Login

Posted by Karthick P.K on January 25, 2009

Do you know how to find who changed SQL Server login or Password?

Here is the way…..

SELECT [Transaction SID],suser_sname([Transaction SID]) as ‘Login Name’   FROM ::fn_dblog(default, default)   WHERE [Transaction Name]=’ALTER LOGIN’

 

 

Thanks

Karthick P.K

Posted in DBCC, Security, SQL General | 3 Comments »

How to Browse (or) view objects and there code in mssqlsystemresource Database

Posted by Karthick P.K on January 15, 2009

How to Browse (or) view mssqlsystemresource Database.

1. Stop the SQL Server service
Copy the mssqlsystemresource.MDF and mssqlsystemresource.LDF to a new path (This
two files will be in same path where master databse is located).
Start the SQL Server Service
Ues the following command to attach the data and log file as a new user database.

EXEC sp_attach_db
‘mssqlsystemresource _Copy’, ‘<Path where you copied>\resource_copy.mdf’,
‘<Path where you copied>\resource_copy.ldf’
Now you browse through all the system objects and there code.

OR

1. Start SQL Server in single user mode.

2. Open SSMS and connect using DAC (ADMIN) Connection and change DB context to “mssqlsystemresource”.

use mssqlsystemresource

3. Query Resource DB objects.

Regards

Karthick P.K

Posted in Configuration, SQL General, SQL Server Engine | Tagged: , , | 3 Comments »

SQL Server: Table Variables (VS) Temp Tables

Posted by Karthick P.K on January 15, 2009

Table Variables (VS) Temp Tables

SQLServer2005 caches temp tables and temp variables only under some conditions.
Scenarios where temp table/variable are not cached (see below) may cause performance degradation as compared to SQLServer2000.

Following are scenarios where temp table/variable are not cached:
1. select into #t
2. alter table #t
3. create index on #t
4. Global temp tables (##t)
5. Local temp tables on adhoc level (nest level 0)
6. table variables are also not cached for dynamic SQL.

What are some of the drawbacks of table variables?

These are some of the drawbacks as compared to temporary tables:

Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement.

Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

The table definition cannot be changed after the initial DECLARE statement.
Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was
created outside the EXEC statement or the sp_executesql stored procedure because table variables can be referenced in their local scope only, an EXEC statement and
a sp_executesql stored procedure would be outside the scope of the table variable.

However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table
variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because temporary or permanent tables
are maintained in a database that resides on the physical disk and also logged?

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store
data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are
created and processed while in memory (data cache).

Do I have to use table variables instead of temporary tables?

The answer depends on these three factors:
The number of rows that are inserted to the table and are they joined with other tables.
The number of recompilations the query is saved from.
The type of queries and their dependency on indexes and statistics for performance.

 

Option recompile can help optimizer to estimate the number of rows table variable  refer http://blogs.msdn.com/b/psssql/archive/2010/08/24/query-performance-and-table-variables.aspx 

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in Memory, Performance, SQL Query, SQL Server Engine | Tagged: , , , | 8 Comments »

Behavior of SQL Server default instance on a NON-Default port

Posted by Karthick P.K on January 15, 2009

Unable to connect to SQLServer listening on Non-Default Port?

If you connect to SQLServer using the “hostname” it connects to the instance which is listening on 1433.

If you make named instance listen on 1433 and connect using “hostname” it connects to named instance which is listening on 1433.

if you connect to SQLServer using hostname or ip-address (without “\instance name”) it connects to instance which is listening on 1433, it can be named instance as well.(We have a belief that it connects to default instance because default port of default instance is 1433).

When you are connecting to SQLServer using hostname (without “\instance name”) client will not communicate with SQL Browser.
SQLClient would communicate with SQLBrowser only if there is “\instancename”  followed by host name.

Dynamic port detection is only available for named instances of SQL Server 2000.
The behavior of SQL Server 2000 for a default instance is exactly the same as in earlier versions of SQL Server. The network libraries assume either 1433 or the
global default port established with the Client Configuration Utility.

If a default instance is listening on a port other than the standard 1433 port, you can provide an alias or change the global default port. You can also connect to the
instance of SQL Server by using its server name, its FQDN, or its IP address followed by a comma and the port number.

The mdac rules :

If you connect to a Named Instance, then you need to either specify the Server\Instance or Server,Port.
If you change the default port number for the default instance, then you need to specify Port number as well.

Regards

Karthick P.K

Posted in Configuration, Connectivity | Tagged: , , , | 16 Comments »

Unable to start SQLServer agent resource on cluster after upgrading to 9.00.3186 or Higher

Posted by Karthick P.K on January 14, 2009

SQLServer agent resource fails to come online on cluster after upgrading to build 9.00.3186 or Higher

 

Error

2008-03-19 23:06:23 – ? [100] Microsoft SQLServerAgent version 9.00.3186.00 (x86
unicode retail build) : Process ID 2428
2008-03-19 23:06:23 – ? [101] SQL Server SNETNAME version 9.00.3186 (0 connection
limit)
2008-03-19 23:06:23 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-19 23:06:23 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is
2008-03-19 23:06:23 – ? [310] 4 processor(s) and 2560 MB RAM detected
2008-03-19 23:06:23 – ? [339] Local computer is SNETNAME running Windows NT 5.2
(3790) Service Pack 2
2008-03-19 23:06:23 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-19 23:06:38 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-19 23:06:38 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-19 23:06:38 – + [260] Unable to start mail session (reason: No mail profile
defined)
2008-03-19 23:06:38 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-19 23:06:38 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-19 23:06:39 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146893813, ‘Key not valid for use in specified state.’ [SQLSTATE 42000]
2008-03-19 23:06:39 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-19 23:06:40 – ? [098] SQLServerAgent terminated (normally)
Error2

2008-03-18 12:18:30 – ? [100] Microsoft SQLServerAgent version 9.00.3200.00
((Unknown) unicode retail build) : Process ID 6512
2008-03-18 12:18:30 – ? [101] SQL Server PISTONDIST version 9.00.3200 (0 connection
limit)
2008-03-18 12:18:30 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-18 12:18:30 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is np:pistondist
2008-03-18 12:18:30 – ? [310] 16 processor(s) and 32765 MB RAM detected
2008-03-18 12:18:30 – ? [339] Local computer is PISTONDIST running Windows NT 5.2
(3790) Service Pack 2
2008-03-18 12:18:31 – ? [432] There are 11 subsystems in the subsystems cache
2008-03-18 12:18:31 – ! [364] The Messenger service has not been started – NetSend
notifications will not be sent
2008-03-18 12:18:31 – ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-18 12:18:32 – + [396] An idle CPU condition has not been defined – OnIdle
job schedules will have no effect
2008-03-18 12:18:32 – + [408] SQL Server MSSQLSERVER is clustered – AutoRestart has
been disabled
2008-03-18 12:18:32 – + [162] Internal request (from SetJobNextRunDate [reason:
schedule will not run again]) to deactivate schedule 66
2008-03-18 12:18:32 – ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146892987, ‘The requested operation cannot be completed. The computer must
be trusted for delegation and the current user account must be configured to allow
delegation.’ [SQLSTATE 42000]
2008-03-18 12:18:32 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-18 12:18:33 – ? [098] SQLServerAgent terminated (normally)

 

Resolution

Modify the the following Key.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
Modify the value data of the serverhost key to np:Virtualservername

Ie:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\SQLServerAgent
ServerHost
Value: np:Virtualservername.

This will force the SQLServer agent to connect with SQLserver using Named Pipes so
delegation is not used.

—————————————————————————————————————–

We have a HOTFIX available for this issue and it is included in the cumulative update pack9 for SQLServer service pack2. http://support.microsoft.com/?id=956378
Note: Before applying the Hotfix. you have to follow the  steps mentioned in Resolution else hotfix would fail. Revert the steps after applying the fix.

 

Regards

Karthick PK

Posted in Configuration, Connectivity, SQL Cluster Setup, SQL Server Cluster | Tagged: , , , , , | 2 Comments »

How to enable Constraint delegation for SQLServer2005

Posted by Karthick P.K on January 14, 2009

Constraint delegation for SQLServer2005

Follow the below steps to set the constraint delegation for SQLServer2005

1. In active directory users and computers for the startup account of SQLServer
a.Select “Trust this user for delegation to specified services
only”
b.Add host service for each node of the cluster
2. In active directory users and computers for each node under the delegation
tab
a. Select “Trust this computer for specified services only”
b. Add CIFS and Protected storage for each node of the cluster and each DC which
SQLserver may use to authenticate.

Take the SQLServer Group offline in clutser administrator and bring it online.

Please refer the attached document for additional information with Image and example   ConstraintDelegationforSQLServer2005

Regards

Karthick PK

Posted in Configuration, SQL Server Cluster | Tagged: , , | 10 Comments »

 
%d bloggers like this: