SQL 2008 Setup SP1

If one gets the error message "The current SKU is invalid" while trying to add in another node to a SQL 2008 Cluster (and, perhaps significantly), one on which the other node(s) are running SP1, it is possible to get round this without slipstreaming.

Check, in Control Panel Add/Remove Programs, that the SQL 2008 Server Setup application is installed. If it is, back out of the node install, and run SQL 2008 SP1. It seems counter-intuitive, since SQL 2008 has not yet been installed. It does however work. I didn't stop the installer when I ran SP1, so I had to restart the node: I suspect that had I stopped the installer, I would not have needed to restart the node.

Once you've got the installation to work, you'll still need to service pack SQL Server itself, however, since you're on the passive node, it's pretty simple. And, since the installation is fresh, you won't need to restart again.

Microsoft error link.

Microsoft fix link.

Anything, but anything, to avoid slipstreaming the install.

About concurrency....

Twice, in the last week, concurrency issues have jumped up to bite me.

The first time round, the server had 'NO COUNT' set to on. This confused the .Net software, which couldn't tell if any rows had actually changed before it updated the table: LINQ to SQL uses @@ROWCOUNT after updates to issue an optimistic automated concurrency check. If 'NO COUNT' is on, the @@ROWCOUNT value is always zero, and the concurrency check fails.

The second occurred while using the SSMS GUI to try and edit data in a table: right click on the table, pick 'edit top 200 rows'. The error we got was

Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

Again: concurrency issues. So, the first thing I did was to check whether 'NO COUNT' was on. It wasn't. Then I went in for a bit of google. It turns out the problem was in the data.

Several rows in the table were identical, and contained text fields. Despite having an 'id' column, the table didn't have a primary key. Via the GUI, SQL couldn't work out which row to delete, and, of course, running a DELETE T-SQL statement would have deleted all the rows. We wanted to keep one of them (natch).

To tidy up the data, I ran an INSERT, based upon a SELECT TOP 1 T-SQL statement (it was not possible to SELECT DISTINCT - there were text fields involved), but with a new ID value. Then I DELETEd the duplicate data, and finally UPDATEd the new row to the original ID value.

At this point, we tried changing some other data in the table. We had the same error message, despite the fact that the data was, this time, unique.

Unique, but without a primary key. Redesigning the table to include a primary key on the 'id' column (and why there wasn't one on there in the first place is anyone's guess) has made it possible to update the data via the GUI.

There's one last reason why this particular concurrency issue may happen: data. Columns containing '%', '_' or '[' can also cause issues.

Database Mail and Reporting Services: a high level how to

Preparation:
Set up SMTP Mail account, ensuring that the SQL Server's IP Address is whitelisted against the internal relay if required (this allows emails to be sent internally).
Ensure AWE addressing is enabled.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

If necessary, follow the instructions to enable the Lock Pages in Memory Option, according to company security policies, and restart SQL.

Configuration

Easiest to do this using the Database Mail Configuration Wizard. The following steps make more sense if you step through the wizard at the same time.

• Setup Database Mail
• Enable Database Mail
• Create a new profile for SQL Server Mail
• Use the add button to bring up the dialogue to input the account details.
• Add in the SMTP details: your port and SSL settings should be double checked with the sysadmin.
• Chose 'Basic Authentication' to log onto the SMTP server, which uses the account that you've just specified, rahter than the SQL Database Engine or Anonymous Authentication (Anonymous Authentication should be avoided).
• Make this the default mail profile, and either set the privacy for specific accounts, or make it public.

Enable the Database Mails XP Parameter:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

Then test that the email is running OK, either by executing the following store procedure, or by right clicking on Database Mail and choosing 'Send Test Email'. Check the results in the Database Mail Log (it's an option in SSMS and can be found in the same spot as the SQL Agent Logs and the SQL Server Logs). Using the Database Mail Log is simpler than using the Database Mail Troubleshooting option (which didn't actually work for me).

USE msdb
GO
EXEC SP_SEND_DBMAIL @profilename = 'ProfileName'
@recipients = 'sweetsql@blogspot.com'
@subject = 'Test SQL Mail'
@body = 'Hurrah, this test has been successful!'
GO

Finally, it's necessary to configure Reporting Services to recognise the existence of the email, so that it can be used for an automatically generated scheduled emailed report. Use the Reporting Services Configuration Manager, add in the details of the mail profile and the smtp server, and save.

The reports that are emailed need to use a data source with saved credentials, rather than a data source which passes on the credentials of the user viewing the report. Happily, parameterised reports can be set up to use a specific set of parameters for emailing purposes: this dialogue can be found when you add an (email) subscription to the report.

Self-Blocking SPIDs

My nightly DBCC CheckDB job is failing on the CRM Server, when it checks the MSCRM database. It fails because the SPID is blocking itself: and there's no point in trying to kill the SPID, because that just hangs. The self-blocking SPID also blocks everything else, rendering it almost impossible to stop the Scheduled Job via SSMS, as SQL Agent doesn't load. The self-blocking SPID also renders it impossible to stop SQL via SSMS or SQL Configuration Manager: the only way to stop SQL seems to be by killing the process in Task Manager (and that can't possibly be healthy).

There is, if one checks the sys.dm_os_waiting_tasks DMV, a lot of action going on in tempdb while this process is failing: the blocked resource is blocked on a LCK_M_IX wait_type, which is an intent exclusive lock. The memory involved in the process seems to be rather intensive (the memory used by SQL suddenly skyrockets). The executing SQL that is involved is really quite interesting: and not many people seem to have come across it based on a quick google about the place.

DECLARE @BlobEater VARBINARY(8000)
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0xE093909D00000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)

So far, I have drawn a complete blank. However, I've decided that trying to increase the size of TempDB might be a sensible thing to do, simply because so much that is being blocked involves tempdb, and tempdb itself was about 8MB in size.

SystemCenterReporting Reindex Script

Can be found here. It was referred to here and here, however, the links within those pages are no longer extant.

You'll have to register, and I give no warranty as to how workable this is: however, it's taken me half an hour to find the scdw_reindex1.zip or the scdw_reindex.zip file, so I thought I'd link.

State 38

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]

