tag:blogger.com,1999:blog-34660522908119229372011-12-15T15:54:35.232ZAs I learn more as a DBA: I may as well put it here in one place.Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.comBlogger571100tag:blogger.com,1999:blog-3466052290811922937.post-34504313574177504282011-12-15T15:44:00.004Z2011-12-15T15:54:35.247Z

And managed to set your primary database as your secondary database, because, when you scripted out the T-SQL to create log shipping, you forgot to change server half way through... (yes, I did it twice today in a bit of a DR scenario).First, disable the Log Shipping Copy and Log Shipping Restore Jobs on the Primary Server. You won't be able to do this by right clicking on the SQL Agent job and

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-77034093915072377982011-11-30T14:38:00.003Z2011-11-30T14:46:18.319Z

Collation can be set at all different levels in SQL, from the server, down to the column level. And it's a pain when it does't match up. Fortunately, there is a fast workaround for T-SQL queries. SELECT a.employeeid, a.name, b.employeeid, b.nameFROM employee a, employeedetails bWHERE a.employeeid = b.employeeidIf the collation on employeeid in table a does not match the collation for employeeid

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-81461350317774304192011-11-23T12:35:00.003Z2011-11-23T12:42:35.052Z

Microsoft have a very long list of equations to work out, including the use of logarithms.So I put it into a spreadsheet.The spreadsheet only deals with non-unique non-clustered indexes at the moment, because that's what I was trying to work out. It should be reasonably simple - plug in the numbers in the cells adjacent to the bold text, make sure that you're calculating the correct number of

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-62752247391953628892011-10-12T10:03:00.002+01:002011-10-12T10:07:54.614+01:00

For those moments where you can only get access via osql.exe....CREATE LOGIN [Domain\UserName] FROM WINDOWS;GOEXEC master..sp_addsrvrolemember [Domain\UserName],'sysadmin'GOWhy yes. I did just lock myself out of a server accidentally this morning. Where Management Studio wasn't installed. Go figure.

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com1tag:blogger.com,1999:blog-3466052290811922937.post-12488963293970839172011-07-19T14:43:00.002+01:002011-07-19T14:59:19.937+01:00

Or: Why does it work on the source server, but give us errors on the client machine?An error occurred during client rendering.An error has occurred during report processing.Cannot read the next data row for the dataset ThisDataSet.For more information about this error navigate to the report server on the local server machine, or enable remote errorsBefore I got as far as enabling remote errors, I

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-13347678705597964442011-06-13T15:32:00.002+01:002011-06-13T15:53:08.955+01:00

Now: we don't enable xp_cmdshell lightly, as it allows a degree of control over the operating system that renders the SQL server vulnerable. However, when we do enable it, it can only be executed by members of the sysadmin role. Sometimes, this isn't quite what we want: we only want the account to be able to execute xp_cmdshell, not to be able to muck about with other SQL system

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-62762362717127902742011-04-14T13:47:00.003+01:002011-04-14T14:08:23.308+01:00

I had a backup that was hanging: eventually I ended up restarting the server. It wasn't running through the GUI of SSMS, though. It was running from a process on the server, some 3rd party software. Stopping the software didn't stop the backup - it wouldn't. It's a file operation, and these things are pretty-much unkillable within SQL. Stopping the backup was possible solely by restarting the

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com1tag:blogger.com,1999:blog-3466052290811922937.post-30432654969729961462011-02-09T11:34:00.006Z2011-02-09T11:39:29.839Z

To execute sp_helprotect on all databases on a server, saving the results into a temporary table, and then displaying the results, including the database name. Makes use of sp_msforeachdb, an undocumented stored procedure which is very nifty for cycling through each database on a server.It took me a while to work out how to do what I wanted to do... This code is provided as-is, with no warranties

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-41479287983954490752011-01-04T13:42:00.002Z2011-01-04T13:46:41.235Z

