SwampfoxSoft

Locked Databases

Jonathan Moore — Fri, 19 Mar 2010 13:12:12 +0000

Every so often, I’ll run into a database that’s locked and I want to be able to change something about it and cannot. One way to see what is using the database in question is to run the activity monitor in SQL SMS. From the activity monitor, you can kill a process from this screen by right-clicking the process and selecting the “Kill Process” option. Unfortunately, I’ve had a few cases where the connections are on timers, so the connections come right back and lock the database again and I get the locked message again.

However, there is a way around this. By setting the database as a single user database and rolling back all opened transactions, you can allow yourself the time to make the changes needed.

alter database set single_user with rollback immediate

After running the command above, you should see the following result:

“Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.”

After this is ran, only 1 user can now be connected to your database. Just remember, the single user that is connected at that time is the window (or ADO connection) that you run this statement from. If you close your connection, the single connection could be taken by another process. So it’s probably best to have your changes ready in T-SQL to run after you run the statement to alter database to single user. If you are just running this to close all open connections (and there isn’t some process that auto-reconnects), then you can just run the statement below to alter the database back. All open connections (except the current window/connection you are in) were closed by the first statement.

Running this next alter statement sets the database to allow multiple users again. Any connection that was closed by the first statement will remain closed.

alter database set multi_user with rollback immediate

After the command runs successfully, you will see the following result:

“Command(s) completed successfully.”

So if you ever run into an issue where a connection is keeping you from processing changes that need to be made, first try the activity monitor. If the connection(s) still persists, setting the database to single_user is good way to lock out other connections as long as it is handled correctly.

Turning Off Database Constraints

Jonathan Moore — Tue, 19 Jan 2010 12:53:25 +0000

Ever needed to run a sql statement on every single table in a database (like adding a LastUserChanged column or turning off constraints temporarily)? Well, there’s a good stored procedure for doing this:

EXEC sp_msforeachtable

So say you need to change data around on a database but foreign keys are slowing you down? You can turn off all the constraints on your database and then turn them back on when you’re done (and still make sure your data is good)

You could run ALTER TABLE NOCHECK CONSTRAINT all for each table you want to change. Even better, you could just turn off all constraints:

EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

Then turn them all back on (with checking to make sure the data is fine):

EXEC sp_msforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

The stored procedure sp_msforeachtable will allow you to quickly make changes to all tables.

SQL: With Rollup

Jonathan Moore — Tue, 10 Nov 2009 02:30:00 +0000

There have been many cases over the years where I have had to spew out data into some type of output for the user, from Crystal Reports to text files and straight output to a print source. Most of the time, the users wanted multiple data rows and then a summary row to go along with it.

What that usually meant though was that I returned the data to the application and then as I was printing I would do any calculations that needed to be done (Sum / Max / Min / Average) and also have to look for the breaks between groups so I could show subtotals as well. This meant for every aggregate function I wanted to do, I had to have a corresponding Subtotal and Grand Total variable and continuously calculate the variables for each record.

Seems that I have been doing a lot more work than I needed to. With Rollup takes away a lot of the applications need to manipulate the data so that all you really have to do is display the output in the format the user is expecting. With Rollup allows the data to not only have the detail records, but summary records to go with those records.

How does the really change what I’m doing? Well, let’s take a look at how I currently handle a situation and how it changes with With Rollup.

Four friends have decided to go out bowling and want to keep their score across four games. At the end of the night, they want to know their Total Score, Highest Score, Lowest Score and Average Score across all four games. They want these by each person, but for the group as well. Paul goes with the group, but doesn’t actually bowl any while he’s there. This following is the data to show how they bowled and will be used in the examples that follow.



DECLARE @Player TABLE(PlayerID INT IDENTITY, Name VARCHAR(30))
INSERT INTO @Player VALUES (’Chris’)

INSERT INTO @Player VALUES (’Sam’)

INSERT INTO @Player VALUES (’Alex’)

INSERT INTO @Player VALUES (’Paul’)
DECLARE @Scores TABLE(ScoreID INT IDENTITY, PlayerID INT, Score BIGINT)

INSERT INTO @Scores VALUES (3, 170) INSERT INTO @Scores VALUES (1, 223) INSERT INTO @Scores VALUES (3, 187) INSERT INTO @Scores VALUES (1, 250) INSERT INTO @Scores VALUES (2, 195) INSERT INTO @Scores VALUES (1, 244) INSERT INTO @Scores VALUES (3, 148) INSERT INTO @Scores VALUES (3, 166) INSERT INTO @Scores VALUES (2, 195) INSERT INTO @Scores VALUES (1, 237) INSERT INTO @Scores VALUES (2, 212) INSERT INTO @Scores VALUES (2, 172)

The simple way that I’d handle this in the past is just joining these values together and then dumping it out to the application.



SELECT 'PlayerName' = PLY.Name, SCR.Score

FROM @Player PLY

LEFT OUTER JOIN @Scores SCR

ON PLY.PlayerID = SCR.PlayerID

ORDER BY PLY.PlayerID