And the Application Event log detail generally shows something like:

In Bytes

0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..

Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.

The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.

A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.

"I can't take this database offline!"

An anguished cry, which I have heard twice today. The problem is, if you try to take a database offline, it will not actually complete until the current transaction has ended (or been forcibly killed), as there is a lock on the database. Killing a transaction is a bit untidy. As we find here "The ALTER DATABASE statement waits indefinitely if there is any lock on the database." One generally ends up with 'Database is in transition...Error 952' when trying to ascertain what the databases is up to, and very little seems to have an effect, including restarting SQL in some cases: and that assumes that restarting SQL is even an option.

Fortunately, if one uses a bit of T-SQL, one can persuade the last transaction to rollback neatly, unlocking everything, and reducing hassle. It's a lot nicer than going KILL SPID.


USE master

GO

ALTER DATABASE databasename

SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

And there we have it. Neatly and tidily takes the database offline, it doesn't hang around in a transactional

SQL Configuration Manager Suddenly Unavailable

Last night, I installed SP1 onto a SQL 2008 Cluster. This morning, looking at another issue, I decided to mosey into the Configuration Manager, in order to double check which Ports SQL was operating on.

Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:

"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"

Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.

They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:

C:\Program Files (x86)\Microsoft SQL Server\100\Shared

And running this command in cmd fixed the problem. Phew.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"

Oooh! Squee! New Tool

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer.

Tells you what could be improved in your installation, and how to go about it

• Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
• Determines if the configurations are set according to the recommended best practices
• Reports on all configurations, indicating settings that differ from recommendations
• Indicates potential problems in the installed instance of SQL Server
• Recommends solutions to potential problems

Sweet. Note: there have been some problems with installing it owing to Workgroups problems and Kerberos issues. Solution here.

More Fun And Games with SCOM

For various reasons, chief among them the requirement for a more scalable deployment and the fact that the SCOM Server was "really starting to struggle" according to our Chief System Support Analyst (and, he should know: he's the one that's using the server all the time), we decided to shift the OperationsManager database, the OperationsManagerDW database and the ReportingServer databases onto a shiney new SQL Server. I am all about the shiney, after all. I am a girl geek.

Now, I thought it would be simple enough. Shift the OpsManager db. Shift the OpsManagerDW db. Shift the Report Server stuff. There are some nice articles on how to do this. Including a whole article about moving Report Server databases. Go and have a look at them.

