Welcome onboard - Microsoft SQL Server: 2006

Tuesday, December 26, 2006

SysComments v/s sys.sql_modules - Returns different result for same query

In SQL 2000 we used to query system table SysComments to get the SQL Definition of Objects like SP,Views,Trigger,Functions etc. It seems that the counterpart in SQL 2005 is sys.sql_modules. But when i ran same query for SysComments and sys.sql_modules, the rows affected was different(No. of Rows). Out of curiosity, i thought it need a postmortem. When i ran the following query in my R&D database , the SysComments returned 138 rows and sys.sql_modules returned 84 rows.

SELECT obj.name , mod.definition,obj.object_id,len(mod.definition) FROM sys.sql_modules mod INNER JOIN sys.objects obj ON mod.object_id = obj.object_id WHERE obj.type = 'p'

SELECT o.name, m.Text ,o.Object_ID, m.id,len(m.Text) FROM syscomments m INNER JOIN sys.objects o ON m.id = o.object_id WHERE o.type = 'p'

You know why this different result for same query; it because in SQL Server we have new Data Type called Nchar/nvarchar (Max) etc. So in SQL 2005 the SQL Definition is stored in nvarchar(Max) data type where as in SQL Server 2000 it is nvarchar(4000). So you will have different result when you have a large stored procedure definition.

Thursday, December 21, 2006

Maximum Number of Instances by Edition in a Clustered and Nonclustered Configuration

SQL Server Editions Maximum Instances Allowed

Enterprise, Developer, -50
and Evaluation Editions

Enterprise, Developer, - 25
Evaluation Editions in a clustered configuration

Standard, Workgroup, - 16
and Express Editions

Standard, Workgroup, - 16
and Express Editions in a clustered configuration

SQL Server Instance-Aware Components SQL Server Component

SQL Server Component - Instance-aware?
--------------------------------------------------------------------------

SQL Server Database Services -Yes
(includes SQL Server Agent, Full-Text Search, and Replication)

SQL Server Analysis Services -Yes

SQL Server Reporting Services - Yes

SQL Server Integration Services - No

Notification Services -Yes
(instances are created
after installation; not during setup. )

Workstation components and tools - No

Export/Import Surface area Configuration

In larger environment you may need to have same Surface Area Configuration on many instance of SQL Server. In SQL Server 2005 , SAC command line utility make it very easier for you. To change the SAC setting using GUI is always time consuming and tedious. SAC Command line utility allow you to export and import surface area configuration to and from a file.
Here you go….

Suppose you have a master server and in that master you have configured the surface area as per the company security policy. Now you have another 50 instances to which you want to apply same configuration.

(a) Go to command prompt of Master server and change directory to “C:\Program Files\Microsoft SQL Server\90\Shared”
(b) Now you will have C:\Program Files\Microsoft SQL Server\90\Shared> prompt
(c) Give the following command
sac out C:\sac_MasterSetting.xml S MasterComputerName

Now you will have a xml file in C:\ with all you master server surface area configuration setting. Next step is to apply this to other instances

From the same directory, you can give the following command

sac in C:\sac_ MasterSetting.xml S Computer_1
sac in C:\sac_ MasterSetting.xml S Computer_2
sac in C:\sac_ MasterSetting.xml S Computer_3
……
sac in sac_ MasterSetting.xml S Computer_50

or what you can do is create a batch file and keep it in VSS for better management

Connect to Remote SQL Server Configuration Manager

From Start --> SQL Server 2005 --> SQL Server Configuration Manager you can only connect to Local SQL Server instances. To connect to Remote instance’s SQL Server Configuration Manager there are two methods :-
(a) From SQL Server Management Studio

