There have been times when I need to know the name of a default constraint. Most often it is because I need to modify a column or table and to do so I must first remove the constraint. The annoying thing is when there isn’t any kind of naming convention on constraints. If you define a constraint such as a varchar column with a default of empty string like DEFAULT(”), then SQL Server will generate a basically random name for the constraint. To find the name of all the constraints in a database you can use this query.

SELECT s.name AS SchemaName, t.name AS TableName,
       c.name AS ColumnName, d.name AS ConstraintName
FROM sys.default_constraints d
INNER JOIN sys.tables t
    ON d.parent_object_id = t.object_id
INNER JOIN sys.schemas s
    ON d.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
    AND d.parent_column_id = c.column_id;

If you need to know the constraints on a single table then just limit based on the name from sys.tables.

SELECT s.name AS SchemaName, t.name AS TableName,
       c.name AS ColumnName, d.name AS ConstraintName
FROM sys.default_constraints d
INNER JOIN sys.tables t
    ON d.parent_object_id = t.object_id
INNER JOIN sys.schemas s
    ON d.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
    AND d.parent_column_id = c.column_id
WHERE t.name = 'Subscriptions';

There is a handy system stored procedure called sp_spaceused that can help when you need to know the space usage of a database or table within a database. There are two ways you can use it. The first way gives some basic size information about the database itself. There are no parameters and it returns two result sets.

EXEC sp_spaceused;

This call will yield something similar to the following.

The other way to use the procedure will give you information about a particular table within the database.

EXEC sp_spaceused N'Person.Address';

When using sp_spaceused for a single table there is only one result set.

As you will notice, this procedure can only get space usage about one table at a time. There are a few techniques to get the statistics about all of the tables in a database. One is to create a temp table and use the undocumented procedure sp_msforeachtable to run sp_spaceused for each table. Rebecca Lewis has an example of this. One issue with this technique is that the result doesn’t include the schema, so if you have multiple tables with the same name that exist in different schemas, you won’t be able to tell them apart.

There are other techniques around that don’t use sp_spaceused that can give you information all in one query. One example is on Luke Hayler’s blog. Taking it a step further, Yaniv Etrogi has an artcle on SQLServerPerformance.com that explains a way to track table growth.

I recently attended the DevConnections conference in Las Vegas. This was my first time attending this particular conference. Having attended the PASS conference the previous 4 years, I thought I’d do a little analysis of the two from my perspective.

Sessions

The main and obvious difference is in the subject matter. PASS is designed specifically for SQL Server. It covers all things relating to SQL Server from query performance tuning, to data warehousing, to Sharepoint integration. The sessions are clearly marked for difficulty level and can get very deep and technical.

DevConnections, on the other hand, has a plethora of “connections”. They each have their own focus and so there is a little bit for everyone. If you focus on Windows management or .NET development there are sessions for each. There is also a SQL Server Connections track for DBAs. If you happen to span both camps of DBA and .NET developer then you can attend a mix of the sessions as the registration allows for access to any session at the entire conference. Strangely, the sessions under the SQL Server track were designated with numbers that gave you an idea of the experience level required, but all of the other tracks were numbered seemingly randomly.

Presenters

There are between 100 and 200 sessions at both events. That’s quite a lot for three days and there is potential for seeing lots of different presenters. I was very pleased at the overall quality of the presenters at DevConnections. Every session that I attended had a very good to excellent presenter. It is my understanding that there are only seasoned presenters at DevConnections. With PASS anyone can submit to present. The presenters are then chosen to fill all available slots. Because there are roughly the same number of sessions, but all focus on SQL Server instead of spread across different fields, there is opportunity for newer presenters to enter the scene. This can be hit or miss. I have attended a couple of session over the past 4 years that were not well delivered. But PASS also has many excellent presenters from all across the SQL Server field.

Vendors

There isn’t much surprising about the vendors. The number of vendors is about the same. There are some big ones and there are some smaller ones. At PASS the vendors are all focused in some area related to SQL Server. At DevConnections they are as diverse as the breadth of the sessions. Both conferences give ample opportunity to visit the booths during exclusive hours and around lunch time. Each conference also provides for a time one evening set aside for meeting the vendors while enjoying some food and drink.