Chris	223
Chris	250
Chris	244
Chris	237
Sam	195
Sam	195
Sam	212
Sam	172
Alex	170
Alex	187
Alex	148
Alex	166
Paul	NULL

The application would then figure out the Total, High, Low and Average Score for each person as well as the group. For this, we’d need 10 different variables (also would need the count so that we could average correctly) and each calculation is prone to mistakes in coding. The application also has to compare current user to the user from the last record to know when to put in summary rows. To make life easier, we could just group the items together and return that after returning our detail records.



SELECT 'PlayerID' = PLY.PlayerID

	, ‘PlayerName’ = (SELECT P2.Name FROM @Player P2 WHERE P2.PlayerID = PLY.PlayerID)

	, ‘TotalScore’ = SUM(SCR.Score), ‘HighScore’ = MAX(SCR.Score), ‘LowScore’ = MIN(SCR.Score)

	, ‘AverageScore’ =  AVG(CONVERT(MONEY,SCR.Score))

FROM @Player PLY

LEFT OUTER JOIN @Scores SCR

ON PLY.PlayerID = SCR.PlayerID 

GROUP BY PLY.PlayerID

ORDER BY PLY.PlayerID

PlayerId	PlayerName	TotalScore	HighScore	LowScore	AverageScore
1	Chris	954	250	223	238.5
2	Sam	774	212	172	193.5
3	Alex	671	187	148	167.67
4	Paul	NULL	NULL	NULL	NULL



SELECT 'PlayerID' = NULL, 'PlayerName' = 'Total', 'TotalScore' = SUM(SCR.Score)

	, ‘HighScore’ = MAX(SCR.Score), ‘LowScore’ = MIN(SCR.Score)

	, ‘AverageScore’ = AVG(CONVERT(MONEY,SCR.Score))

FROM @Player PLY

LEFT OUTER JOIN @Scores SCR

ON PLY.PlayerID = SCR.PlayerID

This way, we’ve gotten rid of calculations in the application, but now our application has 3 different sets of data. It also has to know when to use which set of data.
With Rollup simplifies all of this by allowing detail and summary data in the same set.

PlayerId	PlayerName	TotalScore	HighScore	LowScore	AverageScore
NULL	Total	2399	250	148	199.9166



SELECT 'PlayerID' = PLY.PlayerID

	, ‘PlayerName’ = (SELECT P2.Name FROM @Player P2 WHERE P2.PlayerID = PLY.PlayerID)

	, SCR.ScoreID, ‘TotalScore’ = SUM(SCR.Score), ‘HighScore’ = MAX(SCR.Score)

	, ‘LowScore’ = MIN(SCR.Score), ‘AverageScore’ =  AVG(CONVERT(MONEY,SCR.Score))

FROM @Player PLY

LEFT OUTER JOIN @Scores SCR

ON PLY.PlayerID = SCR.PlayerID

GROUP BY PLY.PlayerID, SCR.ScoreID

WITH ROLLUP

PlayerId	PlayerName	ScoreID	TotalScore	HighScore	LowScore	AverageScore
1	Chris	2	223	223	223	223
1	Chris	4	250	250	250	250
1	Chris	6	244	244	244	244
1	Chris	10	237	237	237	237
1	Chris	NULL	954	250	223	238.5
2	Sam	5	195	195	195	195
2	Sam	9	195	195	195	195
2	Sam	11	212	212	212	212
2	Sam	12	172	172	172	172
2	Sam	NULL	774	212	172	193.5
3	Alex	1	170	170	170	170
3	Alex	3	187	187	187	187
3	Alex	7	148	148	148	148
3	Alex	8	166	166	166	166
3	Alex	NULL	671	187	148	167.67
4	Paul	NULL	NULL	NULL	NULL	NULL
4	Paul	NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	2399	250	148	199.9166

As we see with the data returned, there are some rows with a NULL ScoreID and one row with both a NULL ScoreID and a NULL PlayerID. The rows with a NULL ScoreID are summary rows that contains the aggregates of all the scores for a player. The row with both a NULL ScoreID and a NULL PlayerID is the summary row that contains the aggregates for all scores for all players.
However, this means that we are relying on NULL comparisons to find which rows are summary rows. To make this an easier check, we can add the GROUPING clause.



SELECT 'PlayerID' = PLY.PlayerID

	, ‘PlayerName’ = (SELECT P2.Name FROM @Player P2 WHERE P2.PlayerID = PLY.PlayerID)

	, ‘ScoreGroup’ = GROUPING(SCR.ScoreID), ‘PlayerGroup’ = GROUPING(PLY.PlayerID)

	, ‘TotalScore’ = SUM(SCR.Score), ‘HighScore’ = MAX(SCR.Score), ‘LowScore’ = MIN(SCR.Score)

	, ‘AverageScore’ =  AVG(CONVERT(MONEY,SCR.Score))

FROM @Player PLY

LEFT OUTER JOIN @Scores SCR

ON PLY.PlayerID = SCR.PlayerID

GROUP BY PLY.PlayerID, SCR.ScoreID

WITH ROLLUP

