Posted by Jugal Shah on April 28, 2012
Problem
Recently I experienced a situation where a SQL Server login failed with this error message:
Error: 18456, Severity: 14, State: 10.
Login failed for user ‘SQL_Login’. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XXX.XXX.XXX].
Can you help me decipher this SQL Server issue and correct it? Check out this tip to learn more.
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Error 18456, Login Failure, Severity 14, State 10, sys.dm_os_ring_buffers, sys.ms_ticks | 1 Comment »
Posted by Jugal Shah on March 2, 2012
Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.
Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”
SA login account and the few policy certificates also come in SQL Login type, I have excluded them.
select * from sys.server_principals where name = 'sa' or substring(name, 1, 1) = '#'
select 'DROP LOGIN ' + [name] from sys.server_principals where type_desc = 'SQL_LOGIN' and sid <> 0x01 and substring(name, 1, 1) <> '#'
Next step is to execute the output of the above query.
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Drop Login, SQL Login, SQL Server, SQLDBPool, T-SQL Script | Leave a Comment »
Posted by Jugal Shah on February 16, 2012
You can use the below scripts to store/save the file into SQL Server database table. Please note it is not recommended to store file into database. You can store the file on file system and path in the database.
use sqlDBPool --documents table will store files into varbinary field --drop table documents create table documents ( documentID int identity(1,1), doctype char(5), document varbinary(max) ) --script to store/save document into table insert into documents Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document go select * from documents
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: How to store image into database, Jugal Shah, save file into table, SQL Script, SQLDBPool, store, T-SQL | 1 Comment »
Posted by Jugal Shah on February 12, 2012
Problem: Take an example you are writing a script which is going to be deployed on the all the versions of SQL Servers and you want to check the SQL Server version details using T-SQL code. Below solution will guide you how to check the SQL Server version in stored procedure/t-sql batch.
Solution: You can use the @@MICROSOFTVERSION to get the SQL Server version information. If the output of the below script is 9 than its SQL 2005, if 10 than SQL Server 2008 and if 11 than SQL Server 2011
select @@VERSION --method - 1 select @@MICROSOFTVERSION as MSVersion, CAST (@@MICROSOFTVERSION as BINARY(5)) as MsVersionInBinary -- Remove the first non-zero character after 0x0 from binary output here it is A and divide the @@MicrosoftVersion outout select substring(cast(@@MICROSOFTVERSION/0x000000640 as varchar(10)),1,2) as MsSQLVersion --Method 2 select @@MICROSOFTVERSION / POWER(2,24) as usingPowerFunctionMSSQLVersion
Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: @@MICROSOFTVERSION, How to check SQL Version, Jugal Shah, SQL Script, SQLDBPool, T-SQL | Leave a Comment »
Posted by Jugal Shah on February 12, 2012
A deadlock occurs when two or more processes permanently block each other by each process having a lock on a resource which the other process are trying to lock.
Please execute the below queries as per the mentioned comments to produce a deadlock.
--turning on the traceflag to record deadlock info into error log dbcc traceon(1204,-1) dbcc tracestatus(1204) --creating test database create database sqlDBPool --Connecting to SQLDBPool database use sqldbpool --table creation create table tb1 (col1 int) create table tb2 (col1 int) --inserting dummy records insert into tb1 values(1),(2),(3) insert into tb2 values(1),(2),(3) --Open first connection to update table explicit transaction begin transaction update tb1 set col1 = 5 --Open second connection to update table explicit transaction use sqlDBPool begin transaction update tb2 set col1 = 6 update tb1 set col1 = 6 --Open first connection to update table explicit transaction update tb2 set col1 = 5
You can see the one of the transaction will fail with the below error message.
Msg 1205, Level 13, State 45, Line 3
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
As we have turned on the deadlock trace flag, you can see the below information in the SQL Server error log.
Starting up database 'sqlDBPool'.
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1
RID: 9:1:153:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 1:
Owner:0x05684480 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x065F82A8
SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update tb2 set col1 = 5
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)
NULL
Node:2
RID: 9:1:155:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 2:
Owner:0x067679A0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x05A8CC38
SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 3
Input Buf: Language Event: begin transaction update tb2 set col1 = 6 update tb1 set col1 = 6
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x065F8280 Mode: U SPID:52 BatchID:0 ECID:0 TaskProxy:(0x0941A354) Value:0x6a943a0 Cost:(0/432)
NULL
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)
Posted in Database, DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Deadlock, deadlock scenario, Deadlock trace flag, Jugal Shah, Script, SQLDBPool, T-SQL | 2 Comments »
Posted by Jugal Shah on February 11, 2012
Sp_Configure procedure is used to display or change the SQL Server setting. Once you execute the SP_Configure procedure it will display the below columns in the output.
name – Name of the configuration parameter
minimum – Minimum value setting that is allowed
maximum – Maximum value that is allowed
config_value – value which currently configured
run_value – value which currently running
How to update the configuration value?
Here I will show you how to enable the XP_CmdShell using SP_Configure. Please note don’t update configuration values until you are sure, otherwise it will affect the your SQL Server performance and behavioral.
--XP_Cmdshell is an andvanced option, enbale the advanced option EXEC sp_configure 'show advanced options', 1 GO --Enable the advance option RECONFIGURE GO --enable the xp_cmdshell EXEC sp_configure 'xp_cmdshell', 1 GO --Reconfigure the xp_cmdshell value RECONFIGURE GO
What is the difference between Config_Value and Run_Value?
When we change the Configuration Parameter value as above it will update the Config_Value filed only, but wouldn’t be in effect until you run reconfigure command. Once the reconfigure command execute or SQL Server restarted, SQL Server will run as per the new configured value.
You can get the description of the configuration parameters from books online or you can query sys.configurations and check for the description column.
select
*
from
sys.configurations
Output of the Sp_Configure
|
Name |
Minimum | Maximum | Value | Run Value |
|
access check cache bucket count |
0 |
16384 |
0 |
0 |
|
access check cache quota |
0 |
2147483647 |
0 |
0 |
|
Ad Hoc Distributed Queries |
0 |
1 |
0 |
0 |
|
affinity I/O mask |
-2147483648 |
2147483647 |
0 |
0 |
|
affinity mask |
-2147483648 |
2147483647 |
0 |
0 |
|
Agent XPs |
0 |
1 |
1 |
1 |
|
allow updates |
0 |
1 |
0 |
0 |
|
awe enabled |
0 |
1 |
0 |
0 |
|
backup compression default |
0 |
1 |
0 |
0 |
|
blocked process threshold (s) |
0 |
86400 |
0 |
0 |
|
c2 audit mode |
0 |
1 |
0 |
0 |
|
clr enabled |
0 |
1 |
0 |
0 |
|
common criteria compliance enabled |
0 |
1 |
0 |
0 |
|
cost threshold for parallelism |
0 |
32767 |
5 |
5 |
|
cross db ownership chaining |
0 |
1 |
0 |
0 |
|
cursor threshold |
-1 |
2147483647 |
-1 |
-1 |
|
Database Mail XPs |
0 |
1 |
0 |
0 |
|
default full-text language |
0 |
2147483647 |
1033 |
1033 |
|
default language |
0 |
9999 |
0 |
0 |
|
default trace enabled |
0 |
1 |
1 |
1 |
|
disallow results from triggers |
0 |
1 |
0 |
0 |
|
EKM provider enabled |
0 |
1 |
0 |
0 |
|
filestream access level |
0 |
2 |
0 |
0 |
|
fill factor (%) |
0 |
100 |
0 |
0 |
|
ft crawl bandwidth (max) |
0 |
32767 |
100 |
100 |
|
ft crawl bandwidth (min) |
0 |
32767 |
0 |
0 |
|
ft notify bandwidth (max) |
0 |
32767 |
100 |
100 |
|
ft notify bandwidth (min) |
0 |
32767 |
0 |
0 |
|
index create memory (KB) |
704 |
2147483647 |
0 |
0 |
|
in-doubt xact resolution |
0 |
2 |
0 |
0 |
|
lightweight pooling |
0 |
1 |
0 |
0 |
|
locks |
5000 |
2147483647 |
0 |
0 |
|
max degree of parallelism |
0 |
64 |
0 |
0 |
|
max full-text crawl range |
0 |
256 |
4 |
4 |
|
max server memory (MB) |
16 |
2147483647 |
2147483647 |
2147483647 |
|
max text repl size (B) |
-1 |
2147483647 |
65536 |
65536 |
|
max worker threads |
128 |
32767 |
0 |
0 |
|
media retention |
0 |
365 |
0 |
0 |
|
min memory per query (KB) |
512 |
2147483647 |
1024 |
1024 |
|
min server memory (MB) |
0 |
2147483647 |
0 |
0 |
|
nested triggers |
0 |
1 |
1 |
1 |
|
network packet size (B) |
512 |
32767 |
4096 |
4096 |
|
Ole Automation Procedures |
0 |
1 |
0 |
0 |
|
open objects |
0 |
2147483647 |
0 |
0 |
|
optimize for ad hoc workloads |
0 |
1 |
0 |
0 |
|
PH timeout (s) |
1 |
3600 |
60 |
60 |
|
precompute rank |
0 |
1 |
0 |
0 |
|
priority boost |
0 |
1 |
0 |
0 |
|
query governor cost limit |
0 |
2147483647 |
0 |
0 |
|
query wait (s) |
-1 |
2147483647 |
-1 |
-1 |
|
recovery interval (min) |
0 |
32767 |
0 |
0 |
|
remote access |
0 |
1 |
1 |
1 |
|
remote admin connections |
0 |
1 |
0 |
0 |
|
remote login timeout (s) |
0 |
2147483647 |
20 |
20 |
|
remote proc trans |
0 |
1 |
0 |
0 |
|
remote query timeout (s) |
0 |
2147483647 |
600 |
600 |
|
Replication XPs |
0 |
1 |
0 |
0 |
|
scan for startup procs |
0 |
1 |
0 |
0 |
|
server trigger recursion |
0 |
1 |
1 |
1 |
|
set working set size |
0 |
1 |
0 |
0 |
|
show advanced options |
0 |
1 |
1 |
1 |
|
SMO and DMO XPs |
0 |
1 |
1 |
1 |
|
SQL Mail XPs |
0 |
1 |
0 |
0 |
|
transform noise words |
0 |
1 |
0 |
0 |
|
two digit year cutoff |
1753 |
9999 |
2049 |
2049 |
|
user connections |
0 |
32767 |
0 |
0 |
|
user options |
0 |
32767 |
0 |
0 |
|
xp_cmdshell |
0 |
1 |
1 |
1 |
Posted in DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: Configuration Parameters, How to enable XP_CmdShell, Jugal Shah, SP_Configure, SQL Script, SQLDBPool, T-SQL | 1 Comment »
Posted by Jugal Shah on February 3, 2012
Execute below query to get TOP 25 completed queries which have the highest cumulative CPU usage
SELECT highest_cpu_queries.plan_handle, (highest_cpu_queries.total_worker_time/highest_cpu_queries.execution_count) AS AverageCPU, highest_cpu_queries.execution_count, highest_cpu_queries.total_worker_time, highest_cpu_queries.plan_generation_num, highest_cpu_queries.creation_time, highest_cpu_queries.last_execution_time, highest_cpu_queries.last_physical_reads, highest_cpu_queries.min_physical_reads, q.dbid, q.objectid, q.number, q.encrypted, q.[text] FROM (SELECT TOP 25 qs.plan_handle, qs.total_worker_time, qs.last_execution_time, qs.plan_generation_num, qs.creation_time, qs.execution_count, qs.last_physical_reads, qs.min_physical_reads FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q ORDER BY AverageCPU DESC
Above script will return the queries which are completed. You can check the active sessions and CPU details using below query.
SELECT SPID, CPU, s2.text, open_tran, status, program_name, net_library, loginame FROM sys.sysprocesses CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 where cpu > 5000 and status = 'runnable'
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: Jugal Shah, Performance Tuning, SQL Optimization, SQL Scripts, SQLDBPool, TSQL | 2 Comments »
Posted by Jugal Shah on January 18, 2012
Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.
Solution: You can check the list of port number used by the SQL Server instances using one of the below way.
Soln 1# Using SQL Server Configuration Manager
Soln 2#From Registry Values
SQL Server 2005
Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll
SQL Server 2008
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Soln 3# Error Log
Query the error log as below to get the port number.
EXEC xp_readerrorlog 0,1,”Server is listening on”,Null
Soln 4# Command Prompts
Execute the below command from the command prompt.
Netstat -abn
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Default Port Number, SQL Troubleshooting, T-SQL Script, Unable to Connect | 3 Comments »
Posted by Jugal Shah on November 18, 2011
DAC: Dedicated Administrator Connection feature is available from the SQL Server 2005. It is available in all the higher editions by default except express edition. DAC will be useful when SQL Server is not responding any connections; in such kind of situation DBA will connect through the DAC and troubleshoot/fix the issue.
You can execute below kind of command for the initial troubleshooting.
-- Locking Info SELECT * FROM sys.dm_tran_locks GO -- Running Sessions SELECT * FROM sys.dm_exec_sessions GO -- Requests Status SELECT * FROM sys.dm_exec_requests GO --Open Sessions SP_WHO2 --To get the SQL Text DBCC OPENTRAN (SPID) --To terminate the curlprit process KILL SPID
DAC is disabled by default, it is a good practice to enable the DAC. You can enable the DAC using by executing below query.
Use master GO --0 = Allow Local Connection, --1 = Allow Remote Connections*/ sp_configure 'remote admin connections', 1 GO RECONFIGURE GO
You can connect using DAC on of the following method.
Command Prompt
SQL Server Management Studio
Using Command Prompt: Use SQLCMD utility to connect to SQL Server as below.
-A argument is to specify the DAC connection.
-S argument is to specify the server name.
-d argument is to specify the database name.
-E argument is for windows connection with integrated security true
Using Management Studio: Write ADMIN: before the server name in management studio connection window. It will give you the DAC connection.
To enable the DAC connection in SQL Server express edition add ;-T7806 trace flag as startup parameter.
Go into configuration manager — right click on SQL Server Service and select properties — go into advanced tab and add the trace flag ;-T7806. Once done restart the SQL Server Services.
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: Database, Dedicated Administrator Connection, FAQs Help and Tutorials, Microsoft, Microsoft SQL Server, Select (SQL), SQL, SQL Server Express, SQL Server Management Studio | Leave a Comment »
Posted by Jugal Shah on November 17, 2011
Heap Table: Table without a clustered index is called Heap Table. You can find out the Heap Table by querying the sys.indexes or sys.partitions against the index_id column.
select OBJECT_NAME(object_id),* from sys.indexes where index_id = 0 Select OBJECT_NAME(object_id),* from sys.partitions where index_id = 0
Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Heap Table, Index, Jugal Shah, SQL Optimization, SQL Script, SQLDBPool | Leave a Comment »
Posted by Jugal Shah on October 10, 2011
At the start up of SQL Server records the startup parameter details into the error log.
You can query the error log to check the error log file physical location.
USE master GO xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' GO
Posted in SQL Scripts | Leave a Comment »