Recordings

One big difference I see is the availability of session recordings after the conference. PASS records the audio and slides/demos of every session and makes them available for purchase during and after the conference. With these you can virtually attend any sessions that you missed due to time conflicts or non-session experiences. When registering for DevConnections you automatically get a DVD for the connection of your choosing. You can also get the other DVDs pretty cheaply. What I came to find out was that it simply contained the slides and scripts for the presentation. Sometimes, the presenter must not have gotten their materials in on time so you simply get a session description. In the SQL Server and .NET sessions in particular there is heavy emphasis on demonstration and so slides are sparse and scripts don’t tell the whole story. Because of this I found that I had difficulty deciding what I would attend and what I would miss. With the session recordings from PASS I never feel that I will miss something I want to hear.

Non-Session Experiences

At PASS there are multiple reasons I might miss a session. One simple one would be a time conflict between two sessions of interest. But PASS also offers a computer lab where you can go through lessons on how to use certain features of SQL Server. There are also tables in the exhibit hall where you can talk to experts on different areas from database design to BI. In another area you can meet with members of the SQLCAT team to discuss matters large or small with your SQL Server environment. Yet another option is hallway chat with other members of the SQL community that you only get to see in person at this event.

DevConnections offers sessions. That’s about where it stops. There was one evening that you could attend an open forum to discuss any matter thrown out. I did attend with about 75 other people out of the thousands of attendees. It was a good experience and I did get some insight out of it. However, it was after a long day of sitting through presentations.

Community

The last difference I noticed was that of community. PASS, being specific to SQL Server probably has an easier time developing ways to bring together to whole group of attendees seeing as how they can at least relate in their product focus. DevConnections, because of its diversity, seems like it has some cliques. DevConnections does have a Facebook page and a Twitter hash tag but I found that after the first day I stopped paying attention to them because people rarely posted anything, it didn’t relate to my areas of interest, or it was a vendor asking for people to stop by their booth.

Conversely, at PASS, Twitter is constantly humming. Perhaps the problem is trying to keep up. Additionally, each night there is a gathering of some sort to bring everyone together for merriment and discussion outside of sessions. It’s a chance to unwind and have a good time with each other. As I mentioned before, you also have opportunity throughout each day to sit with various people in the community and get to know fellow SQL Server professionals. PASS has also pushed to get local user groups to host events throughout the year all around the world to encourage continued learning and community. Perhaps the user groups are strong in the other areas like .NET but it isn’t obvious from the DevConnections conference what’s going on when you get back to the office.

Conclusion

Both conferences definitely have their merits. Obviously, if you are not a SQL Server professional then PASS doesn’t hold much interest for you. But if you are a SQL Server professional or wear many hats, then you may find yourself deciding which to attend. If you primarily focus on SQL Server I would suggest PASS. If you deal mostly with another area and only dabble in SQL Server then you might find DevConnections useful for learning something about many different areas. DevConnections does happen twice a year in the spring and fall so if your budget allows you could even give them both a shot. Give me a shout if you attend and hopefully I’ll see you there.

Backups in SQL Server have the potential for a great amount of compression. Back with SQL Server 2000 I had devised a way the automate the compression of the backups after the backup jobs ran. Even with SQL Server 2005, all database backups to disk are stored as an uncompressed bak file. You have to look to third party tools like Red Gate SQL Backup to compress on the fly or perform compression of the files after the backup process completed. With SQL Server 2008 you are able to create a backup that compresses during the backup process. One unfortunate caveat is that the feature was only available in Enterprise Edition and above. But with SQL Server 2008 R2, Microsoft added the feature to Standard Edition as well. Another thing to keep in mind is that compressed backups can be restored to any edition of SQL Server since SQL Server 2008, but not any version from SQL Server 2005 and before.

Using compression during backup is simply a matter of selecting compression from a drop down, adding a switch to a T-SQL command, or setting the server default for compression. Using the SSMS interface, right click the database, go to Tasks, and select Back up. Set any of the General settings as you normally would. Under the Options page, at the bottom, is a drop down for compression. Simply choose to “Compress Backup” and that’s it.

