SwampfoxSoft

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 <Database Name> 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 <Database Name> 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.

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.

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.
Read more…

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.