Tempdb Destroyer

One of my previous posts, I covered how to create a really big database with just garbage data in it. Recently, I was given a task to blow up tempdb. I thought about using a similar script and tried it out.

Of course it worked and it blew up TempDb but it also errored out. This wasn’t acceptable. I needed to find a way to blow up the tempdb with out it erroring out.

I tried a couple little things but ended up using the following script:

SET nocount ON
 
USE tempdb
 
IF OBJECT_ID('tempdb..#freespace') IS NOT NULL
DROP TABLE #freespace
IF OBJECT_ID('tempdb..#BigGuy1') IS NOT NULL
DROP TABLE #BigGuy1
IF OBJECT_ID('tempdb..#BigGuy2') IS NOT NULL
DROP TABLE #BigGuy2
IF OBJECT_ID('tempdb..#BigGuy3') IS NOT NULL
DROP TABLE #BigGuy3
IF OBJECT_ID('tempdb..#BigGuy4') IS NOT NULL
DROP TABLE #BigGuy4
IF OBJECT_ID('tempdb..#BigGuy5') IS NOT NULL
DROP TABLE #BigGuy5
GO
 
CREATE TABLE #BigGuy1 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy2 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy3 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy4 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy5 ( column1 CHAR(7100), column2 CHAR(900))
 
CREATE TABLE #freespace
( drive VARCHAR(1),
[MB Free] INT)
 
DECLARE @drive VARCHAR(1),
@free INT
 
SELECT @drive = LEFT(physical_name, 1)
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
AND TYPE = 0
 
INSERT INTO #freespace
EXEC master..xp_fixeddrives
 
SELECT @free = [MB Free]
FROM #freespace
WHERE drive = @drive
 
while @free > 1
BEGIN
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
 
DELETE #freespace
 
INSERT INTO #freespace
EXEC master..xp_fixeddrives
 
SELECT @free = [MB Free]
FROM #freespace
WHERE drive = @drive
 
print @free
 
END

This one uses a loop to check how much free space you have left. You can also set the threshold. For example, if you want to have 100MB free, you would just change @free > 1 to @free > 100.

With a word of caution, do NOT run this on your production system. We used this for a training session in a TEST environment.

The easy part about this one is that when you are finished expanding your tempdb, you can just restart your SQL services and it will reset the size.

Have fun!

 

I will count you all – Return row count for all tables

I’ve built some pretty rad SSIS packages to move massive amounts of data from an older ingres database system into SQL.

My package moves millions and millions of records in numerous amounts of tables in about 15-20 minutes.

When I was testing this sucker, I really wanted to monitor how many rows were getting inserted into each table. Since I am impatient and need a fresh slice of status all the time, I created this little diddy to return all row counts for all tables:

IF Object_id('tempdb..#count_table') IS NOT NULL
  DROP TABLE #count_table 
 
CREATE TABLE #count_table
  (
     TABLE_NAME VARCHAR(100),
     ROW_COUNT  INT
  ) 
 
INSERT INTO #count_table
EXEC sys.Sp_msforeachtable ' SELECT ''?'', count(*) from ?' 
 
SELECT *
FROM   #count_table
ORDER  BY ROW_COUNT DESC

Anyone know a better way?

UPDATE: I’ve tried another method below. This should should be faster

IF OBJECT_ID('tempdb..#sizeo') IS NOT NULL 
DROP TABLE #sizeo
 
CREATE TABLE #sizeo (
NAME VARCHAR(100),
ROWS INT,
reserved VARCHAR(50),
DATA VARCHAR(50),
index_size VARCHAR(50), 
unused VARCHAR(50)
)
 
EXEC sp_msforeachtable '
insert into #sizeo
EXEC sp_spaceused ''?''
'
SELECT name, ROWS FROM #sizeo

Splitting IDs with commas, updating them, then slamming them back together!

Unfortunately, my company still uses MS Access DBs. That’s Access 97, folks! I’ve battled many challenges over the last couple months with these databases. It hasn’t been fun, but it’s been a great learning experience.

I had a fun adventure last week dealing with one of these DBs. We have a user access DB that connects to our SQL user DB. If HR updates something in their system, it also gets updated to the Access DB system. Then we have a job that updates the SQL DB with the Access DB. The job isn’t done in SQL, though. The dev team was given a task to update that job. The dev team that was lucky enough to get this task made a simple change that should have been a cakewalk.