1. Open SQL Server Management Studio.
2. Click on Cancel if the Connect to Server dialog pops up.
3. In Management Studio, click on the View menu and select Registered Servers (if it is not visible)
4. In the Registered Servers window, right-click on Database Engine, select New and then Server Registration. This will launch the New Server Registration dialog.
5. In the New Server Registration dialog, enter the name of the remote computer in the Server name field, click on Test and then Save. If the Database Engine on the remote computer requires SQL Server authentication, you have to change Authentication to SQL Server Authentication to enter the username and password.
6. Back in the Registered Servers window in SQL Server Management Studio, right-click on the remote computer and select SQL Server Configuration Manager to configure the remote computer

(b) From MyComputer

1. Right button on “My computer” and select Manage and you will get Computer Management Window
2. Right click on Computer Management and select Connect to another computer.
3. You will have a "Select Computer Window"
4. Give the Remote Computer Name and press OK
5. Now, you will have the remote computer's Computer Management Window
6. Click on Services and Applications7. Select SQL Server Configuration Manager

Difference in SQL Server Profiler of 2005 and 2000

The main enhancement in SQL 2005 is integration with Analysis Server. SQL Profiler 2005 allows users to look at current activity happening inside Analysis Server.

Performance Counter Value from System Table

To see the current performance counter value
Select *from sys.sysperfinfo

Error Severity Level Ranges in SQL Server

Severity level Description

0-10 Indicate informational messages and non-severe errors
11-16 Indicate errors that can be corrected by the user
17-19 Indicate software errors that cannot be corrected by the user
20-25 Indicate system problems and fatal errors

Database Engine Error Severities
http://msdn.microsoft.com/en-us/library/ms164086.aspx

SQL Server DMO and SMO

SQL Server 2000 Enterprise Manager uses SQL-DMO COM-Based API to manage SQL Server.
SQL Server 2005 replaces SQL-DMO with .NET based object libraries
(a) SQL Server Management Objects (SMO)
(b) Replication Management Objects (RMO).
You can't use SMO on VB6 or .NET 1.1 (2003). it is only available for .NET 2.0 and later

Saturday, December 16, 2006

FAQ : How to Parameterize TOP clause in SQL Server 2000

CREATE PROCEDURE spTopRecords
@Top int
as
-- set how many records to return
SET ROWCOUNT @Top
SELECT * FROM
SET ROWCOUNT 0

Note : In SQL Server 2005 top can be used with varaiable

declare @i int
set @i=10
Select top(@i) * from sysobjects

Tuesday, December 12, 2006

Configure SQL 2005 for Remote Access

Configure SQL 2005 for remote access in a Windows Firewall enabled Box

While connecting to a SQL Server instance installed in a Windows Firewall enabled box using web browser we may get an error saying “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.” The cause of this error is that the web browser is not able to interact with the server because the port is blocked by the Firewall. It is also to be noted that the behavior differ from Named Instance to Default Instance. Generally, when we debug this error there are few steps to be followed :-

For Default Instance :-

(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vi) Give the SQL Server Default Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK
(You can do this for the SQL Service instead of mentioning the port number, by using Add Program in the Exception Tab)
For Named Instance
When we use named instance generally the port number will be different from 1433(as 1433 is the default port for SQL Server). For named instance, you have to mention the port number in the connection string. If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. Then you have to follow the above mentioned step for the corresponding ports. Apart from that you should make sure that the SQL Browser Service is running and the UDP Port 1434 is added in the exception. Steps are as follows
(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vii) Give the SQL Server Named Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK

(c) Now, you should add an exemption for UDP port 1434
(i) In Windows Firewall, click the Exceptions tab, and then click Add Port
(ii) Give your Instance Name
(ii) Give the Port No as 1434
(iii) Select UDP
(iv) Click OK
(d) Go to the SQL Server Configuration Manager and Make sure that the SQL Browser is running

Monday, December 11, 2006

Implementation and usage of DDL Trigger and its saga in SQL Server 2005

A new feature was introduced in SQL Server 2005 called DDL Trigger, which fires in response to a variety of Server scope or Database scope, Data Definition Language (DDL) events. These events can generally be fired by any DDL statements like Create, Alter, Drop, or DCL (Data Control Language) like Grant, Deny or Revoke.

DDL trigger can be created at two levels:•

