Category Archives: Administration - Page 2

Script Commands to Disable/Enable SQL Agent Jobs

The script below is a quick way to create disable and enable SQL Agent job commands for existing jobs. It will script out the correct ‘EXEC msdb..sp_update_job’ statements. In this example, the where clause filters for “enabled = 1”, but you can use it create statements based of various job characteristics. We tend to use these when migrating to new servers and want to leave the old server in place.

/*
Script commands to disable/enable jobs
5/20/2014 JCD
Tested: 2005, 2008 R2 and 2012 SP1

You can further filter jobs with other sysjobs columns to specify
an even smaller subset of enabled jobs. Good examples for adding
filers are owner_sid, description and category_id.

*/
SET NOCOUNT ON
GO

USE msdb
GO
-- Script disable commands for enabled jobs
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=0;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
-- Script enable commands to enabled jobs after disabling. Should be run when creating the disable script
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=1;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO

SSRS Active Session Information

SQL Server Reporting Services (SSRS) showed up to the party in SQL Server 2000.  A really awesome tool for data delivery.  Most SQL Server environments heavily leverage this powerful tool.  Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog, dbo.ExecutionLog2 and dbo.ExecutionLog3).  SSRS also supplies a text log with additional details for errors and warnings.  All are  useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?

Recently, I needed to  identify an active report that was creating a considerable amount of blocking.  We were able to identify a reporting login , verify the  blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking.  As I mentioned, for the most part the ReportServer database gives us historical information.  I always based most of my queries on the ReportServer database.  In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB database might just store ‘temporary’ data about active sessions. Welcome back to the show, Jack!
Read more »

SQLPerformance.com from SQLSentry

I was pretty excited to see the launch of a new website from SQLSentrySQLPerformance is going to be a great resource for the SQL Server community.  Congrats to SQL Server MVPs Aaron Bertrand and Kevin Kline on the launch of the site.

Read Aaron Bertrand’s announcement  here.

Check out the free tool SQL Sentry Plan Explorer v1.3 . We use it a lot to review queryplans.

Quick Server Info – sys.dm_os_sys_info and SERVERPROPERTY ()

I needed a quick query to get some server information using  sys.dm_os_sys_info and SERVERPROPERTY ( propertyname ) .  It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide a high level look at you infrastructure. Tested on SQL 2005 and SQL 2008 R2.

The SQLOS Team  has a neat article about changes in 2012 for the DMV.

Read more »

XQUERY – Examples and Scripts

There are tons of thing in this world that I really don’t understand. SQL Server XQuery is just one of those just out of reach.  Poking around trying to answer a question for a developer, I found the following XML script examples. Teh writer provides excellent explanations and examples. Immediately found the right path(pun intended) to go down.  I so wished my brain worked this way!

XQuery Labs – A Collection of XQuery Sample Scripts

Jun 26 2008 3:41PM by Jacob Sebastian

Read more »

Disk Space – Finding I/O Bottlenecks(SQLIO)

As a production DBA , I get to start diving into the internals of the SQL Server environment. Over the last year, we had an opportunity to ‘let loose’ on a new clustered environment with a dedicated SAN.  Zoom… Zoom…Zoom! It was like performing sea trials or a shakedown tour in a new ship. Much of the testing centered around the execution of existing SQL code and processes. We were able to set-up a series of tests using the  Microsoft ‘s  SQLIO Disk Subsystem Benchmark Tool to saturate the disk subsystems.

 The tool does not actually test your SQL Server configuration , but provides the means to script out and execute a controlled load against the disk subsystem. Scripts can be executed to create a variety of read and write operations. Parameters allow the load to use a variety of file sizes, number of threads, latency, outstanding requests in random or sequential reads/writes for specific durations.   I am not going to get into the specifics of the scripts we used. The links below provide a much better examples than I could tap out in this post.

Read more »

Disk Space – DBCC UPDATEUSAGE

One important tool in managing disk space is to make sure the system accurately reports space used by tables.  DBCC UPDATEUSAGE will update the values returned by sp_spaceused.

The output of sp_spaceused “displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.”  One thing to note when reviewing the output is you may see the the reserved size is disportionately larger than the data and index space used. The reserved space should always be greater, but in one case I did see > 20 GB of difference. The space reported by system can get out of whack (technical term) from repeated use of TRUNCATE statements and frequent DDL statements.

Excellent article on DBCC UPDATEUSAGE by Armando Prato.

Google search for DBCC UPDATEUSAGE.

Read more »

Query Plans and active query information

Quick script to see active processes and to grab the query plan. It leverages some DMVs to get the query text and plan. Only can be used on SQL 2005 and above. It is a great and light wight way to see what is going on with the server and performance tuning.

Read more »

Disk Space – Get table sizes

Below is a script  I use to determine table size.  Basically, it walks through the user databases and for each table.  The information is converted in GB. It’s a pretty simple use of MS sp_spaceused with sp_msforeachtable. More info on sp_spaceused.

Full script, below. Download script here.

Read more »

Jeff’s SQL Server Blog – Proper Case UDF

Using My Favorite Tech Service –http://www.Google.Com.

I Found this Posting for a Proper case T-Sql User Defined Function on Jeff’s SQL Server Blog — Go to the post as it has an port to MySQL and a couple of other ‘ADVANCED’ examples in the comments.

From the original Blog :

I posted this one a long time ago and needed to use it today, so I thought I’d post it up here as well in case anyone finds it useful.  This simply attempts to capitalize the first letter of each word for the string passed in.  Use it to help clean up some pre-existing data, but don’t use it as a way of presenting your data that is stored improperly since it isn’t exact.�
Read more »