SQL SERVER – Database File Names and Extentions – Notes from the Field #025

[Notes from Pinal]: People believe Shakespeare’s birth date is on April 26. ” What is there in a name?” – I often hear this statement, which famously quotes from Shakespeare. I think we developer believe in this statement as most of our various are either BAR or FOO. However, this is extremely inefficient and not convenient. There are many reasons we should have meaningful names to our objects, variables and various elements. There are times when incorrect naming convention can just get user in trouble or lead organizations to disaster. My friend Tim shares this humorous and interesting story. Read on …

SQL SERVER - Database File Names and Extentions - Notes from the Field #025 timradney Linchpin People are database coaches and wellness experts for a data driven world. In this 25th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a very simple words of names and extensions.

Database file naming standards are important. With Microsoft SQL Server the default naming standard is not a requirement so administrators can change from the default .mdf, .ldf and .ndf. Recently I encountered a SQL Server instance that was having performance issues. The customer was reporting very slow response times and couldn’t figure out what the issue was.

Reluctantly the administrator reached out to the database team to get a professional opinion. For the database team this was the first they learned of this new server so we quickly performed our standard check. Two things stood out which was high CPU utilization and the executable consuming the most CPU was antivirus as well as high disk IO. We stopped the “on access” scan and performance of the SQL Server improved as well as disk IO dropped very low.

We reached out to our antivirus team and was assured that our standard policies were applied to this server which includes excludes for our standards for database file naming. We continued to research deviations from our standard server build against this instance, since we did not build it and that is when we found the admin/vendor/customer (non dba) chose a different naming standard for the files. The extensions they chose were.001 for data, .002 for logs, .003 for ndf.

Due to not having proper excludes for those files by the antivirus software, the customer created a denial of service attack against their self. The on access scan effectively created such a load that it prevented anything else from running.

This is not the first time I have encountered this type of issue and blogged about this in 2012.

If you decide that for whatever reason you would like to deviate from the default naming standard or your companies naming standard for database files, please get with the people that manage your antivirus software to make sure they create exclusions for your new naming standard.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – GIDS – Two Technical Sessions Today – Resources and Whereabouts
Next Post
SQL SERVER – GIDS – Two Technical Sessions Today – Resources and Whereabouts – Part 2

Related Posts

Leave a Reply