Database level (like Create, Alter, Drop table, Procedure, Function or any database scoped objects)
• Server Level trigger (like Create, Alter, Drop Databases/Login/ Certificate )
Problem
In the earlier versions, tracking DDL activities on a database or on a server was not straight forward. Running profiler or trace was the only solution. Consider, you have a group of developers responsible for all the Development activities like Creation or Modification of Stored Procedures. Typically, this group is added to DB_DDLAdmin database fixed role. Here, to track which developer has modified a particular stored procedure was not possible, since all users have the DDLAdmin privilege. To resolve these type of issues DDL trigger is introduced in SQL Server 2005.

DDL Triggers is useful for the following purpose:• Auditing
• Regulating database or server operation
• Securing the database or server from malpractice
• Issue an alert when a DDL event occurs in Database or Server

Permission
To create a DDL trigger with a server scope (ON ALL SERVER) requires CONTROL SERVER permissions on the server. To create a DDL trigger within the database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

DDL Event Group

The Figure 1 lists the DDL event groups that can be used to run a DDL trigger or an event notification and also the Transact-SQL statements they cover. You can use a top level Event group instead of mentioning all the events in the trigger. For example, using DDL_TABLE_VIEW_EVENTS event group instead of mentioning specified events like Create_Table,Alter_Table,Drop_Table,Create_view, Alter_View,Drop_View

Figure 1

How DDL Trigger works?

EventData()
EVENTDATA function returns XML data which contains the information about server or database events. EVENTDATA is called when an event notification fires and it can also be used inside the body of a DDL or logon trigger.

Database scoped DDL Trigger.

Any DDL statement, which alters the schema of an object inside the database, will be tracked in this type of trigger. (Please note that few events are not tracked (read as bug) and this will be fixed in SQL Server 2008 version). Generally, the DDL Statement at the database level are Create, Alter, Drop tables , Procedure ,Views ,Functions , Users, Role , Certificate, etc. (For all the database level events , please refer to http://msdn.microsoft.com/en-us/library/ms189871.aspx ). Either you can hard code the specific event to be tracked inside the trigger (like Create_Table,Create_Procedure etc) or use DDL_DATABASE_LEVEL_EVENTS event group to track all the database scoped events. Whether you need to track all the events or only specific events will depend upon the requirements. Also there are event groups like DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS which are also used in EventNotification. Mostly all the events are applicable for DDL Triggers as well.

Step by step in Brief

(a) Create a table to store the logged data. Here in this scenario the table created is DDLDatabaseScopedAuditTrail.
(b) Create DDL Trigger which logs the information into this table. Which event needs to be tracked will depend upon the requirements. EventData() function returns a whole lot of information. In this example, let us track Eventtype,Login,Database User name and TSQL which caused the trigger to fire and the fired Datetime. For a better understanding the output of EventData() function to an XML type column has been captured However, this may not be needed in the actual senario. Select only the necessary events as logging any extra information is always a performance hinderance.
This scenario, dipicts tacking three kind of events:
• Trigger to track all events .
• Trigger to track a group of events
• Trigger to track a specific event

Code Block for Database scoped DDL Trigger.

-- Database for DDL Trigger Demo
Create Database DDLTriggerTest
Go

-- Create Log table

If Object_ID('DDLAuditTrail') is not null
BEGIN
DROP TABLE DDLAuditTrail
END

CREATE TABLE dbo.DDLDatabaseScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)
Go

--Trigger to track all Database Events using EventGroup

Create TRIGGER db_DDLtrg_Audit_All_Database_Scoped_Events
ON DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()
INSERT dbo. DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go

-- Trigger to track specific group of Database events using Database Event Group

Create TRIGGER db_DDLtrg_Audit_All_TABLE_VIEW_EVENTS
ON DATABASE
FOR
DDL_TABLE_VIEW_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go

-- Trigger to track very specific Database events

Create TRIGGER db_DDLtrg_Audit_Specific_Event
ON DATABASE
FOR
CREATE_Table, Create_Procedure
AS
SET NOCOUNT ON
If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go