PlayerId	PlayerName	ScoreGroup	PlayerGroup	TotalScore	HighScore	LowScore	AverageScore
1	Chris	0	0	223	223	223	223
1	Chris	0	0	250	250	250	250
1	Chris	0	0	244	244	244	244
1	Chris	0	0	237	237	237	237
1	Chris	1	0	954	250	223	238.5
2	Sam	0	0	195	195	195	195
2	Sam	0	0	195	195	195	195
2	Sam	0	0	212	212	212	212
2	Sam	0	0	172	172	172	172
2	Sam	1	0	774	212	172	193.5
3	Alex	0	0	170	170	170	170
3	Alex	0	0	187	187	187	187
3	Alex	0	0	148	148	148	148
3	Alex	0	0	166	166	166	166
3	Alex	1	0	671	187	148	167.67
4	Paul	0	0	NULL	NULL	NULL	NULL
4	Paul	1	0	NULL	NULL	NULL	NULL
NULL	NULL	1	1	2399	250	148	199.9166

As you can now see, we now have 2 columns that contain a bit type to determine whether or not the row is a summary row. When the ScoreGroup is 1, you can expect this row to be a summary of scores for the group. If PlayerGroup is also 1, then the scores are summarized for all the scores for the all the players. We now have one set of data to work with and can easily output it from the application without having to do the summaries, keep up with current users or worry about how many records are in the set.
With Rollup allows for SQL to handle a lot of the dirty work that I used to handcode. Using SQL for this makes code cleaner and more consistent and just plain simpler.

SQL

Jonathan Moore — Wed, 09 Sep 2009 12:11:09 +0000

As a programmer and then later a developer at the job I have, I’ve always been someone that had to know a lot of different technologies. By this, I mean that I really have never been in able to focus on in any one technology. I work for a small company that does custom application development to suit the customers needs, so we adapt to them. In my work, I use VB6, Crystal Reports (8, 8.5, 10, 11), Visual Basic.Net, C#, SQL, LLBLGen, ASP, etc, etc. On top of those responsibilities I also help with the Network and I run the phone system here.

After all these years though, I’ve always been drawn to one thing here at work, SQL Server. I love designing databases and writing stored procedures. So, I’m pushing deeper into MSSQL to learn everything I possibly can, big or small, to help me be better at what I do. Thankfully, I’ve been pushed more into the database world lately, so now is the perfect opportunity to expand my SQL knowledge even further.

Basically, I’m stating this because I am going to start blogging about things I learn SQL-wise along the way, no matter how small. I know it will be helpful to me but hopefully, I’ll be a help to someone else along the way as well.

The Realm

Jonathan Moore — Mon, 18 May 2009 12:28:21 +0000

Was looking up an article on Cisco Network Security and stumbled on their nifty little web series: The Realm.

New Computer

Jonathan Moore — Fri, 15 May 2009 11:53:05 +0000

Since I’m going to be studying for my SQL Exams (70-431 or 70-432, 70-433 and 70-448), I needed a new computer to run all the software I need to study (as well as for a little bit of gaming). My old PC was purchased from Wal-Mart about 5 years ago, so it definitely has seen better days. I set up a computer on NewEgg and was actually shocked at the amount of power you can get for relatively cheap. Here’s the specs on my new computer:

Motherboard	GIGABYTE GA-EP43-UD3L LGA 775 Intel P43 ATX Intel Motherboard
CPU	Intel Core 2 Duo E7400 Wolfdale 2.8GHz LGA 775 65W Dual-Core Processor Model BX80571E7400
Memory	CORSAIR 4GB (2 x 2GB) 240-Pin DDR2 SDRAM DDR2 1066 (PC2 8500) Dual Channel Kit Desktop Memory
Hard Drive	Western Digital Caviar Black WD5001AALS 500GB 7200 RPM 32MB Cache SATA 3.0Gb/s 3.5″ Internal Hard Drive
Video	XFX GS250XYDFU GeForce GTS 250 512MB 256-bit GDDR3 PCI Express 2.0 x16 HDCP Ready Video Card
DVD Drive	SAMSUNG Black DVD Burner with LightScribe
Power	OCZ Fatal1ty OCZ550FTY 550W ATX12V / EPS12V SLI Ready CrossFire Ready Modular Active PFC Power Supply
Case	GIGABYTE GZ-X3BPD-500 Black 0.6mm SECC Steel ATX Mid Tower Computer Case

After putting the computer together and making sure that everything was working, I loaded Windows XP Media Center on it. It took about a week to get all the software I needed on it and to get it configured the way I wanted. It was a huge improvement over my old PC. However, I was a little disappointed that XP Media Center wasn’t using all 4 gigs of RAM due to it being 32-bit.

The Lord works in mysterious ways. I attended the PDANUG and at the end they gave out some door prizes. The prize that I won just happened to be exactly what I needed: Windows Vista Ultimate, 32-bit AND 64-bit. So I’ve got my PC loaded with it now, running like a dream on all 4 Gb of RAM!

Development Blog

Jonathan Moore — Thu, 14 May 2009 16:01:54 +0000

This is now strictly a development blog, focusing on Microsoft SQL Server. My personal blog has moved to 42 Roads to Walk.