SQL Server Tips & Tricks

SQL Server Migration Checklist

2011-07-20T06:57:00.000+05:30

Here is a quick checklist for your SQL Server Migration. 1. Build your New Server, Install SQL Server and required updates and keep the server ready for migration 2. Stop Application Service(s) and kill all the connections from sql server. This is to ensure that the no applications are connected to the Databases during the migration process. 3. Disable all the jobs that refers to this

DBCC REINDEX vs DBCC INDEXDEFRAG

2011-06-23T20:04:00.000+05:30

Note that DBCC DBREINDEX uses one big transaction, killing the operation in the middle would rollback the entire transaction. It would not help you reduce log space consumption if you restart the index rebuild after killing. I noticed that you were doing index rebuild monthly. DBCC DBREINDEX is an offline operation. While doing clustered index rebuild, the entire table is offline. You should

Lists user login attempts to a SQL Server (failed and successful login attempts)

2011-06-14T20:17:00.000+05:30

This script is useful for security audits (helping you gather information about failed login attempts), and for checking recent activity by a particular login (before you delete a login, for example). The script lists all failed login attempts by a user, including the IP address of the computer from which the login attempts were made. The last successful login for a user is also listed. Note

Script to Generate BCP out and Bulk insert statements

2011-06-14T20:13:00.000+05:30

This will be useful while migrating the data from one server to another SET NOCOUNT ONGOSELECT @@SERVERNAMEDECLARE @path nvarchar(2000), @batchsize nvarchar(40), @format nvarchar(40), @serverinstance nvarchar(200), @security nvarchar(800) SET @path = 'C:\Temp\';SET @batchsize = '100000' -- COMMIT EVERY n RECORDSSET @serverinstance = @@SERVERNAME --SQL Server \ Instance nameSET

Query to return fragmentation information on partitioned indexes

2011-06-01T19:47:00.000+05:30

This will very useful, if you have implemented the partitioning functionality . SELECT OBJECT_NAME(a.object_id) AS object_name ,a.index_id ,b.name ,b.type_desc ,a.partition_number ,a.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') aJOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id =

Minimally Logged Inserts in sql server 2008

2011-05-30T09:18:00.000+05:30

This is another great feature that was introduced in sql server 2008. This is one of the enhanced features of ‘SELECT INTO ‘ statements. All you need to do is to make sure that the following conditions are met ensure that the database recovery model is bulk-logged / simple enable the Trace flag 610. insert the data ordered by the clustered index So what is exactly happening behind

Parameter Sniffing

2011-05-23T22:32:00.000+05:30

When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as "parameter sniffing". This is good as long as the input parameters for the first invocation are typical for future invocations. But if that is not the case this will cause performance problems. Okay, I understand parameter sniffing, How should I

find the tables having composite clustered index

2011-05-05T19:55:00.000+05:30