DDL Trigger for Server Scoped Objects Event tracking

Server scoped events can be tracked using a server scoped DDL trigger. Here also , you can track all the events using DDL_SERVER_LEVEL_EVENTS group event or you can be more specific tracking only CREATE_DATABASE and CREATE_LOGIN.

Steps in Breif :-(a) Create a table to store the logged data. Here the table created is DDLServerScopedAuditTrail. It is better to create this table in the Master or in any specific user database other than the business related database..
(b) Create DDL Trigger which logs the information into this table. Events depend upon the requirements. Here three types of events have been detailed.

DDL Trigger to track Server Scoped events

Use Master
GO
If Object_ID('DDLServerScopedAuditTrail') is not null
BEGIN
DROP TABLE DDLServerScopedAuditTrail
END

CREATE TABLE dbo.DDLServerScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)

Go

--Trigger to track all Server Events using EventGroup

CREATE TRIGGER Svr_DDLtrg_Audit_All_Server_events
ON All Server
FOR
DDL_SERVER_LEVEL_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)

End
Go

Trigger to track specific group of Server events using Server Event Group

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SECURITY_EVENTS
ON All Server
FOR
DDL_SERVER_SECURITY_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
End
GO

-- Trigger to track very specific Server events

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SPECIFIC_EVENT
ON All Server
FOR
CREATE_DATABASE ,CREATE_LOGIN
AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)

End

Limitation

• DDL triggers will not fire in response to events that affect local or global temporary tables and stored procedures.
• DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers
Known issues of DDL Trigger in SQL Server 2005
• Truncate statement is not tracked by any event
• SP_Rename event is not tracked

For more information, please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124493
For an issue reported when you alter a Replicated Table, Please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331367

SQL Server 2008
In SQL Server 2008, there are few more events added like sp_Rename is tracked using RENAME Event.

System objects for DDL Triggers

You can use the sys.triggers catalog view to see the database scoped DDL triggers. The parent_class column in this catalog view has a value of Zero which indicates that it’s a DDL Trigger. You can use the sys.server_triggers catalog view to get a list of server scoped DDL triggers. The sys.trigger_events and sys.server_trigger_events catalog views contain a row for each time a database is scoped and a server scoped trigger is fired, respectively.

Query to find out the Events and other details of DDL Trigger

SELECT Name, s.type_desc SQL_or_CLR,is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON s.object_id = e.object_id

Where can I find DDL trigger in SSMO?

Database level DDL triggers are listed in the Programmability -- >> Database Triggers folder in Object Explorer. The server-level triggers are listed under the Server Objects -- >>Triggers folder in Object Explorer.

Figure 2

Drop DDL Trigger

The ON DATABASE or ON ALL SERVER clause should be used with DROP TRIGGER to drop a DDL trigger. All the other operations are similar to the DML trigger ie. ALTER TRIGGER to modify a DDL trigger definition, DISABLE TRIGGER to disable a DDL trigger and ENABLE TRIGGER to enable a DDL trigger.

Summary
DDL Trigger without any doubt is one of the best features of SQL Server 2005. There are known issues like few commands are not tracked by any events. In spite of these limitations, it gives a very good value add to the product in day to day activities. Now-a-days, auditing is obvious in any application and DDL trigger fits in there. We can make a complete automated auditing system using the DDL trigger and Reporting Service of SQL Server 2005.

Sunday, December 3, 2006

Monitoring Memory Requirement

System Monitor is used to monitor memory usage in the server. In addition to System Monitor Tool you can use new Dynamic Management views also to collect data about SQL Server Memory.

Steps for Analyzing Memory Requirement

