Sql

PostgreSQL 9.4.2, 9.3.7, 9.2.11, 9.1.16 & 9.0.20 Released!

The PostgreSQL Global Development Group has released an update with multiple functionality and security fixes to all supported versions of the PostgreSQL database system, which includes minor versions 9.4.2, 9.3.7, 9.2.11, 9.1.16, and 9.0.20. The update contains a critical fix for a potential data corruption issue in PostgreSQL 9.3 and 9.4; users of those versions should update their servers at the next possible opportunity. Data Corruption Fix For users of PostgreSQL versions 9.

DB Browser for SQLite 3.5.0 released with support for encrypted databases

SQLite 3.5.0 Database Browser recently released, is a visual tool used to create, design and edit database files compatible with SQLite. One of the major improvements in this release, is support for encrypted databases (for MacOS X and Linux only, not Windows yet). This is done using the SQLCipher encryption library. SQLite Database Browser 3.5.0 update Enhancements I18N – Simplified Chinese language support Add tooltip for all table items Remember CSV export options Distinguish Save and Save As when saving SQL files in the Execute SQL tab Add support for compiling on OS/2 Add two buttons to navigate to the head and the tail Compatibility with shadow build Support Indices without sorting – FreeBSD Port Enhancements with progress, though more still needs to be done Open Encrypted Database Add full support for tables without rowid NULL field values are not highlighted Ability to change language Bug fixes Foreign Key in Edit Create Table CSV export bug – Fields aren’t automatically quoted when separator character is present in the data [feature request]”Execute SQL”: clear output if query returns nothing Export to SQL INT Datatype Expression Crash when opening db without rowid and composite primary key Database Structure view: wrong parsing of CHECK constraints tests: fix QCoreApplication parameters Other notes The Windows release uses SQLite v3.

MySQL Cluster NDB 7.3.8 released

MySQL Cluster NDB 7.3.8 has just been released and its based on MySQL Server 5.6 and including features from version 7.3 of the NDB storage engine, as well as fixing a number of recently discovered bugs in previous MySQL Cluster releases. This release also incorporates all bugfixes and changes made in previous MySQL Cluster releases, as well as all bugfixes and feature changes which were added in mainline MySQL 5.6 through MySQL 5.

Error: Cannot use file for clustered server

Recently I was trying to restore 100 databases into a newly provisioned shared clustered disks and ran into this error message using Commvault: [code language=”SQL”] ERROR CODE: Query Result [Cannot use file ‘G:\Data\database1.mdf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

SQL Server First Responder Kit

Just thought I share this with you if you haven’t come across this video. When your SQL Server is critically injured, you need a first responder kit to help you diagnose the problem and apply emergency aid. In this session, Kendra Little introduces you to invaluable tools and techniques for triaging an emergency. If you have one year’s experience with database administration, this session will set you up to triage like a pro…

Determine SQL Server install date

[sourcecode language=”sql”] SELECT createdate AS [SQL Server Install Date] FROM sys.syslogins WHERE [sid] = 0x010100000000000512000000; [/sourcecode] – This T-SQL command helps to determine the date and time that SQL Server was installed

Use T-SQL command to get Windows information

[sourcecode language=”sql”]SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info OPTION (RECOMPILE); [/sourcecode] – Gives you major OS version, Service Pack, Edition, and language info for the operating system

Find SQL server and OS Version information for current instance

[sourcecode language=”sql”] SELECT @@VERSION AS [SQL Server and OS Version Info]; or SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) [/sourcecode] – This T-SQL command will help to determine the version of SQL server you are running and corresponding Windows Operating System version

Error: Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited

I recently encountered this error message on our SQL server 2008: Error: Cannot use the ROW granularity hint on the table “xxx” because locking at the specified granularity is inhibited Cause: This is usually caused by indexes created on a database table where ALLOW_PAGE_LOCKS is set to OFF. This behaviour means that all access to the index will be via a table lock, not a row lock. Run following TSQL command against the affected database table: [sourcecode language=”sql”]

Top Cached SPs By Execution Count (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(250) p.name AS [SP Name], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.execution_count DESC OPTION (RECOMPILE); [/sourcecode] – This script tells you which cached stored procedures are called the most often – This helps you characterize and baseline your workload

Top Cached SPs By Avg Elapsed Time (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE); [/sourcecode] – This helps you find long-running cached stored procedures that may be easy to optimize with standard query tuning techniques

Top Cached SPs By Total Worker time (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE); [/sourcecode] — This helps you find the most expensive cached stored procedures from a CPU perspective — You should look at this if you see signs of CPU pressure

Top Cached SPs By Total Logical Reads (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE); [/sourcecode] – This helps you find the most expensive cached stored procedures from a memory perspective – You should look at this if you see signs of memory pressure

Top Cached SPs By Total Physical Reads (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() AND qs.total_physical_reads > 0 ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE); [/sourcecode] — This helps you find the most expensive cached stored procedures from a read I/O perspective — You should look at this if you see signs of I/O pressure or of memory pressure

Top Cached SPs By Total Logical Writes (SQL 2008)

[sourcecode language=”sql”] SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time FROM sys.procedures AS p WITH (NOLOCK) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE); [/sourcecode] — Logical writes relate to both memory and disk I/O pressure — This helps you find the most expensive cached stored procedures from a write I/O perspective

Lists top statements by average input/output usage for current database

[sourcecode language=”sql”] SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name], (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], SUBSTRING(qt.[text],qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS [Query Text] FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.[dbid] = DB_ID() ORDER BY [Avg IO] DESC OPTION (RECOMPILE); [/sourcecode] — Helps you find the most expensive statements for I/O by SP

Backup all SQL instance databases

I had a situation where I needed to backup hundreds of databases to a USB drive. In this circumstance the GUI was out of the question. After some searching online I found this script: [sourcecode language="sql"]<br />DECLARE @name VARCHAR(50) -- database name<br />DECLARE @path VARCHAR(256) -- path for backup files<br />DECLARE @fileName VARCHAR(256) -- filename for backup<br />DECLARE @fileDate VARCHAR(20) -- used for file name<br /><br />SET @path = 'D:\SQL\Backup\'<br /><br />SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)<br /><br />DECLARE db_cursor CURSOR FOR<br />SELECT name<br />FROM master.

Changing SQL Server 2008/2008 R2 Collation

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server including newly created user databases. Changing the default SQL Server collation can be a complex operation so if you can please avoid must especially those with large user databases. Follow the steps below to change the SQL Server collation for newly installed SQL rather than having to re-install SQL all over again.

Add additional SQL instance to existing SQL Server 2008 R2 Cluster

This post will be focused on how to add additional SQL instance to an existing SQL Server 2008 R2 Cluster. At first I was a little confused about the concept of Active/Active and Active/Passive and its relationship with SQL failover clustering. In a multi failover cluster instances, these terms can be seen as ambiguous when used to describe the configuration of the SQL Server instances and the nodes they are running on.

Install SQL Server 2008 R2 SP1 in a SQL cluster

In our quest to deliver a secured SQL Server 2008 R2 cluster solution, I will continue from my last post Part3 with the installation of SQL Server 2008 R2 SP1. Please note that when installing SQL server 2008 service pack in a cluster, this needs to be installed on the Passive node at any point in time. If this is installed on the Active node, then SQL will require a reboot thereby defeating the purpose of clustering.