Firstly, notice how the article about moving Report Server is almost identical to the one about moving OperationsManagerDW? That was the first gotcha. If you are moving the entire SQL Server, you need to follow the instructions in the article about moving in the document entitled 'How to Move the Operations Manager Reporting Server in Operations Manager 2007'. Ignore the one about 'How to Move the OperationsManagerDW Database in Operatios Manager 2007', other than the information about the Logins (step 9 onwards) and data sources (step 13 onwards).

Second gotcha? There is no sensible time to move SQL Server Reporting Services (SSRS) if you have custom reports. If you move it before the SCOM Report Server component, then there is the risk that installing SCOM's Report Server will wipe the custom reports. If you move it after the SCOM Report Server component, then there is the risk that you destroy the SCOM Report Server: it relies on a functional instance of SSRS.

The mistake I made was to move OperationsManagerDW according to that document's instructions, and then try to move the ReportServer having moved over the Report Server Databases part way through the process. This was silly: the ReportServer component did not install properly, it destroyed SSRS to the point that I had to reinstall it, and the IIS Application Pools refused to run:

Source W3SVC
Category (0)
Event 3221226474
Computer SQLSCOM

Message
Application pool 'ReportServer' is being automatically disabled due to a series of failures in the process(es) serving that application pool.

Source W3SVC
Category (0)
Event 2147484657
Computer SQLSCOM

Message
A process serving application pool 'ReportServer' terminated unexpectedly. The process id was '7468'. The process exit code was '0xffffffff'.

(there were several errors with the exit code '0xffffffff')

These linked to:
Error loading configuration file: Root element is missing.
w3wp!library!5!22/06/2010-09:20:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.

Report Server's Logs also gave me:
An internal error occurred on the report server. See the error log for more details., Incorrect security descriptor version;

and

w3wp!library!7!06/22/2010-11:01:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Unable to load assembly Microsoft.EnterpriseManagement.Reporting.Security;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.EnterpriseManagement.Reporting.Security' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.EnterpriseManagement.Reporting.Security'
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.LoadAssembly(String name)


If I tried running the application pools under the Local\System Account, well, they didn't fail, but Reporting Services refused to function. I had service unavailable messages, I had unable to connect messages, and I had a lovely set of errors for which I was referred to the Installation Log File for SCOM Report Server, and to look for 'value 3'. I had error 108 all about me.


In other words, I had a partial installation of SCOM Report Server, and it destroyed SSRS. Nothing wanted to work.

I fixed this by:

Backing up everything I could think of.
Uninstalling and Reinstalling SSRS (I had tried to revert back to the original databases on the server, with the original .snk file - and every other .snk file I could find. No dice). With SP3 (as it was SQL 2005).
Using Add and Remove Programs to get rid of the SCOM Report Server, which also took out the Data Warehouse component, but not the Data Warehouse itself. Windows was under the impression SCOM Report Server was properly installed - trying to repair that particular component made no difference whatsoever.
Using the resetsrs.exe tool, as part of the SCOM installation disk to make sure that SSRS was working properly.
Keeping a close eye on IIS and Application Pools while installing.
Treble checking that SSRS was working: without SSRS working, SCOM Report Server won't install or work.
Stopping the SCOM Services.
Reinstalling SCOM Report Server, and only installing the Report Server element.

This worked: however, I'd lost my custom reports. I will, at some point, try shifting SSRS to another server to see what went wrong with moving SSRS - at the moment, I'm quite happy with the default reports. The custom reports can be recreated based on the screenshots we took of them as a backup before we failed to move SSRS. It wasn't the end of the world to lose them.

The final hiccup with the whole thing was the removal of SQL from the original SCOM server. This went without a hitch: until we got a whole batch of errors to inform us that the Scheduler Data Source Module Failed Initialization: The Microsoft Operations Manager Scheduler Data Source Module has some invalid configuration. The IIS Discovery Probe Module Failed Initialization: an Error initializing IISDiscoveryProbe from config. The answer, after a lot of swearing was here. Turns out that the MSXMLS parser got uninstalled with SQL: I had no idea that there was a dependency for that, and we simply reinstalled that. All was then well.

If I were going to do this again, I would make very sure that I could restore SSRS properly before I did anything else. I would also test moving everything in a VM, and test moving it in different orders, to work out how best to get SSRS and all custom reports across.

OSQL -E