(a) Check how much physical memory is installed on the Box
(b) What other application/processes are consuming memory on the server
© Use System Monitor to capture the following counters
• Memory : Available Bytes :- it indicate how many bytes of memory are available
• Memory : Pages/Sec : Specifies how many pages must be read from the disk or written to the disk to resolve page faults
• SQLServer:BufferManager : This is very important counter , which will give you the Cache-hit ratio. This counter identifies the percentage of pages that were found in the buffer pool without read the disk. The value of this counter should be over 90%. High values indicate good cache usage and minimal disk access when searching for data
• SQLServer:MemoryManager:Total Server Memory :- Determines the amount of physical memory used by each instance of SQL Server
• Process:Working Set :- Gives the set of memory pages that have been recently accessed by the threads running in the process and can be used to determine how much memory SQL Server is using
• SQLServer:Buffer Manager:Page Life Expectance : It gives the average time spent by a data page in the data cache. A value of less than 3000 seconds indicates that SQL Server need more memory.

Dynamic Management Views used to collect data about SQL Server Memory Usage

• Sys.dm_exec_query_stats : Provides statistics on memory and CPU usage for a specific query.
• Sys.dm_exec_cached_plans : Gives a list of the query plans that are currently cached in mamory
• Sys.dm_os_memory_objects : Provides information about object types in memory, such as Memobj_Compile_Adhoc and Memobj_statement
• Sys.dm_os_memory_clerks returns the se of all memory clerks that are currently active in the instance of SQL Server.

Wednesday, November 22, 2006

Activity Monitor Auto Refresh

View refresh settings on the left side pane of the Activity Monitor dialog , will give u a window to setup a auto refresh period for the Activity Monitor. Be aware that it is not recommended on a production server.

Tuesday, November 21, 2006

Reuse of Schedule in SQL 2005

In previous versions SQL Server, the job schedule was tightly coupled to a specific job and could never be reused. In SQL Server 2005, a user can create a schedule and then link multiple jobs to it. However, other users cannot link to a shared job schedule for security reasons.

Monday, November 20, 2006

Remove Secondary Transaction Log File from Database

At time your database may have multiple Transaction log physical files and you may want to remove one of the Transaction log file from the database. Then run the following script

DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file

Eg.
DBCC SHRINKFILE ('test1_log',EMPTYFILE)
alter database test remove file Test1_log

Note : The primary data file and log file cannot be removed.

Sunday, November 19, 2006

Dedicated Administrator Connection (DAC)

SQL Server 2005 provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
Only members of the SQL Server sysadmin role can connect using the DAC.
The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:.

SQL 2005 Online Index (Only Available in Enterprise Edition)

Index operations can now be performed online; users can still access the table data and use other indexes on the table while one index is being created, altered, or dropped. In SQL Server 2005 EE , you can create, rebuild, or drop indexes online. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations.
Eg.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD WITH (ONLINE = ON);

Online Restore in SQL 2005

Restoring data while the database is online is called an online restore. Online restore is supported only on SQL Server 2005 Enterprise Edition. In this edition, a file, page, or piecemeal restore is online by default. Online Restore applies to only those database which have multiple files or file groups. The database can be online when the primary filegroup is online and all other secondary filegroups are offline. By default the restore command in SQL 2005 EE is Online only.

SQL Server 2005- Piecemeal Restores

In SQL Server 2005, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one of more secondary filegroups. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

All editions of SQL Server 2005 support offline piecemeal restores. In SQL Server 2005 Enterprise Edition, a piecemeal restore can be either online or offline.

Thursday, November 16, 2006

FAQ : Table row count from all the tables in the database

1. Simple Method using undocumented stored procedure sp_msforeachtable

CREATE TABLE #TableCount (tablename sysname, ROWCNT int)

INSERT INTO #TableCount

EXEC sp_msforeachtable 'select ''?'', count(*) from ?'

SELECT * FROM #TableCount where rowcnt>0

DROP TABLE #TableCount

Method 2 : Using SysIndexes and Sysobjects system tables

SELECT O.[name]
,I.rowcnt
FROM sysobjects O
JOIN sysindexes I
ON O.[id] = I.[id]
WHERE O.xtype = 'U'
AND O.status > 0
AND I.indid <2

Method 3. Using Cursors