To use compression with T-SQL, all you need to do is add the COMPRESSION switch to the backup statement in the WITH clause.

BACKUP DATABASE [CAI] TO  DISK = N'E:\MSSQLBackup\CAI.bak' WITH COMPRESSION;

By default, SQL Server sets the backup compression to 0, or off. To change the default value use sp_configure.

sp_configure 'backup compression default', 1
RECONFIGURE
GO

You may want to evaluate whether or not you want the default value set to true. Books Online states that compression significantly increases CPU usage. But since most backups occur during non-peak hours, it may be a great trade off for the disk space savings.

You can analyze the effects of compression by looking at a couple of fields in the backupset table. For example, the following statement shows that the backup achieved a 5:1, or 80%, compression ratio. Instead of a 1.4GB backup it created a 283MB backup.

SELECT TOP 1 name, backup_size, compressed_backup_size,
       backup_size/compressed_backup_size as ratio
FROM msdb..backupset where name like 'MID%';

Give compression a try and save some space. For more in depth discussion see this article by SQLCAT back in 2008 that talks more about compression ratios and ways to optimize the process.

I recently needed to concatenate or pivot some data into a comma-delimited list. There are numerous examples around (Ken Simmons, Rob Farley) showing how to accomplish this by using FOR XML PATH and the STUFF function. My only addition is to add the ability to count the number of items in each list. Here is a simple example to get started.

-- Uses syntax for SQL Server 2008 R2
DECLARE @table TABLE (value varchar(10));

INSERT INTO @table VALUES ('A'),('B'),('C'),('D');

-- Pivot with STUFF and FOR XML PATH
SELECT STUFF((SELECT ',' + value FROM @table
FOR XML PATH('')), 1, 1, '') as val;

Here is the result:

Often it isn’t so useful to just rotate a single column to get a list. Instead, you may want to rotate a list of values per some group of divisions. You might want a list per state or per department. Here is an example that shows how to rotate per a division.

-- Take it a step further with additional values
DECLARE @table2 TABLE (division int, value varchar(10));

INSERT INTO @table2 VALUES (1,'A'),(2, 'B'),
                   (1, 'C'),(2,'D'),(3,'E');

-- Pivot with STUFF and FOR XML PATH and DISTINCT
SELECT DISTINCT division,
  STUFF((SELECT ',' + value FROM @table2 t2 WHERE t1.division = t2.division
  FOR XML PATH('')), 1, 1, '') as val
FROM @table2 t1;

Here is the result:

For my purpose it was also going to be handy to know the number of items in each list. With just a couple of tweaks you can get the count of values per division at the same time as the rotate and even drop the DISTINCT.

-- Take it one more step doing groupings and counts</span>
DECLARE @table3 TABLE (division int, value varchar(10));

INSERT INTO @table3 VALUES (1,'A'),(2, 'B'),(1, 'C'),(2,'D'),(3,'E');

-- Pivot with STUFF and FOR XML PATH and DISTINCT
SELECT division, COUNT(*) as divisioncount,
  STUFF((SELECT ',' + value
  FROM @table3 t2 WHERE t1.division = t2.division
  FOR XML PATH('')), 1, 1, '') as val
FROM @table3 t1
GROUP BY division;

Here is the final result:

Every time there is a backup or restore action, SQL Server stores a record in msdb. In my setup I perform quite a few log backups every day. If I allow the logs to continue to grow I eventually start to see performance degradation on the backup and the restore sides. You can clean up this history in a couple of ways. One way is to set up a Maintenance Plan that cleans up the history. I use this on a day to day basis. Sometimes I may need to remove history records manually so I use this little snippet.

USE msdb
GO
DECLARE @OldestDateToKeep DATETIME
SET @OldestDateToKeep = CONVERT(VARCHAR(10), DATEADD(dd, -30, GETDATE()), 101)
EXEC sp_delete_backuphistory @OldestDateToKeep
GO

I recently changed around the entire security structure at my company to give more modularization to personnel roles. In doing so I came across something about SQL Profiler. While I had given full SUID permissions to a group of users to everything in a database, they could not do any traces with SQL Profiler. This is documented in Books Online, but for the DBA who doesn’t venture into these permissions on a regular basis it may not be something often referenced. To enable the group to use SQL Profiler simply grant them the ALTER TRACE permission.