Sometimes, you get a black-box installation by a third party supplier: and, when you politely enquire as to whether you can install SSMS on the server so that you can pull out pertinent information for your SQL Estate Audit (because, naturally, the installation on the server is such that you cannot connect remotely: you just know that the server is there), the answer is no. Grrr.

But not the end of the world. Via the command prompt, we can open a connection to that instance of SQL, and run whatever queries we fancy with a local administrator's account (because, let's face it, if they haven't installed SSMS, and they're a third party supplier, the likelihood is that they haven't disabled the BuiltIn\Administrators group....).

Start off by checking the Services on the Server, to get an indication of what you've got: also check SQL Server Configuration. This will let you know how many instances of SQL are on the server, and whether they have a name other than the default servername. On the server I'm looking at, I've got two instances of SQL Express. I can connect to one by typing the following in the command prompt:

OSQL -E

For the second, I need to be a bit more specific, and type

OSQL -E -S ServerName\InstanceName

Once I've got OSQL running, I can query merrily. Every T-SQL statement that I can run in SSMS or Query Analyzer can be run in OSQL. I can execute stored procedures. I can change databases. I just need to remember that instead of hitting 'F5' to run my statement, I need to end my statement with 'GO', and press enter. It's worthwhile not running 'SELECT *', since the formatting leaves something to be desired, and it's a bit tricky....

So, what might my useful statements be? I've used a selection of the following today

To list my databases
SELECT name
FROM sys.databases
GO

To check authentication mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown'
END
GO

To check recovery mode

SELECT recovery_model_desc
FROM sys.databases
GO

To check collation

SELECT name, collation_name
FROM sys.databases
GO

To check database owner

SELECT name, SUSER_SNAME(owner_sid)FROM sys.databases
GO

To check SQL Version, Windows Version and SQL Service Pack

SELECT @@VERSION
GO
SELECT ServerProperty('ProductLevel')
GO

To check database size

USE dbname
GO
EXEC sp_spaceused
GO

It's pretty easy to find out what you need by searching for the criteria, with an additional search of 'T-SQL' slapped at the front of the string. More additions will be made here as I come to need them, however, this got me what I wanted for my spreadsheet....

experts-exchange.com

Have you ever tried scrolling right the way down to the bottom of the page when Google has pointed you at a potential solution?

Go on. Past all the random grey boxes.

Useful, eh?

If you're navigating round the site itself, this scrolling doesn't work: but if you take the URL of the discussion you're looking at and stick that into Google (or Bing, or whatever) and then click back through from there, scroll down: bingo! An answer.

You still need to sign up if you want to ask questions, though.

How to transfer logins and passwords

A couple of years ago, when I was a very junior DBA, we upgraded our main database (and everything else on the server), which contained all the information about everything at the University that employed me at the time, (students, faculty, staff, courses: you name it, it was in there) from SQL 2000 to SQL 2005, moving it across servers at the same time. This database was used by many members of staff on a daily basis, via a custom built Delphi interface. When it came to migrating all the usernames and passwords, we made use of this interface to log the users' keystrokes to find out their passwords, and input this all into a great script to recreate them on the new server. Where a user had not changed their password from the inital password given, or where a user's password did not fit the new server's security policies, or a user had not logged in during the window we were monitoring keystrokes, we reset the password. We then forced all users to change their passwords on their next login, and it worked pretty well. It wasn't my decision to use this method, and I'm not sure I'd chose it now: it all depends on the business needs.

At the moment, I'm setting up database mirroring. I need to transfer over the logins from my principal, source server to my mirror server. These are all system accounts, where I know the password (heck, it's in the password safe), but I don't want to create them all manually. Nor do I want to use IDERA's brilliant SQL Permissions tool - I don't need all the user account information - just to script out the logins themselves. After all, the user account information will be contained in the databases that I'm mirroring, once I've restored them to the server. Ideally, I'd like to script them all out in one go. Ideally, I'd like them to have the passwords in the script too.