The update was completed easily, but no one took consideration of the dependences. We didn’t even know we had dependences on this old job/db until the emails started rolling in.

Long story short, we found the problem and were able to update it. The bad part was fixing the busted data. We found out that this DB was tied to two other MS-Access DBs. D’OH!

From there I was given the task of fixing the data in the other two DBs a.s.a.p so people could work on their documents. I thought, “No big deal, update some old Access DBs”. Then I saw the data in the DBs.

The field I had to update was tied to the UserID in the User DB. This wouldn’t have been a problem except there were many cases where there was more than one UserID in that column.

Instead of a nice USERID = 123, I had loads of USERID = 123,234.

My game plan was to take all those multiple USERIDs, split them apart, update them, then slam them back together again. How the hell was I supposed to do this in Access?

Instead of making this a novel, I’ll just get to the juicy stuff. The main goal was to update old USERIDs with new USERIDs.

The first thing I did was acquire the latest and greatest ID and toss that dude into a temp table.

SELECT USERID, MAX(USERID) AS MAXUID INTO #max
FROM USERTABLE
WHERE USERID <> USERID
GROUP BY USERID

Next I needed to get all the columns that had commas, split them up, and put them into a temp table. For this I used a snippet I found here that used CTE and XML

WITH Cte AS
( SELECT
        TableID,
       CAST('' + REPLACE( USERID,  ',' , '') + '' AS XML) AS USERID
      FROM AccessDB...CommaTable    
 
)
SELECT
   TableID,
    Split.a.value('.', 'VARCHAR(100)') AS UID,
    0 AS updated INTO #splitter
FROM Cte
CROSS APPLY UID.nodes('/M') Split(a)