script to find the tables having composite clustered index ;WITH cte AS (SELECT OBJECT_NAME(i.object_id) NAME, type_desc, i.index_id ,c.name columnName ,p.rows FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_idINNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_idINNER JOIN sys.columns c on ic.column_id =

Historic information – sql backups

2011-05-04T21:28:00.000+05:30

Need to find out the historic information of backups , try this script SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log'

Suggested Max Memory Settings for SQL Server 2005/2008

2011-04-27T19:54:00.000+05:30

It is important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. This setting controls how much memory can

Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters

2011-04-26T09:22:00.000+05:30

Buffer cache hit ratio This means what is the percentage that SQL Server had the data in cache and did not have to read the data from disk. Ideally you want this number to be as close to 100 as possible. In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one

script – percentage of identity values being used

2011-04-20T20:04:00.000+05:30

Here is a script which list out all the identity columns and percentage used SET NOCOUNT ONSELECT SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName, idc.name AS ColumnName,CASE idc.system_type_id WHEN 127 THEN 'bigint' WHEN 56 THEN 'int' WHEN 52 THEN 'smallint' WHEN 48 THEN 'tinyint'END AS 'DataType',idc.last_value AS CurrentIdentityValue,idc.seed_value,increment_value,CASE

Index Fragmentation on Partitioned tables

2011-04-14T21:17:00.000+05:30

Partitioning can be effectively used to break up a very large table into multiple partitions based on a column or partitioning key. However, there can be significant management overhead in maintaining partitioned tables. The features of partitioned tables that give you the ease of management can also conspire against you to make your maintenance a nightmare! Thinking of each partition as its own

Find the Size of Database

2011-04-13T20:51:00.000+05:30

I encountered the situation recently where I needed to find the size of the database. --size of database SELECT DB_NAME(database_id) AS DatabaseName,SUM((size*8)/1024) SizeInMBFROM sys.master_filesGROUP BY DB_NAME(database_id) -- size of individual db filesSELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeInMBFROM sys.master_files

Best practices for Indexing

2011-03-29T04:19:00.000+05:30

Create index on columns that are frequently used for data retrieval or in filter conditions. For example in SELECT statement and WHERE clause or in ORDER BY, GROUP BY, and DISTINCT clauses. As in our first example, if most of the queries are referencing emp_name column instead of emp_id column, then it is wise to create index on emp_name instead of emp_id i.e. create indexes based on use. Every

How to run a stored procedure at SQL Server start-up

2011-02-25T22:43:00.000+05:30

This is actually quite simple. There is 'startup' option that you can set to the procedure. There are a few limitations though: - sp must reside in the [master] database - it's owner must be dbo - it mustn't have any input or output parameters Note that each stored procedure run at start up takes up one worker thread until finished. So if you want to run multiple sps at runtime and

alternate to IF EXISTS ..UPDATE ELSE ..INSERT

2011-02-24T01:45:00.000+05:30

On some occasions we need to update an entry in a table in case there is an entry otherwise we need to insert that data into the table. The easiest way to do this is something like this IF EXISTS ( SELECT customer_id FROM dbo.customer_comments WHERE customer_id = @customerId ) UPDATE dbo.customer_comments SET customer_comment = @comment + 'something here' WHERE

Script to Check and Fix Orphan Users

2011-02-10T22:25:00.000+05:30

-- Script to check the orphan userEXEC sp_change_users_login 'Report'--Use below code to fix the Orphan User issueDECLARE @username varchar(25)DECLARE fixusers CURSOR FORSELECT UserName = name FROM sysusersWHERE issqluser = 1 and (sid is not null and sid <> 0x0)and suser_sname(sid) is nullORDER BY nameOPEN fixusersFETCH NEXT FROM fixusersINTO @usernameWHILE @@FETCH_STATUS = 0BEGINEXEC

DBCC UPDATEUSAGE and sp_updatestats

2011-02-10T22:08:00.000+05:30

This may provide some light on the usage of DBCC UPDATEUSAGE and sp_updatestatsDBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes. Size information is not maintained for nonclustered indexes.If there are no inaccuracies in sysindexes, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and the WITH

sys.dm_io_virtual_file_stats

2011-02-07T23:56:00.000+05:30

Excellent dmf that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I use almost daily (well, not usually when I am on vacation, but even sometimes then!) The file can be any file used in the database, including the log or full text files. Parameters: database_id –

Register an Extended Stored Procedure in SQL Server 2008 R2

2011-02-07T22:21:00.000+05:30

Aside from those integrated into SQL Server (e.g. xp_cmdshell) it's not often I come across the need to use Extended Stored Procedures but an application I installed recently required that I add their custom procedure to the SQL Server housing the application data. Having been years since I had last added one it took a little figuring out since I would've expected to find it in Management Studio

How to Enable xp_cmdshell

2011-02-07T22:20:00.000+05:30

There's a great little feature of SQL Server that allows you to execute command-line statements, allowing you to run batch files, get directory listings or call executables. The feature is accessed via the xp_cmdshell Extended Stored Procedure, the syntax is pretty simple and looks like this…EXEC xp_cmdshell 'dir *.exe'; In the wrong hands xp_cmdshell could cause havoc, for that

Reset Default Database from Command Line

2011-02-07T22:18:00.000+05:30

During a recent consolidation exercise I was migrating one database at a time and then taking the old copy offline, unfortunately one of the DBs had (un-knowingly) been my default database and next time I tried to log in I received the dreaded "Cannot open user default database. Login Failed." Message.In my case it was to execute the sp_defaultdb stored procedure using the OSQL utility, for

What is Database Mirroring

2011-01-21T04:22:00.000+05:30

In the proposed database mirroring solution there will be two groups of active-passive windows clusters deployed at each location (PRIMARY SITE and Secondary Site). The Primary Site will be the primary site and the Secondary Site will be the disaster recovery (DR) site.The different failure scenarios are as follows: - In case of a failure at the Primary Site, Node-A would automatically

SQL Server Management Studio (SSMS) – Keyboard Shortcut Keys

2011-01-20T04:04:00.000+05:30

Here is list of Shortcut keys to work with SSMS handy.ActionSSMS-Shortcut KeyMove to the SQL Server Management Studio menu barALTActivate the menu for a tool componentALT+HYPHENDisplay the context menuSHIFT+F10Display the New File dialog box to create a fileCTRL+NDisplay the New Project dialog box to create a new projectCTRL+SHIFT+NDisplay the Open File dialog box to open an existing fileCTRL+

SQL Server Service Account Privileges

2011-01-13T22:18:00.000+05:30

SQL Server 2005 Books Online indicates that the SQL Server service account requires permission to start the following related services (among many other requirements): SQL Server Active Directory Helper and SQL Server VSS Writer services. How can I verify service-related permissions for the SQL Server service account? Obviously, the service really would NOT NEED permissions to start these

Log Shipping vs. database mirroring

2011-01-11T20:44:00.000+05:30

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. It can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring

Log Shipping – NORECOVERY vs. STANDBY mode

2011-01-10T22:07:00.000+05:30

When you restore a transaction log using the NORECOVERY option, SQL Server does not need to analyze the transaction log and roll back any uncommitted transactions at the end of the restore process. When you restore a transaction log using the STANDBY option, SQL Server has to undo any uncommitted transactions so that the data files contain only committed transactions. While it does this, it

Log shipping –Failover

2011-01-10T21:49:00.000+05:30

When it's time to failover to the secondary database, so that it becomes our primary database, we have to do the following· if possible, back up the transaction log on the primary database Apply all outstanding transaction logs to the secondary database after careful consideration. You may not want to restore all the transactions, if the reason you are failing over is because some

Reading SQL Trace Files using fn_trace_gettable function

2011-01-06T22:46:00.000+05:30

While working in SQL Server, most of the time we create trace files(.trc) to identify performance bottleneck. You can create trace files using SQL Profiler or you can use SQL as well. Most of us would go SQL Profiler way, because simply putting is very easy and quite elegant too, further more it gives you WYSIWYG environment. Now suppose somebody has already generated a trace file, and you just

How to create a SQL trace using T-SQL

2010-12-23T01:11:00.000+05:30

Some users want to know if there is a way to monitor events on SQL server without using SQL Profiler. Yes, there is: the engine support behind SQL Profiler is the feature called SQL Trace which is introduced in SQL 2005. SQL Trace provides a set of stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within user's own

Find Best Clustered Index

2010-12-09T20:27:00.000+05:30

The following query will compare the nonclustered indexes vs the clustered index and determine which index would qualify as the best clustered index based upon the DMV statistics. If a clustered index does not exist on the table, it will also suggest one of the existing nonclustered indexes as the clustered index. DECLARE @NonClusteredSeekPct float DECLARE @ClusteredLookupFromNCPct float --

Find Table without Clustered Index / Primary Key

2010-12-09T04:15:00.000+05:30

One of the basic Database Rule I have is that all the table must Clustered Index. Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily.SELECT OBJECT_NAME(i.OBJECT_ID) AS tableName, rowsFROM sys.indexes iINNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_idWHERE i.INDEX_ID = 0AND

Using TRY...CATCH to Rollback a Transaction in case of an error

2010-12-08T21:57:00.000+05:30

As you all might know, one of the downsides of the @@ERROR variable approach to catch the error is that we must check the value of this variable after each and every DML /DDL statement to determine if an error occurred and, if so, to rollback the transaction. With SQL Server 2005's TRY...CATCH block, however, these types of scripts are greatly simplified. BEGIN TRANBEGIN TRY SELECT 1 /

Currently Executing SQL Statements

2010-07-21T20:12:00.002+05:30

Here is a script to find out the currently executing sql statements, of course you can use the ‘sp_who2’, but I use this one very often select C.client_tcp_port, T.textfrom sys.dm_exec_connections C CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) Twhere client_net_address = 'myComputerIpHere'

Currently Executing SQL Statements

2010-07-21T20:12:00.001+05:30

Here is a script to find out the currently executing sql statements, of course you can use the ‘sp_who2’, but I use this one very often select C.client_tcp_port, T.text from sys.dm_exec_connections C CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T where client_net_address = 'myComputerIpHere'

Find the Index fragmentation on SQL Server 2005 /2008

2010-07-09T21:52:00.003+05:30

Here is the script to find out the average index fragmentation levels. Based on this value you can either Rebuild or Reorganize the indexes. Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and

Find the Index fragmentation on SQL Server 2005 /2008

2010-07-09T21:52:00.002+05:30

Find table and index name for fragmented indexes

2010-07-03T00:17:00.003+05:30

SELECT OBJECT_NAME(object_id) AS tblName ,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) aWHERE avg_fragmentation_in_percent > 30 AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX') ORDER BY avg_fragmentation_in_percent DESC

Find table and index name for fragmented indexes

2010-07-03T00:17:00.002+05:30

SELECT OBJECT_NAME(object_id) AS tblName ,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) a WHERE avg_fragmentation_in_percent > 30 AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX') ORDER BY avg_fragmentation_in_percent DESC

Rebuilding Indexes vs Updating Statistics

2010-07-03T00:15:00.002+05:30

No matter in which order you perform these, here are some points to keep in mind 1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table. 2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only.

Rebuilding Indexes vs Updating Statistics

2010-07-03T00:15:00.001+05:30

How to Setup High Performance (asynchronous) Mirroring with No Witness

2010-06-29T21:20:00.002+05:30

Principal Instance - Put the database you want to mirror in FULL recovery mode. For example: 1: BACKUP DATABASE AdventureWorksLT2008 2: TO DISK = 'c:\AdventureWorksLT2008.bak' 3: 4: BACKUP LOG AdventureWorksLT2008 5: TO DISK = 'c:\AdventureWorksLT2008.trn' Copy the backups you made on the Principal Instance to the Mirror Instance Mirror

How to Setup High Performance (asynchronous) Mirroring with No Witness

2010-06-29T21:20:00.001+05:30

Principal Instance - Put the database you want to mirror in FULL recovery mode. For example: 1: BACKUP DATABASE AdventureWorksLT2008 TO DISK = 'c:\AdventureWorksLT2008.bak' 2: BACKUP LOG AdventureWorksLT2008 TO DISK = 'c:\AdventureWorksLT2008.trn' Copy the backups you made on the Principal Instance to the Mirror Instance Mirror Instance - Restore the database and log with

How To: Reset Identity column in SQL Server

2010-06-16T20:54:00.002+05:30

This is one of those simple tip posts that may seem obvious and taken for granted by those of us who have been working with SQL Server for a while now but maybe a newbie or two out there will find this helpful. The following line resets the Identity value for the UserTable to ‘0’ so that the next record added starts at 1. DBCC CHECKIDENT(‘UserTable’ , RESEED, 0)

How To: Reset Identity column in SQL Server

2010-06-16T20:54:00.001+05:30

Free SQL Server tools that might make your life a little easier

2010-06-14T21:59:00.002+05:30

This list will grow as I find new tools. So if you know of some not on this list do post them in the comments. SQL Server Management Studio Add-in's SSMS Tools Pack - an add-in with lots of IDE options (Query Execution history, regions, debug sections, CRUD stored procedures creation, new query templates, running custom scripts from Object explorer's context menu) for SQL Server Management

Free SQL Server tools that might make your life a little easier

2010-06-14T21:59:00.001+05:30

Generate Comma Separated List – SQL Server

2010-05-21T21:23:00.002+05:30