Create proc spCountNoofRecords
as
Begin
SET NOCOUNT ON
create table #RecCount(TableName varchar(300),NoofRecords int)
Declare @RecCount1 int,@TableName varchar(300),
@SqlStatement nchar(2000),@ParmDefinition NVARCHAR(500)
DECLARE cRecCount CURSOR FOR
select Name from Sysobjects where xtype='u' order by name
OPEN cRecCount
FETCH NEXT FROM cRecCount INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @SqlStatement=N'Select @RecCount=count(*) from ['+@TableName +']'
SET @ParmDefinition = '@RecCount int OutPut'
EXECUTE sp_executesql @SqlStatement, @ParmDefinition,
@RecCount = @RecCount1 OUTPUT;
insert into #RecCount select @TableName,@RecCount1
FETCH NEXT FROM cRecCount INTO @TableName
END
CLOSE cRecCount
DEALLOCATE cRecCount
Select *From #RecCount
End

Saturday, November 11, 2006

Multiple Activity Monitor dialogs

You can have multiple activity monitor in SQL Server 2005 and you can also filter on Database/Process/User/BlockType etc. It really a wonderful enhanced feature from SQL 2000. Activity Monitor provides monitoring of blocking and lock information in the same interface, with options such as filtering and auto-refresh.

Friday, November 10, 2006

RESTORE VERIFYONLY Different behavior in SQL 2000 and SQL 2005

You have a RESTORE VERIFYONLY option in both SQL 2000 and SQL 2005. But it has different behavior in SQL 2000 and SQL 2005. In 2000 what VERIFYONLY do is, it will check the media header and then returned a success or an error. The entire backup set could be invalid, and every other sector on the media could be bad. But aslong as the media header was intact, it would return a success. So this behavior effectively rendered this command worthless in SQL 2000.I would tell in sql 2000 you should not use this command. However, SQL Server2005 now performs the necessary checks, so you should execute this command every time you perform a backup.

Friday, November 3, 2006

Grant SQL Profiler Run Privilege to Login

In SQL 2000 , If you want to grant permission to run profiler to user /login , then the only way was to add that login to SysAdmin Serverrole. And no DBA would like to do that and ultimately DBA has to run profiler for this user. This was always a administrative hindrance. In SQL 2005, this long standing demand has been granted by Microsoft. Now what you can do is u can Grant Alter Trace permission to any login who want to run profiler and no need to add this login to SysAdmin Server role.

Grant Alter Trace LoginName

Significance of Tempdb in SQL Server

Tempdb is instance wide Temporary workspace that SQL Server uses for various operations. Tempdb is created from Model Database whenever the SQLSERVER service is started/restarted. Tempdb has simple recover model. It is a rule of thumb that you should never keep tempdb on the same location where your user database is stored. It is always recommended that tempdb should locate in a high performing disk subsystem (preferably RAID 10 or RAID 0). It is also to be configured with enough size for better performance, so that system no need to do the space allocation dynamically. You can also create multiple tempdb datafiles to minimize on IAM and SGAM and there by improve the performance.

Very common operation which consume tempdb are appended below :-

(a) Store explicitly created Temp tables , Table variables, Stored procedure, cursors etc.
(b) Stores Private and Global variable used throughout the instance
(c) Worktables associated with ORDER BY, GROUP BY, CURSOR
(d) Many System administration activities like DBCC commands uses TempDB
(e) If you have choose Snapshot Isolation Level then it uses Tempdb
(f) If your Report Server Uses Caching, then tempdb is used extensively
(g) If your have used SORT_IN_TEMPDB option while rebuilding indexes.

To relocate Tempdb Follow these steps

(a) Alter the file path by following command

Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
GO

(b) Restart the service and run sp_helpfile tempdb to confirm the above action

Refer :

Tempdb in Sql Server 2005 -http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Shrinking -- http://support.microsoft.com/kb/307487

Concurrency enhancements for the tempdb database --

http://support.microsoft.com/kb/328551

Monday, October 16, 2006

