•  
  • Archives for TSQL (47)
  • Page 2

On the First Day…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC
Tags: , , ,
Comments: 2 Comments
Published on: December 13, 2012

Many of us have heard the song “The Twelve Days of Christmas.”  Some are familiar with how that song has some connection with the Holiday season for Christians.  Fewer are aware that this song is connected to the tradition of giving traditions on each of the days of Christmas.  Fewer yet know that the first day of Christmas is actually December 25th.

So, instead of a blog series for the 12 Days of Christmas, I am doing a blog series for the 12 Days of pre-Christmas.  Today is Day 1, or day -12, depending on how you look at it.

My caveat is that in my own little world, the 12th day of pre-Christmas will overlap with the actual first day of Christmas.  You may find that this is similar to how the First Day of Epiphany and 12th day of Christmas can overlap.

Now that we have that all squared away, on to my First day of pre-Christmas gift for you.

On the First Day of pre-Christmas…

My DBA gave to me  - A maintenance plan log.  No, not a yule log.  But a maintenance plan log.  Have you heard of sysmaintplan_log and sysmaintplan_logdetail?  These are system tables in the msdb database.

These tables are used to support logging of maintenance plans (SSIS style) since SQL 2005.  If you have not introduced yourself to them and you are running maintenance plans (SSIS style), you may have some large tables.  I have seen these tables become very large and as a result cause the msdb database to also be very large.

Part of a good maintenance plan is also to take care of the undercarriage.  In this case, we need to take good care of the supporting tables for the SSIS style maintenance plans.  One quick and easy method to do this is to add a SQL Agent job to help maintain these tables.

In the SQL Agent job that you create, add this query to maintain the amount of historical information held by these tables.

DECLARE @OldDate DATETIME
SET @OldDate = GETDATE() - 14;
 
EXECUTE msdb.dbo.sp_maintplan_delete_log @oldest_time = @OldDate

And there you have it.  Something you may also want to do is to add a Clustered Index to the sysmaintplan_logdetail table.  There are no indexes on this table.  Since the stored procedure is looking at start_time, and this is likely to be the most frequently queried field, I chose to create a Clustered Index on start_time.

CREATE CLUSTERED INDEX [CI_Start_time] ON [dbo].[sysmaintplan_logdetail] 
(
	[start_time] ASC
)

Conclusion

Simple but effective.  The idea here is to decrease the chance of bloat in the msdb database.  We don’t want the database to grow out of control.  We don’t want the size of these tables to cause the Maintenance plans to run slower.  And we certainly don’t want these tables to grow to a size that causes the database to fill and subsequently cause backups to start failing (for instance).

Tune in each day between now and December 25th for a new gift in this season of giving.

Let’s Talk About Joins

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV, TSQL Tuesday
Tags: ,
Comments: 1 Comment
Published on: December 11, 2012

T-SQL Tuesday #37

This month please join us in the TSQL blog party that happens on the second tuesday of the month.  It is hosted this month by Sebastian Meine (@sqlity).

Sebastien has a month long blog going this month all about Joins.  You can read all about that fun in his post titled A Join A Day – Introduction.

 

This is a good topic.  And I pondered what to write this month for a bit.  I immediately went to a topic that I had on my toblog list.  Unfortunately I had already covered that topic once upon a time.  But with it being at the first thing my mind went to when thinking of this topic, I feel it would be good to re-hash it.  I may change it up a bit, I may not – we’ll just have to see where this post takes us.  So jump on board the Crazy Train for a little Salt N Peppa remix – Let’s Talk About Joins.

Business Requirement

I have some tables that I need to query.  One of the tables has lookup information with a bitmask applied to the id field.  Another table references this table but the ids can be a direct match or an indirect match to the id of the lookup table.  For this case, they will always only differ by the value of 1 if the two values do not directly match.  No other match possibility is considered for this example.

Based on this need, the solution dictates some sort of bitwise math.  I have several examples of how a join could be written to accomplish this primary objective.  I am only going to show the possible ways of performing this.  In my environment these all yield the same results and the data is unique and large enough (4.2 million records).  I will compare performance of these different queries in a later post as I demonstrate a query tuning method to drive the query down from nine seconds to 100ms or less.  For the record, I would choose any of queries 5, 6, or 7 for this particular need based on plan cost and performance.

The JOINS

First up is the ANSI style INNER Join using addition in one of the conditions as well as an OR to fulfill the second portion of the business requirement.

PRINT 'Query 1 -- Join with Or and source+1'
------
SELECT TOP 1000
		PPV.RumorID,PPV.PersonRumorID,PPV.PersonID
		,US.Source,Us.SourceID, PPV.SourceID
	FROM	RumorView PPV
		INNER Join SourceType US
			ON (PPV.SourceID = US.SourceID
				Or PPV.SourceID = US.SourceID+1)