Fortunately, I can: and without knowing the passwords. Microsoft have created a stored procedure, sp_help_revlogin, that can be used to transfer logins between instances of SQL 2005 and SQL 2008 on different servers (For SQL 2000 and SQL 7.0, check this KB article). It gets created in the master database (yes, I know, goes against all received wisdom about NOT making changes to the master database), along with a second stored procedure, sp_hexadecimal. Executing sp_help_revlogin results in a script that contains all the logins on the server, their default databases, and hexadecimal representations of the binary strings that hold login and password information. Run the script generated by sp_help_revlogin on the destination server, and the logins will be created. You will need to have databases with the correct names on the destination server, which need not be restored versions of the source databases, as the script will not run without the presence of the logins' default databases. They will not have access to connect to any of the databases, as user accounts have not been linked to the logins, but it's easy enough to do that (and there are many scripts available). If you can, it makes sense to restore the databases from the principal server to the mirror server, leaving them in a fully functional state, before running the logins script. And, naturally, if you're not setting up mirroring, then you would do this anyway. With mirroring, you need to make a call: create empty versions of the databases, and restore over them, or restore the databases twice over.

That I could, essentially, copy the logins and passwords across, without having to monitor anything, or know the passwords, was a bit of a revelation. I wish we had known this two years ago.

Nothing to do with SQL

For months, now, I have been unable to navigate to the software server from my PC: every time, I'd get a login box, and then an account locked error. Bizarrely, I can RDP to the server just fine, and navigate to the various folders on it if I'm on any other machine. Just not from my PC

In the event viewer on my PC, I found Event 14 (Kerberos)
"There were password errors using the Credential Manager. To remedy, launch the Stored User Names and Passwords control panel applet, and reenter the password for the credential domain\sweetsql"

Well, there's no such applet in the Control Panel, but some googling revealed how to get to it, courtesy of tweekxp.com.

Click on START - RUN and type the following (follwed by ENTER):
rundll32.exe keymgr.dll,KRShowKeyMgr

It seems that Windows XP sometimes stored login credentials in a local cache, and this can be completely out of synch with the rest of the domain or corrupted. Rather than modifying the password in the Stored User Names and Passwords applet, I decided to simply delete the reference to the relevant server: it saves it all going wrong the next time I change my password (and, if I remember correctly, it first started happening when I changed my password).

Although this problem wasn't SQL based, it could affect you when you are using Windows Authentication to connect to a server via SQL Management Studio.

And, now it makes sense...

I had another email from Lightspeed, in response to my request to know why my databases were shrinking even though autoshrink = false, and this got to the root of the matter.

Previous versions of Total Traffic Control could be run on SQL Express, which has a 4GB database size limit: so, TTC has a subroutine that automatically shrinks databases, in order to get round this issue.

Now, however, they don't support running the system on SQL Express, so this code is redundant. However, it's not yet been removed. They're working on that: it'll have to be thoroughly tested first, so it won't be instant.

I'm very glad I asked: and I'm very pleased to have had such a good reply!

A response from Lightspeed

Remember this post? Well, I emailed Lightspeed. Their response?

"By default, the database is set to autogrow and autoshrink to save space."

In the last week, I have had the following.

Initial size 5430MB
Manually grown database to 8000MB
Autogrow to 8800MB
Autoshrink to 5430MB
Autogrow to 5900MB

There is plenty of space on the drive on which the database files reside (C:\, because Lightspeed recommend that SQL be installed on the C:\drive running under the local system account).

Moreover, autoshrink is set to false for every single database on the server, so I don't know what exactly is causing this shrinkage, but it ain't SQL. In fact, I would posit that it is completely inaccurate to say that the databases are autoshrinking, as this shrinkage does not show up in the 'Data/Log files Autogrow/Autoshrink' events part of the Disk Usage reports for the databases. It just happens, it doesn't show up in the logs, and it is really rather annoying me.

Just because

Just because you can use spaces, and inverted commas, and all sorts of other rubbish in your tablenames, doesn't mean that you should.

Credentials, Proxies, and SSIS SQL Agent Jobs

What I want to do:
Give one user access to see and execute one SSIS package as a SQL Agent Job, but none of the rest. Although the package exports data from one database on Server A to a new database on Server B, this does not count as a multi-server job in SQL terms. I've assumed that the package itself has been created and works as a SQL Agent Job under a sysadmin login, but I don't want to give this guy sysadmin access. He's a developer.

1. Create the credential, based on an existing login, on Server B. If you use a Windows login, it will want the correct password for that login, so don't get clever and make one up (can you spot the mistake I made?).





















2. Make sure that the credential's login has the correct access on Server B
3. Create the proxy, point it at the credential.
4. Add principals to the Proxy i.e. the accounts that you want to have the rights of the proxy when executing the SQL Agent Job.



















