Sunday, February 19, 2017

Row Count of all tables - returned in a single dataset

When you need the row count from a table, the most common way is to use count(*). But what if you need the row count for all the tables in your database?  You could use the sp_MSforeachtable stored procedure:

    EXEC sys.sp_MSforeachtable 'select ''?'',count(*) from ?;';

Nice, but the return format is a bit difficult to manage, since it effectively runs the query for as many times as you have tables in your database.  The output might look something like this:

          -------------------- -----------
          [dbo].[Product_info] 504

          --------------------- -----------
          [dbo].[database_info] 24

Sure, you could package this to run an insert into a temp table, but still, it's a bit awkward. What we want is a single, elegant query. One that returns a single dataset. Fortunately, we can get the information from the DMV sys.dm_db_partition_stats. One of the columns returned from this DMV is row_count. The one caveat is that row_count is approximate.

Our query will look something like this:

  select p.row_count, tableName = object_name(object_id), * 
   from sys.dm_db_partition_stats as p
inner join sys.sysobjects o 
      on p.object_id =  and o.xtype = N'U'

where p.index_id IN( 0,1) and p.row_count > 0
   order by p.row_count desc;

A table is either a heap, or it has a clustered index. Heaps have an index_id of 0, while clustered indexes have an index_id of 1. They are mutually exclusive.

You can read more about sys.dm_db_partition_stats on MSDN here:

Thursday, February 16, 2017

A tool to Optimize SQL Server Indexes

SQL Server Management Studio has a nifty tool that allows you to setup daily maintenance plans that run a CHECKDB and will also rebuild your indexes. Great. But, those index rebuilds called by the maintenance tools have a drawback. A big, log growing, disk hogging drawback.

It wants to do full rebuilds of all indexes. And these processes are logged. Low fragmentation? No problem - full rebuild. Large table? No problem - full rebuild.  Low on disk space on your server, and you really do not want your log file to grow. Problem!


Years ago, in my days as a DBA, I created a process to review all the indexes for fragmentation, and then do targeted updates. But, that was years ago when we all carried flip-phones. Fortunately, Ola Hallengren has released a complete set of index optimization tools that work with current versions of SQL Server.

You can find them here:

And, if you need some additional suggestions on the best way to adjust all the optional parameters, start with the post at Brent Ozar:  Tweaking the Defaults for Ola Hallengren’s Maintenance Scripts.

And did I consider digging out my old index optimization scripts? After looking over the work Ola Hallengren did - not a chance. 

Logging the spaced used by Databases in SQL Server

I recently had a need to setup  a process to periodically capture the size of the database files, along with the used (or unused) space. And not just the data files, the log files as well. Years ago, when I was doing more DBA work, I had a script to do just that.  Unfortunately, it was for a much older version of SQL Server, and it no longer worked.

No problem, just find the current DMV's that provide the data.  It turns out, it's not as straight forward as I would have liked. Sure there a many procedures, DMV's and DBCC commands that provide some of the data, but not all of the data. And I could have just used the profiler, and then pulled up the requisite form in SSMS, but that seems less informative than reading what the community had to offer.

sp_spaceused, sp_databases, DBCC SQLPERF(logspace) Oh my!

Looking over the posts, it seems there were endless ways to get some of the data. But ideally, I wanted a single SELECT that returned just the data I wanted, with out all of the wild SQL CTE, inline-query or other such code.

After reviewing the posts, many, perhaps all of the procedures and commands I found provided valuable information. Just not what I wanted. We have:

       sp_spaceused, but it returns data in two data sets. Not ideal.  Next we have...

       sp_databases, but it just returns a list of databases and the size. Not ideal.  Or use...

       DBCC showfilestats,   Ok, but still incomplete. And, since it is not listed on the main DBCC                                                     page at MSDN, its most likely deprecated.

And speaking of DBCC, there are other commands to consider. But what I was really hoping to find was a simple way to use the sys.database_files DMV. It is very close, but not quite complete.

            select * from sys.database_files;

In time, I found a post by Glenn Berry of SQLSkills. His query used a simple SELECT against the sys.database_files DMV, but with just a few extra additions. He used FILEPROPERTY.