GRANT ALTER TRACE TO [user or group name]

Books Online does warn that granting this permission could reveal sensitive information such as passwords, so use this with caution.

Recently there was a contest by RedGate in which they gave out iPads and a license to SQL Monitor to 30 lucky winners. They did this by tasking three well-known individuals in the SQL community with giving away 10 each by whatever means they saw fit to use. I’ve met them all at SQL PASS and they deliver a lot to the community. Each came up with his own question for comment and picked the winners from the list of entries. I read through a lot of the entries and realized I was picking up new ideas for ways to use an iPad (I don’t actually have one) or other portable devices. The contest was a way to show the usefulness of SQL Monitor in particular on the iPad. I thought I would compile my observations of how people might use these portable products.

Grant Fritchey blog

He is the Scary DBA. I’ve met him and he’s not all that scary. Actually, he’s a nice guy. I had a chat with him when he happened to be standing next to me when I picked up a book he co-authored. He gave me some good ideas for source control and confirmed some things we were already doing at my company. Grant posed the question, “What do you think the most common cause of server outages is, why, and how would being able to monitor your systems remotely help solve this issue, thereby improving the quality of your life?” His goal was to see how people could achieve a life/work balance. Here’s what I found.

Causes

• All those inforeseen, festering issues that monitoring software could find
• Poor application code
• Disk space (or lack thereof) and other hardware failures
• Undertraining of developers and DBAs
• The DBA did it, in the server room, with the $200 gaming mouse
• Poor planning
• Bad Communications
• Not following best practices

How life is better with your iPad/Monitoring solution

• Know the problem exists
• Get to the solution faster
• Work remotely instead of dragging your body everwhere
• Monitor databases, watch tv, and play Angry Birds…all at the same time.
• Increased communications
• Less shoveling – pick your substance
• Getting alerts before the problems get too big
• Time to write haiku

Steve Jones blog

He is the editor of SQL Server Central. I’ve met him a couple of time at SQL PASS. He also has a ranch. For some reason it seems a lot of DBAs are have a farm or ranch. Interesting. Anyway, Steve wanted to know how you could could do your job better, quicker, or in a new way to enhance your work and bring value to your employer. These are a few of the many responses.

• Consume administrative DBA reports with the ability to send repair requests to Jr. DBAs.
• Watch training videos
• Develop a gestures SQL writer
• Read blogs
• Deliver presentations
• Run an IT Career and a farm at the same time
• Write funny limericks
• Update information while making rounds in a hospital
• Have a lighter weight option for quick remote troubleshooting
• Track casino players on the floor
• Lots of quick troubleshooting alerted by SQL Monitor

Brent Ozar blog

It seems like he does a lot. It looks like he currently does consulting and helps with SQLSkills.com. He is one of a very few Microsoft Certified Masters of SQL Server. I shook his hand once at SQL PASS. Brent wanted to know where you would go if you didn’t have to worry about what your servers were doing.

• Cruise
• Germany
• Disneyland/Disneyworld
• Various Hawaiian and Caribbean Islands
• The Kitchen
• The Bathroom
• Home
• Fantasy lands with unicorns and leprechauns OR Margaritaville
• North, South, East, or West
• Bed (not sure whether alone or not)

The lists are certainly not all from the 300+ comments across the three blogs, but it gives an idea of what people are thinking.

Recently I needed to transfer about 50 smallish databases from one machine running SQL Server 2005 to another machine running SQL Server 2008 R2. These were all production databases that had the potential to be in use 24/7 so the transfer needed to take as little time as possible. I built several scripts that in turn built other scripts that helped me accomplish this task. I ended up breaking the process down into four scripts. The first script detached all of the databases. The second script was actually a batch file that copied the files from one machine to the other. In my case, on the original server the mdfs and ldfs were all on the same array. The copy script actually split them to put the mdfs on one array and the ldfs on another. The third script attached the databases on the new machine. The fourth one simply changed the compatibility level of the databases to 2008 R2.

