DBPedias

Your Database Knowledge Community

Jack Vamvas

  1. Cannot connect to SQL Server

    Question: I cannot connect to Sql server remotely from management studio , It is the correct User name and password.

     Cannot connect to xxxx.xxxx.xxxx.xxxx

     ===================================

     A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

     Answer:  Depending on the exact problem – some of these tests will help

    a) Ping test  - Can you ping to the server?

    b) Telnet test  - Can you make a telnet connection connection?

    c) Are the SQL services started  ?

    d) Can you connect from the server where the SQL Server is based?

    e) Checked the Authentication Mode settings on SQL Server. Are you using the correct setting? The setting must be in Mixed Mode to accept SQL usernames and passwords.

    f) SQL Server Versions – For example, You cannot connect from SSMS 2005 to SQL Server 2008. Make sure the client and server are compatible.

    If you’re connecting through a client , ensure the drivers support the version.

    g) Check Server network configuration and make sure the correct protocols are used, such as Named Pipes or TCP\IP.

    h) Read the SQL Server Error 26 troubleshooting checklist

  2. The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time

    Question: This SQL Server Error message is appearing on a SQL Server Production Server Error Logs :

     “The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.”

    How can I troubleshoot this error ?

    Answer: Each lock that SQL takes out uses up a small amount of memory.  Upper limit on locks can be set , but the default is to set dynamic lock escalation.

    This message is triggered when : a) When a single Transact-SQL statement acquires 5,000 locks on a single table or index.

    b) When the number of locks in an instance of the Database Engine exceeds memory  i.e 60% of Max server memory) or SQL Server lock configuration thresholds.

    1) step is to identify the queries running at the time of this message. Analyse these queries a) Can they be tuned for lesser locks b) Can they be tuned to run in a shorter time? 

     2)  Check for excessive fragmentation levels on tables?  Check SQL Index Fragmentation and sys.dm_db_index_physical_stats

     3) Does the instance have enough memory ?    Check SQL Server memory configuration management

     

     

  3. How to create a SQL Server Security Audit

    Question:  I’d like to create  a regular SQL Server security audit. This information will be scanned by an internal audit tool . Could you recommend a list of SQL Server procedures to cover users and objects?

    I’ve already ready through the post : Powershell sql server security audit ,  but I’d like to focus on objects and user privileges 

    Answer:   These queries and procedures cover the basics of user and object level privileges.

    Gather these recordsets and  run the rules over the data regularly. Highlight any  disparities with the SQL Server Security Policy

     

    --Contains one row for each logon account.
--The system views are : sys.sql_logins , sys.server_principals , but use this one. 
select * from    sys.syslogins


--Reports the login security configuration of Microsoft® SQL Server™
--Is a deprecated feature 
exec xp_loginconfig

--Returns version information about Microsoft SQL Server
exec xp_msver

--Returns one row for each table privilege that is granted to or granted by the current user in the current database. 
Exec sp_table_privileges  '%'

--Returns information about the roles in the current database.
--sp_helprole for every database
exec sp_helprole

--Reports information about database-level principals in the current database
--sp_helpuser for every database 
exec sp_helpuser

--Returns the physical names and attributes of files associated with the current database. 
--Use this stored procedure to determine the names of files to attach to or detach from the server. 
--sp_helpfile for every database
exec sp_helpfile

--Returns a report that has information about user permissions for an object, or statement permissions, in the current database. 
--sp_helprotect for every database
exec sp_helprotect

     

  4. Compare two files with Powershell

    Question: How can I compare two files and report on differences using Powershell ? I have two large T-SQL scripts , which both parse successfully on SSMS , but give me different results

    Answer:   Comparing two files is a common DBA task. Use a  combination of  Powershell Get-Content and Compare-Object.

    Get-Content gets the content of the file at the specified location

    Compare-Object compares the two items and reports on the differences

     Create two variables and use Get-Content to assign the values. Compare the two variables using Compare-Object . Output the results to a file for analysis.

     

    $strPrimary = Get-Content C:\primary.sql
$strSecondary = Get-Content  C:\secondary.sql
Compare-Object $strPrimary $strSecondary >C:\compareResults.txt



