Get rowcount for all tables in a database

This one a simple post, we are getting the rowcount for each table in the current database. That’s it.

--Get the rowcount for each table in the current database
IF OBJECT_ID('tempdb.dbo.#tcounts', 'U') IS NOT NULL
 DROP TABLE #tcounts; 
CREATE TABLE #tcounts (
 Table_Name nvarchar(255),
 Row_Count int
)
EXEC sp_MSForEachTable @command1='INSERT #tcounts (Table_Name, Row_Count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #tcounts ORDER BY Table_Name
IF OBJECT_ID('tempdb.dbo.#tcounts', 'U') IS NOT NULL
 DROP TABLE #tcounts;

 

Debugging Windows Service Tip

This is a quick tip on debugging Windows services or anything that runs as a seperate thread.  In other words, if you have to attach to that process to debug it, and you have the source code, the following tip will make it easy to debug.

A Windows service usually runs automatically, it is designed to run as a continuous service and, therefore, spawns one or more threads to do the actual work. This happens very quickly. As such, it is difficult to attach to the process (Alt+P) in time to properly debug the service and attach during a specific part of the execution. As a multi-threaded app, it becomes cumbersome and painful to debug. Fortunately since Visual Studio 2015 that we can vote invoke that allows us to attach to the process through the Visual Studio just-in-time debugger. It’s super simple, call:

System.Diagnostics.Debugger.Launch();

Use this call anywhere in your code where you want the code to break into Visual Studio.  It will invoke a Visual Studio just-in-time debugger prompt:

Visual Studio 2015 Just-In-Time Debugger
Visual Studio 2015 Just-In-Time Debugger

Make your selection and debug.  Happy coding my friends.

For more information: https://msdn.microsoft.com/en-us/library/cktt23yw.aspx

SQL Server Backup and Restore from Network Drives

Working with virtual machine environments has always presented a challenge in figuring out how to “easily” or “effectively” restore SQL Server databases. This is especially true during migrations and upgrades. It is easy to run out of hard drive space when copying, moving and manipulating several terabytes of content databases. Recently, I found myself working with tens of terabytes and I found myself lacking in space and time. Even with enough storage, copying and moving the databases was taking significant amounts of time. I concluded that it would be to my benefit, my client’s benefit and all of those great people reading this blog to figure out how to backup and restore a SQL database from a network drive. And, I figured it out after putting together several scripts and testing various credentials on the network.  Yes, you heard right, it exists. The following script effectively maps a network drive under the SQL Server process account which also performs the backups and restores. This is huge: its a huge storage saver, but more importantly and huge, huge time saver. Alas, here’s the script (in three steps):

Step 1: Map the drive

In this step, we issue the commands to map a drive (i.e “U:”) under the SQL Server process account with an account that has access. Replace the “DOMAIN\username” with your account if you have access to the network share to map the network drive under your credentials.  By running the command in the Query Window, the SQL Server process account will be able to see the mapped drive, thus, allowing us to backup and restore to that network drive directly.

Use Master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC XP_CMDSHELL 'net use u: /delete'
EXEC XP_CMDSHELL 'net use u: \\networkserver\ShareBackups MySecretPassword /USER:DOMAIN\username'
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
Step 2: Backup or Restore

In this step, we backup or restore the desired databases.

To perform a backup (note we use copy_only to avoid breaking the backup chain and compression):

BACKUP DATABASE WSS_CONTENT TO DISK = 'u:\WSS_CONTENT.bak' WITH COPY_ONLY,COMPRESSION;
GO

To perform a restore:

RESTORE DATABASE WSS_CONTENT FROM DISK = 'U:\WSS_CONTENT.bak' WITH MOVE 'WSS_CONTENT' TO 'D:\SQLData\WSS_CONTENT_Data.mdf', MOVE 'WSS_CONTENT_Log' TO 'D:\SQLLogs\WSS_CONTENT_Log.ldf', RECOVERY, REPLACE, STATS = 10;
Step 3: Unmap the drive

Lastly, we issue the commands to delete the mapped drive (i.e “U:”).

Use Master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC XP_CMDSHELL 'net use u: /delete'
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

 

Killing Active SQL Connections

So there are many times we are waiting to take a database offline or delete a database. I finally decided there had to be a better way than to wait … specially when this is my development environment. So here are two queries we can use.

First, find the active connection for all the databases:


select
  db_name(dbid) as [DB Name],
  count(dbid) as [Active Connections],
  loginame as [Login Name]
from sys.sysprocesses
where dbid > 0
group by dbid, loginame;

Then from that, we can determine which database needs the connections terminated. Or you can use this SQL directly if you already know which database you want to kill the active connections on:


set nocount on;
declare @databasename varchar(100);
declare @qry varchar(max);
set @qry = '';
---------------------------------------------------
set @databasename = '[Database name]';
---------------------------------------------------
if db_id(@databasename) < 4 begin print 'Hey, system database connections cannot be killed!!!'; return end select @qry=coalesce(@qry,',' )+'kill '+convert(varchar, spid)+ '; ' from master..sysprocesses where dbid=db_id(@databasename); if len(@qry) > 0
begin
  print @qry;
  exec(@qry);
end

Hope you all find this as useful as I have. Cheers!

Build a SP2013 Content Enrichment Service

Hi, yes it has been a while. Its been a brutal year.  I now have 4 girls, two recent twins. Probably doubled the gray hairs on my head by now.  Had to move to a bigger home.  Had to get a minivan to accommodate the bigger family. After moving into the house, I have been fixing things for 3 months now … an still not done.  There’s much more as well. Nonetheless, here I am … now to CEWS (Content Enrichment Web Service).

So there are many articles out there, between several I was able to get something working. Lets start with the basics:

  • Content Enrichment is a process by which you can add/modify/enhance metadata to the search index directly.
  • Content Enrichment does not add metadata to the actual document or list item.
  • Content Enrichment only works for on-premise farms.  Thus, not Office 365.
  • Content Enrichment is deployed as a web service.
  • Content Enrichment can be great if done properly … poor code will slow down search very significantly.

The basic steps for creating and deploying a CEWS are:

  1. Using Visual Studio 2012 or 2013, create a WCF Service Application project.
  2. Create your Content Enrichment Service
    1. Implement the IContentProcessingEnrichmentService interface from the Microsoft.Office.Server.Search.ContentProcessingEnrichment assembly which you must reference (located at c:\Program Files\Microsoft Office Servers\15.0\Search\Applications\External\microsoft.office.server.search.contentprocessingenrichment.dll by default; unless you installed SharePoint somewhere else).
  3. Register your Content Enrichment Service with Powershell
    1. http://msdn.microsoft.com/en-us/library/office/jj163983%28v=office.15%29.aspx
  4. Test and Validate
  5. Once satisfied, package your solution
  6. Deploy your solution to Azure or IIS (or elsewhere as you need).

Here a couple other resources for more information:

  • How to: Use the Content Enrichment web service callout for SharePoint Server
    http://msdn.microsoft.com/en-us/library/office/jj163982%28v=office.15%29.aspx

  • Custom content processing with the Content Enrichment web service callout
    http://msdn.microsoft.com/en-us/library/office/jj163968%28v=office.15%29.aspx