Genius

Genius: "http://www.boycottscotland.co.uk/

Please read through the site; it raises some very good points.

U2 - 360 toour

Yeah, the U2 stage is pretty impressive, that'll be more than half a football pitch impressive.

Unblocking your SQL Server

Finding which process is locking up your SQL Server can cause some headaches to people new to DBA activities. Digging deaper into the information that is available and refining the data to inform how you proceed is something that can still cause a well established DBA.

sp_who is a stored procedure that ships with SQL Server. If you run EXEC sp_who from a Management Studio query window then you have a grid of data returned that details the connection details of all activity on your server. This is useful as it shows who is connected to which database and what they are up to. Now this can be a small amount of data on your test server but on a production server could return hundreds of rows. This is also instantaneous, in so far as when you run it next it could all have changed.

I find it useful to get these results into a temporary table so that I can shake it up a little and refer to the same set of results more than once while I am trying to resolve block or some such. To do this you need to get the output of sp_who into the aforementioned temporary table with a section of code like this:

IF OBJECT_ID('tempdb.dbo.#who') > 0
BEGIN
TRUNCATE TABLE #who
END
ELSE
BEGIN
CREATE TABLE #who
(SPID INT ,
ECID INT ,
[Status] NVARCHAR(255) ,
[LOGINNAME] NVARCHAR(255) ,
[HostName] NVARCHAR(255) ,
[Blk] NVARCHAR(255) ,
[DBName] NVARCHAR(255) ,
[Cmd] NVARCHAR(255) ,
[REQUESTID] INT)
END
go
INSERT INTO #who EXEC sp_who ;