##Results
InputObject                                                 SideIndicator                                              
-----------                                                 -------------                                              
 GROUP BY mem.[first]                                         =>                                                         
                                                            =>                                                         
 GROUP BY mem.[last]                                        <=

  5. Export-CSV Powershell

    Question: How can I export a Powershell recordset to a CSV file format, so I can then open the file in MS Excel?

    Answer:   Powershell has the Export-CSV cmdlet . The Export-CSV cmdlet creates a CSV  file  of the script output.  The script needs to pipe the results to the Export-CSV cmdlet – with a filename parameter.

    I add –notype to the Export-CSV cmdlet , as I prefer to not include the .Net object type in the first line

    Get-Process | Where-Object {$_.PrivateMemorySize -gt 100MB} | SELECT ProcessName, PrivateMemorySize | Export-CSV -notype c:\privatememory.csv

     

    See Also

    SQL Server – Powershell Excel to HTML

  6. SDD_Service error and vmware multipath

    Question:

    I started seeing the SDD_Service  migrating physical servers to Virtual Servers (VMWare 4.1), as part of Virtualization and database servers  project

     

    7011

    Server1

    Error

    Timeout (30000 milliseconds) waiting for a transaction response from the SDD_Service service.

    Service Control Manager

    02/04/2012 11:10:42

     

    What is the cause and how can I fix the SDD _Service Error?

     Answer:

    The Subsystem Device Driver is a pseudo device driver designed to support the multipath configuration environments in the IBM TotalStorage Enterprise Storage Server.

    Deleting the SDD service resolved the issue. The multipath is managed by the ESX host

  7. CREATE INDEX and STATISTICS_NORECOMPUTE

    Question : When a CREATE INDEX  is executed on an existing table with rows in it, is it  required to execute a UPDATE STATISTICS or will CREATE INDEX  update the stats?  Does the STATISTICS_NORECOMPUTE effect whether statistics are updated on a INDEX rebuild?


    Answer: The STATISTICS_NORECOMPUTE setting doesn’t determine  whether statistics re refreshed by this index rebuild. An index rebuild refreshes index statistics. It can’t be disabled.

    STATISTICS_NORECOMPUTE controls whether auto-update of stats is ON|OFF. The auto_update is a database option.It is possible to have the option ON|OFF for specific indexes

    CREATE TABLE Guitarists
(guitaristID INT,
guitarist_name NVARCHAR(100))

INSERT INTO Guitarists
SELECT 1, 'Hendrix'
UNION ALL
SELECT 2, 'Santana'

CREATE INDEX x1 ON Guitarists(guitarist_name)
--Stats:Up-to-date
DBCC SHOW_STATISTICS('Guitarists', 'x1') WITH HISTOGRAM

UPDATE Guitarists SET guitarist_name = 'Hendrix' WHERE guitarist_name = 'Santana'
DBCC SHOW_STATISTICS('Guitarists', 'x1') WITH HISTOGRAM
--Stats:out-of-date

ALTER INDEX x1 ON Guitarists REBUILD
DBCC SHOW_STATISTICS('Guitarists', 'x1') WITH HISTOGRAM
--Stats:Up-to-date

UPDATE Guitarists SET guitarist_name = 'Prince' WHERE guitarist_name = 'Hendrix'
DBCC SHOW_STATISTICS('Guitarists', 'x1') WITH HISTOGRAM
--Stats:out-of-date

ALTER INDEX x1 ON Guitarists REBUILD WITH(STATISTICS_NORECOMPUTE = ON)
DBCC SHOW_STATISTICS('Guitarists', 'x1') WITH HISTOGRAM
--Stats:Up-to-date

SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('Guitarists')

     See Also

    SQL Server sp_updatestats and UPDATE STATISTICS

    Auto Update Statistics and Update Statistics in SQL Server

  8. Who made DDL table changes on the database.

    Question:

    Some DDL changes have occurred on the SQL Server database. Can I find out who made the changes?

    Answer:

    Yes. The SQL Server default trace has the Object Altered event. 

    Read this FAQ for details on SQL default trace

    This script will list all Object Altered event. Add WHERE predicates on date and databasename to refine  the search and isolate the DDL change

     

    select e.name as eventclass,t.loginname, t.spid, t.starttime, 
t.textdata, t.objectid, t.objectname, t.databasename, 
t.hostname, t.ntusername, 
t.ntdomainname, t.clientprocessid, t.applicationname, t.error 
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
inner join sys.trace_events e on t.eventclass = e.trace_event_id
where eventclass=164

  9. Modify a SQL CHECK CONSTRAINT

    Question: How do I modify a SQL Server CHECK CONSTRAINT?

    Answer : To modify a CHECK constraint, you must first delete the existing CHECK constraint and then re-create it with the new definition

    An example

     

    ALTER TABLE dbo.MYTABLE DROP CONSTRAINT  MYTABLE_contsraint_name

 ALTER TABLE dbo.MYTABLE WITH CHECK ADD  CONSTRAINT  MYTABLE_contsraint_name 
CHECK ( Partition_Key >=  Minimum_table_month  AND Partition_Key

  10. Error 26073 and TCP connection closed

    Question : The  “error 26073 - TCP connection closed but a child process of SQL Server may be holding a duplicate of the connection's socket” appears in the SQL Server Error Logs. How can I fix it ?

    Answer:  This issue occurs because SQL Server Native Client 10.0 handles a TCP close call on a socket incorrectly

    When an application connects to SQL Server 2008 or SQL Server 2008 R2 by using Microsoft SQL Server Native Client 10.0, you receive a "Non-yielding Scheduler" error intermittently.

    Check the current SQL Server version . An alternative method is SQL Server Version with Powershell 

    PRINT @@version

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) 
	Sep 16 2010 19:43:16 
	Copyright (c) 1988-2008 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2)

     

    Options

    1) Install latest cumulative update

    For SQL Server 2008 R2

    http://support.microsoft.com/kb/981356  - The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released

     For SQL Server 2008 Service Pack 2

    http://support.microsoft.com/kb/2402659/LN  The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released

    2) For SQL Server 2008 R2

     The fix for this problem was introduced at cumulative update 6 - http://support.microsoft.com/kb/2489376

    For SQL Server 2008 Service Pack 2 - The fix for this problem was introduced at cumulative update 6 -

  1. 1
  2. Next ›
  3. Last »