Drop a trigger after a specific date

Download PDF

 

The question of how to delete a trigger after a specific date came up.  Here is what could be done.

If you want to trigger to disappear after a specific date or time, you can just drop the trigger from inside the trigger itself. Something like this.

CREATE TRIGGER Table1_Updated
ON Table1
FOR INSERT, UPDATE
AS BEGIN
    -- do something

    -- drop the trigger after a given date
    IF (GETDATE() > '2016/05/10')
    BEGIN
        DROP TRIGGER Table1_Updated;
    END
END

If instead of dropping the trigger you just want to disable it, you could use DISABLE TRIGGER instead of DROP TRIGGER.

-Steve Stedman

Posted in TSQL Tagged with: , , ,

How Many VLFs is Too Many?

Download PDF

Here is an error that popped up in the SQL Server error log today:

5/6/2016 10:10:10 AM spid22s Database [DatabaseName] has more than 10000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Virtual Log Files (VLFs) are part of the SQL Server log file. When space is allocated in the log due to growth, that new chunk of log is broken up into Virtual Log Files

After looking further into this server, the VLF count turned out to be around 163,000. That is certainly the highest VLF that I have ever seen.

My personal threshold for too many VLFs is usually around 200 with my preference to be less than 100 VLF files. Others may have their preferences, they will all be far less than 10,000, and certainly less than the 163,000 that I had the opportunity to see today.

Read more ›

Posted in Performance Tuning Tagged with:

Updating SQL Server Statistics

Download PDF

Updating SQL Server statistics may not be as obvious as it may sound.

IUpdating SQL Server Statisticsmagine this scenario. I invite you over for dinner and a game of Scrabble. After dinner, I start to do the dishes, while you set up the game. Rather than just doing the dishes that are dirty from our dinner, I decide to take all the dishes from the shelves, and drawers in my kitchen. I create a gigantic pile of all of the dishes, both clean and dirty on the kitchen counter. You get the game setup, and we start playing the game. Between turns, I jump back into the kitchen, and wash a few dishes, then on my turn, I jump back into the game and play my turn, then back to the kitchen again. During the game you are constantly waiting for me to jump back into the game to make my move. You could see how long this would draw out the game with me washing all the dishes, even the ones that were already clean. 4 hours later, I finally finish the dishes, and shortly thereafter we finish the game. It is unlikely, even if you love to play Scrabble that you would ever return to my house for dinner and a game again. Tomorrow night, when I have dinner with my family, I do the same thing, and every night after dinner I wash all the dishes in the kitchen, even the clean ones. You can see how wasteful this sounds, and perhaps some people would even label me as OCD or something worse in this scenario.

Although this is obviously the wrong way to do things, often times we end up doing the equivalent of this in our SQL Server maintenance tasks.

Read more ›

Posted in Performance Tuning Tagged with: , , , , , , ,

Is enabling xp_cmdshell a security risk?

Download PDF

After listening to a podcast from Carlos L Chacon (twitter) at SQL Data Partners interviewing Sean McCown (twitter) and some lively discussion on xp_cmdshell, I decided it would be a worthy enough discussion to create a blog post. At a minimum it might stir up some opinions.

So before you read the rest of the post, please vote on this survey.

Is enabling xp_cmdshell a security risk?

There are many varying opinions out there, including some really good opinions that applied to SQL Server 2000, but don’t really apply any more.

Read more ›

Posted in SQL Server Tagged with: ,

Database Corruption Webcast – May 3rd

Download PDF

Please join Carlos L Chacon and I for our first Database Corruption webcast on May 3rd. I have teamed up with Carlos and we will be presenting some thoughts on database corruption with an extended Q&A session. Carlos is with SqlDataPartners, and has a weekly webcast with varying SQL Server topics.

You can find more details about the webcast and register on the website here, http://sqldatapartners.com/databasecorruptionwebcast/

One important detail is we will be taking questions and answering them live on the webcast so this will be a great way to engage and ask a follow up if needed. I think you will love this format–way better than soaking up 50 minutes of boring and then calling it quits.

The webcast will cover these major topics.
1) Preparing for database corruption and taking the right steps to ensure you can recover
2) A few lessons learned about our experience with database corruption

This weeks session will cover:

corruptionpodcast1

Corruption Prevention is Different Than Disaster Recovery

The best DR plan still needs to account for corruption.

 

 

Read more ›

Posted in Corruption Tagged with: , , ,

DBCC ShrinkDatabase – I want to shrink my database.

Download PDF

TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it.

This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however shrink database does shrink the log file.

Not shrinking your database is one of the more counter intuitive things out there. You might think that a smaller database is a good thing, however there are some negative side effect if you shrink your database regularly, or have the autoshrink option enabled. Side effects of shrinking your database include:

  • Excessive I/O due to the shrink.
  • Index fragmentation (most likely all of your indexes).
  • Excessive I/O to defragment your indexes.
  • After the shrink is complete, inserting or updating rows that require more space in your database will be slowed due to the time involved with growing your data file.

