Browsing the archives for the T-SQL category

SQL Server PBM: On Schedule Failures

I tend to like the automation and convenience of using Policy Based Management (PBM) to check simple things like my backups have been running, there’s enough disk space on my servers, and the consistency of my configurations (min/max server memory, ad hoc workloads, etc.). While it is easy to build and test policies by executing […]


The report server installation is not initialized. (rsReportServerNotActivated)

I recently ported an SSRS instance to a freshly minted virtual machine. As usual I went through the normal SSRS configurations and everything looked happy. As part of the process I restored the reporting services databases to the new SSRS database instance. Apparently I didn’t follow my own instructions Migrate SSRS from SQL Server 2008R2 […]


Migrate SSRS from SQL Server 2008R2 to 2014

I recently had a requirement to migrate a SQL Server instance from 2008R2 to 2014.  The core SQL Server functionality, the engine is always the easy part for me.  I don’t do much with SSRS, so moving the reporting services component was the big unknown. So let me share with you my biggest take away […]


Table Info for Clustered Column Store Indexes

I’ve used a variation of this query for years and it worked pretty well, until clustered column store indexes came along. The general problem is that the column store indexes store the storage in LOB records, although data records are also included.  This resulted in the tables with column store clustered indexes having twice the […]


Identifying Netezza Backupsets

This is more a note to self than anything… Each week we run FULL backups against our databases, and write the backup files to disk.  In the past we kept the most current backup on disk for a week, and deleted it after the backups have been saved to tape. Then we start the cycle […]


Bug in sys.fn_hadr_backup_is_preferred_replica

While testing backups in SQL Server 2014 Availability Groups I hit a bug in a system function that is used to determine which replica is the most appropriate to be backed up. You can set the desired backup pattern by RIGHT clicking on the availability group to bring up the properties:     I’ve decided […]


View to get DB Size

The DBCC SQLPERF(logspace) is a handy routine to check the transaction log sizes.  But it does have some limitations: The log file size is in KB.  All of our databases are in GB, so I end up unconsciously converting the KB output into GB. Saving the data to a table is not convenient. It provides […]


Netezza: Restore a table from an INCREMENTAL Backup

Netezza supports 2 types of backups: FULL INCREMENTAL CUMULATIVE – All changes since the last FULL backup DIFFERENTIAL – All changes since the last FULL or CUMMULATIVE The first step to restore a single table is to see what backups we have.  Note: That NZ calls the FULL backup Increment #1. Determine what incremental backups […]


Filtered Indexes and CONCAT_NULL_YIELDS_NULL

Another lesson learned at the school of hard knocks… I recently deployed a filtered index to a table that had a FLAG column.  I showed without a doubt in the test environment that this filtered index would be used by production queries at a much lower cost than without the index. What I didn’t know […]


Change Default Backup Location on SQL Server

This is just a quick post of a task that seldom needs to happen… Changing the default location of the backup directory. There is no way to change this setting in SSMS… The quickest way is to use an extended stored procedure. [sourcecode language='sql' padlinenumbers='true'] DECLARE @BackupDirectory VARCHAR(100) EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', @value_name='BackupDirectory', […]


LiteSpeed Restore from Remote Share v8.0

In versions of LiteSpeed for SQL Server prior to 8.0 I’ve always been able to restore backups from remote shares by cutting/pasting the remote share path (\\serverName\driveLetter$\backups).  In fact, the remote shares are not always actual shares.  If they were LiteSpeed would pick them up in the “browser”.  Often times they are just unshared paths […]


Color code SSMS status bar

I work with all kinds of SQL Server instances.  All of them are one of three possible types: Development Test Production Many of my scripts have a quick query at the top to get the server name (@@servername).  I use this to double check the server instance I’m on.  More specifically, I use this to […]


Using EVENT NOTIFCATION to ALERT on user query CPU usage

We often have customers running ad hoc queries on the databases that we host.  Most of the time the queries run fine… sometimes they don’t. The first step we took towards managing rogue queries was to deploy Resource Governor (only available in Enterprise or Developer editions) to limit their MAXDOP.  In general, a rule of […]


Return First and Last Day of Previous Month

I had a colleague approach me today with a production issue.  The base challenge was to produce a query that will return the first and last day of the previous month.  When I reviewed the query that he provided (ie., the one that was in the production code) I quickly determined that it was too […]


Size Matters: Keep Model Small

I recently discovered an undesirable behavior of SQL Server on a brand new SQL 2008R2SP2 cluster.  If the cluster failed over once it was fine.  If it was immediately failed back to the original node, it would hang when taking SQL Agent offline. The cluster had two physical nodes (01P and 02P) and three (3) […]


Mounting a TV in Fireplace

About a month ago I completed a home project that involved mounting my 40 inch flat screen TV “in” my existing gas log fireplace.  In order to convince my skeptical wife that the project would look nice I had to have a rollback plan that included restoring the fireplace to it’s original working condition.  That […]


10 Tips to Optimize Data Warehouse Reporting

Unless your marketing data warehouse is stored in a massively parallel appliance such as Netezza, your data most likely resides on a Symmetric Multi-Processing (SMP) computer in a relational database engine, such as Microsoft SQL Server. SQL Server is a highly scalable, versatile database product that can serve the needs of a wide range of […]


How to List Size of Tables

Even though disk space is cheap it continues to the be the first resource to run out on a server.  And once it runs out it’s a constant battle to keep the database size in check. For sure there are things that can be done to keep database growth in control: Archive.  I’ve seen databases […]


Size of Filegroups and Files

In recent posts I looked at queries that show us data at the instance, the database, backups and now we will look at filegroups and the individual physical files. Too many times disk space is not an issue, it is THE issue.  This query will show several key attributes of our database at the file […]

Database Backup Snapshot

  Every Monday morning I always make it a point to run some basic checks on our production database servers.  Yes, there are alerts… but sometimes those alerts get over looked, don’t get delivered, etc.  I like to know implicitly that we are in decent shape… In previous posts I showed how to look at […]