Archive for May, 2012

To amend the default path for the SSIS package store amend this file on the server:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

<Folder xsi:type=”FileSystemFolder”>

<Name>File System</Name>



For Meta data tables users can see records for each object they have permission for.

So if I have 10 tables and I deny access to one of them for you, you will only see nine items if you run Select * from sys.Objects

You should be able to deny access through a different route:

This will deny view definition to all objects

USE Master;

GRANT  EXECUTE ON xp_readerrorlog TO [DomainName\AD USER or Group];

They should now be able to run this:

exec xp_readerrorlog;

If you don’t want to give SysAdmin to developers on a test or live server you can apply these permissions:

Use Master;


GRANT SHOWPLAN TO [Domain\ADGroup or User];

GRANT ALTER TRACE TO [Domain\ADGroup or User];

as per this article:

We had a deadlock on a SQL 2008 r2 server but did not have the deadlock trace flags running. Luckily we now have Extended Events available for deadlocks.

To investigate I ran this code (taken from a post By Jonathan Kehayias

select CAST(
REPLACE(XEventData.XEvent.value(‘(data/value)[1]’, ‘varchar(max)’),

”, ”),
as xml) as DeadlockGraph
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = ‘system_health’) AS Data
CROSS APPLY TargetData.nodes (‘//RingBufferTarget/event’) AS XEventData (XEvent)
where XEventData.XEvent.value(‘@name’, ‘varchar(4000)’) = ‘xml_deadlock_report’;

Which gave be a deadlock graph. Actually it gave a malformed deadlock graph because we need to patch the server (

To fix this I replaced the final </deadlock> with </victim-list> although you may not need to do this.

The graph gave me these bits of information (the info for both was the same (other than SPids etc) because it was the same stored proc running twice which cause the deadlock.))

<process id=”process50934c8″ taskpriority=”0″ logused=”568″ waitresource=”PAGE: 9:4:8453″ waittime=”2822″ ownerId=”73869146″
transactionname=”INSERT” lasttranstarted=”2012-05-04T18:15:41.003″ XDES=”0x17d4aa3b0″ lockMode=”IX” schedulerid=”12″
kpid=”5132″ status=”background” spid=”35″ sbid=”0″ ecid=”0″ priority=”0″ trancount=”2″>
<frame procname=”” line=”120″ stmtstart=”9448″ stmtend=”11092″ sqlhandle=”0x030009002a854154312dff0045a000000100000000000000″ />
<frame procname=”” line=”1″ stmtstart=”102″ sqlhandle=”0x010009009c37861ef05ac675050000000000000000000000″ />
<frame procname=”” line=”1″ sqlhandle=”0x0400ff7fbe80662601000000000000000000000000000000″ />
<frame procname=”” line=”78″ stmtstart=”4870″ stmtend=”5260″ sqlhandle=”0x030009000c8ae7465a93010145a000000100000000000000″ />
<frame procname=”” line=”34″ stmtstart=”1774″ stmtend=”1888″ sqlhandle=”0x030009009b87944d7ab3ff00489f00000100000000000000″ />
<inputbuf />

I took the sql handles and ran them here:

FRoM sys.dm_exec_sql_text(0x01000900953c3516b086df9f020000000000000000000000);
FRoM sys.dm_exec_sql_text(0x0400ff7fbe80662601000000000000000000000000000000);
FRoM sys.dm_exec_sql_text(0x030009000c8ae7465a93010145a000000100000000000000);
FRoM sys.dm_exec_sql_text(0x030009009b87944d7ab3ff00489f00000100000000000000);

It then listed three different stored procs. Only had an Insert candidate but I wanted to confirm it.

Back to the graph:
<pagelock fileid=”4″ pageid=”8453″ dbid=”9″ objectname=”” id=”locked7e4900″ mode=”S” associatedObjectId=”72057594040614912″>

From this I can get the object that was blocked, either by this:

dbcc traceon (3604); –needs to be on to display the results of dbcc page
dbcc page ( 9, 4, 8453 , 0);

Partial result set:

name object_id type type_desc
RealNameWithheld 133575514 U USER_TABLE

I confirmed this result by running this:

FROM sys.partitions
WHERE partition_id = 72057594040614912;

Using the associatedObjectId from the Deadlock graph

The result: SameTableName

Back to the grap to look at this:
<pagelock fileid=”4″ pageid=”8453″ dbid=”9″ objectname=”” id=”locked7e4900″ mode=”S” associatedObjectId=”72057594040614912″>
<owner id=”process3ce748″ mode=”S” />
<waiter id=”process50934c8″ mode=”IX” requestType=”convert” />
<pagelock fileid=”4″ pageid=”8453″ dbid=”9″ objectname=”” id=”locked7e4900″ mode=”S” associatedObjectId=”72057594040614912″>
<owner id=”process50934c8″ mode=”S” />
<waiter id=”process3ce748″ mode=”IX” requestType=”convert” />

In the stored procedure there was this code:

INSERT INTO RealNameWithheld (Column Listing)
SELECT A mix of variables and column names
FROM RealNameWithheld
WHERE request_id = ISNULL(@LastRequestID, -1)

The table being inserted into is the same as the table being used in the Select. The Select is asking for the shared lock (S) the insert is asking for an exclusive lock (IX – intent exclusive).

Both procedures ran at almost the same time – 3 milliseconds apart – and so both acquired a shared lock at almost the same time. They both then tried to acquire the exclusive lock but to do that they needed to wait for the other to relinquish its shared lock. A normal Select would eventually return its results and drop the lock. In this case it wouldn’t drop the lock as it is waiting to escalate it.

This problem doesn’t occur often so we can either leave it as it is. To fix it we could add a query hint to the SELECT part of the statement – with (TabLock). With this hint the first query to reach the table gains an exclusive lock on the table. The subsequent query will then wait.

In other circumstances we could use (NoLock) but that would be too risky on this table where the data is being updated frequently.

A work around: First select into a temporary table. Then use the temporary table to insert back into the original. This will be slightly slower but won’t cause the deadlock.

SSMS TSQL Short Cuts

Posted: May 3, 2012 in SSMS

SSMS allows you to create your own shortcut keys:
Under the Menu Tools Select Options
Under Environment >> Keyboard there is a list of shortcut commands.
A Couple are pre-populated Alt_F1 sp_help; Ctrl+1 sp_who; Ctrl+2 sp_lock;
I have added the following

ctrl+F1 master..xp_fixedDrives;
Shows how much space is available on the fixed drives

Ctrl+3 Select * from sys.databases;
Lists all databases

Ctrl+4 SELECT name AS ‘File Name’ , physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’ FROM sys.database_files;
Shows how much space is available in SQL Data files – uses the context of the current database.

Ctrl+5 DBCC SQLPERF(logspace);
Shows TLog sizes and space used.

Ctrl+6 dbcc Loginfo();
Each row represents a Virtual Log File.

Ctrl+7 Exec xp_msver;
Details server edition etc

Ctrl-8 SELECT database_name, backup_start_date, name, [description], [user_name], [type] FROM msdb.dbo.backupset where type != ‘l’ order by backup_start_date desc; Show backup history (not TLogs)

Ctrl+0 EXECUTE xp_instance_regRead N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’BackupDirectory’; EXECUTE xp_instance_regRead N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’defaultData’; EXECUTE xp_instance_regRead N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’defaultLog’;
Shows the default file locations for Data, Logs, and Backups