Skip to main content


Showing posts from August, 2011

Sharon Dooley’s Lightening Talk and the WIT Luncheon

At the end of last week I received an email from my friend and colleague Sharon Dooley telling me her “Lightening Talk” had been accepted and she would be speaking at the Summit in October. Sharon has many years experience in the IT industry, a former SQL Server MVP, currently working as a trainer and course author specializing in SQL Server. She is a volunteer with PASS and is the Virtual Chapter Lead for the DBA virtual chapter. Sharon is also involved in the Women in Technology virtual chapter. A couple of weeks ago Sharon and I were chatting about the PASS Summit and even though she hadn’t submitted a full session she was considering submitting a lightening talk. Sharon doesn’t currently maintain a blog so I said if she did submit a talk and it was accepted I would promote her “Lightening Talk.” Sharon has a really awesome query that returns a user’s permissions; she will be demonstrating this query during her talk. She also promised to share the query with us after the Summit so …

Using PowerShell to Monitor SQL Server

By Ian TreasureOver the past few years, I have used a number of approaches to monitor SQL Server. For example, I have configured jobs to e-mail the DBA team if they fail. I have written programs to check that everything is OK. I have used a number of third-party tools to monitor my instances. Each approach has its own strengths and weaknesses. If the SQL Server Agent is not running, the jobs don’t run and I don’t get any messages telling me that they have failed. To stop this happening, I have used server monitoring tools for example  Big Brother which can warn if a service has stopped. This sort of tool is pretty good, but you need to invest time and effort setting it up. The same applies to monitoring tools like Ideas Diagnostic Manager or Red Gates SQL Monitor. Also, these tools can cost a lot of money and can themselves affect performance of the server.

So I want to find a tool to monitor SQL that is independent of SQL Server, free and easy to use. PowerShell fi…

VMFS LUN resizing options in vSphere

IntroductionFirst off, many thanks to Gethyn for the opportunity to post on his excellent blog. I’m Christian Dadswell and I am a Senior Infrastructure Analyst at the Intellectual Property Office. I have 15+ years cross platform IT experience and currently support and maintain, amongst other things, the vSphere infrastructure from SAN (fabric and storage) thru vSphere to the virtual machines. In this article it is my intention to give you some options for resizing a VMFS LUN to allow you to increase the size of a Windows VM volume. It will also cover a scenario whereby a VM has been frozen in operation due to lack of disk space. Previous experience has shown me that VM’s occasionally require more space for whatever reason. This happens more often than not with SQL Server VM’s. Sorry, but you SQL DBA types love to eat SAN space. But seriously if a VM needs more space for its OS or data volumes something will need to be done. To kick things off I will create a couple of scenarios that …

Get your AD information with .NET

.NET has a useful class called system.DirectoryServices to query LDAP such as Active DirectoryHere is some example code to retrieve all the AD information for a user called FullName,On a web site, your Active Directory Name can be found using FullName = HttpContext.Current.Request.ServerVariables["AUTH_USER"];(If you are not using AD then this will return a blank)In this procedure we first write the results to a data table. we can then sort the results before appending them to a stringbuilder which is then return to the web page.using System.DirectoryServices;publicstaticString GetUserInfo(string Fullname)        {DataTable UserInfo = newDataTable();            UserInfo.Columns.Add("Name", typeof(string));            UserInfo.Columns.Add("Info", typeof(string));            System.Text.StringBuilder sb = new System.Text.StringBuilder();//extract the user name from DomainNamestring username = Fullname.Substring(Fullname.IndexOf("\\") + 1);//create…

Another New Contributor–Christian Dadswell

This is just a short post to introduce you all to my good friend Christian Dadswell who has kindly agreed to write guest post for has a wealth of experience in IT especially in the field of virtualisation. I had the pleasure of working with Christian on some recent projects to virtualise a SQL Server estateChristian’s post will be focusing on resizing LUNS in vSphere…a subject which we are all likely to have interest in.As a hobby Christian writes and develops mobile apps for the Android market. I’m told that he is actually very good it. His BBC Browse Cast application had in the region of 20,000 downloads the last time I checked with him… It is probably much more by now.Chris’ post will be published in the next couple of weeks

Auditing SQL Logins Addendum

One thing that has come out of auditing successful logins is the number of applications called ".Net SqlClient Data Provider". This is slightly annoying because we don't want to audit a generic name because you won't then pick a rogue application.This can be easily fixed by adding " Application Name=AppName" to the connection string of the .NET application.i.e.connectionString="Data Source=Server1;Initial Catalog=master;Integrated Security=SSPI; Application Name=Application1;" providerName="System.Data.SqlClient

Auditing SQL Logins Part II

Written by David PostlethwaiteIn a previous blog  I talked about how to audit successful logins. So what about failed logins?As mentioned before logon Auditing can be configured under server properties. Here you can audit failed logins..This puts the audit records into the Windows event log but a more useful solution would be to have these failed logins written to a table.It turns out to be quite simple using the master..xp_ReadErrorLog command which reads from the Windows Event log.So first let's create a table to store the resultsCREATETABLE [dbo].[lv_Failed_Logins](      [Failed_Date] [datetime] NULL,      [Failed_Userid] [varchar](50)NULL,      [Failed_Hostname] [varchar](50)NULL,      [Failed_ServerName] [varchar](100)NULL)ON [PRIMARY]In this example we will run a scheduled job every night to write the failed logins into a tableFirst we will cycle the error log using the following command. This makes the current log file an archive file and creates a new empty log.sp_cycle_e…

Putting a Database into Single User Mode

I was recently discussing with a group of budding DBA’s how you go about performing a restore of database. They were trying the restore for themselves  and a few of them got an error about not having exclusive access:Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
This was a test setup and obviously we had other sessions connecting to the databases. these connections were  preventing the restore.If you are restoring a database and you have other sessions connected it can be quite simple to kill those connections in one go  by putting the database into single user mode and using the ‘ROLLBACK IMMEDIATE’ switch. This will rollback any open transactions and allow you to restore the database. I like to wrap this around the restore statementalterdatabase TestDB set single_user withrollbackimmediate

FROMDISK = N'C:\Data\SQLBackup\…

Audit SQL Logins

Written by David Postlethwaite There are several ways that you can audit who has logged in to your SQL Server. The easiest way is to turn on logon Auditing under server properties. Here you can audit failed logins, successful logins, or both. This will put the audit records into the Windows event log. The failed logins is pretty useful because there shouldn't be many of them. But the successful logins turns out not to be so helpful because it records every single login which includes the service account. It turns out this logs into the server every few seconds so you end up with so much data in the event log that it's impossible to find anything else, especially the rogue user. The Enterprise version has a built in auditing option which writes to a user defined event log but again this still suffers the same problem in that it audits every login. What would be better is a way of selectively auditing only those logins we want to see (or not recording those we aren't interested …