Change Service Broker identifier After Database Restore

When u want to create a copy of a Database using Restore/sp_Detach &sp_Attach command if the database is Service Broker Enabled, then you should change the Database UniqueIdentifier after restoration. Ie. Each database has a identifier used for routing Service Broker messages to that database. In restore and sp_Attach/Detach method both database will have same identifier, so erratic routing may occur. In such scenario after restoration of the database, you must change the Identifier by the following command

ALTER DATABASE [DatabaseName] SET NEW_BROKER

Thursday, September 21, 2006

Starting Database Engine in Single User Mode - SQL Server 2005

(a) Start --> SQL Server 2005 --> SQL Server Configuration Manager
(b) Select the Database Engine Service which u want to Start in Single User Mode and press Right Button
© You will have a SQL Server Properties window
(c) select Startup Parameter
(d) press “End” button of your key board
(e) Type ;-m
(f) Restart the Service

Sunday, September 3, 2006

SQL Browser Service in SQL 2005

SQL Server Browser Service in SQL 2005 provides information about the SQL Server 2005 instances installed on a machine. It provides the instance name and version number for each instance of the database engine and analysis server engine installed on a system. Its counter part in SQL Server 2000 is SQL Server Resolution Service which operates on UDP Port 1434 and provides information about the SQL Server 2000 instances on a particular machine. SQL Server Browser service is instance insensitive and it gets installed with the first instance of a machine. When SQL Server Browser Service starts, it accumulate information about Port number, named pipes of all instances installed on the system from windows registry. The it begins to listen network request on UDP port 1434.

It is not necessary to have SQL Server browser running , if it is not running you can still connect to a instance of a system by providing protocol , ServerName/,PortNumber or named pipe directly in the connection string Eg. TCP:ServerName,1456. If you have only one default instance then SQL Server Browser does not have any significance, because default instance always listens to 1433.

Tuesday, July 18, 2006

FAQ : @@Servername and SQLServername result does not match

OR
Why @@Servername and Serverproperty(‘Servername’) is giving different name.
OR
Why my default sql server instance name does not match the machine name?

If you rename the Physical Machine (OS) which was already installed with an SQL Server default instance the ServerProperty() function will show you the lastest name and the @@Servername function will show you the old name. Because SQL Server Setup sets this @@Servername variable to the computer name during installation. To synchronize the result of @@Servername with the new name, you need to drop the old servername and add the new server name using sp_DropServer and sp_AddServer

Steps are as follows

--Check Servername
select @@Servername,Serverproperty('Servername')

-- (a) Drop old server name
Exec Sp_dropServer ‘OldServername’

-- (b) –Add new servername
Exec Sp_addServer ‘NewServerName’, 'Local'

-- (c) See the changes
Exec sp_Helpserver

-- (d) Check Servername
select @@Servername,Serverproperty('Servername')

Refer :
http://msdn.microsoft.com/en-us/library/aa197071(SQL.80).aspx

Monday, July 3, 2006

SQL Server Compatibility Level

Compatibility level of a database specifies SQL Server version compatibility and can be set to SQL Server 7.0(70), SQL Server 2000 (80) or SQL Server 2005 (90). When set to other than SQL Server 2005 (90), the compatibility makes the database behavior compatible with that version of SQL Server. By default it is set to 90. other options are given for primarily to address Upgradation problem, if any. The compatibility level can not be set using ALTER DATABASE Command. You should use sp_dbcmptlevel system stroed procedure to change compatibility.

Syntax : sp_dbcmptlevel datbasename , [70/80/90]

Sunday, July 2, 2006

New Features of SQL Server 2005

These are the few new fetures of sql server 2005. There are many but, we can consider these are the key features

