SQL Server Backup Files Cleanup

I learned something interesting today. Something so simple yet crucial. Alright, let me jump straight on to it.

After going live with GP2015R2, as part of SQL maintenance tasks, I created daily backup schedule in SQL Server. Client’s backup retention policy is 3 prior days. So I created a “Maintenance Cleanup Task” to remove all backup files that are older than 3 days.

All good. Only that, it didn’t work. And we realised that only yesterday. Bummer. Thankfully, it was a new server so we had plenty of space. And size of DBs are quite small. So I could see backup files dating yesterday. Which is nice.

I had to figure out what’s going wrong all this time. It’s a fairly simple setup. Nothing complex. Something must be wrong. I opened the maintenance task definition window. Shown below:

Screen Shot 2016-02-23 at 8.55.04 AM.png

Looks perfectly alright to me. Isn’t it? But nope. Something makes this task fail every night.

When I looked at this post, Maintenance Cleanup Task (Maintenance Plan), on Microsoft MSDN site, I could not get much help from. It was good, detailed, but not good enough to help me why my task fails every night.

I then did something that I thought is weird, but turned out to be the solution.

I changed the File extension value from .bak to just bak, as shown below:

Screen Shot 2016-02-23 at 8.55.04 AM.png

Saved the definition and ran the task manually to check if it worked.

Voila… It worked…!!!

Those who are reading this and thinking about why I am blogging something as simple as this (as if I found the god particle), well in all honesty, I had absolutely no idea about this till now. Just thought I would share this, as I always do. So please be nice on me.


Delete a Company in Microsoft Dynamics GP – Compatible With GP 2013

We have a SQL script named ClearCompanies.sql, which is available on Customer Source or Partner Source. This script removes all references to those companies that are not available in SQL Server, but pretty much exist in GP records.

It’s an all important script for all implementers, developers and consultants. Now this script has been updated to cater for also GP 2013. I had not used this script for a long time, so never realised it till today. This is particularly important as GP 2013 now support multi-tenant architecture (multiple GP System DB on same SQL instance).

You can download this script from here (provided you have a Customer Source / Partner Source account): ClearCompanies.sql.


SQL Server Uninstall: Removal Architecture Mismatch Error

When I was trying to uninstall SQL Server 2008 from my machine, I received the following error message:



At a glance, this error may seem to be something critical, but it is not. It’s something quite silly to be honest. When you install SQL Server 2008 on a 64 bit machine, it installs SQL Server for both x86 and x64 compatibility, leaving two separate items under Control Panel -> Programs and Features, as shown below:



If you try to uninstall by clicking on first one (above 64-bit one), then you will get this rule mismatch error. You must select the second one (64-bit one) to successfully uninstall.

When I selected the appropriate one, my uninstall validations passed without hassles.



Happy troubleshooting…



There is an amazingly simpler yet highly informative post up on TECHNET portal, which proved very handy this morning.

My systems admin reported to me that our SharePoint server ran out of storage for some reason unknown to him, but he could identify that it was SQL Server program files folder which consumed 80% of storage.

I immediately knew what could be the reason. It’s those crazy log files problem. To truncate and clear these overeating log files, I had to use this command: DBCC SHRINKFILE.


Once I cleared out all unnecessary file space from these log files, I had to setup the File Growth and Maximum File Size (shown in the screenshot beside):

Never for a DB log file, we should setup the file growth as “In Percent”. This would prove disastrous over a period of time. Instead, always set it up to “In Megabytes” and enter a least sensible value; in my case I had set it up to 5MB.

For a DB Log file, 2GB should be reasonable amount of file space to hold the logs. To learn more about Log files, read this post on TECHNET: The Transaction Log (SQL Server).

We must understand one thing, that shrinking the DB log has got it’s own impact.


Seven Sins Against T-SQL Performance – Grant Fritchey on Simple Talk

UPDATE: this was drafted long ago, but couldn’t really got to post it till now. You might find this post a bit old, BUT it’s quite relevant even now and extremely informative post.

I came across this gem of an article which lists out 7 things that affect T-SQL performance.

I do not want to take anything out from that article and post it here, as you would realize by reading the original post, you would learn some very important concepts.

Read it here: The Seven Sins Against T-SQL Performance.


Implicit Conversions Causing Deadlocks in SQL Server

Quite an interesting but crucial post up there on Sans SQL blog.

The post explains that Implicit conversions in SQL Server could actually trigger a deadlock. And there are definitions for each important term (such as deadlock, implicit conversion, etc.) with an example.

I believe this post would be useful for those who work extensively on SQL Server.


SQL Server 2012 Performance Dashboards – Some Real-Time Snapshots

Following are some real time SQL Server 2012 Performance Dashboards, taken from a real time SQL Server 2012 environment:







and much more…

It’s a great set of reports for every SQL Server DBA to understand his/her SQL Server environment.