You can then recall this information at any time by running
SELECT * FROM [#who]

If your headache is blocking flavoured then running this will show you which SPID is the cause:

SELECT
CAST([w].[Status] AS NVARCHAR(30)) AS [status],
[w].[LOGINNAME] ,
[w].[SPID] ,
[w].[ECID] ,
[w].[HostName] ,
[w].[Cmd] ,
[w].[REQUESTID] ,
[w].[Blk] AS [Blocked by],
[w].[DBName]FROM
[#who] AS w
ORDER BY [DBName], [blocked BY]

The Blocked by column will have a number in it if that process is being blocked. The value in the column is the SPID of the problem process or, if that process itself has a number in its Blocked By column then it too is being blocked. Keep following the trail until you reach a process that has a zero in its Blocked by column but is being shown as blocking a different process.

Using a temporary table means that the information gathered is only available in the single process and will be dropped when you close the query.

I have this script set up as a SQL Snippet in SQL Prompt so that it is readily available in 3 keystrokes. SQL Prompt is an application that provides shortcuts to SQL Editors, making SQL development simpler and faster from Red Gate. Other editors are available and it is possible to create a script template in SSMS if you are not fortunate enough to have any third party tools working for you.

sp_who is documented in Books Online here so please read more about it there. There is also an undocumented version called sp_who2 that is very similar but has even more information regarding each process that is connecting to your databases.

Jaguar Skills x Chris Moyles - TV Theme Double Whammy - 12 August 2009.mp3

zSHARE - Jaguar Skills x Chris Moyles - TV Theme Double Whammy - 12 August 2009.mp3

Shared via AddThis

The Red Gate Incubator

Free food, office space, training and guest speakers, expenses, accommodation and mentoring for 10 weeks ...

Sounds too good to be true?

Not if you know Red Gate. Not only do they make awesome applications to help your average DBA become an awesome DBA they are now running the Springboard Project for the burgeoning software business.

Take a look here: http://springboard.red-gate.com/

Dont thank me, thank Red Gate

Log Parser - the best value 3MB on your hard drive

I was wondering how best to describe LogParser and couldnt come up with anything better than the description in its help file:

• Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®.

They are right. One thing they didnt mention is that its free - the price we all like and can all afford.

To my mind this application works much more intuitively than PowerShell and I use it regularly for grabbing data from all sorts of sources and bringing it into a SQL table for further dissection.

Got problems in a web application and need to review the application log? Then use something like "SELECT * INTO AppLog FROM \\Server\Application".

Want to analyse the security log of a server for failure events? Then use "select top 100 * from \\boa\security where eventtypename = 'Failure Audit Event'"

These pseudo scripts all make immediate sense to a SQL DBA, OK its not exactly like this but the differences wont stop you understanding the script syntax and getting to use more data about your environment.

Lets start off by downloading and installing the application. You get it from Microsoft here:Logparser download.
OK, you should have the application all installed now, along with its comprehensive help documentation.

Start the app and you will get a command-prompt-like window open, titled Log Parser 2.2 and with a stream of BNF description. Now, all Log Parser commands begin with the key word LOGPARSER, followed by the query in double quotes, with any switches and parameters following on behind that. Hitting enter runs the query.

Are you ready for your first Log Parser query? OK, we'll get the names, sizes and last accessed date of files in the root of C:\. Type this into the Log Parser window.

• LOGPARSER "SELECT TOP 10 name, size, lastaccesstime from C:\*.txt" -i:FS

Simple isnt it? We asked for the name, size and lastaccesstime for top ten records from C:\ that have a .txt extension. Just as a little hint to Logparser that we are asking for data from the file system, we set input format with -i:FS. If you want Log Parser to describe this to you itself type in:

• LOGPARSER -h -i:FS

To get a chart illustrating the types of event in your application log from a given date then use

• Logparser "select eventtypename, count(*) into c:\application.jpg from file://machinename/application where timegenerated > '2009-08-12 00:00:00' group by eventtypename" -charttype:column3d

To review the massive list of options on what sort of chart you can have then use this:

• LOGPARSER -h -o:charttype

Thats it for the minute, next time we'll look at getting Log Parser to get data into our database so that we can analyse it further.

LogParser resources:

Download details
http://www.microsoft.com/DownLoads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Support forum
http://forums.iis.net/default.aspx?GroupID=51

Keeping orphans away from your servers

When restoring a database backup to a different server it is wholly possible that there will be security issues when you take the next step and make the system live to the user interface.

Most often in our environment this happens when a live database backup is used to 'refresh' the data in the test system. To do this we take a backup file made from the database on Server A and restore it onto Server B. This also applies however if you have to restore to a new server in the event of a disaster - imagine Server A is on fire and you are under an SLA to get the system live again. First, make sure someone deals with the fire, preferably a trained professional such as someone from the local fire service.

In live the server may have a mixture of users - Windows domain user and group accounts, SQL server logins etc and some of these may have been given permission to the database you are in the process of transferring to a new location. This may not compare exactly with the new server configuration. In order to ensure that once the database is live you move on to dealing with all the security issues that may arise from the above scenario you need to reference the database security and the (new) server security details.

In SQL 2008 Database user details are stored in sys.database_principals; Server login details are stored in sys.server_principals.

In SQL 2005 Database user details are stored in sysusers; Server login details are stored in sys.syslogins.

The script below can be switched between the two versions simply by swapping the table names accordingly as the columns used are in both versions.

In order to view the users that have come with the database and dont exist on the new server we need to find them with:

SELECT
[dp].name AS [Orphan DB User]
FROM
[sys].[database_principals] AS dp
LEFT JOIN sys.[server_principals] AS sp
ON dp.[name] = [sp].name
WHERE
( [dp].[sid] IS NOT NULL
AND [sp].[name] IS NULL
)
AND CAST([dp].[sid] AS BIGINT) < 0

These users are in the database but dont have a server security principal to link to. Thats the "LEFT JOIN" and "[sp].[name] IS NULL" working it out for us.

Next are the database users that might have similar named principals on the server but if the SIDs for them dont match SQL Server will consider them different accounts and stop access to the database. This is a great GOTCHA - using SSMS you see the server login and then in the database you see the user, apparently identical. Only when you compare SIDs are the differences there to be seen. These occurrences are found with:

SELECT
[sp].[name] AS [SQLServerLogIn] ,
[sp].[SID] AS [SQLServerSID] ,
[dp].[sid] AS [DatabaseSid]
FROM
sys.server_principals AS sp
INNER JOIN [sys].[database_principals] AS dp
ON
sp.[name] = dp.[name]
WHERE
[dp].[sid] <> sp.sid
AND [sp].[sid] < 1

Now this is OK but we can combine the two queries to have a concise list of security changes that we need to review:

SELECT
NULL AS ['Split' login] ,
NULL AS [Server Principal SID] ,
[dp].[sid] AS [DB Principal SID] ,
[dp].name AS [Orphan DB User]
FROM
[sys].[database_principals] AS dp
LEFT JOIN sys.[server_principals] AS sp
ON dp.[name] = [sp].name
WHERE
( [dp].[sid] IS NOT NULL
AND [sp].[name] IS NULL
)
AND CAST([dp].[sid] AS BIGINT) < 0
UNION ALL
SELECT
[sp].[name] ,
[sp].[SID] ,
[dp].[sid] ,
NULL
FROM
sys.server_principals AS sp
INNER JOIN [sys].[database_principals] AS dp
ON
sp.[name] = dp.[name]
WHERE
[dp].[sid] <> sp.sid
AND CAST([sp].[sid] AS BIGINT) < 0

We filter out SQL Server roles and system principals such as Guest, sa, public etc by CASTing the SIDs to a BIGINT and checking its a -ve value.


Next time we will review what to do with these accounts and how to get them enabled for the database in its new location.