Sans SQL

Welcome to SansSQL...

noreply@blogger.com (Sandesh Segu) — Fri, 05 Nov 2010 21:31:11 PDT

SansSQL is an SQL server blog which is dedicated for SQL Server related topics, tips and tricks. In this Blog I will also be posting about Business Intelligence and Data Warehousing too. For further questions, suggestions, feedback or topics that you would like to post on this blog, feel free to contact me @

Replication Error - SQL Server replication requires the actual server name to make connection to the server

noreply@blogger.com (Sandesh Segu) — Wed, 11 Jan 2012 02:24:50 PST

Sometimes when we try to configuration relpication we might receive the below error which says that "SQL Server replication requires the actual server name to make connection to the server"

This can happen during the situations

When you are connected to the server in object explorer using the IP address
When the actual server host name hosting the SQL server database engine is changed

In the first case we can solve this issue by connecting to the server with the host name.

In the second case, to fix the issue we need to update the system catalogs by dropping and adding the server and restarting the SQL Server services. The updation of system catalogs can be done using the below queries.

USE master 
GO
EXEC sp_dropserver 'OldServerName'

USE master 
GO
EXEC sp_addserver 'NewServerName',local

Happy New Year 2012

noreply@blogger.com (Sandesh Segu) — Sun, 01 Jan 2012 20:16:51 PST

Wishing you all a very happy and prosperous new year.
A Special Query for you all on this new year day :)

SET NOCOUNT ON
Select CHAR(72) + CHAR(97) + CHAR(112) + CHAR(112) + CHAR(121) + 
  SPACE(1) +  
  CHAR(78) + CHAR(101) + CHAR(119) +
  SPACE(1) +
  CHAR(89) + CHAR(101) + CHAR(97) + CHAR(114) +
  SPACE(1) +
CHAR(50)+CHAR(48)+CHAR(49)+CHAR(50) AS 'Surprise'
SET NOCOUNT OFF

Granting Read-Only and administrative access to Central Management Server (CMS)

noreply@blogger.com (Sandesh Segu) — Sun, 04 Dec 2011 17:30:01 PST

In one of my previous post, I had discussed about the Central Management Server and how to Register it.
In this post, I will be telling how to give read-only access and a Administrative access to an existing CMS.

Grating the Read-Only and Administrative access to CMS is very simply and the activity includes adding the right database users to right groups in msdb database.

Granting Read-Only access:

USE [msdb]
GO
CREATE USER [DBUserName] FOR LOGIN [LoginName]
GO
EXEC sp_addrolemember N'ServerGroupReaderRole', N'DBUserName'
GO

Granting Administrator access:
This access is usually give to the DBA's

USE [msdb]
GO
CREATE USER [DBAUserName] FOR LOGIN [DBALoginName]
GO
EXEC sp_addrolemember N'ServerGroupAdministratorRole', N'DBAUserName'
GO

Give access to a non sysadmin user to run Profiler

noreply@blogger.com (Sandesh Segu) — Sat, 03 Dec 2011 20:18:47 PST

Sometimes as a DBA, you come across a situation where you need to give access to a non sysadmin user to run profiler on a particular SQL server.
If you try to run the profiler using the user who does not have sysadmin access then you will get the below error.

Here is the solution how to grant access to a non sysadmin user to run profiler.
Using Query:

-- To Grant access to a Windows Login
USE master;
GRANT ALTER TRACE TO [Domain\WindowsLogin]

-- To Grant access to a SQL Login
USE master;
GRANT ALTER TRACE TO [SQL User]

Using SSMS:

Expand the Server in object Explorer
Expand "Security" folder and then "logins"
Right-Click on the login to which you need to give access and then go to "Properties" of that login
Go to "Securables" Tab
Select the server you want to add the permission
In the "Permission for <Server Name>" block, click on "Grant" check box for "Alter Trace" and click "OK"
Once this is completed, the permission should appear in the "Effective" Tab

Note: The Granter should be a sysadmin user.

Convert Rows to Column using COALESCE() function

noreply@blogger.com (Sandesh Segu) — Fri, 25 Nov 2011 08:25:09 PST

Using the below query we can convert the rows to column sperated by a delimiter.
In the query I am using ';' as the delimiter and you can change the delimiter of your choice by replacing ';'.
Data from Table:

Query:

Use AdventureWorks2008R2
GO
DECLARE @eMailList nvarchar(max) 

SELECT @eMailList = COALESCE(@eMailList + ';', '') + 
   CAST(eMail AS nvarchar(max))
FROM Employees

Select @eMailList as eMailList

Output of the above Query:

Microsoft SQL Server 2012 Release Candidate 0 (RC0) - Available for Download

noreply@blogger.com (Sandesh Segu) — Fri, 18 Nov 2011 06:39:47 PST

Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence.
To read more and download Microsoft SQL Server 2012 RC0 click here.

Backup Database to multiple locations simultaneously - Mirror Backups

noreply@blogger.com (Sandesh Segu) — Tue, 27 Sep 2011 08:20:32 PDT

Database backup is one the regular activity a DBA would perform. Some times you might come across a situation where in you need to backup the database to different location. When I say backup database to different locations, it means that a copy of backup file needs to be placed on a different location as well and this is different from the Split Backups.

