I recently came across a situation where I was working with a vendor that was upgrading one of our applications. He asked me to backup the production database and disable all jobs on the production SQL Server. I could just go through the SSMS GUI and do all of this, but I like to automate anything I can. It keeps me on my toes and (most of the time) makes it to where I don't have to be remoted in or at my desk if something needs to happen during off hours.
Simply disabling all of the jobs should be pretty easy, right? Jobs in SQL Server are stored in the msdb database in the sysjobs table. Since I want to disable all the jobs, I need to change the [enabled] tinyint field in the sysjobs table and change it to 0 (0 for disable, 1 for enable). Easy peasy:
USE [msdb]
UPDATE sysjobs
SET [enabled] = 0
However, there is one small problem with this. I this particular situation, there are 15 jobs on this SQL Server and only 13 of them are enable. There are 2 disabled jobs on this server, but we do use those jobs from time to time, so I don't want to delete them. The jobs will need to be re-enabled when the vendor finishes the work, but if I simply run:
USE [msdb]
UPDATE sysjobs
SET [enabled] = 1
that enables ALL of the jobs, including the 2 that need to stay disabled. So here is how I got around it. I decided to create a table and store the job_id (the unique identifier for the job in the sysjobs table) for each enabled job in it. I can use our good friend, the SELECT INTO statement, to create this table on the fly:
USE [msdb]
SELECT job_id
INTO enabled_jobs
FROM sysjobs
WHERE [enabled] = 1
So now I have a snapshot of the enabled jobs at a given point in time. I can then run an UPDATE statement and disable all of these jobs for the vendor:
USE [msdb]
UPDATE sysjobs
SET [enabled] = 0
WHERE job_id IN
(SELECT job_id
FROM enabled_jobs)
But there is just one more little problem: I want to automate this. Since I need to take my snapshot of which jobs are enabled at a point in time, I may need to create and recreate the enabled_jobs table over and over - especially if this code is to be reusable. So let's try this:
USE [msdb]
-- DROP TABLE if it already exists
IF OBJECT_ID (N'dbo.enabled_jobs') IS NOT NULL
DROP TABLE enabled_jobs
-- Get the job_id of all jobs that are currently enabled and create a new table
-- on the fly to hold those IDs
-- Store these job_ids in a table so that there is a record of the jobs that were
-- enabled and the ones that were not
SELECT job_id
INTO enabled_jobs
FROM sysjobs
WHERE [enabled] = 1
-- Disable all jobs that are enabled
UPDATE sysjobs
SET [enabled] = 0
WHERE job_id IN
(SELECT job_id
FROM enabled_jobs)
Now I can put all of this in a stored procedure and reuse it. Since we do not drop the enabled_jobs table at the end of our statement, it still exists and I can use it later to re-enable these jobs when our vendor has completed the work. It will re-enabled the ones that were previously enabled and keep the ones that were disabled disabled:
USE [msdb]
UPDATE sysjobs
SET [enabled] = 1
WHERE job_id IN
(SELECT job_id
FROM enabled_jobs)
I hope you enjoyed this entry and that maybe it even saved you a little time or that you learned something.
Wednesday, August 14, 2013
Disable All Enabled Jobs and Re-enable Them
Labels:
SQL Server Administration,
T-SQL
Location:
Columbus, GA 31904, USA
Wednesday, July 31, 2013
Changing the Restrict Access Option in SQL Server
Ever get something like this annoying popup when you try to change a SQL Server database from SINGLE_USER to MULTI_USER?
This is what always happens to me when a database is already locked down into SINGLE_USER mode and I try to change the property in the GUI. In order to change it, I run this T-SQL in SSMS (SQL Server Management Studio):
ALTER DATABASE [MyDatabase]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
If there is already an operation going on in the database, the "WITH ROLLBACK IMMEDIATE" will take things back to their previous state before the transaction started. After the statement completes, more than one authorized user should be able to access the database. Always use caution when performing any ALTER DATABASE statement on a production server. When a database is locked down in SINGLE_USER mode, an icon of a person will appear next to the database in SSMS:
ALTER DATABASE [MyDatabase]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
If there is already an operation going on in the database, the "WITH ROLLBACK IMMEDIATE" will take things back to their previous state before the transaction started. After the statement completes, more than one authorized user should be able to access the database. Always use caution when performing any ALTER DATABASE statement on a production server. When a database is locked down in SINGLE_USER mode, an icon of a person will appear next to the database in SSMS:
If you refresh your view of the databases in object explorer after running the above T-SQL, the person icon should disappear from the database indication that it is in MULTI_USER mode.
Labels:
SQL Server Administration,
T-SQL
Location:
Columbus, GA 31904, USA
Wednesday, November 14, 2012
The DATALENGTH() Function
Yesterday I was getting a very interesting error in SSRS. A user was trying to export data to Excel and it was basically telling her that it was too much data to fit into Excel. I began to suspect that one of the fields in the report was quite large. I went to run a standard T-SQL query to see exactly how large the field in question was. The field was named [Description] and it's type was text:
USE [MyDatabase]
SELECT LEN([Description])
FROM MyTable
Of course, you know what happened:
Msg 8116, Level 16, State 1, Line 3
Argument data type text is invalid for argument 1 of len function.
So, the LEN() function cannot be used on text type fields. Was there a LEN() equivalent for the text type? A little googling revealed that there was:
DATALENGTH()
This function was exactly what I needed to get the length for a text type field.
USE [MyDatabase]
SELECT LEN([Description])
FROM MyTable
Of course, you know what happened:
Msg 8116, Level 16, State 1, Line 3
Argument data type text is invalid for argument 1 of len function.
So, the LEN() function cannot be used on text type fields. Was there a LEN() equivalent for the text type? A little googling revealed that there was:
DATALENGTH()
This function was exactly what I needed to get the length for a text type field.
Monday, October 1, 2012
Finding the Name of the Primary Domain Controller
1. Open a command prompt
2. Type echo %logonserver%
That's it - not very complicated at all. The output in the command prompt is that machine's PDC (Primary Domain Controller).
2. Type echo %logonserver%
That's it - not very complicated at all. The output in the command prompt is that machine's PDC (Primary Domain Controller).
Labels:
Windows Server Administration
Location:
Columbus, GA 31904, USA
Monday, July 30, 2012
Rebuilding Indexes with T-SQL
In my last position, I ended up writing a lot of T-SQL. I have recently taken on a new position and I am the company's sole DBA, so there is less T-SQL, but it certainly has not gone away completely. Case in point, SQL Server's maintenance plans are pretty cool and very simple to implement, but they are not bullet proof. Last week I kept getting an alert an index rebuild job had failed. I took a look at the maintenance plan, troubleshot it, and even rebuilt it but it continued to fail. I then decided to look at the underlying T-SQL associated w/ this job. It was failing on one particular index rebuild and succeeding on the rest of them. However, this still shows up in the job as a failure. I decided to script out the index rebuild and since then I have had no issues with the job. I opted to leave the CATCH block empty. Depending on how important the job is, it may be wise to have something in the CATCH that writes to a table so that one can be aware of exactly which index rebuilds failed.Additionally, I opted to sort in the tempdb as opposed to the DB itself. Once again, that one is your call. It will depend on how your environment is set up as to whether or not this is a good idea.
USE [DB_NAME_GOES_HERE]
DECLARE @SQL VARCHAR(3000)
, @START INT
, @LAST INT
, @TABLE_NAME VARCHAR(200)
, @IX_NAME VARCHAR(200)
CREATE TABLE #TEMP(ID INT IDENTITY(1,1), [TABLE_NAME] VARCHAR(200)
, [IX_NAME] VARCHAR(200))
INSERT #TEMP
SELECT '[' + S.NAME + '].[' + T.NAME + ']' AS [TABLE_NAME], I.NAME AS [IX_NAME]
FROM SYS.INDEXES I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE I.INDEX_ID > 0
ORDER BY T.NAME, I.NAME
SET @START = (SELECT MIN(ID) FROM #TEMP)
SET @LAST = (SELECT MAX(ID) FROM #TEMP)
WHILE @START <= @LAST
BEGIN
SET @TABLE_NAME = (SELECT [TABLE_NAME] FROM #TEMP WHERE ID = @START)
SET @IX_NAME = (SELECT [IX_NAME] FROM #TEMP WHERE ID = @START)
SET @SQL = ' BEGIN TRY
ALTER INDEX [' + @IX_NAME + '] ON ' + @TABLE_NAME + '
REBUILD WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF
, SORT_IN_TEMPDB = ON )
END TRY
BEGIN CATCH ' +
-- If you want something to happen in the Catch block
-- , place that code here
' END CATCH'
EXEC (@SQL)
SET @START = @START + 1
END
DROP TABLE #TEMP
USE [DB_NAME_GOES_HERE]
DECLARE @SQL VARCHAR(3000)
, @START INT
, @LAST INT
, @TABLE_NAME VARCHAR(200)
, @IX_NAME VARCHAR(200)
CREATE TABLE #TEMP(ID INT IDENTITY(1,1), [TABLE_NAME] VARCHAR(200)
, [IX_NAME] VARCHAR(200))
INSERT #TEMP
SELECT '[' + S.NAME + '].[' + T.NAME + ']' AS [TABLE_NAME], I.NAME AS [IX_NAME]
FROM SYS.INDEXES I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE I.INDEX_ID > 0
ORDER BY T.NAME, I.NAME
SET @START = (SELECT MIN(ID) FROM #TEMP)
SET @LAST = (SELECT MAX(ID) FROM #TEMP)
WHILE @START <= @LAST
BEGIN
SET @TABLE_NAME = (SELECT [TABLE_NAME] FROM #TEMP WHERE ID = @START)
SET @IX_NAME = (SELECT [IX_NAME] FROM #TEMP WHERE ID = @START)
SET @SQL = ' BEGIN TRY
ALTER INDEX [' + @IX_NAME + '] ON ' + @TABLE_NAME + '
REBUILD WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF
, SORT_IN_TEMPDB = ON )
END TRY
BEGIN CATCH ' +
-- If you want something to happen in the Catch block
-- , place that code here
' END CATCH'
EXEC (@SQL)
SET @START = @START + 1
END
DROP TABLE #TEMP
Labels:
SQL Server Administration,
T-SQL
Location:
Columbus, GA 31904, USA
Tuesday, July 10, 2012
Great Quote of the Day
This concept is simple and primary, but often overlooked:
"...it is a good idea to create non-clustered indexes on all foreign keys. You will always run lots of queries, and also use the JOIN operator, based on your primary and foreign keys."
- Wagner Crivelini
From the article SQL and the JOIN Operator
"...it is a good idea to create non-clustered indexes on all foreign keys. You will always run lots of queries, and also use the JOIN operator, based on your primary and foreign keys."
- Wagner Crivelini
From the article SQL and the JOIN Operator
Location:
Columbus, GA 31909, USA
Friday, May 25, 2012
Converting a Julian Date
I had to accomplish this at work yesterday. I did not see anything in the pre-defined SQL Server functions that would do it, so I started Googleing. Thank goodness for stackoverflow.com.
DECLARE @JULIAN_DATE INT = 2012146
SELECT DATEADD(D, CAST(SUBSTRING(
CAST(@JULIAN_DATE AS VARCHAR), 5, LEN(@JULIAN_DATE) - 4) AS INT) - 1,
CAST('1/1/' + SUBSTRING(CAST(@JULIAN_DATE AS VARCHAR),1,4) AS DATE))
AS [NEW DATE]
http://stackoverflow.com/questions/2692361/most-concise-way-to-convert-julian-date-yyyyday-of-year-to-sql-datetime
This T-SQL is a little too verbose to be writing over and over again so later today I'm going to roll it up into a UDF (User Defined Function).
DECLARE @JULIAN_DATE INT = 2012146
SELECT DATEADD(D, CAST(SUBSTRING(
CAST(@JULIAN_DATE AS VARCHAR), 5, LEN(@JULIAN_DATE) - 4) AS INT) - 1,
CAST('1/1/' + SUBSTRING(CAST(@JULIAN_DATE AS VARCHAR),1,4) AS DATE))
AS [NEW DATE]
http://stackoverflow.com/questions/2692361/most-concise-way-to-convert-julian-date-yyyyday-of-year-to-sql-datetime
This T-SQL is a little too verbose to be writing over and over again so later today I'm going to roll it up into a UDF (User Defined Function).
Labels:
T-SQL
Subscribe to:
Posts (Atom)

