SQLServer

As a DBA, you probably have your toolbox full of scripts, procedures and functions that you use for the day-to-day administration of your instances. I’m no exception and my hard drive is full of scripts that I tend to accumulate and never throw away, even if I know I will never need (or find?) them again. However, my preferred way to organize and maintain my administration scripts is a database called “TOOLS”, which contains all the scripts I regularly use. [Read More]

Recently I had to assess and tune quite a lot of SQL Server instances and one the things that are often overlooked is the location of the system databases. I often see instance where the system databases are located in the system drives under the SQL Server default installation path, which is bad for many reasons, especially for tempdb. I had to move the system databases so many times that I ended up coding a script to automate the process. [Read More]

During the last few days I’ve been struggling to work around something that seems to be a bug in SQL Server 2008 R2 Data Collector in a clustered environment. It’s been quite a struggle, so I decided to post my findings and my resolution, hoping I didn’t contend in vain. SYMPTOMS: After setting up a Utility Control Point, I started to enroll my instances to the UCP and everything was looking fine. [Read More]

If you are one among the many that downloaded my consistency check stored procedure called “dba_RunCHECKDB”, you may have noticed a “small” glitch… it doesn’t work on SQL Server 2012! This is due to the resultset definition of DBCC CHECKDB, which has changed again in SQL Server 2012. Trying to pipe the results of that command in the table definition for SQL Server 2008 produces a column mismatch and it obviously fails. [Read More]

Back in 2011 I showed a method to discover the resultset definition of DBCC undocumented commands. At the time, SQL Server 2012 had not been released yet and nothing suggested that the linked server trick could stop working on the new major version. Surprisingly enough it did. If you try to run the same code showed in that old post on a 2012 instance, you will get a quite explicit error message: [Read More]

Yesterday I posted a method to convert trace files from SQL Server 2012 to SQL Server 2008R2 using a trace table. As already mentioned in that post, having to load the whole file into a trace table has many shortcomings: The trace file can be huge and loading it into a trace table could take forever The trace data will consume even more space when loaded into a SQL Server table The table has to be written back to disk in order to obtain the converted file You need to have Profiler 2008 in order to write a trace in the " [Read More]

Recently I started using RML utilities quite a lot. ReadTrace and Ostress are awesome tools for benchmarking and baselining and many of the features found there have not been fully implemented in SQLServer 2012, though Distributed Replay was a nice addition. However, as you may have noticed, ReadTrace is just unable to read trace files from SQLServer 2012, so you may get stuck with a trace file you wont’ abe able to process. [Read More]

First of all, let me say it: I don’t think this should ever be done on the database side. Formatting dates is a task that belongs to the application side and procedural languages are already featured with lots of functions to deal with dates and regional formats. However, since the question keeps coming up on the forums at SQLServerCentral, I decided to code a simple scalar UDF to format dates. [Read More]