Next, I updated the temp table(#splitter) with the newest IDs

UPDATE  #splitter
SET UserID = m.maxuid,
updated = 1
FROM #splitter s INNER JOIN #newUID m ON s.UID = m.uid
WHERE tableid IN (
SELECT tableid
FROM #splitter
GROUP BY tableid
HAVING COUNT(tableid) > 1)

Finally, I had to smash those guys back together as they were before. I used another temp table to put them back to together

SELECT t.tableid,
      --Using the STUFF command
       STUFF(ISNULL((SELECT ', ' + x.USERID
                FROM #splitter x
               WHERE x.tableid = t.tableid
            GROUP BY x.USERID
            --AND using XML
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') AS USERID INTO #updated
  FROM #splitter t
  WHERE updated = 1
GROUP BY tableid

And then I  updated the real table with the updated records

UPDATE AccessDB...CommaTable
SET USERID = u.USERID
FROM #updated u
      INNER JOIN AccessDB...CommaTable  p ON u.tableid =
p.tableid

It was a nice adventure and hopefully it’s something I can use again.
Have you been in this situation before? What did you do? What would you do differently?

Where the hell is that column???

“Hey Database dude, I need you to update the PDADE33e433 Table with values for columns GrAnDaMa12333 and WTF32323 with values ‘ASdjendnd’ and ‘BarfFartwekljweljk3′. Cool? Thanks.’

I work with engineers who use databases. They design all the products that my company sells. They are really good at designing but…they use databases. For them, I am a necessary evil. We all get along very well but they would rather be designing products than deal with storing information. Did I mention it’s one of me to about 300 of them?

With those odds it’s very hard to keep track of what they are doing in their databases. With supporting over 100 databases (possibly more, only on month 3) I am still finding it a hard time to try to find out how they are building these databases (that’s a whole different blog entry).

What I do know is that I get requests to change data that they don’t have access to. When this happens I have to figure out where this data is. Most of the time they tell me a server and a table. I always say “Sure, no problem, just make sure you send me an email with the details”

This is where my journey begins. To save time I have built a stored proc that I have been installing in all the master DBs of all the instances. This SP checks all columns in all databases to see if one is LIKE the keyword that I pass in. Feel free to use it. NOTE: it also gives you view names.

SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
/*
-- =============================================
-- Author:    Adam Mikolaj-- Create date: 19 May 2011
-- Description:  Use this SP to search for a column in every database on your server.
-- I created this because sometimes the I have no idea what database a column I need is in. 
-- This quickly searches all your DBs
-- Sample - exec uSP_WheresThatColumn 'Birthdate'
-- =============================================
*/
 
CREATE PROCEDURE Usp_wheresthatcolumn
 
@COLUMN VARCHAR(255)AS
BEGIN
SET nocount ON;
 
CREATE TABLE #whereisit(
database_name VARCHAR(255),
TABLE_NAME VARCHAR(255),
column_name VARCHAR(255))
 
DECLARE @SQL VARCHAR(MAX)
SET @SQL =
 'insert into #whereisit
 exec sp_MSforeachdb  ''
use [?] select ''''?'''' as DATABASE_NAME , TABLE_NAME, COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS
 where COLUMN_NAME like ''''%'+@COLUMN+'%'''' and TABLE_NAME not like ''''#%''''
 ''
  '
 
EXEC (@SQL)
SELECT * FROM #whereisit
DROP TABLE #whereisit
END
GO

To call it just type in master..uSP_WheresThatColumn ‘column name’

Enjoy!

Adding domain users with TRY CATCH

I was recently given a task to give 20 people read permissions on a dev server. My organization uses AD groups to manage these on the production side of things but on the dev side, it’s a bit like the wild west.

I wasn’t sure if these users were already in the database or if they were new. I figured this was a good time to play around and create some sort of auto inserter for me. I wanted to be able to put the person’s user name in and wanted it to return if they were in the system or not. If they were, great, add them to this DB, if not, add them to the server and then DB.

I ran into the situation where there was a name on the list of people I had who’s username was wrong. Well…how can I check AD to make sure they were there without it blowing up? I couldn’t find a solution so I decided to use TRY…CATCH and grab the error.

Before anyone says anything about automatically entering users, this was just for my personal use and is not intended to be placed in any application.

Check it out:

USE MASTER
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
-- =============================================
 
-- Author:    Adam Mikolaj-- Create date: 12 May 2011-- Description:  Little stored procedure for entering in users
 
-- =============================================
 
CREATE PROCEDURE uSP_AutIinserter
 
 @USER VARCHAR (100),
 
@dbname VARCHAR(100),
 
@DOMAIN VARCHAR(100)
 
AS
BEGIN
 
DECLARE @LiveUser BIT,@Sqls VARCHAR(500)
 
IF EXISTS (SELECT *FROM syslogins
 
WHERE name LIKE '%' + @USER + '%')
 
BEGIN
 
SET @LiveUser = 1
 
END
 
ELSE
 
SET @LiveUser = 0
 
IF @LiveUser = 0
 
BEGIN
 
SET @Sqls = 'CREATE LOGIN [' + @DOMAIN + '\' + @user +'] 
 
FROM WINDOWS WITH DEFAULT_DATABASE=' +@DBName+''
 
BEGIN TRY
 
EXEC (@Sqls)
 
END TRY
 
BEGIN CATCH
 
SELECT Error_message() AS errormessage;
 
END CATCH
PRINT 'USER IS now IN the system' END
ELSE
PRINT 'USER already IN the system'END
 
GO

To call this procedure just do this:

uSP_AutoInserter ‘Dude’, ‘DB’, ‘domain’

Fixing Orphaned Users

One my favorite commands when I was started my career was Sp_changeuserslogin. I had to move a lot of databases around and I orphaned a lot of users. This command really helped me find what users were orphaned and reconnect them with the appropriate database.

When should you use this command?

This command is very useful when you have a user in your database and a login on your server that should be connected (or mapped) but are not.

To find out if you have an oprhaned users run:

EXEC Sp_change_users_login ‘report’

This will give you a list of orphaned users in your DB.

If you find you have some orphaned users you can run this command to fix them:

EXEC Sp_change_users_login
‘auto_fix’,
‘YourUser’

It’s a handy tool to use but BOL warns avoiding it because it will be going away soon. They suggest using ALTER USER instead.

ALTER USER YourUser WITH LOGIN = ‘YourUser’

Query to find Duplicate records in a column

We are going to start SQL sandwiches with a tasty little snack. I have had this asked in an interview before. “What is an easy way to find duplicate records in one table?”

The answer is quite easy yet it can stump anyone who hasn’t done it before.

It’s a simple select statement that you can use for multiple columns.

1
2
3
4
5
SELECT column1,
COUNT(column1)
FROM table1
GROUP BY column1
HAVING COUNT(column1) > 1

You can add other columns in there if you are looking for a record that has multiple columns that are the same.