Rather annoyingly today, I had this error message pop up when trying to view a Stored Procedure.
The error is generated from SMO (). I knew that the stored procedure hadn’t been updated in a long time and my first thought was that there was some form of corruption.
Read more…
Ever had this really annoying error pop up when you are trying to view or edit a step within a Job? It happened to me again today.
I found a nice simple fix in the end.
Open up the command line tool Navigate to your Microsoft SQL Server\100\DTS\binn folder. From there run regsvr32 dts.dll
You should get a popup stating “DLLReigsterServer in dts.dll succeeded.” You’ll then need to restart Management Studio.
I discovered a small issue in production today. Our new DR server which is currently going through the rigors of testing had all of the Scheduled Jobs left enabled. I was confused why I was getting two alerts for some data validation processing we perform overnight with two different sets of data.
After a few minutes thought, I realised it could only be the DR system and I wanted a quick way to disable all of the jobs rather than shut down the Agent service which would trigger an alert in our monitoring software.
Here is my simple query to disable all jobs as well as one to re-enable, although our DR process is a little more sophisticated to only online jobs which are enabled in production.
/* Disable All Jobs */
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 0;’
FROM msdb.dbo.sysjobs
EXEC (@SQL)
/* Enable All Jobs */
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 1;’
FROM msdb.dbo.sysjobs
EXEC (@SQL)
I was recently installing the SQL Server 2008 R2 sample databases. I was repeatedly getting an error message indicating the filestream was not enabled on the R2 OLTP database.
Read more…
A number of our internal applications connect to our production databases for data cleansing operations. Unfortunately, the developers saw fit to have the server, login & password details in a plain text configuration file. This allows the team who use the cleansing tools all the information they need to access my production servers.
Read more…
A useful query to find the size of each data file in your database instance
Read more…
I recieved this error message today. The full error message was:
Windows error occurred while running SP_TRACE_CREATE. Error = 0×80070050(error not found).
I was in the process of testing a server side trace to capture a new overnight process rather than sit around waiting. In this case, the trace file I was attempting to create already exsited. I deleted the old trace file and ran the script again. Problem solved!
I found an interesting website today caled SQL Share. It has video tutorials from community leaders such as Andy Warren, Brian Knight, Kathi Kallenberger, to name but a few.
Read more…
Today a colleague kicked off a trace and went to a meeting. After a while, we noticed some performance issues. After looking at the processes running, I could see a trace was running. I had two options. I could kill the spid or I could stop the trace.
Read more…
Up until a few years ago, I’ll admit to having never been bitten by the parameter sniffing performance issue and if I am honest, I had not even heard of it. I got bitten by this yesterday
Read more…