My ERRORLOG shows some spids have an ‘s’ after them. What’s going on?

Published On: 2008-04-24By:

That “s” is new in SQL Server 2005.  It means that the SPID is a system process.  For example my database is having a problem with tempdb running out of space for no reason.  When I look in the log I see that SPID 118s is the offending SPID.  When I watch SPID 118 using sp_who3 I see that the output looks a little strange.  I see a lot of CPU and disk load and that the thread and been logged in for ever using the sa account but with no hostname, host process, or mac address listed.  This is because this is the service broker process which is what runs the activated procedures.  Starting in SQL Server 2005 system processes no longer need to run with SPIDs less than 50.  Because of this, Microsoft has decided to make live easier when trying to identify the system processes by putting the s after the SPID number in the logs.

This same information appears to hold true for SQL Sever 2008 (so far at least).

Denny


Contact the Author | Contact DCAC

Back To Basics: The DELETE Statement

Published On: 2008-04-21By:

When you have data in your table that you need to remove the DELETE statement is the way to do that.  Using the DELETE statement without any WHERE clause will remove all the data from your table.  The WHERE clause works the same way as the WHERE clause for SELECT and UPDATE statements.

DELETE FROM Employee
WHERE EmployeeId = 4

You can also JOIN to a table when you delete data from a table which allows you to use the second table within the filter.  This requires a slightly different syntax than your normal DELETE syntax.

DELETE e
FROM Employee e
JOIN Department ON Employee.DepartmentId = Department.DepartmentId
  AND Department.DepartmentName = 'Sales'

As you can see you need to set an alias for the table you are deleting from, and put that alias between the DELETE and FROM words.

Denny

P.S. Sorry this didn’t come up sooner, I could have sworn that I wrote it and published it back when I started the series.


Contact the Author | Contact DCAC

New Article: Configure SQL Server Service Broker for sending stored procedure data

Published On: 2008-04-17By:

SQL 2008 one click database encryption gives a false sense of security

Published On: By:

While I think that the one click database encryption that Microsoft has built into SQL Server 2008 is a good idea, but I’m not sure how useful it actually is.  It’s touted as giving you data encryption of the entire database without any code change.

What this actually means is that if someone stops the SQL Server and copies the data files to another server and attaches the files to that SQL Server the files won’t be of any use.  This however isn’t the common way that data is stolen from a SQL Server.  A much more common method is someone logs into the database using a perfectly legit account and exports the table or tables which contain the useful information to Excel, Access, csv, etc.  This method of database encryption does nothing to protect against this as if you query the table directly you are displayed the data in clear text.

All this database encryption does (as it appears to me) is that it protects the data at rest (ie. when the SQL Server service is stopped) and does nothing else to protect the data.  If all you are looking for is a compliance check off saying that you data is encrypted for SOX, HIPAA, the California Online Privacy Protection Act of 2003, etc then this will probably do the trick.  If you want actual data encryption where the data is not viewable when you query the table unless you know the key to unlock the data this isn’t it.

If you do want that quick and easy encryption for a compliance check off be sure to check your data storage requirements and IO requirements.  Encrypted data is typically larger than unencrypted data and you may need to increase your allocated storage or IO requirements in order to have the encryption completed.  Also remember that the higher level of encryption you use the more CPU time is required to decrypt the data.

Feel free to share your opinions below.  I’m interested to here what others have to say on the topic.

Denny


Contact the Author | Contact DCAC
1 417 418 419 420 421 440

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link