By SQLRx Admin | Miscellaneous , SQL Administration , SQL Server , Upgrade
If you performed an in place upgrade of your SQL Server, your master database may be at a lower compatibility level than all of the other system databases. This was done by conscious decision from Microsoft in an attempt to make for a smooth upgrade and ensure that existing applications can still function post upgrade. […]
Continue readingBy SQLRx Admin | Helpful Scripts , SQL Administration , SQL Server , Tip of the Month
Database backups are a critical part of SQL Server disaster planning. It’s also critical to know
that your backups are viable.
By SQLRx Admin | Performance Tuning , SQL Administration , SQL Maintenance , SQL Server , Tip of the Month
Performance Monitor: Monitor the performance counters SQLServer:Buffer Manager (Page reads/sec) and SQLServer:Buffer Manager (Page writes/sec) to detect poor performance. These counters measure physical IOs and not logical IOs. Heavy activity can indicate lack of database memory, a poor table implementation or can show that applications are not accessing a database correctly. Monitoring these counters will […]
Continue readingBy SQLRx Admin | Helpful Scripts , SQL Administration , Tip of the Month
DBA’s need to know when it is safe to stop services or to do other work. It is preferable to do such things when no jobs are running to prevent job failures and data integrity issues. Check out the query below to help. SQL Server Administration: Find the next time that any enabled job will run. Run the […]
Continue readingBy SQLRx Admin | Helpful Scripts , SQL Administration , SQL Server , Tip of the Month
If you are a DBA who needs to know how much space tables take in your databases to monitor growth trends and plan for maintenance, we have the query for you.
Continue readingBy SQLRx Admin | Performance Tuning , SQL Administration , SQL Server , Tip of the Month
SQL Server Administration: SQL Server keeps track of indexes that it thinks it needs in order to optimize queries. But there are several “missing index” dynamic management views that give valuable information. Find missing indexes with this query. Always test the suggested indexes, as they do not always help, and are only suggestions. SELECT mig.index_group_handle, […]
Continue readingBy SQLRx Admin | Helpful Scripts , Miscellaneous , SQL Server , Tip of the Month
SQL Server T-SQL: Working with time values in queries has always presented challenges. Especially when you need only part of a datetime value. Either truncating or rounding time has usually been done by converting a datetime value to a string and then using the parts that are needed. The functions below can be used to […]
Continue readingBy SQLRx Admin | Helpful Scripts , SQL Administration , SQL Server , Tip of the Month
SQL Server Administration: Monitor disk space free with a simple routine. Make sure that you have room on your drives to hold the work being performed. If you run out of disk space, your SQL Server will stop accepting requests. In the code below, the @Threshold variable is designed to hold a value in MB […]
Continue readingBy SQLRx Admin | Helpful Scripts , SQL Administration , SQL Server , Tip of the Month
SQL Server Administration: List all principal and object permissions with the following query. Use this to periodically check that permissions have been properly set for access to objects in your databases. SELECT USER_NAME(p.grantee_principal_id) AS principal_name, dp.type_desc AS principal_type_desc, p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc AS permission_state_desc FROM sys.database_permissions p INNER JOIN sys.database_principals dp […]
Continue reading