5. Create the job, and make the user's Login the owner of the job.
6. Give the user's Login the following rights in MSDB: db_ssisoperator and SQLAgentUserRole
7. Test.

I found it very helpful to test this process out using my non-sysadmin Windows account: it meant I could test out the process while being able to see all error messages on my own machine.

Doubtless, there are better explanations of how to do this out there: and I am not sure what I would do if I needed to allow multiple users to execute the same package under such constraints. I am sure someone will let me know...

But what if the FTS won't?

When building a Full Text Search Catalog and Index, it is a Good Idea to check that the search works properly at the end of it, by using something along the following lines:

SELECT *

FROM tablename

WHERE Freetext (COLUMN, 'some text')

Now, I knew the create script worked, as I'd used it to create my Very First Full Text Catalog and Index only a week ago. I knew the select would work, because, again, I'd tested it. SQL 2008 SP1 was about to have a bit of a disagreement with me about what was, and what wasn't, going to work.

Not so.

SQL Server encountered error 0x80070005 while communicating with full-textfilter daemon host (FDHost) process. Make sure that the FDHost process isrunning. To re-start the FDHost process, run the sp_fulltext_service 'restart_all_fdhosts' command or restart the SQL Server instance.


and in the Logs:


SQL Server failed to communicate with filter daemon launch service (Windows error: Windows Error: hr = 0x80070005(failed to retrieve text for this error)). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Running sp_fulltext_service 'restart_all_fdhosts' did not improve matters: the command ran happily, but the error persisted. Restarting SQL didn't help either (heck, it's a DEV box, and not yet ready for the Developers).

Searching for 0x80070005 told me that the problem was to do with permissions: and the solution turned out to be setting the SQL Full Text Filter Daemon Launcher Service to run under the same account as the SQL Server Service.

The SELECT statement then deigned to execute: but did not return any results. Rebuilding the index did not help matters: however, dropping the FULLTEXT index, the index, and the Full Text catalog did work. T-SQL here for posterity for next time I have this problem.



DROP FULLTEXT CATALOG CatalogName

DROP FULLTEXT INDEX ON dbo.TableName

DROP INDEX IndexName ON dbo.TableName

When the autoshrink/autogrow won't show in SQL Reports

...Try the script below, from this discussion on MSDN. It worked beautifully for me when it turned out that there were well over a million rows of autogrow/autoshrink data, and thus the autogrow/autoshrink data from SQL 2008's built in reports wasn't going to show correctly.

I then changed the data file size, so that the thing grew in one BIG chunk, gave it lots of elbow room for more data to fill up the file, and left it well alone.

DECLARE @filename VARCHAR(100), @filenum int

SELECT @filename = CAST(value AS VARCHAR(100))

FROM fn_trace_getinfo(DEFAULT)

WHERE property = 2

  AND traceid = 1

  AND value IS NOT NULL



-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'



--Check if you have any events

SELECT gt.HostName,

       gt.ApplicationName,

       gt.NTUserName,

       gt.NTDomainName,

       gt.LoginName,

       gt.SPID,

       gt.EventClass,

       te.Name AS EventName,

       gt.EventSubClass,

       gt.TEXTData,

       gt.StartTime,

       gt.EndTime,

       gt.ObjectName,

       gt.DatabaseName,

       gt.FileName

FROM [fn_trace_gettable](@filename, DEFAULT) gt

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id

WHERE EventClass in (92, 93, 94, 95)

and gt.DatabaseName = 'tempdb'  --Change the DB Name here

ORDER BY StartTime;

Taking it futher

The awesome SQLRockStar came out with a nifty tip about fragmented Virtual Log Files. SQL has this tendency to split the transaction log file out into different chunks. It might be physically contiguous, but, logically, it is fragmented, and this affects performance.



The tip teaches us how to use Policy Based Management in SQL 2008 to work out which databases have extensive Virtual Log File fragmentation. So far, so good. But hark! I hear you cry! I only have one SQL 2008 Server. Everything else is still SQL 2000 or SQL 2005. There is no Policy Based Management tool there.


Panic not. If you have set up a Central Management Server in SQL 2008, and registered your servers within it, you can evaluate the Policy against all your servers. Just right click on the group you want to check, pick 'Evaluate Policies', choose your source (i.e. the SQL 2008 box on which you set up the initial policy), and let it run. It tends to look as though it's stuck if one of the servers in the group you've chosen isn't running SQL or is turned off, but leave it to run, and, eventually it'll stop trying to connect to that box, and move onto the next one.

