Enabling IntelliSense and Refreshing IntelliSense Data in SSMS 2008

Dear Readers,

You can check out IntelliSense Article on MSSQLTips.com.

Click Me to read...

 
Thanks,
Jugal Shah

Script to find out Orphaned AD/Windows Logins

It is easy to find out the orphaned SQL logins by comparing the SID of SQL Login and User, but what in case of windows login.

Take an example if windows login is dropped and it is still exists in SQL Server. You can find out all the delete windows login which is orphaned in SQL Server, using below procedure.

Sp_validatelogins
Reports information about Windows users and groups that are mapped to SQL Server principals but no longer exist in the Windows environment.

[sourcecode language="sql"]
CREATE TABLE #dropped_windows_logins
(
[sid] VARBINARY(85),
[name] SYSNAME
)

INSERT #dropped_windows_logins
EXEC sys.Sp_validatelogins

SELECT *
FROM #dropped_windows_logins

DROP TABLE #dropped_windows_logins
[/sourcecode]

Script to create Folder/Directory using SSMS

One of my blog reader requested that, how to create the directory using Script/SSMS. Here is the answer.


-- Below query will list out the directories on C drive
EXEC MASTER.sys.Xp_dirtree 'C:\JugalA'

-- Below query will create the folder JugalA on C drive
EXEC MASTER.dbo.Xp_create_subdir 'C:\JugalA'

-- Below query will create the folder JugalB on C:\JugalA drive
EXEC MASTER.dbo.Xp_create_subdir 'C:\JugalA\JugalB'

Query to Check the SQL Server Restart time

You can simply run one of the below query to check the SQL Server last restart time.

[sourcecode language="sql"]

SELECT Dateadd(s, ( ( -1 ) * ( osd.[ms_ticks] / 1000 ) ), Getdate()) AS serverrestartdatetime,
osd.sqlserver_start_time
FROM sys.[dm_os_sys_info] osd;

OR

SELECT name,
crdate
FROM sys.sysdatabases
WHERE name = 'tempdb'
[/sourcecode]

Useful Links for Microsoft SQL Server Certification

http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx

http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-432

http://www.measureup.com/70-432-TS-Microsoft-SQL-Server-2008-Implementation-and-Maintenance--P586.aspx

https://mcp.microsoft.com/mcp

Troubleshooting CPU bottleneck

sys.dm_os_schedulers is a useful DMV to find out the CPU pressure. It returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor.

We can use this DMV to monitor the condition of a scheduler or to identify runaway tasks. It will help us to identify if there is any CPU bottleneck in the SQL Server machine.

We have to check for "runnable_tasks_count" column value which indicates the count of workers that have tasks assigned to them that are just waiting for their share of time on the scheduler (logical CPU). The value of runnable_tasks_count should be as low as possible.

Permission
Requires VIEW SERVER STATE permission on the server


SELECT scheduler_id,
status,
cpu_id,
is_online,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count,
load_factor
FROM sys.dm_os_schedulers
GO




If you find the the avg(runnable_tasks_count) greater then 0 that means system is waiting for CPU time. If  Pending_disk_io_count is greater then 0, that means system is bound by IO you need to get disks to perform better.

Script to Rename Database

CREATE DATABASE sqldbpool
--Solution - I
EXEC Sp_renamedb  'SQLDBPool',  'Jugal'

--Solution - II
ALTER DATABASE sqldbpool MODIFY name=jugal 

Clear/Remove SQL Server Services from Services.msc

Problem:- 
One of my friend was installing cluster SQL Server, installation was stopped in the middle of something and he was not able to remove the SQL Server from add/remove programs or SQL Server setup. He has deleted the registry, folder manually but still he can see the SQL Server Services in Services.msc. He called me for the issue, I have provided him the below solution and it will work.

Solution:
1) Go to command prompt and use SC command to delete service

sc delete sqlserveragent
sc delete mssqlserver

