Archive for the ‘SQL Server’ Category
Posted by Jugal Shah on March 7, 2012
Problem
As a best practice in the industry, a DBA often has two logins that are used to access SQL Server; one is their normal Windows login and the other is an admin level login account which has sysAdmin rights on the SQL Server boxes. In addition most of the time the SQL Server client tools are only installed on the local desktop and not on the SQL Server Production Box. In order to use the different login to connect to SQL Server using SSMS you need to use the “Run as” feature. What do you do in the case of Windows 7 or Windows Vista where you can’t find the Run As Different User option.
Solution
http://www.mssqltips.com/sqlservertip/2617/how-to-use-runas-command-for-ssms-if-option-does-not-exist/
Posted in SQL Server | Leave a Comment »
Posted by Jugal Shah on March 5, 2012
Posted in SQL Server | Leave a Comment »
Posted by Jugal Shah on March 4, 2012
One of my blog reader has asked how to create the database with different collation. You can create the database with different collation using below script.
create database SQLDB collate Latin1_General_CS_AS;
go
create database SQLDB1 collate Latin1_General_CI_AS;
go
SELECT DATABASEPROPERTYEX('SQLDB', 'Collation') SQLDB;
SELECT DATABASEPROPERTYEX('SQLDB1', 'Collation') SQLDB1;
Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Collation, http://sqldbguru.blogspot.com/, Jugal Shah, SQLDBPool.com, T-SQL Script | Leave a Comment »
Posted by Jugal Shah on March 2, 2012
Posted in SQL Server | 2 Comments »
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 March 2, 2012
Problem
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages.
Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.</code
Solution
http://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/
Posted in SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Jugal Shah, Login Issue, Orphan Login, Orphan User, SQLDBPool, T-SQL Script | Leave a Comment »
Posted by Jugal Shah on February 28, 2012
It is a best practice to keep the SQL Agent Job owner to SA or Service Account. Please don’t make the job owner to any user’s windows ID. If the employee left the company his ID will be terminated and access will be revoked.
You can find out the jobs owner by executing the below query.
SELECT
j.name,
l.[name] as [JobOwner]
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
You can change the job owner by executing the below query.
EXEC MSDB.dbo.sp_update_job
@job_name = 'Job Name',
@owner_login_name = 'sa'
Posted in SQL Server, SQL Server 2008, SQL Server 2008 R2 | Tagged: change job owner, job owner, Jugal Shah, MSDB, sql agent, SQL Scripts, SQLDBPool | Leave a Comment »
Posted by Jugal Shah on February 25, 2012
You can check the SQL Server is cluster or standalone using one of the below technique.
Technique 1: Go to server properties from SSMS and check the Is Clustered property.
Technique 2: Execute the below query, it will retun Boolean value. (1 = True and 0=False)
SELECT
CONVERT(char(20),
SERVERPROPERTY(‘IsClustered’))
Posted in SQL Server | 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
You can follow below steps to capture the deadlock graph using profiler. First we will setup the profiler and deadlock events and later on we will run the deadlock scenario.
Step 1: Open the SQL Profiler. You can start the SQL Profiler from the SSMS.
Step 2: Configure the trace, in General tab give the name to trace file.
Step 3: Select the below events from the Event Selection tab and Run the trace.
Deadlock Graph
Deadlock Graph event captures deadlock in both XML format and graphically, a graph that shows us exactly the cause of the deadlock.
Lock:Deadlock
This event is fired whenever a deadlock occurs.
Lock:Deadlock Chain
This event is fired once for every process involved in a deadlock.
Step 4: Run the deadlock scenario queries as per http://sqldbpool.com/2012/02/12/steps-to-create-the-deadlock-scenario/ article.
Step 5: You can see the below graph once the deadlock occurred.
Posted in SQL Server | 1 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 »