To fit my needs, all I needed was a few extra bits added to the query provided by Glenn Berry. I now had the ideal database size monitoring statement. A query that told me all I needed to know to monitor the database sizes over time.

   @@SERVERNAME as ServerName 
 , db_Name()  as DatabaseName
 , cast(CURRENT_TIMESTAMP as date) as RunDate
 , cast(CURRENT_TIMESTAMP as time) as Runtime
 , name AS LogicalFileName 
 , physical_name AS PhysicalFileName
 , CAST(size/128 AS DECIMAL(10,2)) AS TotalSizeMB
 , CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') 
                 AS int)/128.0 AS decimal(10,2))  AS AvailSpaceMB
 , case when (size/128) != 0 then
        CAST(100 - (((size/128.0 
                              - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int
                                      )/128.0 )/(size/128))*100)  AS DECIMAL(10,2))  
  else 0 end  as PercentUsed
 , type, type_desc,state, state_desc,size, max_size ,file_id,  file_guid, data_space_id
 , growth, is_media_read_only,  is_read_only, is_sparse, is_percent_growth
        , is_name_reserved, create_lsn, drop_lsn, read_only_lsn, read_write_lsn
        , differential_base_lsn, differential_base_guid, differential_base_time
 , redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid
        , backup_lsn
   FROM sys.database_files;        

There was just one problem. The query was database specific. And I needed the information for all databases.

sp_MSforeachdb to the rescue

All that was required was to repackage the query and run it using sp_MSforeachdb.  And you'll notice I push all of this into a table using a procedure. 

if exists(select * from sys.objects 
         where object_id = OBJECT_ID('dbo.usp_database_file_info') and type = N'P')
 DROP PROCEDURE dbo.usp_database_file_info;



CREATE PROCEDURE dbo.usp_database_file_info
 declare @myCommand Nvarchar(4000);
 select @myCommand  =  
    ' USE ? ' 
  + 'insert into test.dbo.database_file_info  ( ServerName, DatabaseName, RunDate, Runtime, LogicalFileName '
  + ', PhysicalFileName, TotalSizeMB, AvailSpaceMB, PercentUsed, type, type_desc, state, state_desc, size, max_size '
  + ', file_id, file_guid, data_space_id, growth, is_media_read_only, is_read_only, is_sparse, is_percent_growth' 
  + ', is_name_reserved, create_lsn, drop_lsn, read_only_lsn, read_write_lsn, differential_base_lsn, differential_base_guid'
  + ', differential_base_time, redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid, backup_lsn ) '
  + ' SELECT @@SERVERNAME as ServerName' 
  + ', db_Name()  as DatabaseName' 
  + ', cast(CURRENT_TIMESTAMP as date) as RunDate'
  + ', cast(CURRENT_TIMESTAMP as time) as Runtime'
  + ',  name AS LogicalFileName' 
  + ', physical_name AS PhysicalFileName'
  + ', CAST(size/128 AS DECIMAL(10,2)) AS TotalSizeMB'
  + ', CAST(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS decimal(10,2))  AS AvailSpaceMB'
  + ', Case when (size/128) != 0 then'
  + ' CAST(100 - (((size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 )/(size/128))*100)  AS DECIMAL(10,2))'  
  + '   else 0 end  as PercentUsed'
  + ', type, type_desc,state, state_desc,size, max_size ,file_id,  file_guid, data_space_id'
  + ', growth, is_media_read_only,  is_read_only, is_sparse, is_percent_growth, is_name_reserved'
  + ', create_lsn, drop_lsn, read_only_lsn, read_write_lsn, differential_base_lsn, differential_base_guid, differential_base_time'
  + ', redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid, backup_lsn'
  + ' FROM sys.database_files;';

   --  EXEC (@myCommand);
    EXEC sp_MSforeachdb  @myCommand ;

All we need is the table create statement:

if exists(  select * from sys.objects where object_id = OBJECT_ID('dbo.database_file_info') )
 DROP TABLE dbo.database_file_info;