2) You have to also delete the services registry entry using regedit
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

How to take database out of emergency mode?

In SQL Server 2000

sp_configure 'allow' ,1
GO
Reconfigure with override
GO
Update sysdatabases set status = status&(~32768) where name = 'SQLDBPool'
GO
sp_configure 'allow', 0
GO
Reconfigure with override
go


IN SQL Server 2005/2008

ALTER DATABASE sqldbpool
SET online

How to open database in Emergency Mode?

In SQL Server 2000

sp_configure 'allow' ,1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = 'SQLDBPool'
GO
sp_configure 'allow', 0
GO
Reconfigure with override
go


IN SQL Server 2005/2008

ALTER DATABASE sqldbpool
SET emergency 

Script to calculate DB size and available size

SELECT Db_name()                                                           AS
       dbname,
       name                                                                AS
       filename,
       size / 128.0                                                        AS
       currentsizemb,
       size / 128.0 - CAST(Fileproperty(name, 'SpaceUsed') AS INT) / 128.0 AS
       freespacemb
FROM   sys.database_files; 

Disconnect Users

Many times we want to restore the existing database or do DDL operation and often we are getting error Database is in use.


ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE 

You required only DBO rights on the target database, even you don't have sysAdmin rights it will work for that particular database.

Scripts which make you Database Hero

-- Create databsae SQLDBPool
CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N'sqldbpool', FILENAME = N'C:\sqldbpool.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'sqldbpool_log', FILENAME = N'C:\sqldbpool_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

--Script to create schema
USE [sqldbpool]
GO
CREATE SCHEMA [mySQLDBPool] AUTHORIZATION [dbo]

-- Script to create table with constraints
create table mySQLDBPool.Emp
(
EmpID int Primary key identity(100,1),
EmpName Varchar(20) Constraint UK1 Unique,
DOB datetime Not Null,
JoinDate datetime default getdate(),
Age int Constraint Ck1 Check (Age > 18)
)

-- Script to change the recovery model of the databsae
USE [master]
GO
ALTER DATABASE [SQLDBPool] SET RECOVERY FULL WITH NO_WAIT
GO

ALTER DATABASE [SQLDBPool] SET RECOVERY FULL
GO

-- Script to take the full backup of database
BACKUP DATABASE [SQLDBPool] TO DISK = N'D:\SQLDBPool.bak'
WITH NOFORMAT, INIT, NAME = N'SQLDBPool-Full Database Backup',
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

--Script to take the Differential Database backup
BACKUP DATABASE [SQLDBPool] TO DISK = N'D:\SQLDBPool.diff.bak'
WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N'SQLDBPool-Diff Backup',
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

--Script to take the Transaction Log backup that truncates the log
BACKUP LOG [SQLDBPool] TO DISK = N'D:\SQLDBPoolTlog.trn'
WITH NOFORMAT, INIT, NAME = N'SQLDBPool-Transaction Log Backup',SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- Backup the tail of the log (not normal procedure)
BACKUP LOG [SQLDBPool] TO DISK = N'D:\SQLDBPoolLog.tailLog.trn'
WITH NO_TRUNCATE , NOFORMAT, INIT, NAME = N'SQLDBPool-Transaction Log Backup',NOREWIND,SKIP, NOUNLOAD, NORECOVERY , STATS = 10
GO

-- Script to Get the backup file properties
RESTORE FILELISTONLY FROM DISK = 'D:\SQLDBPool.bak'

-- Script to Restore Full Database Backup
RESTORE DATABASE [SQLDBPool1] FROM DISK = N'D:\SQLDBPool.bak'
WITH FILE = 1, MOVE N'sqldbpool' TO N'D:\SQLDBPooldata.mdf',
MOVE N'sqldbpool_log' TO N'D:\SQLDBPoollog_1.ldf',
NOUNLOAD, STATS = 10
GO

-- Script to delete the backup history of the specific databsae
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SQLDBPool1'
GO

