By SQLRx Admin | SQL Administration , SQL Maintenance , SQL Server , Tip of the Month
SQL Server Administration: Backup database to one file or multiple files. Use these commands to back your databases up without using a maintenance plan. Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. Good to use when large backups […]
Continue readingBy SQLRx Admin | SQL Administration , Tip of the Month
SQL Server Administration: (SQL 2008 – SQL 2012) Use the new dynamic management view sys.dm_exec_procedure_stats to view performance statistics about cached stored procedures. This dmv will only show information on the stored procedures that are still in the cache. If a stored procedure is aged out or removed from the cache then performance info will […]
Continue readingBy SQLRx Admin | SQL Server , Tip of the Month
A new function available in SQL Server 2012 is IIF. IIF is very similar to the CASE expression and is in essence a shorthand way to write a CASE function. The IIF function returns one of two values depending on whether the first expression evaluates to TRUE or FALSE. The IIF function can be nested […]
Continue readingBy SQLRx Admin | SQL Administration , SQL Server , Tip of the Month
(SQL 2008 R2 and SQL 2012) Use the new dynamic management view sys.dm_server_services to see information about SQL Server services such as when service last started, running status, service account, etc… For more information, check out Books Online or http://msdn.microsoft.com/en-us/library/hh204542.aspx
Continue readingBy SQLRx Admin | SQL Administration , SQL Server , Tip of the Month
Get a list of CLR’s registered to a database. This is helpful when migrating or troubleshooting. For more information on CLR’s check out Books Online or http://msdn.microsoft.com/en-us/library/ms131089(SQL.100).aspx SELECT o.name AS ObjectName, a.name AS Assembly, o.type_desc AS ObjectType FROM sys.assembly_modules am JOIN sys.objects o ON am.object_id = o.object_id JOIN sys.assemblies a ON a.assembly_id = am.assembly_id […]
Continue readingBy SQLRx Admin | SQL Maintenance , SQL Server , Tip of the Month
Many times when upgrading SQL Server or migrating to new hardware, it is necessary to rename the server when it’s ready to be used as production. However, if any maintenance plans created before the server was renamed are still present after the rename, you may find that trying to delete the old jobs results in […]
Continue readingBy SQLRx Admin | Helpful Scripts , SQL Server , Tip of the Month
Your SQL database uses statistics to choose the most efficient plan for retrieving or updating data, so you want them accurate and up-to-date. Otherwise, it can cause the SQL Server engine to take the wrong path, and taking the wrong path means slower performance. Check out the code below to see when your stats were last […]
Continue readingBy SQLRx Admin | Helpful Scripts , SQL Administration , SQL Maintenance , SQL Server , Tip of the Month
Every DBA has encountered a database that has a mystery statement that will unexpectedly cause either the transaction log or a table to grow out of control and fill the drive. Here is a way to find out who is running what statement that is causing the problem. Find out what statements are causing database […]
Continue readingBy SQLRx Admin | SQL Administration , SQL Maintenance , SQL Server , Tip of the Month
Databases will go into suspect state for a multitude of reasons. If you find your database in SUSPECT state, check all error logs and the Windows Event Log for any related errors, and try to resolve them. This is a disaster situation! Refer to the script below for help. SQL Server Administration: You can try to […]
Continue reading