In one to many relationships of two tables, sometimes we need to write a query which can return comma separated list of all child values for each parent. Here are two different methods by which you can easily create a Comma Separated List, the first runs from sql 2000 onwards and the second on is for SQL 2005 onwards. USE tempdbGOCREATE TABLE [dbo].[student]( StudentID INT IDENTITY, Name

Generate Comma Separated List – SQL Server

2010-05-21T21:23:00.001+05:30

In one to many relationships of two tables, sometimes we need to write a query which can return comma separated list of all child values for each parent. Here are two different methods by which you can easily create a Comma Separated List, the first runs from sql 2000 onwards and the second on is for SQL 2005 onwards. USE tempdb GO CREATE TABLE [dbo].[student]( StudentID INT IDENTITY, Name

Add Column With Default Column Constraint to Table

2010-05-13T21:23:00.001+05:30

I found many questions regarding the inline named default constraints in many user groups, its actually pretty simple CREATE TABLE dbo.tempabc([col1] [varchar] (16),[col2] [int] NULL CONSTRAINT df__tempabc__Col2 DEFAULT ((0))) ON [PRIMARY]GO

Add Column With Default Column Constraint to Table

2010-05-13T21:23:00.000+05:30

Alter column set default

2010-05-13T21:20:00.001+05:30

CREATE TABLE dbo.tempabc([col1] [varchar] (16),[col2] [int] NULL CONSTRAINT df__tempabc__Col2 DEFAULT ((0))) ON [PRIMARY]GOALTER TABLE dbo.tempabc ALTER COLUMN Col1 VARCHAR(32) NOT NULLALTER TABLE dbo.tempabc ADD CONSTRAINT df__tempabc__Col1 DEFAULT 'abc' FOR Col1

Alter column set default

2010-05-13T21:20:00.000+05:30

Script to find the No of reads/ writes on DB files

2010-05-13T00:35:00.001+05:30

SELECT DB_NAME(mf.database_id) AS databaseName ,mf.physical_name ,num_of_reads ,num_of_bytes_read ,io_stall_read_ms ,num_of_writes ,num_of_bytes_written ,io_stall_write_ms ,io_stall ,size_on_disk_bytesFROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfsJOIN sys.master_files AS mf ON mf.database_id = divfs.database_id

Script to find the No of reads/ writes on DB files

2010-05-13T00:35:00.000+05:30

Splitting CSV string using CTE

2010-04-24T04:01:00.001+05:30

DECLARE @CSVString varchar(2000)DECLARE @Delimiter varchar(1)SELECT @CSVString = 'This,is,a,test,for,splitting,using,a,CTE,to,break,a,varchar,into,records,based,on,a,delimiter', @Delimiter = ','SET @CSVString = @CSVString+@Delimiter --- append the delimiter ;WITH Res(s, r)AS(SELECTSUBSTRING(@CSVString,1, CHARINDEX(@Delimiter, @CSVString)-1) s,SUBSTRING(@CSVString,CHARINDEX(@Delimiter,

Splitting CSV string using CTE

2010-04-24T04:01:00.000+05:30

Some Licensing FAQ

2010-04-19T21:29:00.001+05:30

What is a CAL? A CAL is a Client Access License. This type of license grants one network user or device permission to access a network service (such as a SQL or Exchange Server). There are also different types of CALs for each service. What is the difference between per seat, per server, and per processor? The Per seat licensing model refers to the total number of computers allowed to connect

Some Licensing FAQ

2010-04-19T21:29:00.000+05:30

Using SET XACT_ABORT

2010-04-15T22:24:00.001+05:30

SET XACT_ABORT specifies what action SQL Server should take following run-time errors. The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues. Depending on the severity of the error, the entire transaction and/or batch may be rolled back even with SET XACT_ABORT is OFF. A side

Using SET XACT_ABORT

2010-04-15T22:24:00.000+05:30

Formatting Money datatypes

2010-04-15T00:40:00.001+05:30

Quite often people ask how to format numeric/money formats in more readable forms separated by commas. The same CONVERT function we use to format the date’s can be used for the same purpose. Only money datatypes can be formatted like this, if you want to format a decimal/numeric fields, you need to cast them using the CAST function. DECLARE @Amount MONEYSELECT @Amount = 123456.7899--No

Formatting Money datatypes

2010-04-15T00:40:00.000+05:30

List Object level permissions

2010-04-14T20:42:00.001+05:30

SELECT c.action, o.name, b.name FROM sys.objects oINNER JOIN sysprotects c ON o.object_id = c.idINNER JOIN sysusers b ON c.uid = b.uidWHERE o.type IN ('U','P','V')AND b.name = 'LoginName'

List Object level permissions

2010-04-14T20:42:00.000+05:30

SELECT c.action, o.name, b.name FROM sys.objects oINNER JOIN sysprotects c ON o.object_id = c.idINNER JOIN sysusers b ON c.uid = b.uidWHERE o.type IN ('U','P','V')AND b.name = 'LoginName'

System views – login/permissions

2010-04-01T22:55:00.001+05:30

---logins:SELECT * FROM sys.server_principals --database users (run in the db)SELECT * FROM sys.database_principals --db roles SELECT dp.type_desc, dp.name, p.nameFROM sys.data base_role_members rJOIN sys.database_principals dp ON r.role_principal_id = dp.principal_idJOIN sys.database_principals p ON r.member_principal_id = p.principal_id

System views – login/permissions

2010-04-01T22:55:00.000+05:30

A few maximum limitations for SQL Server 2005

2010-03-20T01:29:00.001+05:30

Every now and then i see a question pop up that asks what is the max this or that in sql server?Well here are some maximum values:Bytes per short string column8,000 Bytes per GROUP BY, ORDER BY8,060 Columns in GROUP BY, ORDER BYLimited only by number of bytes Bytes per index key900 Bytes per foreign key900 Bytes per primary key900 Bytes per row8,060 Bytes per varchar(max), varbinary(max),

A few maximum limitations for SQL Server 2005

2010-03-20T01:29:00.000+05:30

How to move database physical files?

2010-03-17T21:16:00.001+05:30

If you want to move database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database offline, MOVE the files TO the new location and map it . Here is an example EXEC sp_helpDB myDB Note the filenames AND the physical paths ALTER DATABASE MyDB SET ONLINEGOALTER DATABASE MyDB SET OFFLINEGO Now it is safe

How to move database physical files?

2010-03-17T21:16:00.000+05:30

Stress Testing tools for SQL Server

2010-03-02T21:38:00.001+05:30

http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en http://blogs.msdn.com/psssql/archive/2008/12/19/using-sqliosim-to-diagnose-sql-server-reported-checksum-error-824-823-failures.aspx http://www.windowsitpro.com/article/john-savills-windows-faqs/what-tools-are-available-to-stress-benchmark-sql-server-.aspx http://www.quest.com/

Stress Testing tools for SQL Server

2010-03-02T21:38:00.000+05:30

List-all-indexes-statistics-sql-server

2010-02-03T04:15:00.001+05:30

select 'Table name' = object_name(id), 'column_name' = index_col(object_name(id), indid, 1), 'index_description' = convert(varchar(210), case when ( status & 16 ) <> 0 then 'clustered' else 'nonclustered' end + case when ( status & 1 ) <> 0 then ', ' + 'ignore duplicate keys' else

List-all-indexes-statistics-sql-server

2010-02-03T04:15:00.000+05:30

List all indexes /statistics sql server 2000

2010-02-03T04:13:00.001+05:30

List all indexes /statistics sql server 2000

2010-02-03T04:13:00.000+05:30

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

2010-01-27T22:06:00.001+05:30

You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the builtin\Administrators from the sysadmin server role, and no one you can find is in the sysadmin role. At this point you may think that your only options are to reinstall SQL Server and attach the databases, or to directly access the master database files, which

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

2010-01-27T22:06:00.000+05:30

List User permissions

2009-07-27T03:00:00.001+05:30

Another common question I found in several user forums WITH cte AS ( SELECT USER_NAME ( p.grantee_principal_id ) AS principal_name , dp.principal_id , dp.type_desc AS principal_type_desc , p.class_desc , OBJECT_NAME ( p.major_id ) AS object_name , p.permission_name ,

List User permissions

2009-07-27T03:00:00.000+05:30

Step By Step Guide to Database Mirroring

2009-07-26T04:13:00.001+05:30

Principal Server: SQL8Mirrored Server: SQL 4Pre-Requisites - Ensure that SQL Service /SQL Agent Services on all the machines start with the same domain account, otherwise mirroring wont work.Step 1 : Install database on Mirrored ServerIn order to get the database onto the mirrored server, we do a full backup of the ‘YourDatabase’ database on the Principal server, followed by a backup of the

Step By Step Guide to Database Mirroring

2009-07-26T04:13:00.000+05:30

How to kill a profiler trace

2009-07-23T20:58:00.001+05:30

Let's say the DBA ran a trace on a server and left for a long lunch, and the client server where the profiler is running is suffering badly with poor performance on the applications running on it and someone in the admin team want to kill that trace even in the absence of the DBA. Running the following will give you the status of running traces. You may see one running, usually traceid of 1,

How to kill a profiler trace

2009-07-23T20:58:00.000+05:30

Forgot/Lost ‘sa’ password

2009-07-09T08:42:00.001+05:30

Forgot/Lost ‘sa’ password

2009-07-09T08:42:00.000+05:30

Rename Schema / Transfer schema

2009-07-03T21:39:00.001+05:30

1: CREATE SCHEMA new_schema 2: GO 3: -- Now run these queries one by one, copy and paste the result of this in a new window and run it 4: SELECT 'ALTER SCHEMA new_schema TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name 5: FROM sys.tables WHERE schema_id = SCHEMA_ID('old_schema'); 6: 7: SELECT 'ALTER SCHEMA

Rename Schema / Transfer schema

2009-07-03T21:39:00.000+05:30

Maintenance Plans in SQL 2005 Express

2009-07-03T05:16:00.001+05:30

I found lot of questions on the missing in ability of SQL Server Express to automatically perform database backups/integrity checks/reindexing regularly with a Maintenance Plan. Since SQL Express doesn't have Maintenance Plans, it takes a bit of extra effort to set that up. Here's how I do it:A Windows Scheduled Task runs daily, kicking off sqlexpressmaintplan.cmd:sqlcmd -S SQLSERVER\INSTANCE -i

Maintenance Plans in SQL 2005 Express

2009-07-03T05:16:00.000+05:30

Recently executed SQL Statements

2009-06-24T20:43:00.001+05:30

SELECT last_execution_time , [text] AS [Statement]FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY last_execution_time DESC

Recently executed SQL Statements

2009-06-24T20:43:00.000+05:30

SELECT last_execution_time , [text] AS [Statement]FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY last_execution_time DESC

Failover Clustering

2009-06-05T04:08:00.001+05:30

Failover Clustering High availability option, standby instance on a different node In case of failover: active transactions are rolled back, connections retry, reconnect to other node Single copy of databases in shared storage Built on top of Windows Server Failover Clustering – up to 8 nodes in SQL 2005, 16 in SQL 2008 SQL Server 2005 – Requires certified hardware See http://

Failover Clustering

2009-06-05T04:08:00.000+05:30

SQL Server 2008 Database Mirroring

2009-06-05T04:05:00.001+05:30

Database MirroringSoftware solution for high availability. Per database and not per server. Increase data protection, availability, upgrade availability. Careful – A lot of things live outside the database SQL HA options for the masses, without the high-end hardware requirement. Very popular option. See http://msdn.microsoft.com/en-us/library/ms189852.aspx Requirements and LimitationsFull

SQL Server 2008 Database Mirroring

2009-06-05T04:05:00.000+05:30

DBA database management checklist

2009-05-21T22:51:00.001+05:30

Problem For both old and new DBAs there are fundamental procedures that should be addressed and proper processes put in place to handle various areas of database management for SQL Server. Whether you are a full time DBA or this is one of many job roles that you perform the same basic steps should be implemented and adhered to in order to have some peace of mind that you are performing the

DBA database management checklist

2009-05-21T22:51:00.000+05:30