. SQL Server Management Studio (SSMS)
. SQL Server Configuration Manager
. Common language runtime (CLR)/.NET Framework integration
. Dynamic management views (DMVs)
. System catalog views
. SQL Server Management Objects (SMO)
. Dedicated administrator connection (DAC)
. SQLCMD
. Database Mail
. Online index and restore operations
. Native encryption
. Database mirroring
. Database snapshots
. Service Broker
. SQL Server Integration Services
. Table and index partitioning
. Snapshot isolation
. Business Intelligence (BI) Development
. Query Notification
. Multiple active result sets
. New SQL Server data types
. SQL Server Management Studio (SSMS)
. SQL Server Configuration Manager
. Common language runtime (CLR)/.NET Framework integration
. Dynamic management views (DMVs)
. System catalog views
. SQL Server Management Objects (SMO)
. Dedicated administrator connection (DAC)
. SQLCMD
. Database Mail
. Online index and restore operations
. Native encryption
. Database mirroring
. Database snapshots
. Service Broker
. SQL Server Integration Services
. Table and index partitioning
. Snapshot isolation
. Business Intelligence (BI) Development
. Query Notification
. Multiple active result sets
. New SQL Server data types

SQL Server 2005 - Database Engine Enhancements

Several new database-specific enhancements have been added to SQL Server 2005. These changes are focused primarily on the database storage engine. The following are some of the most important enhancements:

. Instant file initialization—New or expanded database files are made available much faster now because the initialization of the file with binary zeros is deferred until data is written to the files.

. Partial availability—In the event of database file corruption, the database can still be brought online if the primary filegroup is available.

. Database file movement—You can now use the ALTER DATABASE command to move a database file. The physical file must be moved manually. This feature was available in SQL Server 2000, but it only worked on tempdb.In addition, many new table-oriented enhancements are available with SQL Server 2005. This includes features that define how the data in the tables will be stored in the database.

The following are two of the key enhancements:
. Large rows—SQL Server 2005 now allows for the storage of rows that are greater than 8060 bytes. The 8060-byte limitation that existed with SQL Server 2000 has been relaxed by allowing the storage of certain data types (such as varchar and nvarchar) on a row overflow data page.

. Stored computed columns—Computed columns that were calculated on-the-fly inprior versions can now be stored in the table structure. You accomplish this by specifying the PERSISTED keyword as part of the computed column definition.

SQL Server 2005 - T-SQL Enhancements

SQL Server 2005 provides many enhancements to the T-SQL language that allow you to improve the performance of your code and extend your error-management capabilities. These enhancements include improved error handling, new recursive query capabilities, and support for new SQL Server database engine capabilities. Some of the T-SQL enhancements are as follows:

. Ranking functions—SQL Server 2005 introduces four new ranking functions:
ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These new functions allow you to efficiently analyze data and provide ranking values to result rows of a query.

. Common table expressions—A common table expression (CTE) is a temporary named result set that can be referred to within a query, similarly to a temporary table. CTEs can be thought of as an improved version of derived tables that more closely resemble a non-persistent type of view. You can also use CTEs to develop recursive queries that you can use to expand a hierarchy.

. PIVOT/UNPIVOT operator—The PIVOT operator allows you to generate crosstab reports for open-schema and other scenarios in which you rotate rows into columns, possibly calculating aggregations along the way and presenting the data in a useful form. The UNPIVOT operator allows you to normalize pre-pivoted data.

. APPLY—The APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression.

. TOP enhancements—In SQL Server 2005, the TOP operator has been enhanced, and it now allows you to specify a numeric expression to return the number or percentage of rows to be affected by your query; you can optionally use variables and subqueries. You can also now use the TOP option in DELETE, UPDATE, and INSERT queries.

. DML with results—SQL Server 2005 introduces a new OUTPUT clause that allows you to return data from a modification statement (INSERT, UPDATE, or DELETE) to the processing application or into a table or table variable.

. Exception handling for transactions—Earlier versions of SQL Server required you to include error-handling code after every statement that you suspected could potentially generate an error. SQL Server 2005 addresses this by introducing a simple but powerful exception-handling mechanism in the form of a TRY...CATCH T-SQL construct.

Monday, June 12, 2006

test

Wednesday, February 1, 2006

Useful Knowledge Bases and Links