This is can be achieved by different methods,

Take backup and then copy to multiple location
Take backup of the same database multiple times pointing to different locations
Use "MIRROR TO" Option in the Backup command

Using the option "MIRROR TO" is very simple, you just need to mention "MIRROR TO" and "WITH FORMAT" options in the normal BACKUP DATABASE Statement and you are done. The backup database statement with these two options will take the backup of the same database to multiple locations at the same time.

This option "MIRROR TO" is introduced in SQL Server 2005 and this works only in SQL Server 2005 Enterprise Edition and later versions.

This can be used for all backup types and the Maximum number of "MIRROR TO" clauses that you can specify is three.

Example:

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\AdventureWorks_Full.bak'

MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Full.bak'

WITH STATS=10, FORMAT

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\AdventureWorks_Differential.bak'

MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Differential.bak'

WITH STATS=10, DIFFERENTIAL, FORMAT

BACKUP LOG AdventureWorks

TO DISK = 'C:\Backup\AdventureWorks_log.trn'

MIRROR TO DISK = 'C:\Mirror\AdventureWorks_log.trn'

WITH STATS=10, FORMAT

When it comes to restoring the database, we can use either of the backup copies to restore or recover the database.

Hide an Instance of SQL Server Database Engine

noreply@blogger.com (Sandesh Segu) — Thu, 15 Sep 2011 21:03:12 PDT

In one of my previous post "List of SQL Server instances currently installed in your network", I explained how to get the list of SQL server instances installed in your network.
Sometimes you might require an SQL Server instance to be not exposed to others due to some security reason. This can be done by using the "Hide" option available for an SQL Server instance.
Before Hiding an Instance:

Steps to Hide an Instance of SQL Server Database Engine

Go to "SQL Server Configuration Manager"
In the Left Pane Expand "SQL Server Network Configuration"
Now Right Click on "Protocols for <ServerName>" and go to Properties for the server you need to hide.
In the "Flags" Tab, change the option to "Yes" for "Hide Instance"
Click on "Apply" and "OK"
Now re-start the SQL Server Service for this Instance and you are done. This instance from now will be hidden.

After Hiding an Instance:

Get last backup details of all databases in a server

noreply@blogger.com (Sandesh Segu) — Thu, 01 Sep 2011 20:55:17 PDT

This stored procedure give you the information about latest backups happened on all databases in a server.
This SP works for SQL server 2005 and up.

Create Proc sp_BackupDetails

DECLARE @BackupDetails table

([Server Name] nvarchar(500),

[Database Name] nvarchar(500),

[Last Full Backup] nvarchar(500),

[Last Differential Backup] nvarchar(500),

[Last Log Backup] nvarchar(500),

[Last File or filegroup Backup] nvarchar(500),

[Last Differential file Backup] nvarchar(500),

[Last Partial Backup] nvarchar(500),

[Last Differential Partial Backup] nvarchar(500)

)

DECLARE @DBName nvarchar(500)

Declare DBName Cursor for

Select name from sys.databases

Open DBName

Fetch Next from DBName into @DBName

While @@fetch_status = 0

BEGIN

Insert into @BackupDetails

select @@ServerName as [Server Name]

,SDB.name AS [Database Name]

,(select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='D') AS [Last Full Backup]

,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='I') AS [Last Differential Backup]

,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='L') AS [Last Log Backup]

,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='F') AS [Last File or filegroup Backup]

,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='G') AS [Last Differential file Backup]

,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='P') AS [Last Partial Backup]

,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='Q') AS [Last Differential Partial Backup]

from sys.databases SDB

where SDB.name =@DBName

Fetch Next from DBName into @DBName

END

Close DBName

DEALLOCATE DBName

Select * from @BackupDetails

Usage:

Exec sp_BackupDetails

Results:

Denali CTP 3 - Show\Hide Results Pane keyboard shortcut not working - Ctrl+R not working

noreply@blogger.com (Sandesh Segu) — Thu, 01 Sep 2011 11:15:31 PDT

Recently I installed Denali CTP 3 version and was writing a code which is coming next in this blog. During that time I attempted to hide the results pane using the keyboard shortcut Ctrl+R and was surprised that the keyboard shortcut Ctrl+R was not working. When I investigated further I found that that the keyboard shortcut was not assigned to the command "Hide Results Pane".

To fix this issue or to assign the keyboard shortcut to the command "Hide Results Pane", follow the below steps.

In a SSMS window, go to "Tools" menu and choose "Options".
In the left pane of "Options" window, expand "Environment" and then click on "Keyboard".
In the right pane, Search for the command "Window.ShowResultsPane" Under "Show Commands Containing:" section and select that command
Now, Under "Shortcuts for selected command:" section choose "SQL Query Editor" from the dropdown list of "Use new shortcut in:" sub-section.
In the "Press shortcut keys:" sub-section place the cursor and press the keys "Ctrl" followed by "R"
Click on "Assign" button and click "OK" to exit the "Options" menu and start using the keyboard shortcut "Ctrl+R" to Hide\Show Results Pane.

Looping through SQL Servers using SSIS or Dynamically build connection to multiple SQL Servers

noreply@blogger.com (Sandesh Segu) — Wed, 09 Nov 2011 06:43:28 PST

