CREATE PROCEDURE #MyProcedureName
Yes, you can create temporary stored procedures by prefixing the name of the sproc with a #. The temporary stored procedure is sort of a “leave no trace” stored procedure that is only good for your session. If you use double ## you will get a global temporary stored procedure.
For instance:
This week on the SQL Data Partners Podcast Episode #59 we discussed Mirroring, Availability Groups, Replication and Log Shipping. There were 4 of us on… Read More »Episode 59: Mirroring, Availability Groups, Replication, and Log shipping
With over 7500 downloads worldwide to date, I have just released the next installment of the FREE Database Health Monitor application.
Here what one person had to say about it “Excellent – It’s the first tool I open every morning to see what is going on in my production environment! Thanks for the great work!”
Download today:
Released March 20th, 2016.
It has been about 3 months since the last release, and I have added several new features, and many bug fixes.
As a DBA you regularly schedule backups you might have regular full backups, incremental backups and hopefully regular backups. Over time you might realize that your error log is not longer filled with errors, but instead a majority of your error log is filled with backup messages. “Backup Database successfully … “, “Database backed up…”, “Log was backed up”. This makes it a bit difficult to find the actual errors in this flood of backup messages. Your error log is no longer an error log, but it looks more like a backup log.
Trace flags in SQL Server are switches or parameters that can be used to turn on or off different features. If you use trace flag 3226 this will turn off all successful backup messages being written to the error log. There are a couple of ways to use trace flag 3226, one is to use DBCC TRACEON to turn on trace flag 3226 for the currently running instance. Using this option will turn it on until the next time the database instance is restarted.
Read More »Silencing Backup Messages with Trace Flag 3226Database corruption repair with SQL Server is one of those things that you generally don’t see every day, but as a DBA, you are expected to be able to fix it quickly without any data loss. From the risk analysis matrix, it is one of those low frequency high impact scenarios that results in an extremely risky situation. This is one of those situations which could lead to great success, or massive failure depending on your training and skills.
These low frequency, high impact, high risk scenarios are the times that you need to rely on your training, or the training of someone more experienced in these areas.
Being the middle of the summer in the northern hemisphere, and with the Database Corruption Challenge over, I thought I would cover something a bit lighter, and not as hard core as database corruption.
If you have been a DBA for more than a year or two you probably have some story about going on vacation, and getting the call from the office with some database problem. It might have been that someone forgot their password, or something more serious like a corrupt database. Either way, the simple or more complex issues are not why you went on vacation.
Here is a picture of a DBA on vacation in Hawaii. Notice the empty chair… They should be sitting in the sun sipping a tropical drink, but instead they are back in the hotel room with a VPN connection to the office dealing with some emergency (or perceived emergency) SQL Server issue instead of enjoying the beach.
DBA on Vacation (photo by Steve Stedman)
Almost a month ago Brent Ozar did a blog interview with me regarding the Database Corruption Challenge so I thought I would return the gesture and do the same for him. I ended up going to the Bellingham SQL Server Users Group (PASS Chapter) to build the list of questions. The members of the group helped build the questions, and here is what we came up with, including responses from Brent.
Brent is a Microsoft MVP, Microsoft Certified Master of SQL Server, and published author. He lives in Chicago and is the founder of Brent Ozar Unlimited.
Question: For the self-taught or accidental DBA where do you suggest they start to improve their SQL Server Skills?
Brent: DBAs get in the most trouble when they assume the backups are working. They’re probably not. Go set up a development server or VM, and restore last night’s backups onto it. Figure out what it would take to go live on that newly restored server. Rehearse it a few times, get confident in it, and then build a little checklist with the length of time it will take. Show it to your manager as proof that you’re working to keep the company’s data safe.
After that, it’s so tempting to get caught up just poking around in metrics. Go to your manager or your top power user and ask, “What do you want to do, but the database is holding you back?” These are the people who will give you a raise, and these are the reasons they’ll give it to you. Help them help you.
Do you need some help with your SQL Server, but perhaps don’t need another full time DBA? Do you need some help to cover your… Read More »Stedman Solutions: Remote, Skilled DBA Services
As the DBA, you get a call from the IT Team who monitors disk space and they tell you the following: “One of our server… Read More »My SQL Server is Out Of Disk Space
If you owned a convenience store, or other business that was likely to get robbed you would likely have surveillance cameras with the intention of… Read More »SQL Forensics