CREATE TABLE [dbo].[database_file_info](
 tblID bigint identity(1,1) not null,
 [ServerName] [nvarchar](128) NULL,
 [DatabaseName] [nvarchar](128) NULL,
 [RunDate] [date] NULL,
 [Runtime] [time](7) NULL,
 [LogicalFileName] [sysname] NOT NULL,
 [PhysicalFileName] [nvarchar](260) NOT NULL,
 [TotalSizeMB] [decimal](10, 2) NULL,
 [AvailSpaceMB] [decimal](10, 2) NULL,
 [PercentUsed] [decimal](10, 2) NULL,
 [type] [tinyint] NOT NULL,
 [type_desc] [nvarchar](60) NULL,
 [state] [tinyint] NULL,
 [state_desc] [nvarchar](60) NULL,
 [size] [int] NOT NULL,
 [max_size] [int] NOT NULL,
 [file_id] [int] NOT NULL,
 [file_guid] [uniqueidentifier] NULL,
 [data_space_id] [int] NOT NULL,
 [growth] [int] NOT NULL,
 [is_media_read_only] [bit] NOT NULL,
 [is_read_only] [bit] NOT NULL,
 [is_sparse] [bit] NOT NULL,
 [is_percent_growth] [bit] NOT NULL,
 [is_name_reserved] [bit] NOT NULL,
 [create_lsn] [numeric](25, 0) NULL,
 [drop_lsn] [numeric](25, 0) NULL,
 [read_only_lsn] [numeric](25, 0) NULL,
 [read_write_lsn] [numeric](25, 0) NULL,
 [differential_base_lsn] [numeric](25, 0) NULL,
 [differential_base_guid] [uniqueidentifier] NULL,
 [differential_base_time] [datetime] NULL,
 [redo_start_lsn] [numeric](25, 0) NULL,
 [redo_start_fork_guid] [uniqueidentifier] NULL,
 [redo_target_lsn] [numeric](25, 0) NULL,
 [redo_target_fork_guid] [uniqueidentifier] NULL,
 [backup_lsn] [numeric](25, 0) NULL

ALTER TABLE [dbo].[database_file_info] ADD  CONSTRAINT 
   [PK_database_file_info] PRIMARY KEY CLUSTERED ( [tblID] ASC )

You can find Glenn Berry's original posted reply on MSDN at:

Cycle the SQL Server and Agent Logs Periodically

Have you ever tried to open the log file for SQL Server, and then wait and wait. Realizing that the log is massive since your server has not been restarted for weeks or months?  We'll congratulation! It's great that you have not had to take the server down for the last x weeks or months. But,....

 ......about that massive log file.  Well, more good news, all you need to do is create a single SQL Agent job with a few steps.

  1. EXEC sys.sp_cycle_errorlog;
  2. EXEC dbo.sp_cycle_agent_errorlog;

That's it.  Typically, a week is a good duration, but every setup is different.

Sunday, February 5, 2017

Replication Setup -FIX- Unable to list directory contents

You're trying to setup SQL Server Replication on a server, and it fails. Looking thru the error message you find this:

        An exception occurred while executing a Transact-SQL statement or batch.

        Destination path not valid. Unable to list directory contents. Specify a valid

            destination path.
            Changed database context to 'master'. (Microsoft SQL Server, Error: 14430)

Unable to list directory contents. Specify a valid destination path.

We were setting up an existing SQL Server 2014 Standard edition to run as a combined Distributor / Publisher, and ran into this problem. Looking over the blogs, the one thing that became apparent was that there was quite a bit of confusion for the problem. Below is how we worked out the steps and configuration issues that ultimately led to success.

Unable to list directory contents

Ultimately, our focus was on permissions. And, in general, the consensus was to run the Configure Replication Wizard as SA. So, this means that any file/folder permissions fall to the account running SQL Server.  For our example, that would be:  MYDOMAIN\sqlserviceacct

We'll admit that after several failed setup attempts, some of the following may be excessive. But they finally worked.


  1. SQL Server service running a domain account:  such as  MYDOMAIN\sqlserviceacct
  2. Replication Snapshot Share
    1. Verify that the account "MYDOMAIN\sqlserviceacct" can both
      1. read the folder
      2. read the UNC share
    2. In addition to the other accounts that will need access to support replication!
  3. Folder(s) to hold the Distribution Database. For example, your distribution database will be placed in the folder E:\SQLsvr\Data\Distribution
    1. Verify that the account "MYDOMAIN\sqlserviceacct" can READ the top level folder
    2. Verify that the account "MYDOMAIN\sqlserviceacct" can READ the next level folder.
    3. Verify that the account "MYDOMAIN\sqlserviceacct" has control over the FINAL folder. In this case E:\SQLsvr\Distribution. 
  4. EXEC sp_configure 'xp_cmdshell', 1;
    1. with a newly setup SQL Server, you may not have disabled xp_cmdshell. But, this server was already in production, and since it did not need xp_cmdshell, it was disabled (a best practice).
    2. Before running the Wizard, we temporarily enabled xp_cmdshell. Once the install was completed, we promptly disabled xp_cmdshell.
  5. Consider running:  sp_removedbreplication.
    1. One thread suggested that you run sp_removedbreplication. It was listed under the premise that you had an earlier replication installation. We did not, but we still ran the procedure to clear out any partial replication objects that may have been left from our earlier failed install. 

sp_configure 'xp_cmdshell', 1;

We are sure you know how to do this, but just incase, see: xp_cmdshell Server Configuration Option.
Basically (from MSDN):

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
-- To update the currently configured value for advanced options.  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
-- To update the currently configured value for this feature.  

Running the Wizard - Configure Replication

  1. Using SSMS, connect to SQL Server as SA.
    Looking over many of the postings, it was generally agreed to run the wizard as SA. 
  2. SnapShot folder
    1. UNC required, such as \\myserver\myreplicationshare
    2. Verify that your UNC is exactly the one you setup.
  3. Database folder
    1. Verify that your folder is exactly the one you plan to use, with the permissions set as listed above.
  4. Script - yes you want a copy of the script.
That should lead to success.  If not, go back and verify that your SQL Server service account really does have access to the folders. And then make sure you have them spelled correctly.