This is probably the easiest to understand and it performs well enough.  Until running into this business requirement, I hadn’t considered putting an OR in the JOIN conditions.  But it makes sense considering that an AND can be used there.

Next is a NON-ANSI style of JOIN.

PRINT 'Query 2 -- Non-Ansi Join with Or and source+1'
------
SELECT TOP 1000
		PPV.RumorID,PPV.PersonRumorID,PPV.PersonID
		,US.Source,Us.SourceID, PPV.SourceID
	FROM	RumorView PPV, SourceType US
	WHERE (PPV.SourceID = US.SourceID
				Or PPV.SourceID = US.SourceID+1)

Through 2008 R2, this works just as well as the ANSI JOIN already shown.  I haven’t tested in SQL 2012 but I do know that the NON-ANSI syntax of *= (for example) no longer works.  I am not a big fan of this style JOIN because it is far too easy to end up with a Cartesian product.

Another type of JOIN that I like is the use of APPLY.

PRINT 'Query 3 -- Cross Apply with Or and source+1'
------
SELECT TOP 1000
		PPV.RumorID,PPV.PersonRumorID,PPV.PersonID
		,US.Source,Us.SourceID, PPV.SourceID
	FROM	RumorView PPV
		Cross Apply SourceType US
	WHERE (PPV.SourceID = US.SourceID
				Or PPV.SourceID = US.SourceID+1)

This particular code segment is the equivalent of the first query shown.  This is the last in the set of using basic math and an OR in the JOIN conditions.  The remaining queries all rely on bitwise operations to perform the JOIN.  Again, until this particular need, I had never even considered using a bitwise operation in a JOIN.  First in this series is the NON-ANSI style JOIN.

PRINT 'Query 4 -- Non-Ansi with COALESCE and Bit compare'
------
SELECT TOP 1000
		PPV.RumorID,PPV.PersonRumorID,PPV.PersonID
		,US.Source,Us.SourceID, PPV.SourceID
	FROM	RumorView PPV, SourceType US
	WHERE (PPV.SourceID|1 = COALESCE(US.SourceID|1,Us.SourceID))

The big change here is in the where clause.  Notice the use of COALESCE and the first comparison value in that COALESCE.  This is called a BITWISE OR.  From MSDN: “The bits in the result are set to 1 if either or both bits (for the current bit being resolved) in the input expressions have a value of 1; if neither bit in the input expressions is 1, the bit in the result is set to 0.”

So I am comparing the bit values of 1 and the SourceID.  The SourceID from RumorView will create a match meeting the requirements put forth thanks in large part to the BIT OR operation being performed on both sides of the equality in the WHERE clause.  It is also worth mentioning that the COALESCE is completely unnecessary in this query but it I am leaving it as a pseudo reference point for the performance tuning article that will be based on these same queries.

Next on tap is the CROSS Apply version.

PRINT 'Query 5 -- Cross with Bit compare'
------
SELECT TOP 1000
		PPV.RumorID,PPV.PersonRumorID,PPV.PersonID
		,US.Source,Us.SourceID, PPV.SourceID
	FROM	RumorView PPV
		Cross Apply SourceType US
	WHERE (PPV.SourceID|1 = US.SourceID|1)

And the last two queries that the optimizer equate to the same query.

PRINT 'Query 6 -- Join with Bit compare'
------
SELECT TOP 1000
		PPV.RumorID,PPV.PersonRumorID,PPV.PersonID
		,US.Source,Us.SourceID, PPV.SourceID
	FROM	RumorView PPV
		INNER Join SourceType US
			ON (PPV.SourceID|1 = US.SourceID|1)
------
PRINT 'Query 7 -- Join with ISNULL and Bit compare'
------
SELECT TOP 1000
		PPV.RumorID,PPV.PersonRumorID,PPV.PersonID
		,US.Source,Us.SourceID, PPV.SourceID
	FROM	RumorView PPV
		INNER Join SourceType US
			ON (PPV.SourceID|1 = ISNULL(US.SourceID|1,Us.SourceID))

The query optimizer in this case is smart and eliminates the ISNULL.  These two queries use the same exact plan, have the same cost and the same execution statistics.  The version with COALESCE is considered more expensive and takes longer to run than these queries.  It is also important to note that the Cross Apply Join also produces the exact same plan as these two queries.

Conclusion

So there you have it.  Many different ways to write the JOIN for this little query.  Performance and results may vary.  It is good to have a few different ways of writing this particular JOIN.  During my testing, it was evident that various methods performed better under different circumstances (such as how the indexes were configured – which will be discussed in the follow-up article).

Yes, I did re-use my previous post on this particular topic.  That said, I want to add another tidbit.

This Post just Goes On and On

When Joining objects, you should take care as to the data type used in the Join.  Without going into implicit conversions in the Joins, I just want to discuss briefly the impact of data type choice in a Join.  This is where knowing your Data, the workload, and usage patterns is going to be an absolute necessity.

That said, you should not be surprised by a change in the performance of your queries if you were to change from a varchar() datatype to a varbinary() datatype.  I’m not saying that the query is going to perform better – but that the performance may change.  As an example, I was effectively able to turn a well performing query into a very poor performing query by changing from varchar to varbinary.  On the flipside, I have seen the reverse also become true.  It all boils down to proper data types for the data.

Last Known Good CheckDB

by Jason Brimhall
Categories: Corner, News, Professional, Scripts, SQLServerPedia Syndication, SSC
Tags: , , ,
Comments: 1 Comment
Published on: November 20, 2012

Diligent DBAs like to check their databases for a little thing called corruption.  Some of those DBAs are masterful at keeping records and logging that the database was checked and came up with a clean bill of health.

There are many different ways of logging this kind of activity.  Today I will share one such way to track when the last successful run of Checkdb happened.

First a little back story

A question came across twitter on the SQLhelp hashtag. The question was “Does restoring a database clear the dbi_dbccLastKnownGood value on the boot page?”

This question prompted me to do a quick test to see.  The test is simple.  Create a small database, backup the database, run checkdb, check to see what the dbi_dbccLastKnownGood value is, restore the previous backup and check the dbi_dbccLastKnownGood value again.

So here is a quick script

USE [master]
GO
 
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TestB')
DROP DATABASE [TestB]
GO
 
USE [master]
GO
CREATE DATABASE [TestB] 
GO
 
DECLARE @BackupPath VARCHAR(256)
	,@BackupName VARCHAR(50)
SET @BackupPath = 'C:\Database\Backup\' --replace with valid file path
SET @BackupName = 'TestB.bak'
 
SET @BackupPath = @BackupPath + @BackupName
BACKUP DATABASE [TestB]
	TO DISK = @BackupPath
	WITH init;
GO
 
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO
 
DBCC CHECKDB(TestB) WITH no_infomsgs;
GO
 
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO
 
/* Now Restore the database */
DECLARE @BackupPath VARCHAR(256)
	,@BackupName VARCHAR(50)
SET @BackupPath = 'C:\DATABASE\BACKUP\' --replace with valid file path
SET @BackupName = 'TestB.bak'
 
SET @BackupPath = @BackupPath + @BackupName
/* for this contrived example, i will not take a tail log backup
and just use replace instead */
 
RESTORE DATABASE TestB
	FROM DISK = @BackupPath
	WITH REPLACE; 
GO
 
/* Rerun The Boot Page Check */
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO

If you run this little test, you should observe that the date value for dbi_dbccLastKnownGood changes with each test.  Now let’s discuss the question and the answer to that original question about whether or not this value gets cleared.

The value does not get cleared.  The value does not get reset.  The cause for the change in the value that you have observed is due simply to the boot page having been restored.  If CheckDB has never been run on the database, you will get the SQL default date of ’1900-01-01 00:00:00.000′.

And then…

Now that the back story is told, that brings us to how to track this – at least one such method.  I had to cover the back story since it is what prompted the writing of a method to gather this information in a quick script so I could use it to monitor.  Yes, it is just another tool to throw into the toolbox.  And to reiterate, it is by no means the only way to track or gather this type of information.  Some are more elaborate than others.  It is up to you to choose.

Keeping in mind that we can get the last time that Checkdb completed without a report of corruption, I delve into this cursor based method to retrieve the dbi_dbccLastKnownGood value for all databases within your SQL Server Instance.

CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [OBJECT] VARCHAR(255),
       Field VARCHAR(255),
       [VALUE] VARCHAR(255)
)
 
CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED, 
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum	INT
)
 
DECLARE
	@DBName SYSNAME,
	@SQL    VARCHAR(512);
 
DECLARE dbccpage CURSOR
	LOCAL STATIC FORWARD_ONLY READ_ONLY
	FOR SELECT name
		FROM sys.databases
		WHERE name not in ('tempdb');
 
OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Use [' + @DBName +'];' +CHAR(10)+CHAR(13)
SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' +CHAR(10)+CHAR(13)
 
INSERT INTO #temp
	EXECUTE (@SQL);
SET @SQL = ''
 
INSERT INTO #DBCCRes
        ( DBName, dbccLastKnownGood,RowNum )
	SELECT @DBName, VALUE
			, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
		FROM #temp
		WHERE field = 'dbi_dbccLastKnownGood';
 
TRUNCATE TABLE #temp;
 
FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;
 
SELECT DBName,dbccLastKnownGood
	FROM #DBCCRes
	WHERE RowNum = 1;
 
DROP TABLE #temp
DROP TABLE #DBCCRes

You can use this or find something else if you desire.  The sole purpose of this script is to provide a means to gather quickly the last date known to report a clean bill of health from Checkdb.  From there, I will leave it to you to add it to another process in order to track over the long term.

You can read the twitter conversation here.  I also have it on good authority that Bob Pusateri (twitter) will be blogging about the same topic.

Finally, it has happened…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, SSSOLV
Tags: , ,
Comments: 2 Comments
Published on: October 4, 2012

Last month I blogged about my absence from the blogosphere.  If you want, you can read about it again – here.  Granted, it has been a month and I still have been slacking.  I am hoping to turn a new leaf and post more regularly here in the next few weeks.

Since that last post I had been anxiously waiting.  The book seemed to keep getting delayed.  Yesterday, reality finally hit home.  It is so weird how much different it feels to finally have a book physically in my hands.

I am one that likes ebooks and I don’t normally have to be able to feel the pages of the book.  But this is one case where the ability to rifle the pages is so much more gratifying.  Finally the book is real!!

If you are interested, you can get the book here.

Oh and if you are wondering why I posted this picture, it is simple.  I didn’t want to take a picture of myself today holding the book and the other books pictured happen to be books that I had on hand because I have been reading them of late.

Where have all the Orphans gone?

by Jason Brimhall
Categories: Corner, News, Professional, SSC, SSSOLV
Tags: , ,
Comments: 2 Comments
Published on: October 3, 2012

Do your database users appear to be having an out of body experience?  Have they lost all ties to their login?

Does it feel like maybe what this baby rhino might be experiencing – just trying to find their place in your database?

Well, have we got a little snippet for you then.  This cure-all elixir … er script may be just what you need for those ghostly ghastly things called orphaned users.

Everybody has heard of them.  Everybody seems to want a quick fix for them.  But before you can fix your orphanage / database of these orphans – you have to know that they are there.

 

Cute Rhino

Well, not really – looks more like a hairless rat at this age.  But that is ok – I like rhinos of all ages.  But that is really beside the point.  I also like quick little scripts that will help me get a better picture of any databases that I may have inherited or that I must work on (which I had not previously worked on).

This script is naturally another one of those tools for the admin toolbox.  It is cursor based – but that is ok.  I might update it later to use a set based method and that new foreachdb that I have blogged about previously.

The reason for the looping is to run this quickly against all databases on an instance and immediately know where the orphans might be hiding.  This script is merely to report – it does not fix the orphans.  That is for you to work through.  Also of interest is that this script is not designed to work on SQL 2000 (it’s getting kinda up there in age anyway).

So here it is…another tool for the toolbox to help you become a better more efficient DBA.

CREATE TABLE #Orphans 
 (
  RowID			INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
  DBName		VARCHAR(100),
  OrphanUser	VARCHAR (100),
  OrphanSid		VARBINARY(85)
 )           
 
DECLARE
     @DBName SYSNAME,
     @SQL NVARCHAR(MAX) = N'';
 
DECLARE dbnams CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
     SELECT name
		FROM sys.databases
		WHERE state_desc NOT IN ('SUSPECT','OFFLINE')
			AND name NOT IN ('tempdb','model')
 
OPEN dbnams
FETCH NEXT FROM dbnams INTO @DBName;
 
WHILE @@FETCH_STATUS = 0
BEGIN		
SET @SQL = 'SELECT ''' + @DBName + ''' as DBName,dp.name AS OrphanUser, dp.sid AS OrphanSid
FROM [' + @DBName + '].sys.database_principals dp
LEFT OUTER JOIN sys.server_principals sp 
    ON dp.sid = sp.sid 
WHERE sp.sid IS NULL 
    AND dp.type_desc = ''SQL_USER''
    AND dp.principal_id > 4;'
 
INSERT INTO #Orphans
        ( DBName ,
          OrphanUser ,
          OrphanSid
        )
EXECUTE (@SQL)
 
FETCH NEXT FROM dbnams INTO @DBName;
 
END
CLOSE dbnams;
DEALLOCATE dbnams;
 
SELECT O.RowID,O.DBName,O.OrphanUser,O.OrphanSid
	FROM #Orphans O
	ORDER BY O.DBName,O.OrphanUser
 
DROP TABLE #Orphans;
GO

Where in the World is…

by Jason Brimhall
Categories: Corner, News, Professional, SSC
Tags: ,
Comments: 4 Comments
Published on: July 18, 2012

You may or may not have noticed that over the past several months I have been somewhat absent from my blog.  Very few articles if any have been produced.  There has been a very good reason for that.

It all started back in November of 2011.  Slowly over time, the ball started gaining momentum.  In about March, the momentum was such that I needed to start cutting back in a lot of other things (like my blog or community activity) sadly.

This project was big with deadlines that just kept approaching faster and faster.  More time needed to be given to the project so we could try and hit our goal.  Well, I have more time these days as the deadlines have all been pretty much hit.  Goal is nearly achieved and it is pretty cool.  It feels good to have accomplished this and now there are some new goals.

I didn’t make this journey by myself.  There were a few other people very involved in the entire project.  One is a good friend Wayne Sheffield.  The other is an employee of Microsoft – Andy Roberts.  Yet another is a civil servant and MVP – David Dye.  The last of the group was our editor Jonathan Gennick.

What?  Did I say editor?  Yes I did.  We wrote a book together.  The book is available for pre-order now and is due to be published on August 22nd of 2012.

This was a lot of hard work and late nights.  I think it was well worth it to this point.  And I really want to get on to another book project soon.

I do have to give thanks to the team, the editor, and to Joe Sack (Twitter) for allowing us to take this version of the book and run with it.  Joe provided great input and helped on the technical review.

There are plenty of good books coming out in the next few months (this being one of them of course).  I urge you to take a look at them.

Grant Fritchey SQL 2012 Query Performance Tuning 

Grant Fritchey, Gail Shaw, Chris Shaw, Tjay Belt, et al Pro SQL Server 2012 Practices

Itzik Ben Gan Microsoft SQL Server 2012 TSQL Fundamentals

That is just a list of a few of the books out there or that will be out there soon.

A Trio of EachDB

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, SSSOLV
Tags: , ,
Comments: 1 Comment
Published on: July 17, 2012

When administering a larger database environment, sometimes one needs to perform repetitive tasks.  Performing repetitive tasks becomes more and more painful (maybe even demoralizing) with the larger the number of databases that might exist on a server.

This kind of administration will have you running in circles.  You may even feel like you are making progress and then suddenly feel like there is no end in sight as you repeat the process with each database.

Some of the tasks that you may need to perform on a regularly basis may be to run reports on security access across all databases for a particular user, or to retrieve file free space information for all databases, or you may even just need to update the statistics across multiple databases.

These tasks can be simplified and even automated to help free your sanity and free your time.  Here is a trio of examples.

Update Statistics

*Disclaimer* These examples are just that – examples.  They are meant to be simple introductions.  The development into a full solution for use in your environment is a project for you to undertake.

In all of the examples I will share, there will be a common theme.  I will employ the looping mechanism introduced via sp_MSforeachdb.  I will also introduce a better version of that proc and how to execute each of these scripts with the newer version.

EXECUTE sp_MSforeachdb 'use ?; Execute sp_updatestats;'
GO

As promised, that script is very simple to create.  I will leave it to you to make modifications for your environment.

User Exists

Suppose you get a request from your manager to list out the databases that a particular user has been granted access.  Here is a simple script to find which databases a user has been created within to report back to your manager.

CREATE TABLE #UserExists (DatabaseName VARCHAR(128), UserName VARCHAR(128));
GO
 
EXECUTE sp_MSforeachdb 'Insert Into #UserExists (DatabaseName, UserName)
				select ''?'' AS DatabaseName, 
                name AS UserName 
                FROM [?].sys.database_principals
                WHERE name = ''guest'' ';
 
SELECT DatabaseName, UserName
	FROM #UserExists;
 
DROP TABLE #UserExists;

Again, the script is not too terribly complex and can get the job done quickly.  The idea here is that each database will be queried on the database_principals catalog view.  I dump the results into a temp table for the each database in which that user exists and then query the temp table for the final result.

File Free Space

Occasionally you will want to know how much free space is in each database file.  This can be very useful should you manage your file growths.  You can use this information to establish alerts for when a file reaches a certain capacity threshold.

CREATE TABLE #FileProp (dbid INT,FILE_ID INT, SpaceUsed DECIMAL(14,2));
GO
 
EXECUTE sp_MSforeachdb 'USE ?; Insert Into #FileProp (dbid,FILE_ID,SpaceUsed) 
SELECT database_id,file_id,FILEPROPERTY(name,''SpaceUsed'') from sys.master_files where DB_NAME(database_id) = ''?'''
 
SELECT DB_NAME(database_id) AS DBName,physical_name,CONVERT(DECIMAL(14,2),SIZE)/128 AS FileSize, growth
		,CONVERT(DECIMAL(14,2),max_size)/128 AS MaxFileSize,FP.SpaceUsed,mf.type_desc
	FROM sys.master_files mf
		INNER JOIN #FileProp FP
			ON FP.dbid = mf.database_id
			AND FP.FILE_ID = mf.FILE_ID;
 
DROP TABLE #FileProp;
GO

Similar to the previous query, this query dumps results for each database into a temp table.  From there, we then query that temp table to get the final result set.  Like the others, this is an example to help produce some ideas.  You can extend this type of query to meet your needs as you see fit.

Alternative

Gianluca Sartori did some good work on building a better procedure to replace sp_MSforeachdb (while not using a cursor).  You can find his work here.  The reason for the updated procedure is due to some limitations/bugs with sp_MSforeachdb.  If you decide to use the new version, here is an example of how you might run one of the previously discussed queries.

CREATE TABLE #UserExists (DatabaseName VARCHAR(128), UserName VARCHAR(128));
GO
 
EXECUTE dbo.[dba_ForEachDB] @STATEMENT = 'Insert Into #UserExists (DatabaseName, UserName)
				select ''?'' AS DatabaseName, 
                name AS UserName 
                FROM [?].sys.database_principals
                WHERE name = ''guest'' '
		,@replacechar = '?';
 
SELECT DatabaseName, UserName
	FROM #UserExists;
 
DROP TABLE #UserExists;

Conclusion

Some simple examples of routine database administration tasks have been illustrated.  Take these examples and extend on them if you like.  It can save you some time.

SQL Hide ‘n Seek

by Jason Brimhall
Categories: Corner, News, Professional, SSC
Tags: , , ,
Comments: No Comments
Published on: July 11, 2012

When was the last time you had to find something within your database?  Did it feel more like a child’s game or an arduous task?

Child's Game or Arduous Task

What would you say if it could be more like child’s play (like the baby elephant is portraying) than a chore?

Child’s Play

The simplest solution may be to use a tool from a respectable vendor.  One such tool that comes to mind is SQL Search from RedGate.  You can find out more about that tool here.

SQL Search does require that an application be installed on the machine on which you will be using the search feature.  The tool is fine and I will leave it up to you to use or not use it.

Alternative

Instead of installing an application onto your machine, you could always write your own script.  The information is readily available within SQL Server for you to find the objects you seek.

For example, if I wanted to find any tables that had a particular column, I could use the following.

DECLARE @ColName VARCHAR(128)
 
SET @ColName = 'yourcolumntosearch'
SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS DataType, c.max_length
		,'('+ CONVERT(VARCHAR,c.PRECISION) +','+ CONVERT(VARCHAR,c.scale)+')' AS Precision_Scale
		,d.definition AS DefaultConstraint
		,c.collation_name
	FROM sys.tables t
		INNER Join sys.columns c
			ON t.OBJECT_ID = c.OBJECT_ID
		INNER Join sys.types ty
			ON c.system_type_id = ty.system_type_id
		LEFT Outer Join sys.default_constraints d
			ON d.parent_object_id = c.OBJECT_ID
			and d.parent_column_id = c.column_id
	WHERE c.NAME = @ColName
	ORDER BY t.name,c.column_id;

Granted, this query returns a bit more information than you require.  Personally, I like to see the additional information related to the columns as I am doing a search through a database.  I always find it interesting to find columns of the same name and intent but to have a different definition within the database.

And if you desire to find code within the database that contains a particular column name, then something like the following could be helpful.

DECLARE @searchstring VARCHAR(128)
 
SET @searchstring = '%*%'
 
SELECT SO.name AS CodeName, st.Query,SO.type_desc
FROM sys.objects AS SO
INNER JOIN sys.sql_modules AS SM 
	ON SM.OBJECT_ID = SO.OBJECT_ID
CROSS APPLY (
					SELECT 
						REPLACE
						(
							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
								CONVERT
								(
									NVARCHAR(MAX),
									N'--' + NCHAR(13) + NCHAR(10) + sqm.definition + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
								),
								NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
								NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
								NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
							NCHAR(0),
							N''
						) AS [processing-instruction(query)]
						FROM sys.sql_modules AS sqm 
							WHERE sqm.OBJECT_ID = SM.OBJECT_ID
					FOR XML
						PATH(''),
						TYPE
				) AS st(Query)
WHERE definition like @searchstring
ORDER BY name

Now, what if I want to search code and tables at the same time for a particular column name usage?  Well, I could take advantage of the following.

DECLARE @ColName VARCHAR(128)
 
SET @ColName = 'yourcolumnhere';
 
SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS DataType, c.max_length
		,'('+ CONVERT(VARCHAR,c.PRECISION) +','+ CONVERT(VARCHAR,c.scale)+')' AS Precision_Scale
		,d.definition AS DefaultConstraint
		,c.collation_name
		,sq.CodeName,sq.Query AS ColNamePresentinCode,sq.type_desc AS CodeType
	FROM sys.tables t
		INNER Join sys.columns c
			ON t.OBJECT_ID = c.OBJECT_ID
		INNER Join sys.types ty
			ON c.system_type_id = ty.system_type_id
		LEFT Outer Join sys.default_constraints d
			ON d.parent_object_id = c.OBJECT_ID
			and d.parent_column_id = c.column_id
		CROSS APPLY (SELECT SO.name AS CodeName, st.Query,SO.type_desc
			FROM sys.objects AS SO
			INNER JOIN sys.sql_modules AS SM 
				ON SM.OBJECT_ID = SO.OBJECT_ID
			CROSS APPLY (
					SELECT 
						REPLACE
						(
							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
							REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
								CONVERT
								(
									NVARCHAR(MAX),
									N'--' + NCHAR(13) + NCHAR(10) + sqm.definition + NCHAR(13) + NCHAR(10) 
									+ N'--' COLLATE Latin1_General_Bin2
								)
								,NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?')
								,NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?')
								,NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?')
								,NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),NCHAR(11),N'?')
								,NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?')
								,NCHAR(2),N'?'),NCHAR(1),N'?'),NCHAR(0)
							,N''
						) AS [processing-instruction(query)]
						FROM sys.sql_modules AS sqm 
							WHERE sqm.OBJECT_ID = SM.OBJECT_ID
					FOR XML
						PATH(''),
						TYPE
				) AS st(Query)
			WHERE definition like '%' + @ColName + '%'
				AND SM.definition LIKE '%' + t.name + '%') AS sq
	WHERE c.NAME = @ColName
	ORDER BY t.name,c.column_id;

Conclusion

Now, I have a script that will return a row for each time a column appears in a proc.  I can correlate which table and column matches to the proc and get the results I need quickly.

Now, you can take this and have a little fun with it.

Database In Recovery

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, SSSOLV
Tags: , , ,
Comments: 4 Comments
Published on: June 4, 2012

What do we do?

Have you ever run into a database that is in the “In Recovery” state?

If that has happened, have the bosses and/or endusers come to you asking “What do we do?” or “When will it be done?”.  They probably have – it is inevitable.

The question is, what do you do when you run into a database that is in this state?

We all know that it doesn’t help much if we are panicked about the issue – that just feeds the already growing anxiety.  If you feel anxiety – that’s OK, just don’t show that to the endusers or to the boss.  You need to portray to them that you are on top of the issue.

While trying to keep everybody calm and apprised of the situation, you would probably like some assurances for yourself that the database is progressing to a usable state.  That is what I want to share today – a little query that I wrote for this very instance.

Anxiety Tranquilizer

DECLARE @ErrorLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database'
 
SELECT  DB_NAME(dt.database_id) AS DBName,GETDATE() AS currenttime, AT.transaction_begin_time
      ,dt.transaction_id,AT.name AS TranName
	  ,cx.PercentComplete,cx.MinutesRemaining
      ,d.log_reuse_wait_desc
      ,database_transaction_log_record_count, database_transaction_log_bytes_used
	, database_transaction_next_undo_lsn
	,CASE AT.transaction_state
		WHEN 0 THEN 'Not Completely Initialized'
		WHEN 1 THEN 'Initialized but Not Started'
		WHEN 2 THEN 'Transaction is Active'
		WHEN 3 THEN 'Read-Only tran has Ended'
		WHEN 4 THEN 'Distributed Tran commit process has been initiated'
		WHEN 5 THEN 'In prepared state and waiting resolution'
		WHEN 6 THEN 'Transaction has been committed'
		WHEN 7 THEN 'Transaction is being rolled back'
		WHEN 8 THEN 'Transaction has been rolled back'
		END AS TranState
      FROM sys.dm_tran_database_transactions dt
		LEFT OUTER JOIN sys.dm_tran_active_transactions AT
			ON dt.transaction_id = AT.transaction_id
		INNER JOIN master.sys.databases d
			ON d.database_id = dt.database_id
		Cross Apply (SELECT TOP 1
			 [LogDate]
			,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
			,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
			,DB_NAME(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1,CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1) ) AS DBName
			,CAST(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1,CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1) AS INT) AS DBID
			FROM @ErrorLog ORDER BY [LogDate] DESC) cx
		WHERE d.state_desc <> 'online'
			And cx.dbid = dt.database_id

Unfortunately, this query does not demonstrate the time remaining for the rollback nor the percent complete without needing to query the error log.  Those would be awesome additions if you know how to do it (and let me know), other than via the error log.  Thanks to a blog post by Tim Loqua for the base info on querying the error log for the percent complete.

I think the key component on this query is the LEFT OUTER JOIN to sys.dm_tran_active_transactions.  This is essential since the recovery is shown in two transactions.  One transaction is numbered and is the placeholder for the un-numbered transaction where the work is actually being done.  In the numbered transaction, you should see a transaction name of “Recovery Allocation Locks” and nothing for the unnumbered transaction.

Now, unnumbered is not entirely accurate because that transaction has an id of 0, but you will not find a correlating transaction for that in the sys.dm_tran_active_transactions DMV.

The transactions displayed here will be displayed until recovery is complete.  That also means that if you really wanted to, you could create a table to log the recovery process by inserting the results from this query into it.  Then you could revisit the table and examine at closer detail what happened during recovery.

The anxiety killer from this query is to watch two columns in the unnumbered transaction.  These columns are database_transaction_log_record_count and database_transaction_next_undo_lsn.  I rerun the query multiple times throughout the process of recovery.  I check those columns to ensure the data in them is changing.  Changing results in those fields means that you are seeing progress and can provide some comfort by seeing actual progress (even though we know in the back of our head that it is progressing).

Another Interesting Sort

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: No Comments
Published on: March 19, 2012

