Memory Grants Pending Counter indicates the total number of processes waiting for a workspace memory grant. The recommended Memory Grants Pending value is zero, meaning no processes are waiting for the memory. Anything above 0 indicates that there are processes...
Read More
Page Life Expectancy Counter
Checking the Page Life Expectancy (PLE) counter in SQL Server is one of the greatest ways to identify memory consumption issues. Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. If...
Read MoreBuffer Cache Hit Ratio
Buffer Cache Hit Ratio is the percentage of SQL server pages requested and retrieved from the buffer cache without reading from disk. Reading data from disk is slower than reading from memory. High Buffer Cache Hit Ratio indicates a large amount...
Read MoreFind SQL Server logins that have blank passwords
Today our data security officer asked me to get list all SQL Server logins that have blank passwords and SQL Server logins that have password exactly same as username. Below script can be used to retrieve those weak SQL Logins.
Blank... Read More
Compressing Backups
SQL backup files can quickly become very large and it might take a lot of space, which can cost quite a lot of money, so SQL Server enables you to compress them. You can set the default backup compression behaviour and...
Read MoreMoving user Database files within the same instance
Assume you have database called SalesCommission _DB in your SQL Server. You want to change the database files location from C: drive to S: drive due to disk capacity issue. You can use the ALTER DATABASE statement to move database files...
Read MoreMonitoring Tools for SQL Server
SQL Server provides a number of tools you can use to carry out performance monitoring and tuning. Each tool is useful in certain scenarios and you will often need to combine several of them to achieve the optimal results.
Restoring Database Files to a Different Location
Assume you have database called Sales_DB in your SQL Server. You planning to migrate this databases to another SQL Server and you want to place the database files (.mdf and .ldf) in different locations to those recorded in the backup...
Read MoreDatabase stuck in “Restoring” state
Earlier today I was experiencing an issue with one of our databases called SALES_DB is in Restoring state. I tried to run below ALTER DATABASE commands on the database to set it in online state but it throws the following error.
[crayon-5b52fe0616871230106111/]
Enabling CLR Integration
The common language runtime (CLR) integration feature in SQL Server is off by default, Below SQL Script will help you to enabled CLR in order to use objects that are implemented using CLR integration. When CLR feature enabled in SQL...
Read More