DBA NEWS & SCRIPTS

SQL Server techniques, how-tos and other random ramblings

How to Temporarily Disable Check Constraints and Database Triggers

Sometimes you will need to change / update / restore some data in a table without the hassle of check constraints getting all up in your face. To do this, you can simply disable and re-enable these via the following commands. To disable check constraints on a table by executing: EXEC sp_msforeachtable "ALTER TABLE ?…

How to Find SQL Agent Tasks and Schedules

The below script is a convenient way to check on your SQL Server Agent tasks, frequency in which they run, last successful completion as well as the task run times to help identify whether these tasks might correlate with other performance issues being experienced in the environment. USE msdb Go SELECT dbo.sysjobs.Name AS 'Job Name',…

How to Find Longest Running Queries

The following DMV query returns the creation time, last execution time, physical and logical reads / writes, as well as time elapsed in the execution of a SQL statement (also detailed). Very useful in establishing the heavier TSQL queries within an environment! SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time ,…

How to update DB File Growth Settings for ALL Databases at the same time

SQL Server’s default filegrowth settings grow a database in only 1MB increments. For larger databases this can lead to additional and unnecessary disk IO overheads, as SQL continually needs to first grow the DB file before it can write to the database. Best practice is to set these growth increments to a more appropriate size…

How to Find the Up-Time of a SQL Server Instance

Your DMV stats are only as good as the amount of data they’ve managed to collect since the last SQL Server restart. To find out how long your SQL Server has been running, you can check the time stamp of the spid 1 using the following TSQL; SELECT login_time AS 'Started', DATEDIFF(DAY, login_time, CURRENT_TIMESTAMP) AS…

How to Fix Very Large MSDB Database

The MSDB database houses your maintenance plan information and job histories (amongst a few other things). For the most part this should generally remain well under 1GB in size (normally only a few hundred MB)… but there are certain conditions where the history clean up task can get a bit overwhelmed and start failing, and…

How Healthy is your SQL Server?

Let’s not beat around the bush, SQL is complex. There are few “experts” out there that deal with the intricacies on a day-in, day-out basis and as such, there is a large portion of sub-optimal configurations in businesses today. Whilst this may not be a factor for small business, if you’re running a high-transaction database…

Surround Yourself with Smart People

Tips for Success from Successful People Love him or hate him, Robert Kiyosaki of “Rich Dad, Poor Dad” fame is a successful person. Whilst his investment advice may not be to everyone’s tastes, we’ve always considered his advice to surround yourself with people who are smarter than you to be pivotal for businesses. It’s no…

5 Reasons NOT to Hire a DBA…

Hiring someone to look after your business critical data can be a scary prospect. Maybe you’ve managed to get this far without issues and can’t see the value. Maybe you have concerns over data security. This is not helped by common misconceptions that hold businesses back from feeling the benefits that an external DBA can…

When’s the Last Time you Serviced your SQL Servers?

When was the last time you looked under the bonnet of your SQL Server? We often have this image in our mind of software as a set-and-forget service, we build the system we need at the time and then sit back and let it do its thing. However, unlike a lot of software services, SQL…

Gettin’ Techie SET STATISTICS TIME

Today we’re going to be getting down and dirty with some good, old-fashioned techno-babble. I’m going to be discussing the finer points of database performance to give you a better understanding of what your server is doing, and how it’s doing it. Let me just say upfront that this isn’t about quick fixes. In fact,…

Contact DBA Services

We typically respond within minutes.

Contact Us

Professional SQL Server Database Administration Services, because we believe every business deserves to have a specialist DBA.

QAssure # 16602. GITC #Q-6452

Office Location
2/67 Dennis Rd,
Springwood QLD,
Australia 4127

Postal Address
PO Box 1208,
Fortitude Valley,
QLD Australia 4006

  • g-pluse
  • linkedin
  • facebook
  • twitter