-- Full restore with no recovery (status will be Restoring)
RESTORE DATABASE [SQLDBPool1] FROM DISK = N'D:\SQLDBPool.bak'
WITH FILE = 1, MOVE N'SQLDBPool' TO N'D:\SQLDBPooldata.mdf',
MOVE N'SQLDBPool_Log' TO N'D:\SQLDBPoolLog_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO

-- Restore transaction log with recovery
RESTORE LOG [SQLDBPool1] FROM DISK = N'D:\SQLDBPoolLog.trn'
WITH FILE = 1, NOUNLOAD, RECOVERY STATS = 10
GO

--Script to bring the database online without restoring log backup
restore database sqldbpool with recovery

--Script to detach database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQLDBPool'
GO

-- Script to get the database information
sp_helpdb 'SQLDBPOOL'

--to Attach database
USE [master]
GO

CREATE DATABASE [SQLDBPool1] ON
( FILENAME = N'C:\SQLDBPool.mdf' ),
( FILENAME = N'C:\SQLDBPool_Log.ldf' )
FOR ATTACH
GO

USE SQLDBPool
GO

-- Get Fragmentation info for each non heap table in SQLDBPool database
-- Avg frag.in percent is External Fragmentation (above 10% is bad)
-- Avg page space used in percent is Internal Fragmention (below 75% is bad)

SELECT OBJECT_NAME(dt.object_id) AS 'Table Name' , si.name AS 'Index Name',
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('SQLDBPool'), NULL, NULL, NULL, 'DETAILED')
WHERE index_id <> 0) AS dt
INNER JOIN sys.indexes AS si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
ORDER BY OBJECT_NAME(dt.object_id)

-- Script to Get Fragmention information for a single table
SELECT TableName = object_name(object_id), database_id, index_id, index_type_desc, avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'SQLDBPool'), OBJECT_ID(N'mySQLDBPool.Emp'), NULL, NULL , 'LIMITED');

--script to get the index information
exec sp_helpindex [mySQLDBPool.Emp]

--Script to Reorganize an index
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE
GO

-- Rebuild an index (offline mode)
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);

--Script to find which columns don't have statistics
SELECT c.name AS 'Column Name'
FROM sys.columns AS c
LEFT OUTER JOIN sys.stats_columns AS sc
ON sc.[object_id] = c.[object_id]
AND sc.column_id = c.column_id
WHERE c.[object_id] = OBJECT_ID('mySQLDBPool.Emp')
AND sc.column_id IS NULL
ORDER BY c.column_id

-- Create Statistics on DOB column
CREATE STATISTICS st_BirthDate
ON mySQLDBPool.Emp(DOB)
WITH FULLSCAN

-- When were statistics on indexes last updated
SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id)
FROM sys.objects AS o WITH (NOLOCK)
JOIN sys.indexes AS i WITH (NOLOCK)
ON o.name = 'Emp'
AND o.object_id = i.object_id
ORDER BY STATS_DATE(i.object_id, i.index_id);

-- Update statistics on all indexes in the table
UPDATE STATISTICS mySQLDBPool.Emp
WITH FULLSCAN


-- Script to shrink database
DBCC SHRINKDATABASE(N'SQLDBPool' )
GO

-- Shrink data file (truncate only)
DBCC SHRINKFILE (N'SQLDBPool_Data' , 0, TRUNCATEONLY)
GO

-- Script to shrink Shrink data file - Very Slow and Enhances the fragmentation
DBCC SHRINKFILE (N'SQLDBPool_Data' , 10)
GO
-- Script Shrink transaction log file
DBCC SHRINKFILE (N'SQLDBPool_Log' , 0, TRUNCATEONLY)
GO

-- Script to create view
CREATE VIEW emp_view
AS
SELECT *
FROM mySQLDBPool.emp

Central Management Server

Central Management Server
SQL Server Central Management Server is just a central repository that holds a list of managed servers. Microsoft has introduced CMS feature in SQL Server 2008 SSMS.

