Free SQL Server command line tool "SQLS*Plus"
There is a free SQL Server command line tool "SQLS*Plus" (on http://www.memfix.com ) - exactly like SQL*Plus for SQL Server. It is very flexible with data formatting (set lines size, p…
Showing posts from 2012
Moving the system database "tempdb"
If the system database "tempdb" is defined on several data files as recommended in general, each data file is moved by a ALTER DATABASE statement. USE master; GO ALTER DATABASE tempdb …
Modify SQL Agent Errorlog path and Default Database Path
If you are moving databases to a new drive/volume or another server (different drive letter), you will find it useful to also change the SQL Server Agent log path as well as the Database Default Loca…
TSQL Script to return files and Free Space
Here is the script ; WITH c AS ( SELECT g.name AS [FileGroupName] ,s.type_desc ,s.physical_name AS [FileName] ,s. size * CONVERT ( FLOAT , 8) AS [ Size ] , C…
How to disable constraints on a table
Sometimes it's useful to disable one or more constraints on a table, do something significant like a BULK INSERT / DELETE, and then re-enable the constraint(s) once you're done. You can dis…
Script to find the size of an Index
Simple query to find the size of the indexes SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, i.index_id AS IndexID, 8 * SUM (a.used_pages)/1024 AS ' IndexSize in…
SQL Server 2012 Virtual launch event
Finally.. the most anticipated version of sql server is ready for launch. Just few more hours..want to register for the virtual launch, click the below link Experience the excitement of the SQL Ser…
ACID properties of transactions
In the context of transaction processing, the acronym ACID refers to the four key properties of a transaction: atomicity, consistency, isolation, and durability. Atomicity All changes to data are …
How to get a history of SQL scripts that have been run on a DB
If you don’t have any sort of auditing mechanism enabled, its really hard to find get these information. But you may get that info from the cached plan. p first step is to make sure that “optimize …
Find all SQL Objects with QUOTED_IDENTIFIER set to OFF
Whenever you create or recreate an SP, make sure QUOTED_IDENTIFIER is set to ON. The default value of OFF is there for backward compatibility only. Many modern features of SQL Server (such as filtere…
Jobs failing due to "incorrect settings: QUOTED_IDENTIFIER"
Did you ever noticed after adding a filtered index or a Computed column, jobs/stored procedures that were using these tables suddenly started failing with the following error Executed as user: XXXX…
Indexes and Constraints on a Partitioned Table
In a partitioned table, the partition column must be a part of: The clustered index key. The primary key. Unique index and uniqueness constraint keys. The partition column…
What’s new on SQL SERVER 2008 SP3
Microsoft SQL Server Sustained Engineering group is pleased to announce the release of SQL Server 2008 Service Pack 3. Both the Service Pack and Feature Pack updates are available for download on the…