SQL Script to Review Backup and Restore Processes

A coworker shared this awesome script to monitor backup and restore operation progress. I am not sure of the origin, but is definitely has become an essential member of my SQL scripts folder.

Read more »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Indexing – SQLServerCentral.com Stairway Series

SQLServerCentral.com has an excellent series on indexing. Indexing seems pretty  straightforward, improve queries performance. Blah. Blah.  In reality, they can get away from even the most seasoned DBA. The stairway series are great to learn or RE-learn SQL server specific topics. Excellent resource!

    Read more »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Scripting User Database Rights

All DBA’s will be tasked at time to refresh a copy of production to a development, reporting or promote as adatbase to a UAT or QA environment. Many times it is important to retain the existing users rights on the target instance.
The script uses the sys.database_role_members, sys.database_principals, sys.database_permissions, sys.sysobjects and sys.sysusers tables.

Some things to note:

  • Script does not remove or reduce rights if the user already exists.
  • Create user statements that test if users already exits.
  • Add users to existing database roles.
  • Scripts object level grants.

Read more »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

SQL Skills – Use Outlook as Your RSS Viewer

RSS feeds in Outlook

Outlook and RSS Feed Viewer

We spend hours and hours staring at our email One of the most popular email client is MS Outlook. One thing I like to do is to add my favorite feeds to my client. It makes posts readily available and easy to share. Plus, it is a good distration to enhance your skills or just a  good distraction. 

Read more »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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 »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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 »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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 »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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 »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

T-SQL Split up an Array

I work with data from a system that stores values as arrays or extents. In 2008, I created a CLR function to expose the C# split command. I tried several iteration with loops in T-SQL, etc. overtime, I have noticed the CLR as being a bit of a memory hog. 

I found this possible solution : Split a string in TSQL (without looping)

Need to change it a bit .. 

1. Reduce the numbers table to MAX Extents position ( 80). Still populated it with 1-120.. just in case.
2. Needed it to return as an INLINE Scalar Value and not a Table Value Function. 

Here is What I Came up with from the original  Script:

/*
Adapted from Split a string in TSQL (without looping)
http://blog.magenic.com/blogs/whitneyw/archive/2008/10/08/Split-a-string-in-TSQL-_2800_without-looping_2900_.aspx
(Link no longer valid)
JCD Changes 5/2010
1. Only have need for 80 position Arrays..
So, I limited my Numbers Table to 120
2. Added a @pos INT to evaluate only one position
3. Need it to act as a SCALAR Function
and not a a Table value Funtion

*/
— ORIGINAL COMMENT
— Create and populate an auxiliary
— table of numbers

IF OBJECT_ID(‘dbo.Numbers’) IS NOT NULL
BEGIN
DROP TABLE dbo.Numbers;
END
GO
CREATE TABLE dbo.Numbers
(
number INT NOT NULL
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED
);

SET NOCOUNT ON;

DECLARE
@max AS INT,
@rc AS INT;

SET @max = 120; — you may need to set this much higher
SET @rc = 1;

BEGIN TRAN;
INSERT INTO dbo.Numbers(number) VALUES(1);

WHILE @rc * 2 <= @max BEGIN INSERT INTO dbo.Numbers(number) SELECT number + @rc FROM dbo.Numbers; SET @rc = @rc * 2; END INSERT INTO dbo.Numbers(number) SELECT number + @rc FROM dbo.Numbers WHERE number + @rc <= @max; COMMIT TRAN; GO /* --ORIGINAL COMMENT Next, we create the inline function that will do the heavy lifting for us. I've added an input for separator as hard coding the comma will almost always guarantee some other delimiter will be needed. Turned it into a scalar function */ IF OBJECT_ID('dbo.SplitString') IS NOT NULL DROP FUNCTION dbo.SplitString; GO CREATE FUNCTION dbo.SplitString(@arr AS VARCHAR(MAX), @sep AS CHAR(1),@pos AS INT ) RETURNS VARCHAR(60) AS BEGIN BEGIN -- SET Value DECLARE @result VARCHAR(60) -- Changed to put return one value to a variable SELECT @result = RTRIM(LTRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number))) FROM dbo.Numbers WHERE number <= LEN(@arr) + 1 AND SUBSTRING(@sep + @arr, number, 1) = @sep AND ((number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1) = @pos; END RETURN RTRIM(@Result) END GO /* --ORIGINAL COMMENT Finally, we call our function with a string of values. If we needed to then utilize this for filtering we could use the CROSS APPLY operator introduced in SQL 2005. */ DECLARE @arr varchar(MAX), @sep char(1), @pos INT; SELECT @arr = '1, 2, Dan, 4, Blah, %, 7, "We the people...", 9,"A string walks into a RBAR and says...", B, C' ,@sep = ',' -- Set the delimiter ,@pos = 6 -- Set the position to return -- Return Single Value SELECT dbo.SplitString(@arr, @sep, @pos) as SplitValue; SELECT dbo.SplitString(@arr, @sep, 8) as SplitValue; SELECT dbo.SplitString(@arr, @sep, 10) as SplitValue; GO

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

SQLServerCentral – Report Server Diagnostic Reports

FROM http://www.sqlservercentral.com/articles/SSRS/69257/

Michael Davis has published this article and RDL files
on SQLServerCentral.com.  Just happened to read the email, today.

SSRS is a powerful tool, but the ReportServer tool lacks some administrative reporting.  Micahel Dais has done a great job filling in that gap!

Super Easy to deploy and gives the end user a better glimpse
into what’s happening on their SSRS installation. While it is
written for a SQL 2005 , some posters announced they had no
problems working against SQL 2008 installations.

Thanks Michael!

Read more »

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •