Recently had need of a SQL Server script that can tell me if there are open transactions on the database. The script below worked for me:

USE MASTER
GO
SELECT spid,
       PROGRAM_NAME,
       nt_userName,
       loginame,
       DB_NAME(s.dbid) AS DatabaseName,
       CR.TEXT AS Query
FROM   sysprocesses s
       CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE  open_tran = 1

Source: Welcome To TechBrothersIT: DBA – How To Find Open Transactions In SQL Server

Tags: #Database #Scripts #SqlServer

Discuss... or leave a comment below.

Unintentionally running data manipulation scripts on a Production database is obviously bad. In this post, I'll share some tips for working with Production databases so the same thing doesn't happen to you.

1. Check your Database Connections

Double-check your database connection before running any scripts. Oftentimes, this simple check is all you need to do, to avoid accidentally deleting data in a Production database.

2. Start new instance of SQL Server Management Studio

When connecting to a Production database, start up a new instance of SQL Server Management Studio. The idea is to keep scripts between Production and other databases separate. This helps you distinguish between database connections. And hopefully stop you from running a script that will mess up the Production database.

3. Work off Database Backups

Better yet, if you have the option, restore a backup copy of the Production database on a dev box and work off that. This way, you can mess with the data and not worry about affecting Production.

Note: Make sure restoring database to a local dev box is allowed per your company policy.

4. Use Database Transactions

If the script you are running has the potential to change a databases' data or schema – like if it has the words ALTER, CREATE, DELETE, INSERT, UPDATE, or any other statement that can modify the contents of a database – then you should be wrapping the scripts in a transaction. BEGIN TRAN is your friend. I do this even when I'm running the scripts on a local database instance. It might sound like overkill to use transactions on scripts running on non-PROD databases, and maybe it is. But what's an extra 10 seconds or so of doing that, if it prevents you from accidentally blowing up the Production database? I do it for peace of mind. It's part of routine for me now. Anytime I write a script that has the words ALTER, CREATE, DELETE, INSERT or UPDATE, I wrap them in a transaction. Doesn't even matter what database I'm connected to.

This is how I wrap scripts in a transaction:

BEGIN TRAN

-- ALTER, CREATE, DELETE, INSERT, UPDATE Statements go here

ROLLBACK TRAN
COMMIT TRAN

I use it this way: I highlight the scripts starting from BEGIN TRAN and stop just before the ROLLBACK TRAN statement. I run those and double-check the results. This gives me the opportunity to either ROLLBACK or COMMIT depending on the results.

Also, you'll notice above that I have a ROLLBACK TRAN just before the COMMIT TRAN statement. This is just an extra safety precaution that can be especially useful when you're sending out scripts to someone else. They might accidentally run the scripts in a Production database, but the ROLLBACK TRAN will make sure nothing bad happens.

5. Close Instances of SQL Server Management Studio

Before going home or logging off from work, close any instances of SQL Server Management Studio, especially those that you used to connect to a Production database. You'll want to do this because closing an SSMS instance while you have an open transaction, will cause it to display a warning. An open transaction has the potential to block queries and cause timeouts. This will remind you to either rollback or commit your transactions before logging off.

That's it for today. I hope those tips are helpful. If you have your own tips for working with Production databases, do share them by leaving a comment below or sending me a message.

Tags: #Database #Scripts #SqlServer

Discuss... or leave a comment below.

For the past week or so, I've noticed that my C drive was constantly running out of space. Normally, I have a little over 200 GB of free space on my C drive. I ran the SpaceSniffer tool to figure out what was taking up all the space. It pointed me to a set of temp files used by SQL Server. One file in particular, “templog.ldf”, was taking up 40 GB of space. The other tempdb files were 17 GB each but there were 8 of them. My understanding is that these files should automatically shrink back down, but why won't they?

I know what caused those files to increase in size. I've been restoring large database backups locally. And I've done it many times throughout the week. The stored procedures in those databases do use the temp tables a lot. So, I'm not surprised the SQL Server temp files got so big. But I'm still not sure why those files were not shrinking back down to their normal sizes. They maintain their size even after republishing to an empty database. If that was always the case, then I would have noticed this issue years ago. Yet, it has only been happening recently.

While I don't exactly have an explanation for why those files remained huge, I did find a way to shrink them back down. The solution was simple: restart the SQL Server service. Once I did this, the temp files went back to their regular sizes. Restarting the PC would also work, but I haven't done it in awhile since I've started working from home. I believe that contributed to the issue of files maintaining their huge sizes. So, going forward I will restart my work PC every weekend.

Tags: #Database #SqlServer

Discuss... or leave a comment below.

As software developers, one of the problems we have to be mindful of, is our tendency to “reinvent the wheel”. By this I mean the tendency for developers to write a new application to solve a problem, that could have been solved by simply using existing applications/tools. When faced with the problem of importing all ObjectIds from a MongoDB collection into a SQL Server table, I thought I needed to write a new migration/utility app. Turns out I don't need to write any code at all, well except for the MongoDB query of course, but that's besides the point. Here is how I solved this problem using free tools available on the Internet.

Solution

First up is the MongoDB query that will get you all ObjectIds from a collection. I use the free Robo 3T app to query MongoDB.

db.getCollection('Collection').find({},{_id:1});

Robo 3T has a “view results in text mode” option that will display the results in JSON format. Select that and then run the query listed above. You will then be presented with results that will look similar to what I have below.

/* 1 */
{
    "_id" : ObjectId("5cba07dcca67bd08e8a6b3e2")
}

/* 2 */
{
    "_id" : ObjectId("5cba07b4ca67bc33705ded1d")
}

/* 3 */
{
    "_id" : ObjectId("5cba07baca67bc33705ded1e")
}

So now we have a list of ObjectIds from MongoDB. Imagine if you got back thousands of ObjectIds. It would be tempting at this point to say something like, hey I need to write a console app that can parse these results. I know I did. And I did write such an app, but eventually I realized I didn't need to. All I needed was Notepad++. Actually any text editor with a good enough “search and replace” feature will work.

After copying those results from Robo 3T into Notepad++, you can then use the “search and replace” functionality to transform the results into INSERT scripts that you can run in SQL Server.

So first thing you need to do is comment out all those brackets { and }. To do this, you simply search for { or } and replace the values with --{ or --}. Adding -- to the start of a line will comment it out in SQL Server. You can also opt to simply just delete all those brackets. SQL Server won't care if there are spaces between INSERT scripts. At this point your text file in Notepad++ will look similar to what I have below.

/* 1 */
--{
   "_id" : ObjectId("5cba07dcca67bd08e8a6b3e2")
--}

/* 2 */
--{
   "_id" : ObjectId("5cba07b4ca67bc33705ded1d")
--}

/* 3 */
--{
   "_id" : ObjectId("5cba07baca67bc33705ded1e")
--}

The second thing you need to do is to transform the lines with the ObjectId values in it, into SQL Server INSERT statements. To do this you go through two steps:

1. Search for "_id" : ObjectId(" and replace the values with INSERT INTO [dbo].[MongoDbObjectIds] ([ObjectId]) VALUES ('. (This is assuming you have a MongoDbObjectIds table in SQL Server with an ObjectId column.)
2. Search for ") and replace the values with ');. This will round out the INSERT statements. At this point, you should have valid INSERT statements that can be used in SQL Server. They should look similar to the ones I have below.

   /* 1 */
   --{
      INSERT INTO [dbo].[MongoDbObjectIds] ([ObjectId]) VALUES ('5cba07dcca67bd08e8a6b3e2');
   --}

   /* 2 */
   --{
      INSERT INTO [dbo].[MongoDbObjectIds] ([ObjectId]) VALUES ('5cba07b4ca67bc33705ded1d');
   --}

   /* 3 */
   --{
      INSERT INTO [dbo].[MongoDbObjectIds] ([ObjectId]) VALUES ('5cba07baca67bc33705ded1e');
   --}

All that needs to be done now is to copy the INSERT statements, run them in SQL Server Management Studio and you are done.

Now it must be noted that this approach to getting ObjectIds out of MongoDB and into SQL Server, is useful only if you are doing it once or twice. As soon as you have to repeat this process multiple times throughout the week or worse, throughout the day, then by all means write a migration/utility app to automate the process. It might take you longer to finish it, but the re-usability of said migration application will pay for itself in the future.

Hope you found this post helpful. Happy Friday to everyone!

Tags: #MongoDB #SqlServer #Scripts #Database

Discuss... or leave a comment below.

One of the best additions to SQL Server 2016 is native support for JSON. There a number of articles already covering how to query JSON in SQL Server, so I won't cover those. What particularly gives me headaches though is querying a JSON Array in SQL Server. Hopefully the scripts below can help someone else.

The SELECT script below can be used when you are working with a very basic JSON array that doesn't even have a Property for the array. In this example, we have an array of OrderIds.

DECLARE @OrderIdsAsJSON VARCHAR(MAX) = '["1", "2", "3"]';

SELECT [value] AS OrderId
FROM OPENJSON(@OrderIdsAsJSON)
WITH
(
       [value] BIGINT '$'
);

The second SELECT script below can be used when you are working with a JSON array that does have a Property defined for the array. In this example, we have an array of OrderIds with a defined OrderId property.

DECLARE @OrderIdsAsJSON VARCHAR(MAX) = '{"OrderId":["4", "5", "6"]}';

SELECT [value] AS OrderId
FROM OPENJSON(@OrderIdsAsJSON, '$.OrderId')
WITH
(
       [value] BIGINT '$'
);

---

Update: 2019-07-17

Adding another example for a JSON array that came from an Int list in C#. This is how you could query it in SQL Server.

DECLARE @OrderIds VARCHAR(MAX) = '{"$type":"System.Int64[], mscorlib","$values":[4, 5, 7, 999]}';

SELECT [value] AS OrderId
FROM OPENJSON(@OrderIds, '$."$values"');

#SqlServer #Scripts #Database #JSON

Discuss... or leave a comment below.

I'm putting this TRY CATCH template on here in case other people might find it helpful. This is what I usually use when wrapping SQL Scripts in a stored procedure. Normally I would have go to Microsoft Docs to get the syntax for using TRY CATCH and RAISERROR. Having a template that I know works and is ready to go is just easier.

BEGIN TRY
   BEGIN TRAN

	-- Add your scripts in here
	
   COMMIT TRAN
END TRY  
BEGIN CATCH  
	WHILE (@@TRANCOUNT > 0)
	BEGIN
		ROLLBACK TRAN;
	END

	DECLARE @ErrorMessage NVARCHAR(4000);  
	DECLARE @ErrorSeverity INT;  
	DECLARE @ErrorState INT;  

	SELECT   
		@ErrorMessage = ERROR_MESSAGE(),  
		@ErrorSeverity = ERROR_SEVERITY(),  
		@ErrorState = ERROR_STATE();  

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
END CATCH;

#SqlServer #Scripts #Database

Discuss... or leave a comment below.

The scripts below will help you find a table in a SQL Server database based on a given “table name” filter. This is helpful if you are not familiar with a rather large database and didn't want to waste time looking for a table manually. In my case, I usually use these scripts to check for an existing table in a database, before I create a new one.

Most of the time when working with a large database, the table that you think you need to add, was already created by someone else. Oftentimes it is named differently than what you would have named it, but the contents and use of the table would have been the same. So it pays to double check before creating an already existing table.

SELECT *
FROM SYS.tables
WHERE NAME LIKE '%tableName%'

/* The script below will give you a result in the "schema.tablename" format, which can help you quickly locate the table. */
SELECT (SELECT [Name] FROM SYS.schemas AS S WHERE S.schema_id = T.schema_id) + '.' +  T.Name AS 'TableName'
FROM SYS.tables AS T
WHERE NAME LIKE '%tableName%'

#SqlServer #Scripts #Database

Discuss... or leave a comment below.