Use of Central Management Server
1. Multiple Server Query Execution, we can execute query against multiple servers and get the result at source.
2.Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort.
3.Evaluate policy against the multiple server from single source.
4.Control Services and bring up SQL Server Configuration Manager
5.Import and export the registered servers:

Pre-requisite:
You must have at least 1 SQL Server 2008 instance which can be used as CMS

Steps to Create CMS and Register Server
1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.
2. Right click on the Central Management Servers and select “Register Central Management Server” and Register the SQL Server 2008 instance as CMS.



3. Create the groups under Registered CMS servers to define the group for each server.
4. Right Click on groups and register the all the SQL Server instances as per their group. (SQL Server 2000,2005 and 2008)

SQL Profiler

SQL Server Profiler is a graphical tool that helps in the monitoring of an instance of SQL Server Database Engine or Analysis Services. SQL Profiler is a tool which monitors the events and activity running on a SQL Server instance. The results can be saved to a file or inside a SQL Server table. We can replay this saved trace. Profiler is mostly used in stress testing a server, analyzing performance, debugging TSQ statements, and auditing SQL Server activity.

See below image to see how to open SQL Profiler


See below list for the Key terms associated with profiler.

Event is an action that is generated within an instance of a SQL Server Database Engine. These could be login failure, connection failure or any disconnection. It will include events such as T-SQL statements, remote procedure call batch status, the start or end of a stored procedure, the start or end of statements within a stored procedure and so on.. These are displayed in the trace in a single row intersected by data columns with descriptive details.

Event Class is an event that can be traced and contains all of the data that can be reported by the event. For example SQL: Batch completed for instance is an event class

Event Category defines the methodology used for grouping events within the SQL Server Profiler. For instance lock events will be categorized under Lock event category.

Data Column is an attribute of an event class that is captured in the trace.

Template is the default configuration for a trace. It includes the event classes that are required for monitoring.

Trace captures data based on selected event classes, data columns and filters.

Filter Data can be filtered by specifying criteria of selection during the execution of an event. This feature is used to reduce the size of the Trace.



Event Selection Tab


You can use fn_trace_gettable function to read the trace file.

SQL Server Services

As per the options you choose during the SQL Server installation, it will install below services on server.

SQL Server Database Services - The service is used for SQL Server relational Database Engine.

SQL Server Agent - is used for scheduling. It executes jobs, monitors SQL Server, send alerts, and enables automation of some of the administrative tasks.

Analysis Services - Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.

Reporting Services - Manages, executes, creates, schedules, and delivers reports.

Integration Services -is used for SSIS package. It provides management support for Integration Services package storage and execution.

SQL Server Browser - The name resolution service that provides SQL Server connection information for client computers. It is used for named instance only.

Full-text search - Provided full text index and searching facility for BLOB columns.

SQL Server Active Directory Helper - Publishes and manages SQL Server services in Active Directory.

SQL Writer - Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

Different ways to secure the SQL Server SA Login

Problem
What are the different ways to secure the SA login? Everyone is aware of the SA login and its administrator rights, so it is very risky if some unwanted users try to use the SA account or hack the SA account. It is also not advisable to use the SA account in any application or by users. In this article we cover a few ways to reduce the risk.

Solution
Read article on MSSQLTips.com here. http://www.mssqltips.com/tip.asp?tip=2221

Copying column headers with grid query results in SQL Server Management Studio

Many times when we are copying result from the Result Set GRID, column headers are not copied with it. To copy the column header you have to enable it.

Go to Tools menu -> Option -> Query Results -> SQL Server -> Results to Grid -> Checked the "Include column headers when copying or saving the result" check box

All Articles - Page is Changed Now

Dear Readers,

I have changed the All Articles Page of SQLDBPOOL.com. Now you can easily find out all the articles from the list.



Thanks,
Jugal Shah

Script to get the last update statistics date