Once you know what's up, you can follow the instructions here. I did make up a handy script to make life a little easier, and I cannot emphasise enough that you must back everything up properly before you get going. It will save a vast amount of heartache in the long run. Honest truly.

USE [databasename]
-- Backup the Log File

BACKUP LOG [databasename] TO devicename
GO

-- Set recovery to simple mode, otherwise the log file refuses to shrink

ALTER DATABASE [databasename]
SET recovery simple
GO

-- Shrink the log file

DBCC shrinkfile ( transactionloglogicalfilename , truncateonly )
GO

-- Grow it in ONE BIG STEP
ALTER DATABASE [databasename]

MODIFY FILE

(

NAME = transactionloglogicalfilename

, SIZE = newtotalsize --MB

)
GO

-- Set recovery back to full mode

ALTER DATABASE databasename
SET recovery FULL
GO

Finally, run a full backup, so that all future transaction log backups have something to work from.

(I used the instant SQL formatter to format the SQL for this post. It looks, um, interesting....)

Scrap of SQL

How to identify duplicates in a table....


SELECT email,

Count(email) AS numoccurrences

FROM users

GROUP BY email

HAVING (Count(email) > 1)

Of course, what to do with all those duplicates is up to you!

3rd Party Software Again...

This time, Lightspeed System's TTC seems to be the culprit. It has a nifty little system that sets autoshrink = true for all its databases, and then it gets terribly upset if the database file fills up. Of course, if it wasn't autoshrinking, then it wouldn't need to autogrow, and it wouldn't get its knickers in a twist because it couldn't autogrow on the terms of SQL's default, which is an autogrow of 10%.

When autoshrink = true, SQL will try to shrink all database files with more than 25% free space. This creates a performance hit for the CPU: the default setting for SQL is to set autoshrink = false (except on the Desktop Edition, where the default setting for SQL is autoshrink = true).

When autogrow = true, SQL will try to grow the database file while running an INSERT. The query may well time out, the autogrow will fail, and the database file will fill up. The default for SQL is to set autogrow = true, with a growth of 10%.

Both autogrow and autoshrink cause problems with file fragmentation and index fragmentation. It is vastly more healthy to set the initial database file size to the anticipated database file size, and then leave it well alone. There will be less load on the CPU, neither files nor indexes nor pages will fragment and the server will run more efficiently. It's like the difference between ironing a shirt on an ironing board, unimpeded, and ironing a shirt on a folded up towel on one corner of your work desk. Yes, it can be done, but no, we really don't want to as it takes longer and is vastly more exhausting.

Why Lightspeed Systems seems to think that Total Traffic Control's databases should be set to autoshrink is something I haven't yet established. My Google-fu is not strong today, so if you do know the answer, I would love to hear it.

Error Number: 3241 Severity: 16 state: 73

When performing a restore from a SQL 2005 .bak file to a SQL 2000 Server, the above is likely to happen.

Another hint that things aren't going to work is the "Name" field being filled with the value *** INCOMPLETE ***.

*head*desk*

SSIS Package fails from SQL Agent

I've been configuring a test version of one of our Virtual Servers, which was created by cloning the 'Live' server using VMWare. I managed to negotiate round the @@Servername issue , and made sure to rename the databases so that we were quite clear that we were on the test server.

It took a couple of days, and the addition of the new server into the SSIS package which checks for failed jobs, before I noticed that the Scheduled Maintenance Plans to rebuild indexes were failing on the new server: because, in part, they were trying to connect to the old server. So, I changed things around, pointed the plans at the new server, waited 24 hours, and still they failed.

Odd.

There was nothing in the job history for the Maintenance Plan, and only this in the Job History Log (servername blanked to protect the guilty).

"Unable to start execution of step 1 (reason: line (1): Syntax error). The step failed".

A similar job gave the error:

"The command line parameters are invalid. The step failed." Again, no errors...

Looking inside the job, the command line seemed reasonable, giving, as it did "/SQL "Maintenance Plans\Weekly Check Integrity and Rebuild Indexes" /SERVER Servername /CHECKPOINTING OFF /SET "\Package\Weekly.Disable";false /REPORTING E"

What I should have done next was run that in sqlcmd. What I did was start searching Google. I found a helpful discussion thread, and peered at the SQL Agent Job Step.

