The other day i wanted to change the "Data Files" location for a 2005 database engine installation and a 2005 Analysis Services installation which you can specify under the advanced options during installation. I quickly found out that there appears to be no documented ways to do this other than uninstall SQL Server and install again specifying a new location for data files. It's also not as simple as moving your system databases as "Data files" covers things like server errors logs, sql agent logs, replication default directory etc. So, as the uninstall route was not one i was prepared to go down i sat down and worked out how to do it and below are the results.

Analysis Services

• At the root level, copy the existing data folders to the new location.
• Open the registry editor and navigate to HKLM\SYSTEM\CurrentControlSet\Services\MSSqlServerOLAPService and edit the imagepath value. You will see a switch –s and after that a path, change that path to the new path.
• Locate the configuration file msmdsrv.ini in the config directory and open it. Update all references to the old location with the new location.

If you have not copied any cube data then there is nothing more to do. If you have copied cube data and you know the cubes are using all default storage locations then you do not need to do anything else but if you are not using default locations then they must exist otherwise the cube will error when starting. To update the locations you must find all the relevant xml files and update the paths.

Database Engine

• Setup the directory structure in the new location.
• Follow knowledge base article KB224071 to move all your system databases and any user databases (don't forget to do the steps for the resource db).
• Stop the fulltext service.
• Fire up the registry editor and go to HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\MSSEARCH\LANGUAGE. Now, work through each key and change the paths for NoiseFile & TsaurusFIle to the new location
• Go to HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\MSSQLServer and change the value for "FullTextDefaultPath" to the new location.
• Move the contents of the FTDATA directory to its new location
• Using the SQL Server configuration manager update the path for Dump Directory and the error log (part of the startup parameters, screenshot below)

  

• In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\MSSQLServer change the value for BackupDirectory and defaultlog to new location
• In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\Setup change the value for SQLDataRoot to the new path. (This updates the Data Path value that is greyed out in the SQL Server configuration manager).
• In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\Replication change the value for WorkingDirectory to the new path.
• In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\SQLServerAgent change the value for ErrorLogFile and WorkingDirectory to the new path.
• Finally move the remaining data in the original location to the new location and rename the root directory. Once you have verified everything is ok (check SQL error logs etc) you can delete it.

Notes

1. Some of the paths will vary when instances are installed. For example the directory path part MSSQL.1 could be MSSQL.2 or MSSQL.3 depending on install order and number of instances.
2. Some of the registry paths may contain an instance name if you have a named instance.
3. When you finish you may need to enable the service broker in the msdb database again as i found this necessary on 1 of my installs. The SQL error log will reveal if this is necessary.

Below is a summary of what ensued for your amusement. Now, some of the statements are actually based on bad real life advice and many we just made up. Can you tell which is which?

Also please comment if you have got any good entries for the Book…..

• Why would i want my server to be enabled for awe!? I`m already in awe of my coding skills and i don't need a server to tell me i`m good.
• Set autoclose on, this means the disks get a rest when no-one is using your database.
• Unicode columns only hold unique values.
• Security adds an extra level of overhead to your applications, place all logins in the sysadmin role for maximum performance..
• Many people understand the more traditional SQL outer join syntax such as *= and =*. Use this as it will make your code easier for matured people to maintain.
• SQL server imprisons cached procedures incorrectly after 200 calls. Run dbcc freeproccache every 5 mins
• Ensure you create fragrant code, it makes parameter sniffing a more pleasant experience
• Always use optimiser hints, rememeber YOU know best.
• Full backup includes lots of old data that hasn't changed, differential backups will restore much quicker!
• Be green! Databases need space not spindles. Buy bigger disks and save on power consumption!
• People understand nested IF THEN statements much better than CASE - don't use it...
• dbcc dropcleanbuffers makes sure your data is clean and contains no corruptions. Use at least once a day...
• Use simple recovery, it's so simple to manage.
• Never ever comment in a piece of code, if you want idle chat go down the pub!
• Do not use SSAS. Cubes are bad as they are unable to roll down hills.
• Its best to run SQL Server from the command line, this way you can see what its doing when you log onto the server.
• Be green! Ensure you set your disks to be powered down when idle.
• Select * means a query will use all the CPUs... use this wherever possible.
• Short varable names are more efficient - never use @longVariableName where @a would do..
• Never backup your log as it removes the entries and you never know when you might need them.
• Fragmentation is the spawn of satan. Rebuild your indexes every half an hour for optimal performance
• Tempdb runs faster if you set it up on the root of your C Drive...
• Cursors rock, use them always. The SQL optimiser often makes bad choices when you use sets of data instead of row at a time
• Normalization can mean you have lots of little tables. Keep things simple by having one big table holding everything.
• Always run SQL trace by using the profiler GUI. Server-side traces just make things complex.
• The first step to recovering a suspect database is to detach it.
• If the database is not yellow in enterprise manager they can't be using it so save resources and delete it.
• Separate development servers are a waste of resource. Ensure all development and testing is done on production
• Sometimes truncate doesn't work properly, use delete instead. Delete is slower because it definitely deletes all rows.
• If you see blocking on a server, kill all spids involved immediately.
• “with tablockx” makes your query run faster. Always use it…
• Always set 'allow updates' to 1. This means you can update in the database, normally you can only insert or delete.
• Better to have many databases storing one table each, than one database holding many tables.
• NTFS compression is your databases best friend! Save space and compress all MDF & Log files.
• All commands are safe, undocumented simply means they forgot to add it!
• The DBA knows best, always ensure you configure recovery intervals and affinities.
• Don't waste valuable disk space when you don't need it. Size all your databases at 10MB and set autogrow to 1MB.
• SQL is more efficient if it's written in upper case.. never use lowercase.
• Nonclustered indexes slow down SQL Server, don't use them unless you are searching for strings in very large character columns.
• Views are better than tables - they are optimized to use less disk storage.
• Unicode columns only hold unique values.
• Statistics are evil! Turn off auto create & update
• Temporary tables don't need any space - use them all the time.
• Autoshrink is your friend.
• Your server will run faster with fewer users, try to restrict access as much as possible
• SQL runs faster when accessing data from disk. Ensure you never allocate more than 200mb
• Backups can be useful - make some when you have some spare time...
• SQL Server does not need stopping and restarting every day - but if you can do this, then do.

Analysis services 2005 (SSAS) added the ability to trace server side events and i have used this feature a number of times. To date i had always used the profiler gui to do the SSAS tracing but today i found myself needing to initiate and manage a trace with scripts.

The good news is that it can be done! It did take a while to piece together how to do it though and i found some of the information quite a challenge to find so i am sharing with you the results and have attached a zip file with the necessary scripts.

So, what did i want to achieve?

• A script that would create a trace on the server and log to a specified directory similar to the way you can with a SQL Server trace.
• A script that would list all running traces on an analysis server.
• A script that would destroy a named running trace, in my case the one i created.

Now, the script that creates the trace is likely to require editing each time to add new events as the script i am attaching only captures command events. The easiest way to define your events is detailed below.

1. Open SQL Server profiler and define the SSAS trace you require.
2. Next script the trace by going to "File – Export – Script Trace Definition – For Analysis Services 2005".
3. Open the script file and cut & copy the Events & Filter elements into my attached script ensuring you replace the existing Events & Filter elements.

Some people might be wondering why i needed to create the script file if i can script it from profiler? Well, profiler only scripts the events and filters and excludes options such as LogFileName, AutoRestart etc.

So, with the events in place you should now update the LogFileName element with your filename & path and check the LogFileSize element is appropriate. Finally, there is a StopTime element that you can uncomment and set which sets a time for the trace to automatically close but do not forget its the time at the server you are setting not the time where you are.

With all the updating done just run the script to create your own server side SSAS trace. It does not end here though because you will need to stop the trace manually if you have not enabled a StopTime. This is where "Delete Named Trace.xmla" comes in. Simply update the name element and run the script to delete the trace. Unlike SQL Server you do not need to stop and then close the trace. If you are not sure of the name of the trace you can run the script "List all server side traces.xmla" which is also useful for validating that you have removed the trace or that it auto closed. The list traces also gives useful information such as where the traces are outputing their results.

The trace script was amended from an example in Analysis Services Processing Best Practices and i would definitely recommend reading the article. The other scripts i hacked together and are very simple as i am a xmla novice.

I hope you find this information useful,

Recently we started to see deadlock errors when backing up our transaction logs. The "important" part of the error is shown below.

Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.

What this meant was that the transaction log backup was occurring but the entry in the msdb was not being made as it was being chosen as a deadlock victim so we investigated the cause of the problem as we had some processes that used this information to copy transaction logs to other servers and we needed it to be complete.

We used trace flag 1222 to output the deadlock information to the error log and found the culprit to be a Microsoft stored procedure called "sp_delete_backuphistory" that is called by SQL 2005 maintenance plans when you use the "History cleanup task" and tick the "Backup and Restore History". Having a look at the stored procedure it was obvious why it was deadlocking so we decided to log our findings with Microsoft. Microsoft have confirmed the bug and have stated it will be fixed in SQL 2008 but have stated they will not be issuing a KB in the immediate future which is one of the reasons i decided to blog about it.

I have had a look at the latest 2008 CTP and can confirm that Microsoft has updated the stored procedure to avoid the deadlocking and i noticed they also added a non-clustered index on the backup_finish_date in the backupset table (finally). I would also like to point out that the changes made to the stored procedure could easily be ported back to SQL 2005 so i`m a little surprised they have not been.

Work Around

To avoid this specific issue, we took the approach of identifying a generic window when transaction log backups would not be running on 95% of our server estate and changed the "History cleanup task" to run at this time. For the remaining 5% we worked out per server windows and now we do not see the issue on any of our servers.

A brief history of msdb backup history tables

Now, for those of you who want to know more about this problem and are wondering why this problem seems to only just be appearing in SQL 2005 here is a brief history lesson.

Prior to SQL 2005, maintenance plans never gave the ability to delete old backup history but the procedure "sp_delete_backuphistory" did exist. So, many DBA's would find that their MSDB's were growing rather large and if they used Enterprise Manager to do a restore it would hang for ages as it read the large backup tables. So, people would then find out about "sp_delete_backuphistory" and schedule it as a job but quite often the first time it was run it would take ages (some time days) to run due to poor coding and volume of data so people then implemented their own more efficient code (Google sp_delete_backuphistory and you will see what i am talking about, for example, see here).

So, what does this lead to? Well, not so many people using the MS stored procedure prior to 2005! But then SQL 2005 rocks up and we have the ability to call the procedure via the gui! Well, lets tick that puppy!!! :D We do need to keep that msdb trim after all and that is how we get to where we are now!

Anyway, back to the post at hand! This is a quick FYI as i`m not going to go through how to interpret a deadlock graph because Bart Duncan does a fantastic job of it here.

When you enable 1222, it will output the deadlock information to the error log. If your using the log file viewer and steam on in and do your analysis you will probably find you get your deadlock victim the wrong way round like I did in the first cut of my analysis. Fortunately I did realise my mistake which made me look at the output again and I realised that the output in the log is upside down! This is because the log file viewer sorts the log so that the most recent entry is first and as such reverses the deadlock output. I`m not aware of any way to configure the sort order of the log file viewer and exporting the log exports it in the same order its displayed….

So, when looking at your deadlock information you have 2 options.

• Find the occurrence of the words “deadlock – list” and read upwards.
• Grab a copy of the error log from the servers log directory and open it in notepad.

I thought i would share this little quirk about the SQL Agent jobs for maintenance plans.

One of our members of staff had left and we had the usual case of a few jobs failing with:

"Unable to determine if the owner (DOMAIN\xxx) of job <JOB_Name> has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."

So, we went around and updated the job owners to one of our appropriate generic admin accounts. A few days later some of the jobs started to fail again with the same error, since we knew we had performed the update previously it was time to investigate how the job had been set back to the old user account.

It was quickly determined that the only jobs that had reverted back to the old owner were the jobs created by maintenance plans so we focused our attention here. It turns out that when you save a change to a maintenance plan the job owners are reset to the owner of the maintenance plan. The owner of the maintenance plan will be the account used to connect to the server in SSMS when creating the plan.

With this determined a slight variation of our fix was deployed. First we changed the job owners and next we updated the owner of the maintenance plan using the script at the end of the post. The script is in two parts, the first part shows you who owns what and the second updates the owner to an account you specify.

Agent jobs being created with a user account have always been a procedural problem. This is simply another variation on the problem that we need to take into consideration and put a process in place to deal with. The most likely processes are either to only create a maintenance plan when logged on with a generic account or run the script after creating the maintenance plan.

I am however curious why Microsoft have implemented updating the jobs in this manner and see it as having the potential to cause significant problems in environments that may not be monitoring their jobs as closely as is required and end up with maintenance tasks not running for some time. How to get around this? Well, given the nature of maintenance plans and the fact you must be a sysadmin to see or create them, surely it makes sense to have the owner as the SQL Service account or a user created by SQL for maintenance plans? Currently someone has posted this feature to connect here and i've added my two pennies worth so if you feel it should change then have you say too!

--See who owns which packages

SELECT

name, description,suser_sname(ownersid)

FROM

msdb.dbo.sysdtspackages90

--Now we update the owner to an appropriate domain account. Either the service account or a generic admin account is good.

UPDATE

msdb.dbo.sysdtspackages90

SET

OWNERSID = SUSER_SID('YOUR_DOMAIN\APPROPRIATE_ACCOUNT')

WHERE

OWNERSID = SUSER_SID('YOUR_DOMAIN\OLD_ACCOUNT')

As soon as i saw the error messages in the logs i thought to myself "Oh my, that did not happen in testing" (ok, maybe it was more colourful than that).

We were creating a clustered index on a tiny little table and the index went through fine. However, the application started to generate the message "Could not complete cursor operation because the table schema changed after the cursor was declared". My gut reaction was to restart each application server in the cluster but having restarted the first one it made no difference. It suddenly clicked that SQL Server must be dishing out the cursor plan from cache.

Now, I did not want to restart the SQL servers because only a small part of the application was affected and I did not want a more significant outage. So, how do we get the plan out of cache? The table below details your options with the corresponding impact.

The lesson to take away here is to always use sp_recompile when making any kind of DDL changes, i also tend to use it on stored procs & views too. I normally always have it in my scripts so believe you me i gave myself a good talking to about forgetting to put it in this time .

And on a related note, have you come across sp_refreshview? No? Well, its worth knowing about.

The user was using SSMS connecting using a SQL login to restore a database and when they went to specify the backup location they got an error to the effect "Cannot access the specified path or file on the server". After clicking ok the tree view in the locate backup dialog was empty and if you typed in the path and filename manually you still recieved an error.

So, i dug out the profiler and found that xp_fixeddrives was being called and decided to check it out. It turns out that when executing xp_fixeddrives using a SQL login it returns no results! Because of this the error is generated in SSMS and the tree view is not populated.

I spent some time investigating xp_fixeddrives and came to the following conclusions regarding its behaviour.

• Currently the only way to get xp_fixeddrives to return results when using SQL authentication is to add it to the sysadmin role.
• I extensively explored proxy account configurations to get xp_fixeddrives to work under SQL authentication and i could find no way to get it working.
• If you use Windows authentication xp_fixeddrives works as expected. You do not need sysadmin privledges nor do you have to do any proxy configuration.
• Some poking around with process monitor showed that when using windows authentication or the sysadmin process that SQLSERVR.exe spawns processes to gather the details using the service account. When using SQL Authentication with sysadmin SQLSERVR.exe does not spawn anything or generate any security errors so what ever is going on is beyond what i can work out (My skills with a debugger are to limited :)  ).

At the end of all this i went down the Windows authentication root to work around the issue. For some reason SSMS still generates the error but the tree view is then populated and you can select the file so i can live with that as i suspect exploring why its still generating an error may end up being a bottomless pit of time.

Finally, this only affects SSMS. If the user had done the restore using T-SQL it would have worked (despite a warning about updating the restore history in msdb).

I read Tony Rogerson's blog on Virtual Log Files today and it reminded me that i really should knock up a little report to list all databases on a server and the number of VLF's per database. Since I had been busy writing some other Operational reports I was in the right frame of mind so knocked up what you see below.

The Report code

CREATE TABLE #VLFS (fileid int,filesize bigint,startoffset bigint,fseqno bigint,status int,parity int,createlsn varchar(1000))

CREATE TABLE #Results (SRV_Name nvarchar(500),Database_Name nvarchar(500),VLFS INT)

exec master.dbo.sp_msforeachdb

    @command1 = 'USE ? INSERT INTO #VLFS EXEC(''DBCC LOGINFO WITH TABLERESULTS'')',

    @command2 = 'insert into #Results SELECT @@SERVERNAME,''?'',count(*) from #vlfs',

    @command3 = 'truncate table #vlfs'

--For this example i`ve just returned the results but you can

--just as easily write the results to a local or central server.

--I write mine to a central server for reporting on.

 
SELECT * FROM #Results

drop table #vlfs

drop table #Results

Example output

For me the beauty of this little report is that i have set it up so that it runs on all 150 of my servers distributed around the world and logs to my central logging server. From here I get one report that tells me the databases and the servers they are hosted on that have excessive VLF's. Having said that in a well managed environment i should never find any databases with lots of VLF's........

You may also be wondering "How many VLF's are too many?". Tony did not cover this but Kimberly Tripp did in a post she put up a few years ago (See point 8) which was when I first learned about VLF's. I don't however think this is a hard and fast number and obviously the performance gains will be less if your not far off this number.

The last thing I am going to mention in this post is part of the code I used in the report. I used the undocumented procedure sp_msforeachdb. This is a great little procedure that will cycle through your databases executing up to 3 commands. To use it, where you would have put a database name you put a '?' and its as simple as that! Incidentally there is also a sp_msforeachtable.

I thought I would do a quick blog about template parameters since I have been writing allot of standard deployment scripts for our 2005 builds and have used them extensively.

So what are they? Well they are place holders and you would you use them in the same places that you would probably put a variable. The key difference comes when you assign the values. With variables you work though the code setting the values as appropriate, with template parameters all you do is press CTRL-SHIFT-M and you get the dialogue box shown below. Simply fill in the values, click ok and your ready to run your script.

The format of a parameter is <Parameter Name, SQL Data type, default value> so to get the Mirrored DB name shown in the screen shot you would enter <Mirrored_DB_Name,nvarchar(128),'My_Mirror'> where you wanted the value to appear.

This feature is available in Management Studio and Query Analyser, if you have ever used one of the standard Microsoft query templates you may have noticed that parameters are used in them as well.

I only realised template parameters existed a few years back because someone pointed it out to me. If this is the first time you have come across them (or had forgotten about them) I hope you find them as useful as I do.

The 64 bit beast has been out there for a while now and new servers are generally 64 bit compatible. The first thing that often comes into my mind when i think 64 bit is performance & memory with "compatibility" hot its heels (and recollection of a good article by Linchi Shea).

My fears around compatibility have been pretty much put to rest and we now recommend SQL 2005 x64 as our base build. However it does seem that 64 bit builds need a bit more attention. I say this because recently i've been having to register 64 bit dll's again and having mentioned this to a few DBA's the conversations went a bit like "Really? So was it x.dll or y.dll". Fortunately though the problems seem to only be affecting the tools (touch wood) but i am curious as to what experiences others are having with 64 bit servers so please do leave some comments.

Now, to help others who run head first into the very misleading errors i experienced i`ll detail the errors and the fixes below.

The first problem

This one occurred when we were doing a server build. One of our final tasks was to deploy a SSIS package so we went to open the package in BIDS and were greeted with the following error when loading the package.

"The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFormXML fails."

Some research threw up a few red herrings but the next clue came when i decided to connect to the SSIS instance using SSMS. At this point i got an error stating

"Error loading type library/DLL"

Further research threw up the following document which gave me a few options none of which i liked and more importantly the top solution was use the latest SP which i was already running so i decided to go hunting and use the sysinternals process monitor to see if i could find the issue. 15 minutes later the culprit was found to be the DTS.dll which process monitor identified as throwing some unusual errors so i located the 64 bit version and use REGSVR32 to register the DTS.DLL again and this resolved my problems.

The second problem

This one came a few weeks down the line on the same server. I was working in SSMS and connected to SSIS, when expanding the tree view i got the following error.

"Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))"

This time i went straight to my trusty process monitor and found that the server was trying to use the 32 bit version of msdtssrvrutil.dll. I located the 64 bit version and registered it and everything worked again. This time i also took to opportunity to check as best i could that everything else was working.

Its been a few weeks since the issues now and alls well so hopefully the server will behave, until the next time.........

I went to remotely edit a maintenance plan the other day and found it took a long time to open any of the objects in the plan. I also found that I was getting errors when clicking the drop down to select a database.

The reason for the problem was that the local server connection embedded in the maintenance plan had the server name defined as "." rather than an actual server name. Because of this the objects were trying to connect to a default instance of SQL on my PC rather than the server. The delay in opening was down to the timeout and the subsequent errors because there was no server to talk to.

The reason this occurred was because when the maintenance plan was created the server was registered as "." rather than <server_name> in SQL Server Management Studio (SSMS) and the maintenance plan uses the registered name in SSMS to populate server name in the connection details. Unfortunately you can not edit the local server connection details so probably the quickest way to fix the problem is to recreate the plan with the full server name registered in SSMS. I say probably the quickest because there may be a way to fix it quickly using the Business Intelligence Development Studio to update the package but getting the package back in is awkward.

The real killer

In many ways I think I got off lightly with this "feature". Why? Well, lets say I was editing the maintenance plan from another location that had got a default instance of SQL installed…… Yep! You guessed it, the maintenance plan loads quickly and without error because its connected to the local instance. When you select databases from the drop down list you are seeing the databases from your local instance and not the remote server and you could then select to run tasks on a database that does not exist on the remote server.

I`m going to flag this on connect and add a check to our install documents to ensure that instances are not registered with a "." in SSMS on the server.

I recently got an e-mail from a friend pointing out it had been a year since I had last blogged so I made the usual mental note to myself that I really should pull my finger out and get blogging again. This time I am actually going to do something! There have been reasons for my absence but now i`m getting back into gear and will be posting regularly so watch this space!

I have also decided to take this opportunity to move my blog to http://sqlblogcasts.com/blogs/acalvett/ as I no longer feel that the SqlJunkies site is a suitable home. I will however cross post for a while.

I don't normally write about a technet article but i must say that 917828 caught my eye and i felt that it was worth highlighting it.

The reason it caught my eye is that it describes a number of scenarios where by the whole procedure cache is flushed which will clearly lead to a decrease in query performance whilst the cache builds up again. The most alarming point was that there are at least 2 scenarios that a DBA may carry out and even schedule during core production hours without realising the impact until the users start complaining about the mysterious slow downs.....

These are

• Deleting a database snapshot.
• Restoring a database backup.

I'd recommend reading the article as there are 5 other situations in which this occurs that may affect you.

Today was a big day for me that made me proud as my first article was published on the Simple Talk website! Its about database snapshots in SQL 2005 and you can find it here.

I really enjoyed writing the article and must say that although i thought i knew how much effort it took to write an article its only now that i truly understand how much work people put into their articles!

In the mean time i intend to continue submitting articles and look forward to any comments you may have about my first one.