Below script will execute against the sys.Objects,sys.indexes and sys.stats. Script will return last statistics update date and meta data of  statistics. Script will return the result for table and view level statistics.

In this script I have used the function STATS_DATE which will return last updated statistics date.

Syntax
STATS_DATE ( table_id , index_id )

SELECT sysobj.name AS objectname,
sysindex.name AS indexname,
Stats_date(sysindex.[object_id], sysindex.index_id) AS
[Statistics Update Date],
CASE sysstats.auto_created
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS
isstatscreatedbyqueryprocessor,
CASE sysstats.user_created
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS
isstatscreatedbyuser,
CASE sysstats.no_recompute
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS
isstatscreatedwithnorecomputeoption
FROM sys.objects AS sysobj WITH (nolock)
INNER JOIN sys.indexes AS sysindex WITH (nolock)
ON sysobj.[object_id] = sysindex.[object_id]
INNER JOIN sys.stats AS sysstats WITH (nolock)
ON sysindex.[object_id] = sysstats.[object_id]
AND sysindex.index_id = sysstats.stats_id
WHERE sysobj.[type] IN ( 'U', 'V' )
ORDER BY Stats_date(sysindex.[object_id], sysindex.index_id);

SQL Server 2005/2008 Standard Reports

In the new versions of SQL Server Microsoft has included very useful standard reports. We can use these different kind of report for performance-tuning, activity monitoring, Query tuning, troubleshooting IO bottlenecks.

You can follow below steps to get the standard reports.
Right Click On SQL Server -> Goto Reports -> Goto Standard Reports

How can we export and import registered servers using SSMS?

In our organization we have number of SQL Server instances which are grouped by their Business Unit, Environment (PRD, DEV etc). For new joiners, it is hard to find out the list servers in absence of CMDB. As a solution, you can ask your team member to give you the list of registered servers by doing using server import/export option.

1. In Registered Servers, right-click a server group, and then click Export.See below image for more information.



2.In the Export Registered Servers dialog box, from the Server Group list select the location where you would like to save the registered server information, in the Export file box type a name for the exported file, and then click Save.



SQL Server will create the file with .RegSvr extension which stores server info in XML format.


In Registered Servers, right-click a server group, and then click Import and select the export file with .RegSvr extension. See below image for more information.

How to rename Column Name and Table Name?

We can use sys.SP_Rename procedure to rename the column and table in SQL Server.

CREATE TABLE empl
(
name VARCHAR(20),
remarks VARCHAR(100)
)


--syntax to rename column
EXEC sys.Sp_rename
@objname = 'table_name.old_column_name',
@newname = 'new_column_name',
@objtype = 'COLUMN'

--rename column example
EXEC sys.Sp_rename
@objname = 'empl.name',
@newname = 'empname',
@objtype = 'COLUMN'

--rename table name Old Table Name and New Table Name
EXEC sys.Sp_rename
'empl',
'empInfo'

EXEC sys.Sp_rename

Logical Joins and Physical Joins

Logical Joins: INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY. This joins are written by the user.

Physical Joins: Nested loop, merge join and hash join is common operators which we will see in execution plan as long as query contains any logical joins (inner join, outer join, cross join, semi join). These three joins are called Physical Joins.

In below example I have executed query against AdventureWorks database with Inner Join. You can see the Nested Join in Execution Plan.

Date Time Functions

CURRENT_TIMESTAMP()
Returns the current database system timestamp as a datetime value without the database time zone offset.

GETDATE ()
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

GETUTCDATE()
Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time).

SYSDATETIME()

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running

SYSDATETIMEOFFSET()
Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running with timezone offset.

SYSUTCDATETIME
Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

SELECT SYSDATETIME()
----------------------
2011-01-10 06:38:25.05

SELECT SYSDATETIMEOFFSET()
----------------------------------
2011-01-10 06:38:25.0527369 -05:00

SELECT SYSUTCDATETIME()
----------------------
2011-01-10 11:38:25.05