Read more ›

Posted in DBCC Commands Tagged with: , , , , , , ,

GE Centricity EMR Performance Tuning.

Download PDF

I have been doing work with multiple clients using the GE Centricity EMR product which makes extensive use of Microsoft SQL Server. The performance issues that you run into with Centricity are very similar to what I see working with other clients on a daily basis. Centricity is a solid product, when you run into performance issues, they can often times be addressed by performance tuning the SQL Server.

GE Centricity Performance Tuning

Here are 5 of my recommendations for IT administrators responsible for the GE Centricity SQL Server.

  1. Stay on top of SQL Server updates, specifically the service packs and security patches. Some of these can have a significant stability or performance impact on your system. This doesn’t mean jumping to the latest version of SQL Server that may not be supported by GE, it just means keeping up on the applying the bug fixes that Microsoft supplies in the service packs, hot fixes and security patches.
  2. Understand the speed of your storage. For all drives that hold data, log files, or backups, have a good understanding of their speed. Without knowing what drives are fast or slow, it’s hard to make the right storage decisions. Get a good understanding of your I/O bottlenecks.
  3. Reboot your SQL Server less often. There is a common belief that when you reboot a server, after the reboot things will run faster, better, and more stable. The opposite is true with SQL Server. When SQL Server runs, it brings a great deal of data into memory from disk, every time you restart the SQL Server process, or reboot the server, all that data is thrown out of memory and has to be reloaded. Sometimes a reboot will impact the overall performance of a SQL Server for the next 24 to 36 hours. Reboot when you need to, but never reboot a SQL Server as a regularly scheduled job.
  4. Take a look at Database Health Monitor, this is a free tool that I have spent the last 5 years building to help track down and repair performance problems. Several of the features in Database Health Monitor have come from tracking down specific performance issues with Centricity. Its free, why not use it.  http://DatabaseHealth.com/download
  5. Sign up for a database health assessment, where Stedman Solutions, LLC can help track down and adjust the SQL Server database to improve performance. http://stedmansolutions.com/centricity

Related Links

 

Posted in Performance Tuning Tagged with: , , , , ,

RAID10 Saved The Day

Download PDF

Reliable DBA Services is one of the things I talk about on my business website at http://StedmanSolutions.com, but that can mean many things to different people. To me this has many meanings. Today the reliability of having RAID10 on my home office computer saved the day.

 

Drive Failure Last Year

A year ago not long after starting working at Stedman Solutions, LLC full time, I had a drive failure, on the boot drive on my home office computer. I lost my entire C: drive, fortunately I had things backed up but it did cost me about 2 days of work while I was replacing drives, reinstalling the OS, reinstalling all my applications, and restoring things from backup. At that point I decided NEVER AGAIN will I allow that to happen.

Sure I had my laptop, but I didn’t have everything I need to do my work on the laptop.

After that I ended up building out another home office computer with the goal of having everything overly redundant. My c: boot drive is now RAID10 with 4 SSD’s, and my data drive is made up of 3 drives using the Two-way mirror feature of Windows Storage spaces.

Drive Failure Today

Today one of the SSD drives in my RAID10 c: drive configuration failed. No big deal, just a few minutes of working time lost. First after a reboot, I was able to get the failed drive to rebuild, that will hold me for a few days. Then I spent 10 minutes to go to Amazon.com and order a replacement drive, and a second one to have as a spare. Total elapsed time today 10 minutes. In 2 days when the new drives arrive, it will take me another 10 minutes to shut down my computer, replace the failed drive, start up the computer and then start the rebuild process.

Savings

Totals savings by having the RAID10 boot drive, about 16 billable hours, and headaches.  Total cost, for the RAID controller and SSDs about $600 plus replacements another $190. Peace of mind having RAID10 priceless.

 

All this and I didn’t even mention the performance benefits with RAID10.

Posted in Uncategorized Tagged with: , , , , , ,

SQL Server 2005 End of Life – End of Support Today

Download PDF

Today marks the end of life of SQL Server 2005, originally release in November of 2005, this product has finally come to its end of life point.

sqlServer2005

One of my favorite features that was introduced in SQL Server 2005 was Common Table Expressions. Another big item that was added to SQL Server 2005 was the SQL Server Management Studio.

Its hard to believe its been 11 years since SQL Server SQL Server 2005 released.

So for anyone still using SQL Server 2005, you may want to think about upgrading to a newer version, there are many new features, bug fixes and some major enhancements to the product in the last 11 years. If you need help with this, Stedman Solutions, LLC can help with the upgrade.

 

SQL Server 2005, Rest In Peace.

Posted in SQL Server Tagged with: , , ,

SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the SteveStedman.com blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA

Archives