Category Archives: SSMS

SQL Server Backup Files Cleanup

Posted on by

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.

VAIDY

Quick SQL Tip: Keyboard Shortcut for Database Selection in SQL Server 2012 SSMS

Posted on by

Till SQL Server 2008 R2, if you want to change the database on which you would like to execute a SQL query in SQL Server Management Studio (SSMS), the keyboard shortcut to do so was CTRL + U from the query window.

Now from SQL Server 2012, it is CTRL + ALT + J.

So those who tried this using the old shortcut, keep in mind that the new shortcut.

VAIDY

Formatting SQL Procedures

Posted on by

Have you ever opened a standard GP stored procedure?

I do it at least on a weekly basis and have always found it impossible to read as it is. So I end up aligning the procedure first and then read it to understand the logic.

Not anymore. David has shared information on some portals which does this in seconds and give us an aligned code.

Standing out from his list is Poor SQL, from what I learned from my usage.

And there is a plugin for SSMS which does this from within SSMS. This tool is called SSMS Tools Pack.

Happy aligning.

VAIDY

SQL Server Management Studio (SSMS) – How to switch off Auto Recover Option?

Posted on by

In spite of having more critical deliverables for today, I still wanted to post this article, because of it’s importance.

This particular issue is no less than an acute pain. And I mean it.

In SQL Server Management Studio, from versions 2005 till 2008, there is an option called “Auto Recover” which triggers a process that save whatever we are writing in the Query Editor… every 5 to 10 mins. And that’s real pain for us, especially if we are working on remote or thru’ internet.

Sometimes, the system gets hung for so long that you would sit in front and wait till it “recovers” from the “recovery” itself.
BUT, there IS a solution which is unsupported by Microsoft SQL Server team, but harmless and effective. There is a registry entry called “AutoRecover Enabled“, which by default is set to 1. We have to change it to 0. This will not trigger that painful process anymore.

Do a find in Registry Editor for the above option, and you would be able to see that.

References for this solution:

1. http://connect.microsoft.com/SQLServer/feedback/details/125840/how-to-turn-off-auto-recovery-in-sql-server-management-studio-2005
2. http://blog-mstechnology.blogspot.com/2009/06/turn-off-saving-auto-recovery.html

We are thru’… Enjoy a Hang-Free SQL Session…!!!

VAIDY