Consider you are giver a task of gathering information from multiple servers. What are the different ways you can automate this?

Create linked servers in each server pointing to your central repository
Create Separate SSIS package in each servers which loads data to your central repository
Create one SSIS package with multiple data sources and duplicate the tasks for each data source.
Create one SSIS package which dynamically builds connection to each server and does the data loading tasks.

If you choose an option between 1 and 3 , then you will have to do some extra work for gathering the server information when there are new servers added to your environment.

If you choose the 4th option then also you will have some extra work but it is very small and easy when compared to the first 3 options. Here you need to just add the new Server Names to the table and that is all and everything else will be taken care by the SSIS package.

In this post I am going to explain how to create a SSIS package which loops through multiple SQL server and gathers information by building the connection to those servers dynamically.

Open “Microsoft Visual Studio”
Create a new “Integration Services Project”
Create a SSIS Package
Create 2 Connection Managers, 1 for the source (which will be set dynamically for each iteration of the loop) and the other for destination (which will remain constant, in other terms your Central Repository)
Store All your Server Names in a table preferably in the Central Repository (Destination Connection)
Add 2 variables

a. Name: ConnectionVariable (A name of your choice)

Scope: Package

Type: Object

Value: System.Object

b. Name: ServerName (A name of your choice)

Scope: Package

Type: String

Value: A Valid Server Name

Add an "Execute SQL Task" with SQL Statement like "select FullName from dba..tbl_ListOfServers".
Set the result set to Full Result Set.
On the Result Set page, Add a Result
Set Result Name "0" and assign it to your Object variable (In our case it is “ConnectionVariable”).
Now add a “Foreach Loop Container”
Connect it from the “Execute SQL Task”
Inside the “Foreach Loop Container” , add the required “Data Flow Task”
Now Right-Click on the “Foreach Loop Container” and click on “Edit”
Now Go to “Collection” page and Set “Enumerator” to “Foreach ADO Enumerator” and “Enumerator Configuration ” to the Object Variable(In Our Case “ConnectionVariable”) and Set “Enumeration Mode” to “Rows in the First Table”
Now go to the “Variable Mapping” page and Choose the “String Variable” (In our case it is “ServerName”) and set the Index=0
Now, in the “Data Flow Task” add a “OLE DB Source” (Dynamic Connection) and connect it down to a “OLE DB Destination” (which will be your Central Repository).
Now Select the Source Connection Manager (In Our Case it is “Source”) and Right-Click on this Source and choose “Properties”.
Expand the “Expressions” Option and click on the browse button (…)
Now in the “Property Expression Editor”, Choose the property “ServerName” and click on browse button (…). Now choose the String Variable (in our case “ServerName”) and Drag and Drop this variable into the “Expression” box and click “OK” and “OK”.

Now when the package runs, the “Execute SQL Task” will read the list of servers and stored in the table you specified and the “Foreach loop” will iterate over each record in that table, running the “Data Flow Task” each time while each time, the ServerName property of the Source Connection in the data flow will get a new value.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

Finding space usage of database files

noreply@blogger.com (Sandesh Segu) — Sun, 07 Aug 2011 06:12:38 PDT

DBA's are required to watch the space usage of database files in order to take preventive measures of future failures with respect to database full issues.
This will be usually required when there is a bulk activity happening on a database.
To do this make use of the below stored procedure.
Create this SP in a database and execute it in regular intervals to get the latest status of the database files.
This can be used to view the space usage of all the database files or for a particular threshold value.

Use master

Create proc sp_SpaceUsageReport (@Threshold int=80)

CREATE TABLE tempdb..SpaceUsage (

DatabaseName nvarchar(100)

,LogicalFileName nvarchar(500)

,FileType nvarchar(10)

,PhysicalFileLocation nvarchar(500)

,[FileSize (MB)] float

,[SpaceUsed (MB)] float

,[FreeSpace (MB)] float

,[% Used] AS 100-(([FileSize (MB)]-[SpaceUsed (MB)])/[FileSize (MB)])*(100)

,[% Free] AS (([FileSize (MB)]-[SpaceUsed (MB)])/[FileSize (MB)])*(100) )