On receiving this error when a user connects to SQL, before you try anything: make sure that the user account is not actually locked out. Then you can go to the Microsoft KB Article, and start futzing about checking that the DNS is definitely resolving, that things are in the correct domain, that the SPN is correct, and that the SQL Server Service Account is not locked out.This suggestion is

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-45502065043254712972010-12-09T15:31:00.000Z2010-12-09T15:37:12.674Z

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-87472828236916852062010-11-16T15:24:00.003Z2010-11-16T16:06:23.591Z

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,

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-74865588234140330452010-10-19T16:42:00.003+01:002010-10-19T17:17:10.813+01:00

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', 1GORECONFIGUREGOSP_CONFIGUREGOSP_CONFIGURE 'awe enabled', 1GOSP_CONFIGURE 'show advanced', 0GORECONFIGUREGOIf necessary, follow the instructions to enable the

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-86943904930777730242010-09-13T11:57:00.002+01:002010-09-13T12:25:04.520+01:00

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-7545449046177258672010-08-19T15:50:00.002+01:002010-08-19T15:53:47.391+01:00

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.

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-82713112058532234392010-07-19T15:38:00.003+01:002010-07-19T15:49:15.572+01:00

Error: 18456, Severity: 14, State: 38.MessageLogin 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 Bytes0000: 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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com1tag:blogger.com,1999:blog-3466052290811922937.post-62828611784367138932010-07-19T14:27:00.002+01:002010-07-19T14:37:47.652+01:00

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-69741061548627163282010-07-06T12:06:00.002+01:002010-07-06T12:16:23.773+01:00

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.

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-27394459919131733942010-06-23T13:58:00.002+01:002010-06-23T14:00:56.809+01:00

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer.Tells you what could be improved in your installation, and how to go about itGathers 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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-66345547717526526022010-06-23T11:10:00.000+01:002010-06-23T12:31:20.769+01:00

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com1tag:blogger.com,1999:blog-3466052290811922937.post-42869068866198124082010-05-26T16:19:00.004+01:002010-05-26T16:40:20.351+01:00

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-45174348634526145932010-05-12T13:06:00.002+01:002010-05-12T13:24:07.367+01:00

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,

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-83615613889851769092010-04-23T09:36:00.004+01:002010-04-23T10:23:23.944+01:00

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-16506490064479227582010-04-16T14:07:00.003+01:002010-04-16T14:19:13.499+01:00

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 PCIn the event viewer on my PC, I found Event 14 (Kerberos)"There were password errors using the Credential

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-91146504374061347022010-04-06T12:00:00.003+01:002010-04-06T12:04:05.700+01:00

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-13898841421838069712010-03-31T16:34:00.003+01:002010-03-31T16:42:28.169+01:00

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 5430MBManually grown database to 8000MBAutogrow to 8800MBAutoshrink to 5430MBAutogrow to 5900MBThere is plenty of space on the drive on which the database files reside (C:\, because Lightspeed recommend

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-1349411792729906602010-03-26T10:05:00.001Z2010-03-26T10:06:37.982Z

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-12030036844554820232010-03-02T15:02:00.004Z2010-03-02T15:16:56.298Z

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-58616789635680237152010-02-17T12:10:00.000Z2010-02-17T12:37:56.427Z

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 tablenameWHERE 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,

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-56318272525908624102010-02-15T10:55:00.000Z2010-02-15T11:11:35.088Z

...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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-24389402591255441992010-02-11T10:28:00.001Z2010-02-11T15:24:52.667Z

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-87883613784240898872010-02-05T10:54:00.000Z2010-02-05T10:57:12.335Z

How to identify duplicates in a table....SELECT email,Count(email) AS numoccurrencesFROM usersGROUP BY emailHAVING (Count(email) > 1)Of course, what to do with all those duplicates is up to you!

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-13562456858954757432010-02-01T14:56:00.000Z2010-02-01T15:53:07.073Z

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-64825382144514211772010-01-21T12:48:00.001Z2010-01-21T12:50:25.685Z

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*

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-25342135754254680202010-01-20T11:10:00.000Z2010-01-20T11:46:53.942Z

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,

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-74877130182823441492010-01-18T14:32:00.000Z2010-01-18T14:43:32.467Z

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com3tag:blogger.com,1999:blog-3466052290811922937.post-46359221272987192452010-01-08T09:49:00.000Z2010-01-08T10:05:52.791Z

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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-31078939392616843882010-01-05T09:44:00.001Z2010-01-21T12:56:21.820Z

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 1Unable 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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-21770675613851114632010-01-04T11:18:00.001Z2010-01-04T11:25:40.741Z

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_catalogN'this_database_index' ,N'start_full'Gave this resultExecuted 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

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-69680458347564601352009-12-15T17:15:00.000Z2009-12-15T17:21:45.056Z

The Midnight DBA has posted something rather nifty that can be found in SQL Profiler. There is the option, using sp_trace_setevent, to check for deprecated code - code that just won't work in future versions of SQL. Her explanation is rather more coherent than mine, I suspect she wasn't quite as excited.I wish I had known about this one before I started migrating databases from SQL 2000 to SQL

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-31162201706072844792009-12-15T17:01:00.000Z2009-12-15T17:15:39.937Z

And they got back to me! Hurrah!Alas, I was out of office the entire time they were trying to get back to me, didn't have a watch on the bug.... and Microsoft concluded that I was no longer having issues, so closed it.I shall try to get them to re-open this one. I know I am not the only one with the problem, but I do seem to be the only one who has reported it.... It's 507494, (if that link

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-61406141296995118382009-11-02T11:05:00.000Z2009-11-02T11:11:48.628Z

1. Set up a Connect Account, using your Windows Live ID Passport (note, I find it more helpful to set these up using a personal email account, rather than a work account which won't migrate jobs with you. It is possible to change email accounts within Live, but it is a complete and utter pain). Join the SQL Server Connect group.2. Visit the general feedback page and 'search this connection for'

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-22682287490350975312009-10-30T10:33:00.000Z2009-10-30T10:34:37.533Z

USE DatabaseEXEC sp_changedbowner 'NewOwner'

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-7423543062967319192009-10-28T13:56:00.000Z2009-10-28T15:06:29.640Z

Set up replication domain accounts for publisher and subscriber before you begin.Make sure that these accounts have access to the serversMake sure that the snapshot folder is on the correct server, in the correct place. If you get this wrong, right click on 'Replication -->Distributor Properties --> the button with the ellipsis (...)next to the publisher in question'. However when I tried this,

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-66677662047432469762009-10-28T09:53:00.000Z2009-10-28T09:57:41.224Z

"The job failed. Unable to determine if the owner (Domain\Administrator) of job IpmsaPolicyLogMaintenance has server access (reason: Violation of PRIMARY KEY constraint 'PK__syscachedcredent__0AD2A005'. Cannot insert duplicate key in object 'syscachedcredentials'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621))."The quick & dirty fix? Set it to

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-17686156299574509782009-10-23T11:09:00.000+01:002009-10-23T11:34:11.643+01:00

And these have been those annoying jobs that are, apparently, undeletable and unwanted maintenance jobs.Job #1A rather ancient backup job, or, rather, the remnants thereof, sitting in SQL 2005 Agent but with no maintenance plan behind it, no subplan schedule within it, and generally going 'Ha Ha' in a manner eerily reminiscent of Nelson Muntz. Assuming that the following error message can be

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-29775599192436142222009-10-19T14:49:00.000+01:002009-10-19T15:14:29.361+01:00

Those of use with MOM 2005 (Microsoft Operations Manager) and Forefront Client Security have faced the problem of large databases (why yes, you do need sunglasses for that page).The SystemCenterReporting Database needs to be really rather large: this nifty graph is my best effort involving Excel and the data given on the Technet Forefront Client Security Blog. The Y-axis is space required in GB,

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-43001683012711568482009-10-14T16:06:00.000+01:002009-10-14T16:22:28.167+01:00

From the Database Structure and Jobs page:WarningIn the BizTalk Management (BizTalkMgmtDb) database, there's a stored procedure named dbo.adm_cleanupmgmtdb. DO NOT RUN THIS STORED PROCEDURE! If you do run this stored procedure, all the entries in the database will be deleted.Why, I wonder, this obsession with deleting the entire contents of a database?All this, plus talk of the fact that BizTalk

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-62430241440447387922009-10-13T17:01:00.000+01:002009-10-13T17:04:21.270+01:00

We've got BizTalk now, and I'm still getting to grips with it. However, I came across this little gem on the Best Practices page."Delete all the dataIf the databases are too large and if the preferred method is to delete all data, you can delete the data.Caution Do not use this method in any environment where the data is business critical or if the data is needed. "You don't say....(more on

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-6287128168251431752009-09-25T11:42:00.001+01:002009-09-25T12:01:36.160+01:00

@@SERVERNAME. Sometimes, it isn't the same as the actual server's name. @@Servername is incorrect, it's wrong, it doesn't match. Argh!! It's easy enough to map though:SELECT @@SERVERNAME AS[@@SERVERNAME],CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceNameThe problem arises, when someone decides to rename

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-17573211233616047152009-09-14T14:51:00.001+01:002009-09-14T14:59:54.974+01:00

Me, I like to faff about with my resultsets in Excel. This probably makes me incredibly old fashioned, and I'm sure various of my friends would call me a flat-earther. Tant pis.However, I got all overexcited just now. Instead of highlighting the resultset, copying it using ctrl+c and pasting it into the spreadsheet at row #2, and then swearing greatly while I transferred the column names

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-10933341034593974222009-09-02T09:49:00.000+01:002009-09-02T09:55:34.293+01:00

This one is apparently a bug. It's a job that SQL creates when it installs SQL 2008, and it will perpetually error on the "Erase Phantom System Health Records" step, owing to the fact that it will insist on calling the method on the node name, rather than on the virtual servernameviz"MessageExecuted as user: Domain\AgentServiceAccount. A job step received an error at line 1 in a PowerShell

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com2tag:blogger.com,1999:blog-3466052290811922937.post-46812808319715669982009-08-18T10:18:00.000+01:002009-08-18T10:21:24.594+01:00

"failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."Or, perhaps, the failure reason is that the SQL Server in question is Standard Edition, and that's pretty much it?

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com2tag:blogger.com,1999:blog-3466052290811922937.post-9914548448290303612009-08-13T10:09:00.001+01:002009-08-13T10:14:33.717+01:00

'OSQL' is not recognized as an internal or external command, operableprogram or batch file.Oh. But it's where it should be! C:\Program Files\Microsoft SQL Server\80\Tools\Binn (not my installation).Within CMD, you can type 'set path' to check the variables in the default path. If the actual location of osql.exe isn't in there (or, rather, its parent folder - and it should be there, because SQL

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-70582778979452371282009-08-11T16:50:00.001+01:002009-08-11T17:04:40.570+01:00

And, because, primarily, this is my repository of Useful SQL Information.MasterContains serverwide information about SQLloginslinked server infoconfiguration infoinfo about user databases - locations of db files, key properties etcVital: SQL cannot start without it.ResourceAKA mssqlsystemreources.mdf in the file system. Rather completely invisible in SSMS. Contains all the system objects

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-10974791795932675152009-07-27T16:29:00.000+01:002009-07-27T16:35:00.875+01:00

Or what I learned about Service Packs.A SQL Maintenance Plan job had been running fine on my SQL 2005 RTM machine for months: until I joined the company, and made the mistake of having a look at it and doing some tidying. Next thing I know?"Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed."."But, but, but, but!" I cried "I've just stopped it faffing about

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-38572325561810257612009-07-17T09:32:00.000+01:002009-07-17T10:27:12.231+01:00

The job failed "Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan1' sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." That's the job history. The history against the Maintenance plan (I only learned about this more detailed history last week, and I think it's fab), said "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0tag:blogger.com,1999:blog-3466052290811922937.post-77100747392208629002009-07-14T14:43:00.001+01:002010-06-15T15:34:59.493+01:00

Trying to find out what the recommended database size for Operations Manager 2007 should be is a headache....Picture this. The boss mentions that you might just want to check the SCOM server, because, based on the situation on the MOM server, it might just be misconfigured. 41,240 events worth of MSSQLSERVER errors in the Application Event Log misconfigured? Owing to lack of space in the

Sweet Camden Lasshttp://www.blogger.com/profile/01983732031040847147noreply@blogger.com0