SELECT CURRENT_TIMESTAMP
-----------------------
2011-01-10 06:38:25.050

SELECT GETDATE()
-----------------------

2011-01-10 06:38:25.050

SELECT GETUTCDATE()
-----------------------
2011-01-10 11:38:25.050

Note: Refer books online for more information.

Download and Install AdventureWorks Sample Database

Microsoft SQL Server Sample Database is moved to Microsoft's Open Source site of CodePlex.

You can download it from the below link.
http://msftdbprodsamples.codeplex.com/

How to Create Alias in SQL Server?

What is Alias?
A SQL Server alias is the user friendly name. For example if there are many application databases hosted on same SQL Server. You can give the different alias name for each application. Simply Alias is an entry in a hosts file, a sort of hard coded DNS lookup a SQL Server instance.

You can create Alias from Configuration Manager.

Go to SQL Server Configuration manager - Go to SQL Native Client -> Right Click and SELECT New Alias from the popup window.

Alias Name -- Alernative name of SQL Server
Port No -- Specify the Port No
Server - Mentioned the Server Name or IP address

Make sure On a 64-bit system, if you have both 32-bit and 64-bit clients, you will need to create an alias for BOTH 32-bit and 64-bit clients.

Identify Objects Type using sys.SysObjects

Today I received an email from my one of blog reader regarding identification of different objects using Sys.sysObjects.

You can query the sys.SysObjects to get all the objects created with in the database.
Example

SELECT *
FROM   sys.sysobjects
WHERE  TYPE = 'u' 


Below are the different kind of object you can retrieve from database.