If (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion')))) = 8

BEGIN

EXEC sp_MSforeachdb 'USE [?];

INSERT INTO tempdb..SpaceUsage(DatabaseName

,LogicalFileName

,FileType

,PhysicalFileLocation

,[FileSize (MB)]

,[SpaceUsed (MB)]

,[FreeSpace (MB)])

SELECT DB_NAME() AS DatabaseName

,name AS LogicalFileName

,FileType = CASE WHEN FILEPROPERTY(name,''IsLogFile'')=0 THEN ''Data File'' WHEN FILEPROPERTY(name,''IsLogFile'')=1 THEN ''Log File'' END

,filename AS PhysicalFileLocation

,CONVERT(float,ROUND(size/128.000,2)) AS [FileSize (MB)]

,CONVERT(float,ROUND(FILEPROPERTY(name,''SpaceUsed'')/128.000,2)) AS [SpaceUsed (MB)]

,CONVERT(float,ROUND((size-FILEPROPERTY(name,''SpaceUsed''))/128.000,2)) AS [FreeSpace (MB)]

FROM dbo.sysfiles

ORDER BY FileType '

END

If (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<> 8

BEGIN

EXEC sp_MSforeachdb 'USE [?];

INSERT INTO tempdb..SpaceUsage(DatabaseName

,LogicalFileName

,FileType

,PhysicalFileLocation

,[FileSize (MB)]

,[SpaceUsed (MB)]

,[FreeSpace (MB)])

SELECT DB_NAME() AS DatabaseName

,name AS LogicalFileName

,FileType = CASE WHEN type_desc =''Rows'' THEN ''Data File'' WHEN type_desc =''LOG'' THEN ''Log File'' END

,physical_name AS PhysicalFileLocation

,CONVERT(float,ROUND(size/128.000,2)) AS [FileSize (MB)]

,CONVERT(float,ROUND(FILEPROPERTY(name,''SpaceUsed'')/128.000,2)) AS [SpaceUsed (MB)]

,CONVERT(float,ROUND((size-FILEPROPERTY(name,''SpaceUsed''))/128.000,2)) AS [FreeSpace (MB)]

FROM sys.database_files

ORDER BY FileType'

END

if (Select COUNT(*) from tempdb..SpaceUsage where [% Used]>@Threshold)>0

Begin

/* -- Enable this Content if you want to send email.

DECLARE @table NVARCHAR(MAX),@Subject Nvarchar(500) ;

Set @Subject='[SQLAlert] Database Files Space Threshold exceeded Report from ' + CAST(@@SERVERNAME as nvarchar)

SET @table =

N'<H1>Threshold Value for this Report is '+CAST(@Threshold AS nvarchar)+' Percentage. </H1>' +

N'<table border="1">' +

N'<tr><th>DatabaseName</th><th>FileType</th><th>PhysicalFileLocation</th><th>FileSize (MB)</th><th>% Used</th></tr> ' +

CAST ( ( Select td=DatabaseName, '',td=FileType, '',td=PhysicalFileLocation,'',td=CAST([FileSize (MB)] as nvarchar),'',td=CAST([% Used] AS nvarchar) from tempdb..SpaceUsage where [% Used]>@Threshold

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name='DatabaseMail', --Change to your Profile Name

@recipients='sandeshsegu@SansSQL.com', --Put the email address of those who want to receive the e-mail

@subject = @Subject,

@body = @table,

@body_format = 'HTML' ;

select * from tempdb..SpaceUsage where [% Used]>@Threshold

End

DROP TABLE tempdb..SpaceUsage

To send an email of this report you need to

Configure Database Mail option. To configure Database mail option, follow this post.
Uncomment the below content in the SP.

/* -- Enable this Content if you want to send email.

DECLARE @table NVARCHAR(MAX),@Subject Nvarchar(500) ;

Set @Subject='[SQLAlert] Database Files Space Threshold exceeded Report from ' + CAST(@@SERVERNAME as nvarchar)

SET @table =

N'<H1>Threshold Value for this Report is '+CAST(@Threshold AS nvarchar)+' Percentage. </H1>' +

N'<table border="1">' +

N'<tr><th>DatabaseName</th><th>FileType</th><th>PhysicalFileLocation</th><th>FileSize (MB)</th><th>% Used</th></tr> ' +

CAST ( ( Select td=DatabaseName, '',td=FileType, '',td=PhysicalFileLocation,'',td=CAST([FileSize (MB)] as nvarchar),'',td=CAST([% Used] AS nvarchar) from tempdb..SpaceUsage where [% Used]>@Threshold

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name='DatabaseMail', --Change to your Profile Name

@recipients='sandeshsegu@SansSQL.com', --Put the email address of those who want to receive the e-mail

@subject = @Subject,

@body = @table,

@body_format = 'HTML' ;

Usage of this SP:
This SP expects a parameter called @Threshold
If you specify the @Threshold=0 then, this SP will give space usage details of all the database files.

Exec sp_SpaceUsageReport @Threshold=0

If you specify the @Threshold=80 then, this SP will give space usage details of those database files which exceeds the threshold 80 percent.

Exec sp_SpaceUsageReport @Threshold=80

T-SQL Query to find the list of Indexed Views in a Database

noreply@blogger.com (Sandesh Segu) — Sun, 24 Jul 2011 01:52:53 PDT

Here is the query which can be used for getting the list of Indexed views in a Database.

USE <DatabaseName>

GO

SELECT * FROM sys.objects

WHERE type='V'

and OBJECTPROPERTY(object_id,'IsIndexed')=1

SQL Server Migration Checklist

noreply@blogger.com (Sandesh Segu) — Thu, 09 Jun 2011 09:27:10 PDT

Are you migrating your SQL Servers?
Here is a quick checklist for your SQL Server Migration.

Build your New Server, Install SQL Server and required updates and keep the server ready for migration
Stop Application Service(s).
This is to ensure that the no applications are connected to the Databases during the migration process.
Change the Properties of the databases that are part of Migration to "Read-only".
This is to ensure that the data modification is not happening by any other sources.
Take a FULL backup of all the User databases that are involved in the Migration Process.
Move the backups to the destination server or a Shared location, then restore them to the appropriate drives on the destination.
Change the compatibility level of the databases (Optional)
Do this if the applications connecting to these databases are independent of the database compatibility level.
Transfer logins using SSIS (Transfer Logins Task) or using "sp_help_revlogin"
More information about sp_help_revlogin is at http://support.microsoft.com/kb/246133
Check for Orphaned Users in the databases and Fix them (if Any)
Update Usage on the migrated Databases
Update Stats on the migrated Databases
Re-Index or Re-Organize Indexes on the migrated Databases
Transfer Jobs using SSIS or manually create them
Build Maintenance plans (if Any)
Recompile database objects if required
Move or rebuild SSIS or DTS packages (if Any)
Create Alerts and Operators (if Any)
Setup High Availability Options (if Any Like Replication, LogShipping, Mirroring)
Test the High Availability options that were setup in the previous step
Point the Application(s) to new Server and start the Application Service(s)
Test the Application(s)

SQL Server 2008 Service fails to start after Service Pack Installation

noreply@blogger.com (Sandesh Segu) — Tue, 07 Jun 2011 07:45:20 PDT

Some times you may find that the SQL Server Service is not starting after applying a service pack and when you check in the Event Viewer you find the below message.

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

SQL Server Setup creates a database with the data file name temp_MS_AgentSigningCertificate_Database.mdf during the installation process and if the SQL Server setup is not able to create that database in the default data path then the above error is returned as it is not able to find the path.

To fix this issue.

Go to Registry editor, To open this, go to "Run" and type "regedit" and click "ok"
First go to this path and make sure that the path in the key SQLDataRoot exists. If not then give a valid path to this key.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\Setup
Then go to this path and make sure that the path in the keys "BackupDirectory", "DefaultData" and "DefaultLog" exists. If not then give a valid path to these keys.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\MSSQLServer
Now you should be able to start the SQL Server Service without any issues.

Once the Service is started, verify the SQL Server, databases and others to make sure everything is fine.

Also verify if the Service pack or the CU is installed correctly.

Unable to start mail session (reason: No mail profile defined) - Message in Error Log

noreply@blogger.com (Sandesh Segu) — Tue, 31 May 2011 20:41:31 PDT

There will be many different messages that will be logged in the Error Log of SQL Server. Among them you might also find the below message some times.

Date 6/1/2011 8:18:27 AM
Log SQL Server Agent (Archive #2 - 6/1/2011 8:18:00 AM)

Message
[098] SQLServerAgent terminated (normally)

This is the most common message that will be logged when there is no "Mail Session" defined for the SQL Server Agent Alert System.

To Fix this or to make this message disappear in the Error Log, you have to enable "Mail Session" and re-start the SQL Server Agent.

To Do this,

Connect to the Server
Right Click on the "SQL Server Agent" and go to "Properties"
Then Go to "Alert System" Tab
Make sure the check box "Enable Mail Profile" is checked
Click OK to exit
Re-start the SQL Server Agent Service

Now if you again look into the Error Log you will not find this message anymore after re-starting the Agent Service.

SQL Server 2011 - Zoom Query Window and Results pane - New Feature

noreply@blogger.com (Sandesh Segu) — Mon, 18 Apr 2011 20:03:49 PDT

SQL Server 2011 comes with lots of new features and here is one such new feature.
SQL Server 2011 has introduced zooming of Query window and results pane.

Query Window - Normal

Query Window - Zoomed

Results Pane - Normal

Results Pane - Zoomed

T-SQL Query to find the SQL Server Protocols Status

noreply@blogger.com (Sandesh Segu) — Thu, 07 Apr 2011 07:39:47 PDT

Here is a T-SQL Query that I have developed to get the status of the SQL Server Protocols.
This query reads data from the registry.

DECLARE @InstanceName nvarchar(50)

DECLARE @value VARCHAR(100)

DECLARE @value_Out VARCHAR(100)

DECLARE @RegKey_InstanceName nvarchar(500)

DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))

CREATE TABLE #SQLServerProtocols

(ProtocolName nvarchar(25),

Value nvarchar(10),

Data bit)

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8

BEGIN

SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey_InstanceName,

@value_name = @InstanceName,

@value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Sm'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'Enabled'

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'DisplayName',

@value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Np'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'Enabled'

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'DisplayName',

@value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'Enabled'

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'DisplayName',

@value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Via'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'Enabled'

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @RegKey,

@value_name = 'DisplayName',

@value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

END

SELECT ProtocolName, IsEnabled=CASE WHEN Data=1 THEN 'Enabled'
ELSE 'Disabled' END FROM #SQLServerProtocols

DROP TABLE #SQLServerProtocols

Download this script from here.

Page Level Restoration

noreply@blogger.com (Sandesh Segu) — Sat, 26 Mar 2011 02:22:15 PDT

Sometimes you might come across a situation where a particular Page of a SQL Server database gets corrupted due to various reasons. In such cases only that corrupted page can be recovered using a backup.
To explain how to recover a page from the backup, I will first need to corrupt a page on a database.
By using the below method I am going to simulate the page corruption and then recover using the backup taken before corruption.
First I am going to create a database by name "TestPageLevelRestore" and Set its recovery Model to FULL.

USE master;

CREATE DATABASE TestPageLevelRestore

( NAME = TestPageLevelRestore,

FILENAME = 'D:\TestPageLevelRestore.mdf',

SIZE = 10)

LOG ON

( NAME = TestPageLevelRestore_log,

FILENAME = 'D:\TestPageLevelRestore_log.ldf',

SIZE = 5MB) ;

Print 'Database TestPageLevelRestore Created'

ALTER DATABASE TestPageLevelRestore SET RECOVERY FULL

Print 'Recovery Model of database TestPageLevelRestore has been changed to FULL'

Now Create a table and insert data into that table.

Use TestPageLevelRestore

CREATE TABLE [Shift](

[ShiftID] tinyint IDENTITY(1,1) NOT NULL,

[Name] nvarchar(50) NOT NULL,

[StartTime] datetime NOT NULL,

[EndTime] datetime NOT NULL,

[ModifiedDate] datetime NOT NULL,

CONSTRAINT [PK_Shift_ShiftID] PRIMARY KEY CLUSTERED ([ShiftID] ASC)

)

Print 'Creation of Table "Shift" Completed'

SET IDENTITY_INSERT [Shift] ON

INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (1, N'Day', '1900-01-01 07:00:00.000', '1900-01-01 15:00:00.000', '1998-06-01 00:00:00.000')

INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (2, N'Evening', '1900-01-01 15:00:00.000', '1900-01-01 23:00:00.000', '1998-06-01 00:00:00.000')

INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (3, N'Night', '1900-01-01 23:00:00.000', '1900-01-01 07:00:00.000', '1998-06-01 00:00:00.000')

SET IDENTITY_INSERT [Shift] OFF

Print 'Data Insertion to table "Shift" Completed'

Now take a FULL backup of the database

BACKUP DATABASE TestPageLevelRestore TO DISK='D:\TestPageLevelRestore_FullBackup.bak' WITH STATS=10

Print 'Full Backup Completed'

After the backup is completed, get the list of index ID's from which you can choose one to corrupt

--To get the list of index ID's from which you can choose one to corrupt
Use TestPageLevelRestore

Select * from sys.indexes where OBJECT_NAME(object_id)='Shift'

Now get the list of pages in that index.

--To get the list of pages

DBCC IND ('TestPageLevelRestore', 'Shift',1)

Now you can get the page level details using the below query

-- To display the contents

DBCC TRACEON (3604);

--TO get the page level data details

DBCC PAGE('TestPageLevelRestore',1,147,3);

For corrupting a particular page using a hex editor, you need to get the offset value, to obtain the offset value of a page simply multiply the PageID with 8192

--Get the Offset Value. This can be obtained by multiplying the page ID with 8192.

--Once you get the result copy the result and set the database to offline

SELECT 147*8192 AS [OffSetValue]

Once you get the offset Value, just copy it and take the database Offline.

USE MASTER

ALTER DATABASE TestPageLevelRestore SET OFFLINE

Print 'Database TestPageLevelRestore is set to Offline. Now Open the TestPageLevelRestore.mdf file in the hex editor and press ctrl+g to go the page where the index data is located.

Choose Decimal and paste the offset value.

once you go to the location, then manuplate the value and save the file and exit hex editor.

After manuplating data bring database online.'

Now Open the data file of the database "TestPageLevelRestore.mdf" file in the hex editor and press ctrl+g to go the page where the index data is located.

Then choose Decimal option and paste the offset value.
Once you go to the location, then manuplate the value and save the file and exit hex editor.

You can now see that I have edited the page and saved the file.

Once you edit the file and saved it, exit the Hex Editor and bring back the database to Online state.

USE MASTER

ALTER DATABASE TestPageLevelRestore SET ONLINE

Print 'Database TestPageLevelRestore is set to Online'

Now try to read data from the table and you will get error which states that the read failed at page (x:xxxx)

--Select the data and you will get error stating that the read failed at page (x:xxxx)

USE TestPageLevelRestore

Select * from shift

select * from sys.master_files where DB_NAME(database_id)='TestPageLevelRestore'

So this means that the page is now corrupted. By this the simulation of page corruption is completed.
Now we need to start looking on how to recover the page using page level restore.
Before we start the recovery process, we need to backup the tail of the log.

USE master

BACKUP LOG TestPageLevelRestore TO DISK = 'D:\TestPageLevelRestore_log.bak' WITH INIT, NORECOVERY;

After backing up the tail log, restore the corrupted page using the below command.

Restore DATABASE TestPageLevelRestore Page='1:147' FROM DISK='D:\TestPageLevelRestore_FullBackup.bak'

After the page restoration is completed, Restore the tail log backup.

RESTORE LOG TestPageLevelRestore FROM DISK = 'D:\TestPageLevelRestore_log.bak';

Now you have restored the corrupted page from a good backup and this can be verified by selecting the data from the table and you will be able to retrieve the data.

USE TestPageLevelRestore

Select * from shift

The complete demo script that I have used in this post can be downloaded from here.

Automating SQL Server Express Backups

noreply@blogger.com (Sandesh Segu) — Sat, 19 Mar 2011 20:36:30 PDT

As we all know that many of the third party applications uses SQL Server Express Edition to store their backend data. And also these application will be used in Live environments which requires backing up of the databases to recover data during a disaster.
Since there is no SQL Server agent in Express Edition, we cannot schedule SQL Backups or any other DB Maintenance activities using SQL Scheduler. So for this purpose, we have to make use of the windows scheduler.
For Automating the backup process, i have developed the below query which can be used for multiple purpose and can be scheduled using windows scheduler as well as SQL Agent.

Script:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BackupDatabases]') AND type in (N'P', N'PC'))

BEGIN

PRINT 'Stored Procedure "sp_BackupDatabases" already exists in the database. Dropping the SP to create a newer Version.'

DROP PROCEDURE [dbo].[sp_BackupDatabases]

END

CREATE PROC sp_BackupDatabases (@BackupDBType nvarchar(10)='Help', @DBName nvarchar(max)=NULL, @BackupPath nvarchar(max)=NULL )

Author: Sandesh Segu

Website: http://www.SansSQL.com

SET NOCOUNT ON

DECLARE @DateTime nvarchar(25)

SET @DateTime=LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),120),':',''),'-',''),' ',''),12)

IF @BackupDBType not in ('ALL','System','User','Specific','Help')

BEGIN

RAISERROR ('Incorrect Parameter Value Passed. @BackupDBType Parameters should be ''ALL'',''System'',''User'',''Specific''',16,1)

END

IF @BackupDBType<> 'HELP' and @BackupPath IS NULL

BEGIN

RAISERROR ('Specify the path to backup databases. @BackupPath must be specified',16,1)

END

IF (RIGHT(@BackupPath,1))<>'\'

BEGIN

Select @BackupPath=@BackupPath+'\'

END

ELSE

BEGIN

Select @BackupPath=@BackupPath

END

IF @BackupDBType='Help' AND @DBName IS NULL AND @BackupPath IS NULL

BEGIN

Print 'Usage of this Stored Proc can be in any of the below format.'

Print '-------------------------------------------------------------------------'

Print '1. To Backup All Databases in a instance'

Print ' Exec sp_BackupDatabases @BackupDBType = ''ALL'', @BackupPath = ''C:\Backup''' + Char(10)

Print '2. To Backup only System Databases in a instance'

Print ' Exec sp_BackupDatabases @BackupDBType = ''System'', @BackupPath = ''C:\Backup''' + Char(10)

Print '3. To Backup only User Databases in a instance'

Print ' Exec sp_BackupDatabases @BackupDBType = ''User'', @BackupPath = ''C:\Backup''' + Char(10)

Print '4. To Backup specific (One) Database(s) in a instance'

Print ' Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks'', @BackupPath = ''C:\Backup''' + Char(10)

Print '5. To Backup specific (more than One) Database(s) in a instance'

Print ' Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks,master,msdb'', @BackupPath = ''C:\Backup'''

Print '-------------------------------------------------------------------------'

END

IF exists (select * from sys.objects where name='BackupDatabases')

DROP TABLE BackupDatabases

CREATE TABLE BackupDatabases

(DBName nvarchar(100),

DatabaseID int,

[BackupStatement] nvarchar(max))

IF @BackupDBType='ALL' AND @DBName IS NULL AND @BackupPath IS NOT NULL

BEGIN

INSERT INTO BackupDatabases

SELECT name,database_id, '' FROM sys.databases WHERE name <>'tempdb'

END

IF @BackupDBType='System' AND @DBName IS NULL AND @BackupPath IS NOT NULL

BEGIN

INSERT INTO BackupDatabases

SELECT name,database_id, '' FROM sys.databases WHERE name in ('master', 'model','msdb')

END

IF @BackupDBType='User' AND @DBName IS NULL AND @BackupPath IS NOT NULL

BEGIN

INSERT INTO BackupDatabases

SELECT name,database_id, '' FROM sys.databases WHERE database_id>4

END

IF @BackupDBType='Specific' AND @DBName IS NULL

BEGIN

RAISERROR ('Specify the Database Name(s) to Backup. @DBName must be specified.',16,1)

END

IF @BackupDBType='Specific' AND @DBName IS NOT NULL AND @BackupPath IS NOT NULL

BEGIN

DECLARE @DelimiterPos int

-- Find the first comma

SET @DelimiterPos = PATINDEX( '%,%', @DBName)

-- If a delimiter was found, @DelimiterPos will be > 0.

WHILE @DelimiterPos > 0

BEGIN

-- Insert the value between the start of the string and the first delimiter, into the table variable.

INSERT INTO BackupDatabases

SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((SUBSTRING(@DBName, 1, @DelimiterPos -1)))) AS nvarchar ))

-- Trim the string of the first value and delimiter.

SET @DBName = SUBSTRING(@DBName, @DelimiterPos +1, LEN(@DBName) - @DelimiterPos)

-- Look for the next delimiter in the string.

SET @DelimiterPos = PATINDEX( '%,%', @DBName)

END

INSERT INTO BackupDatabases

SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((@DBName))) AS nvarchar))

END

UPDATE BackupDatabases SET BackupStatement= 'Print ''Backup of Database '+DBName+' Started''; Backup Database ['+DBName+'] TO DISK='''+@BackupPath+DBName+'_Backup_'+@DateTime+'.bak'' WITH INIT, STATS=10'

Print 'The Requested database(s) are being backed up to the location "'+@BackupPath+'"'

WHILE (Select COUNT(*) from BackupDatabases)>0

BEGIN

DECLARE @BackupStatement nvarchar(max)

SELECT @BackupStatement= [BackupStatement] FROM BackupDatabases

Exec sp_executesql @BackupStatement

Delete from BackupDatabases where [BackupStatement]=@BackupStatement

END

DROP TABLE BackupDatabases

SET NOCOUNT OFF

Usage:

Usage of this Stored Proc can be in any of the below format.
-------------------------------------------------------------------------
1. To Backup All Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'ALL', @BackupPath = 'C:\Backup'

2. To Backup only System Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'System', @BackupPath = 'C:\Backup'

3. To Backup only User Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup'

4. To Backup specific (One) Database(s) in a instance
   Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks', @BackupPath = 'C:\Backup'

5. To Backup specific (more than One) Database(s) in a instance
   Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks,master,msdb', @BackupPath = 'C:\Backup'

To Delete the old backups I use the below query.

DECLARE @TwoDaysOld VARCHAR(50)

Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR)

Select @TwoDaysOld

Exec master.dbo.xp_delete_file 0,N'C:\Backups\',N'bak',@TwoDaysOld

This will delete any .bak files in the given location which are older than 2 days.

So after creating the sp_BackupDatabases stored proc, if we want to schedule the backups using windows scheduler than we have create a batch file (.bat file) which will be called in the windows scheduler at the scheduled time.

sqlcmd -S(local)\SQLEXPRESS -E -Q"DECLARE @TwoDaysOld VARCHAR(50) Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR)
Exec master.dbo.sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup'
Exec master.dbo.xp_delete_file 0,N'D:\Backups\',N'bak',@TwoDaysOld"

Scripts can be downloaded from the below locations

T-SQL Query to find list of Instances Installed on a machine

noreply@blogger.com (Sandesh Segu) — Sun, 13 Mar 2011 01:11:10 PST

Here is a T-SQL Query to find the list of instances Installed on a machine.

DECLARE @GetInstances TABLE

( Value nvarchar(100),

InstanceNames nvarchar(100),

Data nvarchar(100))

Insert into @GetInstances

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

@value_name = 'InstalledInstances'

Select InstanceNames from @GetInstances

Create Table #GetInstances

( Value nvarchar(100),

InstanceNames nvarchar(100),

Data nvarchar(100))

Insert into #GetInstances

EXECUTE xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

@value_name = 'InstalledInstances'

Select InstanceNames from #GetInstances

drop table #GetInstances

Both the queries are almost similar, except for that first query uses a table variable and the second one uses temporary table.

T-SQL Query to find the date when was DBCC CHECKDB Last run

noreply@blogger.com (Sandesh Segu) — Sat, 12 Mar 2011 22:20:19 PST

As a Database Administrator, we know the importance of DBCC CHECKDB and will run this command to check the logical and physical integrity of all objects in the specified database.
When DBCC CHECKDB is run on a database, it does the following actions

Runs DBCC CHECKALLOC on the database
Runs DBCC CHECKTABLE on every table and view in the database
Runs DBCC CHECKCATALOG on the database
Validates the contents of every indexed view in the database
Validates Service Broker data in the database

Before you start the DBCC CHECKDB on a database you might want to know the date and time when this command was lust run by you or someone else from your team.
Prior to SQL Server 2005, this data was not getting logged in the system. But in SQL Server 2005 onwards this data is getting logged in the system and using the below script you can find the date and time when DBCC CHECKDB was last run on a database.

CREATE TABLE #DBInfo (

Id INT IDENTITY(1,1),

ParentObject VARCHAR(255),

[Object] VARCHAR(255),

Field VARCHAR(255),

[Value] VARCHAR(255)

)

CREATE TABLE #Value(

DatabaseName VARCHAR(255),

LastDBCCCHeckDB_RunDate VARCHAR(255)

)

EXECUTE SP_MSFOREACHDB'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'');

INSERT INTO #Value (DatabaseName) SELECT [Value] FROM #DBInfo WHERE Field IN (''dbi_dbname'');

UPDATE #Value SET LastDBCCCHeckDB_RunDate=(SELECT TOP 1 [Value] FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) where LastDBCCCHeckDB_RunDate is NULL;

TRUNCATE TABLE #DBInfo';

SELECT * FROM #Value

DROP TABLE #DBInfo

DROP TABLE #Value

The Script can be downloaded from here.

ERROR - Cannot set a credential for principal 'sa'

noreply@blogger.com (Sandesh Segu) — Sat, 26 Feb 2011 22:43:56 PST

You may sometimes receive an error which says "Cannot set a credential for principal 'sa'" while you are trying to alter the login 'sa' in SQL Server 2005 or SQL Server 2008.

This happens because, the SQL Server tries to drop any credential that is mapped to your login credentials while altering and because your credential cannot be set to "sa" login, you will receive this error.

FIX:
Fix for this error is very simple. Just make sure that the option "Map to Credential" is checked in the "General" tab of the Login Properties Page.

Reference: http://support.microsoft.com/kb/956177

SQL Server 2011 (Denali) - First Look

noreply@blogger.com (Sandesh Segu) — Tue, 22 Feb 2011 04:59:19 PST

First look of SQL Server Denali after Installation.

Splash Screen

Login Window

SQL Server Management Studio