Now, given that the job had been scheduled via the Job Scheduler within the maintenance plan, all should have been well. However, it wasn't. There was a missing "\" in the Maintenance Plan path.

Browsing to the package using the button with the three dots to the right of the Package Path made sure I was looking in the right place: and, to my surprise, the Package Path changed every-so-slightly once I'd clicked OK.

A backslash appeared, and the job ran successfully.

Now, there are other reasons why an SSIS package would fail when called from a SQL Agent Job, and Microsoft describes them here: they apply regardless of the Service Pack you have on your SQL 2005 instance.

As far as I can tell, however, the issue I've described here only applies to SQL 2005 RTM. The server is on the list to be service packed, and, now that we have the test version, we can check that the third party software won't throw a wobbly before applying it to live.

All's well that ends well....

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

So, having decided to use the built-in Standard Reports on SQL 2005 to check how one of my databases had been auto-growing and shrinking, as the free space looked a little suspect (as did the fact that the database had shrunk between Friday morning and Monday morning for no apparent reason other than a reindex...), I got the error in the title:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Fair enough. According to SQLSkills, this is a compatibility issue. I was using SQL 2008 Management Studio, and was checking a SQL 2005 database. Usually, this doesn't cause a problem, as the reporting tools were first introduced in SQL 2005.

Unfortunately, this database turned out to have a compatibility level of 80 - SQL 2000. Running the report in SSMS 2005 gave the following error:

Unabel to display the report because the database has a compatibility level of 80. To view this report, you need to use the Database Properties dialog to change the compatibility level to SQL 2005 (90).

This has the advantage of being rather more explicit, but none the less irritating...

Sometimes, it ought to be obvious....

Over the Christmas break, we moved our Raiser's Edge database files, so they weren't sitting on the c:\ drive (where they installed as default), but on the data drive for that server. One of my colleagues used the BlackBaud Manager for the system, rather than me doing anything clever in the back end.

At which point, the backup job, created via BlackBaud, failed.

First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.

Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.

Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.

It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.

Backups are now running.

Beware Third Party Software: it can do odd things

DBCC CHECKDB on (naturally) the largest database we have, the one which supports the mainwebsite and all the forums, failed.... PANIC! PANIC!

DBCC results for 'MyDB'.Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:576726) with latch type SH. 38
(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object
.CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).

Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'MyDB' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The SQL Error Logs showed a sad tale:

A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a4000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a8000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a6000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199aa000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a0000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a2000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199ae000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The operating system returned error 1784(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x00000074b48000 in file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Error: 17053, Severity: 16, State: 1.O:\DATA\MyDB.mdf:MSSQL_DBCC24: Operating system error 1784(failed to retrieve text for this error. Reason: 15105) encountered.

Enough to give anyone the screaming habdabs, right? SQL 2008, on the SAN, a really important database, and it can't complete a DBCC CHECKDB owing to I/O issues.

Oddly, every other DBCC CHECKDB (bar one, which showed page errors) completed successfully. The SAN guy couldn't find any problems with the SAN I/O, and there hadn't been any changes to the switches.

What else? Well, we use Diskeeper 2010 to keep the database drives defragged (it works rather well), and it would seem that the new Intelliwriter Option causes DBCC CHECKDB to react badly, as the file expands to allow for non-fragmented writing, and DBCC CHECKDB doesn't expect this. I am not the only DBA who has had the problem. Diskeeper have produced a fix, which is available here, but in the short term, disabling Intelliwriter corrects the issue - and DBCC CHECKDB will complete happily and there is no need to panic.

Still, my next move will be to write an SSIS package to connect to all the servers and run DBCC CHECKDB on each database in turn....

sp_fulltext_catalog

And a happy new year to you. Mine started like this (among other issues), with an error for an hourly rebuild index job.


USE [thisdatabase]


EXEC sp_fulltext_catalog

N'this_database_index' ,

N'start_full'

Gave this result

Executed as user: Machinename\SQLAdmin. Execution of a full-text operation failed. The Gatherer is shutting down. [SQLSTATE 42000] (Error 7619). The step failed.

The Gatherer? You what?

This was on a non-clustered SQL 2000 box (8.0.2055), and the solution was to restart MSSeach. This I did via Services Administration, where it is helpfully labelled as 'Microsoft Search'. I have no idea what The Gatherer was, or did, or does, but it seems to be happy now.