Object Type Abbreviation
AF = Aggregate function (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = In-lined table-function
IT = Internal table
P = Stored procedure
PC = Assembly (CLR) stored-procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

Insert data from one table to another table

You can insert the data from one table to another table using SELECT INTO and INSERT INTO with SELECT.. FROM clause.


-- Below statement will create the temp table to insert records
select * INTO #tmpObjects from sys.sysobjects where type = 'u'

-- Below statement will create the user table to insert records.
-- First will create the table and insert it details as well in new table
select * INTO tmpObjects from sys.sysobjects where type = 'u'

--Below statement will insert new data into table
insert into tmpObjects SELECT * from sys.sysobjects where type = 's'

Display the size of all tables in Sql Server 2005

You can use sys.SP_SpaceUsed stored procedure to get the size of all the tables.

Below query will calulate the space of all the tables.
--create temp table to store the result
CREATE TABLE #temptable
  (
     tablename    VARCHAR(100),
     numberofrows VARCHAR(100),
     reservedsize VARCHAR(50),
     datasize     VARCHAR(50),
     indexsize    VARCHAR(50),
     unusedsize   VARCHAR(50)
  )

--Inserting into temp table

INSERT INTO #temptable
            (tablename,
             numberofrows,
             reservedsize,
             datasize,
             indexsize,
             unusedsize)

EXEC Sp_msforeachtable @command1="EXEC sp_spaceused '?'"

SELECT *
FROM   #temptable

-- drop the temporary table
DROP TABLE #temptable 

 

[caption id="attachment_927" align="aligncenter" width="455" caption="Size of All the tables"][/caption]

 



 

Linked Server in SQL Server

A linked server configuration enables SQL Server to execute commands/T-SQL statements against OLE DB data sources on remote servers. You can query heterogeneous databases using linked server.

Advantage of Linked Server
Remote server access
You can execute distributed queries, updates, commands, and transactions on heterogeneous data sources using linked server
The ability to address diverse data sources similarly.


-- Below procedure will create linked server to communicate with access

Sp_addlinkedserver 'Lnk_AccessDB', 'Access', 'Microsoft.Jet.OLEDB.4.0',

'c:\db1.mdb'


-- query the linked server

SELECT *

FROM   lnk_accessdb...tb1


-- Below query will drop the linked server

EXEC Sp_dropserver 'Lnk_AccessDB' 

GO Keyword with Integer Parameter

In SQL Server the keyword GO tells SQL Server to execute the preceding code as one batch. From SQL Server 2005 onwards we can specify the integer parameter with GO keyword which will loop the preceding statement.

Example

create table jugal ( name varchar(100) default 'jugal shah' ) -- below command will insert the default value two times as we have specified 2 as parameter insert into jugal values(default) go 2 -- below command will print 'Jugal Shah' 100 times as we have specified 100 as parameter print 'jugal shah' go 100

SSMS T-SQL Batch Separator

It is often asked by the people can we change the batch separator T-SQL. Answer is yes we can change the batch separator.

We can do it by Tools->Options->Query Execution->SQL Server->General->Batch separator

Backup Start Date Time and Finish Date Time

As best practice it is recommended that you have to backup date time with the backup file name so anyone can get the idea of Backup creation.

Sometimes due some issue we took backup without specifying the datetime with the backup file name so during restore we are unsure that how much data backed up in the backup file, type of backup, Is it Copy only and more.

SQL Server stores the Backup Metadata into backup header. You can restore header only command to get the required information.

RESTORE headeronly FROM disk = 'c:\jshah.bak' 

Column Name	Values	Description
BackupName	NULL
BackupDescription	NULL
BackupType	1	Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial
ExpirationDate	NULL
Compressed	0	0 = Un-Compressed Backup 1 = Compressed Backup
Position	1
DeviceType	2
UserName	JShah
ServerName	SQLDBPool
DatabaseName	jshah
DatabaseVersion	655
DatabaseCreationDate	12/31/10 9:55 AM
BackupSize	1453056
FirstLSN	28000000006000100
LastLSN	28000000013000000
CheckpointLSN	28000000006000100
DatabaseBackupLSN	0
BackupStartDate	12/31/10 10:06 AM
BackupFinishDate	12/31/10 10:06 AM
SortOrder	52
CodePage	0
UnicodeLocaleId	1033
UnicodeComparisonStyle	196609
CompatibilityLevel	100
SoftwareVendorId	4608
SoftwareVersionMajor	10
SoftwareVersionMinor	0
SoftwareVersionBuild	2757
MachineName	SQLDBPool
Flags	512	1 = Log backup contains bulk-logged operations. 2 = Snapshot backup. 4 = Database was read-only when backed up. 8 = Database was in single-user mode when backed up. 16 = Backup contains backup checksums. 32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors. 64 = Tail log backup. 128 = Tail log backup with incomplete metadata. 256 = Tail log backup with NORECOVERY.
BindingID	85A5505D-ADB1-4B33-A181-549DC520A0F8
RecoveryForkID	03DE5437-1E27-4885-9011-91CFED12338A
Collation	SQL_Latin1_General_CP1_CI_AS
FamilyGUID	03DE5437-1E27-4885-9011-91CFED12338A
HasBulkLoggedData	0	1 = Yes 0 = No
IsSnapshot	0	1 = Yes 0 = No
IsReadOnly	0	1 = Yes 0 = No
IsSingleUser	0	1 = Yes 0 = No
HasBackupChecksums	0	1 = Yes 0 = No
IsDamaged	0	1 = Yes 0 = No
BeginsLogChain	0	1 = Yes 0 = No
HasIncompleteMetaData	0	1 = Yes 0 = No
IsForceOffline	0	1 = Yes 0 = No
IsCopyOnly	0	1 = Yes 0 = No
FirstRecoveryForkID	03DE5437-1E27-4885-9011-91CFED12338A
ForkPointLSN	NULL
RecoveryModel	FULL
DifferentialBaseLSN	NULL
DifferentialBaseGUID	NULL
BackupTypeDescription	Database
BackupSetGUID	62EB4399-C119-42C2-91F1-BF0FF19CB896
CompressedBackupSize	1453056

How many databases can be mirrored on a single instance of Microsoft SQL Server?

It is frequently asked by the DBAs or SystemAdmins or Customer that how many databases can be mirrored on a single instance of Microsoft SQL Server?

Answer of the above question is you can configure 10 databases for 32-bit operating system.On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

For 64-Bit Operating system you can mirror more than 10 databases depending on the number of processors and worker threads. Many company has deployed more that 10 Databases as mirrored.

2010 in review

Summary of how we did in year 2010



The Blog-Health-o-Meter™ reads Wow.

Crunchy numbers

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 140,000 times in 2010. If it were an exhibit at The Louvre Museum, it would take 6 days for that many people to see it.

In 2010, there were 81 new posts, growing the total archive of this blog to 220 posts. There were 46 pictures uploaded, taking up a total of 5mb. That's about 4 pictures per month.

The busiest day of the year was January 25th with 1709 views. The most popular post that day was Happy Republic Day.

Where did they come from?

The top referring sites in 2010 were google.co.in, google.com, en.wordpress.com, stackoverflow.com, and yandex.ru.

Some visitors came searching, mostly for sql server 2005 interview questions, republic day, dba responsibilities, error 18456, and system databases in sql server 2005.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

Happy Republic Day January 2009
6 comments

2

How to change SQL Server Instance Name? September 2008
4 comments

3

SQL Server 2005 Interview Questions May 2008
62 comments

4

SQL Server 2008 Active-Passive/Active-Active Cluster Installation October 2009
14 comments

5

DBA Roles and Responsibilities December 2008
46 comments

Articles Posted on MSSQLTips.com

Database Administration

Remote Desktop Error - The terminal server has exceeded the maximum number of allowed connections (4/9/2010) Remotely Shutdown, Restart or Log Off the local or network computers (5/17/2010) SQL Server Database Migration Checklist (2/5/2010) SQL Server restart validation checklist (7/30/2010) Start, Stop, Pause and Query Windows SQL Server Services Remotely (8/26/2010)

Dynamic Management Views\Functions

New DMF for SQL Server 2008 sys.dm_fts_parser to parse a string (12/3/2010)

Monitoring

Monitor, Start and Stop SQL Server services using xp_servicecontrol (6/11/2010)

Security

How to check SQL Server Authentication Mode using T-SQL and SSMS (12/24/2010) SQL Server Login Issue With Default Database (10/7/2010)

SQL Server Management Studio

Issues with Using Run As to Start SQL Server Management Studio with a Different Login (11/12/2010)

Tools

Insert Extended Characters into a SQL Server Table Using OSQL and T-SQL (3/2/2010)

KILL SQL Server 2000 Blocking SPID and Records it details

Problem
Today I got an email from one of my blog reader; they have an application developed with VB6.0 and SQL Server 2000. Application was developed long back and now their database size is increased as well. Due un-efficient coding they are getting blocking issue and stuck all their application transactions. He asked for writing a query which will execute by SQL Server Agent at every 1 minute and will KILL the culprit SPID. He also wants me to store the KILL transaction history as well.

As a solution I have written below query for him and which working fine now.

-- Create below table in master database  create table blkHistory ( SPID int, blocked int, killedSPID int, date datetime default getdate(), querytext varchar(8000) ) -- add below code in to job command text box declare @SPID as int, @blocked as int, @KilledSPID as int declare @querytext as varchar(8000), @sql nvarchar(400) select @SPID = spid,@blocked = blocked from sysprocesses where blocked <> 0 --select spid,blocked from sysprocesses where blocked <> 0 select @sql = 'KILL ' + cast (@blocked as nvarchar(100)) DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = @blocked SELECT @querytext = text FROM ::fn_get_sql(@Handle) EXECUTE sp_executesql @SQL If @SPID > 0 begin insert into blkHistory(SPID,blocked,KilledSPID,querytext) values (@SPID,@blocked,@blocked,@querytext) end --you can use below query to retrieve datafrom blocking history select * from master..blkHistory