In October of 2011, I shared an example of a peculiar set of sort requirements.  Today, I am going to share another similar set of requirements.  Based on prior experience, when I saw this request on a sort order, I was sure I could find a simpler solution.  Today, I will share the requirements and solution with you.

Requirements

Given a set of characters, you must be able to sort according to the following:

  1. !
  2. “
  3. $
  4. ?
  5. @
  6. ^
  7. {
  8. >
  9. ASCII values

Setup

To demonstrate the requirements and solution, let’s create a temp table and populate it with some values like those in the requirements.

CREATE TABLE #Chars
(
	MyChar CHAR(1)
)
 
INSERT INTO #Chars (MyChar)
VALUES
  ('!'), ('"'), ('$'), ('?'), ('@'), ('^')
, ('{'), ('>'), ('1'), ('2'), ('3'), ('4')
, ('5'), ('6'), ('7'), ('8'), ('9'), ('A')
, ('B'), ('C'), ('D')

Solution

SELECT *
	FROM #Chars
	ORDER BY MyChar COLLATE SQL_Latin1_General_Cp1251_CS_AS

Sometimes the simplest solution requires a lot of testing.  When I came across the requirements, I thought it could be solved via a collation.  The problem was that I did not know which collation.  I had to test a few collations to find the collation that would create the correct result set.  If you are interested in learning about other collations, you can read this article.

«page 2 of 5»
Calendar
May 2013
M T W T F S S
« Apr    
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
Now Reading

Now Reading

Planned books:

Current books:

  • SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

    SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach by Jason Brimhall

Recent books:

View full Library

Popular Posts

Popular Posts

Blogroll

Blogroll

Categories

Categories

SQLHelp

SQLHelp


Recent Posts
  • T-SQLTuesday #42! The Long … by Jason Brimhall

          The Long and Winding Road It is time for another installment in the monthly blog party for SQL Server professionals known as TSQL Tuesday. This month we have the pleasure of being hosted by Wendy Pastrick (blog | twitter).  The topic for the month requires a bit of introspection (almost like the[...]

  • May 2013 Las Vegas UG by Jason Brimhall

    Spring is in the air, I think. With that scent in the air, we have a nice juicy topic coming up this month for any and all that are interested. Chad Crawford will be presenting to the group on the tastiness that is Service Broker. Service Broker in Action SQL Server Service Broker is a[...]

  • Lost that Single-User Connection? by Jason Brimhall

    You have changed your database to single_user mode to do a task.  As you go about your business, you lose track of which connection was the single_user connection.  You start closing connections and mistakenly close the session that was your single_user connection.  Now you are unable to start a new single_user session.  It would seem[...]

  • MCM Road Less Traveled by Jason Brimhall

    I began this post back in October of 2012 after learning that I had passed the SQL Server 2008 MCM Knowledge exam.  I had set it aside in hopes of polishing it off after my first lab attempt at Summit 2012.  Notice I said first attempt?  I failed that first attempt.  This is a bit[...]

  • Las Vegas April 2013 UG Meeting by Jason Brimhall

    AlwaysOn – Finally, A usable ‘mirror’! In the past, High Availability and Disaster Recovery (HADR) had many limitations. Clustering and Mirroring are great, but the mirror/secondary database is not very usable since it is not online. Finally, AlwaysOn solves this limitation by merging both multi-node Clustering and mirroring. AlwaysOn also allows the secondary database to[...]

  • T-SQL Tuesday #040: File an… by Jason Brimhall

    Backstory Each month the SQL community comes together for an important party.  This is the blog party that was the brain child of Adam Machanic (Twitter) known as T-SQL Tuesday. The party is a very good collaboration among data professionals on a pre-determined topic.  This month, for TSQL Tuesday #40, the topic is on Files and[...]

  • March 2013 LV UG Meeting by Jason Brimhall

    Another month and we have another installment (meeting) for the Data Professionals of the Las Vegas area. This month we will be getting back on schedule with the usual meeting time and place.  We had a slight divergence last month due to that heartsy holiday. We are excited about some of the upcoming speakers and[...]

  • Las Vegas February 2013 UG by Jason Brimhall

    I know it is right in the middle of TSQL2SDAY when this post is to go live.  If you don’t know what that is, you should go check out this months edition here.  The topic this month is good and should drive up a lot of participation. It also happens that this week we have[...]

  • Is your LOG backed up? by Jason Brimhall

    You have been doing a fantastic job of late.  You have all of your databases being backed up on a regular schedule.  Now you get an alert that your transaction log just keeps growing.  Why is that? You decide to do a little investigation and find that you have your recovery model set to full[...]

  • Audit Configuration Changes by Jason Brimhall

    Do you know the last time a Server Property was changed on your instances of SQL Server? Are you wondering when the setting for max degree of parallelism was changed? Do you know who changed the setting? In some environments there are a lot of hands trying to help mix the pot.  Sometimes more hands[...]

Categories
Recent Comments
Welcome , today is Saturday, May 25, 2013