Browse by Tags

All Tags » Tips (RSS)
Changing the Data Files Location after Installation
10 August 2008 10:28
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... Read More...
by ACALVETT | with no comments
Filed under: , , ,
SSAS 2005 – Server side tracing starter kit
07 April 2008 22:32
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... Read More...
by ACALVETT | 1 comment(s)
Filed under: , , , ,
Transaction log backup deadlock
19 February 2008 23:10
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... Read More...
by ACALVETT | with no comments
Filed under: , , , ,
SSMS Log file viewer and Deadlock Graphs
23 January 2008 16:02
Firstly I must say a big thank you to Microsoft for the new deadlock trace flag 1222. Compared to the trace flag output for 1204 & 1205 that you had to use in SQL 2000 it’s a walk in the park to interpret. 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... Read More...
by ACALVETT | with no comments
Filed under: , ,
The Job whose owner kept coming back......
30 December 2007 19:25
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... Read More...
by ACALVETT | 3 comment(s)
Filed under: , ,
My old mate sp_recompile
12 October 2007 11:33
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... Read More...
by ACALVETT | 2 comment(s)
Filed under: , , ,
Server level VLF report
25 July 2007 23:27
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... Read More...
by ACALVETT | 2 comment(s)
Filed under: , ,
What is that SPID doing?
27 March 2006 17:44
This is certainly not a new topic but something that i felt was worth revisiting and reminding people about, especially because i find many people are not aware of the ability to extract the statement within a stored procedure being executed. So, prior to SQL 2000 SP3, when we want to know what code a SPID is executing we have to use DBCC INPUTBUFFER (). The... Read More...
by ACALVETT | with no comments
Filed under: , ,
2005 - Alter failed for server XXX
13 February 2006 18:04
Thats exactly what i thought!! What the hell does that mean? Well, for me this occured in the context of running the maintenance plan integrity check task. It had been running fine for weeks but suddenly boom every time it ran it would fail with "Alter failed for server XXX". There was no obvious reason for this error so i had to dig out profiler to see what... Read More...
by ACALVETT | with no comments
Filed under: , , ,
Configuring a secure domain account for ASP.NET on Windows 2000
26 January 2006 18:01
I recently had to enable the ASP.NET account on a Windows 2000 machine to use a domain account so we could use NT Authentication to the SQL backend and the implementation of the domain account had to be secure. The hardest bit was securing the account, I wanted to give the account as few privileges as possible and make sure the account details could not be easily... Read More...
by ACALVETT | with no comments
Filed under: ,
A performance analysis example
15 January 2006 17:53
Last week i was dragged kicking and screaming away from my allocated SQL 2005 days to spend 2 days diagnosing performance problems on one of the firms Systems and write a report. Due to the sensitive nature of the data on the system it has been managed by another team and this was the first time i had looked at it. I thought i'd share my thought process to identifying... Read More...
by ACALVETT | with no comments
Filed under: , ,