Over the weekend I went along to the fantastic DeveloperDeveloperDeveloper day held at Microsoft up in Reading. The quality of the presentations was fantastic and even for frameworks/subjects I was familiar with I learnt loads. For me, the most useful presentation came at the end of the day (just as the brain was beginning to shut down due to information overload). Simon Sabin (from SQL Know How) presented “Things you should know about SQL as a developer“.
A snapshot of his presentation can be found on his blog, but for me, the most enlightening point was the battle between reindexing a table and shrinking a database. Typically the two feel like they should go hand in hand, but they will, in fact, fight each other. Re-indexing a table will create a new copy of the table in question, with the data correctly ordered to reduce fragmentation. As the original data is just marked as available, the process of re-indexing a table will potentially result in a database growing by the size of the table being re-indexed. Once the re-index process has completed the database will report all the space taken by the old copy of the table as free space.
However, if at this point if you shrink the database you will refragment your nicely re-indexed table as the shrinking process reads/moves the last row of the table first. This means your table content will end up reversed (and totally fragmented) after a shrink command.
There are many reasons why you shouldn’t shrink your database, this being just one of them!
We just run into an interesting problem where starting the SQL Server Agent would start and then immediately stop. No errors were reported in the event log, but running the following via the command line returned “StartServiceCtrlDispatcher failed (error 6)“ “[[your SQL Path]]BinnSQLAGENT.EXE” -i Googling the error in question returned this forum post which contained the solution. We had reinstalled the service and the account that we were running under did not have the permissions . . .
As part of my current project, I’ve spent some time over the past couple of months trying to determine the best (cheapest) SQL Server configuration to support web servers running in a virtualised environment. As a quick disclaimer, the following are my thoughts on the subject and should be used as guidance for further research only! Firstly you need to figure out whether you are going to license using the “per user” or “per processor” . . .