First, I had to build the commands for each of the databases. I used a script to build everything and copied the result to a file for each of the steps. There were a few items to keep in mind. Before using any of the scripts to detach databases, the databases must not be in use. You can either verify this ahead of time or put the server into single-user mode. Something I ran into with the detach script is that I needed to run it as ‘sa’ or else the Windows permissions got jacked and copying the files in later steps didn’t work. I’m sure it’s something simple but I didn’t research it at the time. I bet someone has the answer why. But back to the scripts.

Create a script to detach the databases from the current server.

SELECT 'USE [master]
GO'
SELECT 'EXEC master.dbo.sp_detach_db @dbname = N''' + D.name + '''
GO '
FROM sys.databases D
WHERE D.database_id > 4
ORDER BY D.name

The script simply uses the sys.databases table and constructs an EXEC statement to call sp_detach_db for each user database on the server. Notice that I limit the results to database_id greater than 4. Typically the first four databases are system databases. You can tweak the number if this doesn’t hold true on your server. Run the script to generate all of the detach statements. You can run the results to Text and do a copy/paste into another file. You can also run the results directly to a file.

Next, create a script to copy the files from the old server to the new server.

SELECT 'robocopy \\OLDSRV\D$\MSSQL\MSSQL.1\MSSQL\Data \\NEWSRV\E$\ ' + RTRIM(substring(right(DbFile.physical_name,charindex('\',reverse(DbFile.physical_name))),2,100))
FROM master.sys.databases D
JOIN master.sys.master_files DbFile ON DbFile.database_id = D.database_id
WHERE D.database_id > 4
ORDER BY D.name

This script creates a line for each file to use robocopy for transfering the file from the old server to the new server. I copied this into a batch file. Another option would be to use this within a Powershell script. With this I copy the databases from one directory on the old server to a different directory on the new server. This was my need but there are all kinds of ways to use this. I happened to also have all of the databases in the same directory. This may not be true in all cases and the script may need to make use of the full path in physical_name of sys.databases.

The third script creates the SQL to attach the databases on the new server.

SELECT 'CREATE DATABASE [' + D.name + '] ON ( FILENAME = N''E:\' + RTRIM(substring(right(DbFile.physical_name,charindex('\',reverse(DbFile.physical_name))),2,100)) + '''),(FILENAME = N''E:\' + RTRIM(substring(right(LogFile.physical_name,charindex('\',reverse(LogFile.physical_name))),2,100)) + ''' ) FOR ATTACH
GO '
FROM sys.databases D
JOIN sys.master_files DbFile ON
D.database_id = DbFile.database_id
AND DbFile.type = 0
JOIN sys.master_files LogFile ON
D.database_id = LogFile.database_id
AND LogFile.type = 1
WHERE D.database_id > 4
ORDER BY D.name

SELECT 'CREATE DATABASE [' + D.name + '] ON ( FILENAME = N''' + DbFile.physical_name + ''' ),( FILENAME = N''' + LogFile.physical_name + ''' ) FOR ATTACH
GO '
FROM sys.databases D
JOIN sys.master_files DbFile ON
D.database_id = DbFile.database_id
AND DbFile.type = 0
JOIN sys.master_files LogFile ON
D.database_id = LogFile.database_id
AND LogFile.type = 1
WHERE D.database_id > 4
ORDER BY D.name

There are two creation scripts here. The first one creates the script to attach the databases for the new server. It attaches the files from their new locations used during the copy step. The second script creates the script to attach the databases back on the old server from their original locations. This is in case you still want the old server or if something went wrong during the transfer process.

The last script is used to update the compatibility mode of the newly attached databases on the SQL Server 2008 R2 server.

SELECT 'ALTER DATABASE [' + D.name + '] SET COMPATIBILITY_LEVEL = 100
GO'
FROM sys.databases D
WHERE D.database_id > 4
ORDER BY D.name

When you restore a 2005 database to a 2008 R2 server the database gets upgraded automatically. However, the database is set to compatibility mode 90, which is SQL Server 2005. This script will update the compatibility mode to 100, which is SQL Server 2008 R2.

After following these steps you can have your SQL Server 2005 databases moved to a SQL Server 2008 R2 machine in no time.

I want to give a reference to Aaron Nelson (@sqlvariant). He had a blog post that got me started down this path.