February 2010 - Posts

Don't always believe your system procs - they may lie !

It's sometimes interesting how a simple thing can catch you out!
I have a set of routines which handle the process of restoring copies of databases on other servers, it also supports full blown log shipping but has various other functionality.
Generally all my servers have identical file locations for SQL Server,  e.g F: for data L: for logs etc.
For one reason or another I have a server which has only one data drive so the logs are now on drive F: too.
My restore database proc correctly generates move statements to deal with exactly this situation, but it uses sp_helpfile to gather the location of the physical database files, it's been happily working in SQL 2000, 2005 and 2008 ( with suitable changes to system tables )
What I find in SQL 2008 is that when the database is in standby sys.database_files and hence the output for sp_helpfile returns the location of the files when the database was backed up, it's source file locations not it's current location. If the database is not in standby the current file locations are returned.
The solution is to use sys.master_files not the local databases files.
I did some searching and discovered an entry on Connect by  Loius Davidson  http://connect.microsoft.com/SQLServer/feedback/details/124491/database-files-not-matching-master-files-in-database-in-standby-mode

It seems Microsoft say this is by design, now this may be so but what I say is that the behaviour of sp_helpfile and the contents of sys.database_files is inconsistent. I'd always expect it to be the same, it was this way in SQL 2000.
Note that BOL makes no mention that sp_helpfile will not return correct information for read-only / standby database

I've only stumbled across this because I have what I call a non standard server.
( My searches didn't seem to find any other mentions of this, however if I am the only DBA who didn't know then I apologise !! )

If you have Enterprise Servers you should read this blog

I feel that a DBA should have a good working understanding of the underlying server that SQL Server sits upon and the storage system(s) that it connects to, otherwise you may not know if you're being spun a yarn when you're trying to understand performance issues which may or may not be outside SQL Server. One of my favourite microsoft teams are the Core ,  http://blogs.technet.com/askcore/

Have a read it has some great stuff.

Print vs Select - security through obscurity ?

Now here's an interesting snippet which I'm sure will provoke a few " ... and you didn't know that! " comments.

I wanted to take the output from a stored procedure into a table using the insert into mytable exec proc; command but despite my best efforts this just wasn't happening.

After a little investigation I discovered that the output from the procedure was being generated using the print command.

My investigations showed that you cannot put the output into a table when it is generated with the print command.

All I had to do was change the print to a select and everything worked as expected.

Detach a database and take out a cluster.

During testing I managed to take my SQL 2008 cluster offline by simply detaching a database.

See http://msdn.microsoft.com/en-us/library/ms189128.aspx  explaining the background.

When you detach a database the ntfs file permissions go to the account that issued the detach, any other permissions, such as those to the SQL Service account are removed. This apparently is considered safer, when you attach, the permissions revert to the SQL Service so everything is ok.

Well should your database mdf or ldf file exist in the root of a clustered drive and you detach the database the drive will instantly go offline, as this will be a dependant drive this will also take your SQL Server offline in double quick time.

The moral of the story is to always make sure your ldf and mdf files exist in a sub folder and never in the root.

Personally, I think this is a bad move ( the permissions ) I spend some time making sure that no indivdual can ever own databases, database files, jobs and so on, now if I detatch a database as myself ownership becomes mine alone -- aarrghhh!  And before there's a comment about logging on as the service account, best practice says you should never use a generic or servcie account to connect - doesn't really leave a good audit trail and if multiple people have access to an account that's even worse!