LessThanDot - Data Management

Use Filters in SSMS to only see the things that are of interest to you

SQLDenis — Sat, 06 Feb 2010 20:13:04 +0000

by SQLDenis

Now that all my server are SQL Server 2008 I use SSMS with SSMS Toolpack and Toad. I don't really use Query Analyzer anymore. The other day I found out that you can hide objects you don't want to see in SSMS by using filters.

Let's first look at some code. Create a new database named test, create a new schema named Denis and then add 3 tables to the dbo schema and 3 tables to the Denis schema. Just run the code below

Code: tsql

CREATE DATABASE test
go

USE test
go

CREATE SCHEMA Denis
go

CREATE TABLE Test1(id INT);
go
CREATE TABLE Test2(id INT);
go
CREATE TABLE Test3(id INT);
go

CREATE TABLE Denis.Test1(id INT);
go
CREATE TABLE Denis.Test2(id INT);
go
CREATE TABLE Denis.Test3(id INT);
go

Now in SSMS you will see the following if you expand the tables folder

What if I only want to see the tables that belong to the Denis schema? Here is how you can setup the filter. Right click on the tables folder, select Filter and then Filter settings.

That will pop up a window that looks like the image below

For the Schema property select equals for the operator and type Denis as value. Click OK and now you will see the following when you navigate to the tables folder.

If you want to remove the filter, just right click on the folder, select Filter and then Remove Filter.

If you work in a team and you created several tables that all start with Report then you can create a new filter and just show these objects. This way you don't need to see any tables that your team members created. Below is a screen shot of a filter like that.

Another cool feature is that you can filter on Creation Date, this gives you the ability to filter all that old Brownfield stuff that nobody maintains anyway because if you change one thing it will break everything else :-)

So are you using filters in SSMS?

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Original post blogged on LessThanDot.

Interesting UNION with ORDER BY behavior

SQLDenis — Fri, 05 Feb 2010 18:26:59 +0000

by SQLDenis

Here is something interesting to think about

Create this table and insert these 2 rows

Code: tsql

CREATE TABLE TableName(id INT, name VARCHAR(50))
INSERT TableName VALUES(1,'bla')
INSERT TableName VALUES(2,'bla2')

Now if you try to do something like this

Code: tsql

SELECT TOP 1 ID,Name
FROM TableName
ORDER BY Name
UNION ALL
SELECT 0,''

You will get the following error

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'UNION'.

Interestingly, this same code will work if you use it in the following matter

Code: tsql

SELECT TOP 1 *
FROM (
SELECT TOP 1 ID,Name
FROM TableName
ORDER BY Name
UNION ALL
SELECT 0,''
) X
ORDER BY ID DESC

This same code also works with Common Table Expressions

Code: tsql

WITH query AS (
SELECT TOP 1 ID,Name
FROM TableName
ORDER BY Name
UNION ALL
SELECT 0,'')

SELECT TOP 1
x.id,
x.name
FROM query x
ORDER BY x.id DESC

My assumption is that the optimizer disregards any order by in the subquery. Still I find this interesting because usually a query will work, you stick it inside another piece of code and it will complain about something

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Original post blogged on LessThanDot.

Read from your mirror in SQL Server

onpnt — Wed, 03 Feb 2010 14:13:02 +0000

by onpnt

This will primarily be an extension into the Wiki section on SQL Server Admin Hacks.

OLTP and Reporting

Reporting from OLTP (Online Transactional Processing) databases can be nothing short of difficult at times. The process of reporting of these types on databases can cause blocking, long wait times and in some severe cases, complete failure on the part of the high level operational requirements of the database. In the stage in which we consider how reporting will affect our databases and how to react proactively to the constant is during the architectural phases of your database server landscape. In regards to SQL Server, this means the feature set that you weigh in for each edition available to us.

A perfect example of editions in SQL Server that provide us the means to better handle our environmental variables and reporting is a comparison of Enterprise to the less feature packed editions. With the advent of SQL Server 2005 and the major overhaul of SQL Server as a database server in enterprise installations, snapshot abilities were introduced. Of course you get what you pay for as with anything. Snapshots are only available to date in Enterprise Edition of SQL Server. You may complain, but I’m all for the marketing of this. There must be a reason to step up to this level and the feature set is that reason. It is important to understand that the SQL Server engine itself does not change across the edition levels. SQL Server in my opinion is still at each edition, a true enterprise functioning landscape with the engine and will bring great benefits to most installations.

Getting to the solutions

Snapshots are the key in this article because they provide us with the ability to not only create views of data easily accessed at a point in time, but they give us the ability to read from a secondary database in mirroring.

Note: snapshots are just that, "snapshots". They do not give us a real-time active ability to read data as it is committed to the database.

Typically in most installations you will never be able to remove all reporting from OLTP databases. There is still business rules that require the user community to see the state of the data as it is at the time of report execution. This is also not a bad thing and with proper indexing and tuning, you can be very affective at limiting the foot print your reports and query executions leave on the database. Looking over my career, I came up with an 80/20 basis of reports on OLTP databases. This equates to, 80% point in time reporting to 20% real-time reporting.

Note: when discussing reporting needs with your business, you may find yourself in the predicament that the business thinks they need real-time reporting. Dig deep into the true business needs however and you may uncover and come to an agreement that point in time reporting successfully meets the requirements. This is one of the highest means of tuning active reports on OLTP installations.

There are other methods that should be noted to point in time reporting including but not limited to

Full Database Restores
Log Shipping
Replication (snapshot primarily)

These three that are mentioned have their advantages and disadvantages. Take all of them into your own planning stages as viable solutions.

In the following setup we will work through setting up a snapshot on a mirror to answer the main question of how we can take advantage of a mirror for reporting. This can be a powerful aspect to your reporting infrastructure and also give you expansion to the data that in early version was not available.

To fully show our lab and setup steps, I will be using the mirror that we setup in, Using mirroring to Reduce Migration Downtime (Part 1). Once we have the mirror setup, creating the actual snapshot from the secondary in the mirroring configure in a T-SQL statement.

Code: tsql

CREATE DATABASE NEEDTOMOVE_20100203072007 ON
(
NAME = NEEDTOMOVE,
FILENAME = 'C:\NEEDTOMOVE_20100203072007.ss'
)
AS SNAPSHOT OF NEEDTOMOVE

Once you have done this the snapshot is available as a read-only, point in time representation of the mirror. You can locate the snapshot in SSMS under the "database snapshots" tree located directly under the system databases section.

Now any read operation can be done on the snapshot supporting any reports that meet the needs of the data that has been captured.

Note: naming conventions of snapshots are typically database name joined with the point in time of the snapshot execution. This allows an easy way to maintain which snapshot holds the data you require.

It depends?

In the note I mentioned that the naming conventions typically use the date and time to show the time the snapshot was taken. In several of my installations this isn’t the case though. For reporting the data source can be very dynamic in nature but for many situations the data requirement can simply be the state the data was given a past time. In this case you can drop the snapshot and create it under the same name. This simplifies the needs in your reporting from platforms such as SQL Server Reporting Services.

Closing

What we went over may weigh heavily on asking for the added budget to your new or upgrading strategies for SQL Server. I hope this will help in justifying those added costs when you take the task of licensing on with your own company.

Original post blogged on LessThanDot.

Running static code analysis on SQL Server database with Visual Studio Team System for database architects

SQLDenis — Mon, 01 Feb 2010 01:35:26 +0000

by SQLDenis

To me it seems that Visual Studio Team System 2008 Database Edition is the stepchild of the Visual Studio family. Even in shops that have MSDN Universal/Ultimate subscriptions this version is just not used that much. Maybe it is that long name of this product, I still prefer DataDude. I would like to show you that if you do have licenses for this tool then you should use it because it has some great features. Today we will focus on static code analysis.

Before we start, make sure to grab Microsoft Visual Studio Team System 2008 Database Edition GDR R2
and install that on top of your Visual Studio Team System 2008 Database Edition. This install adds the following things to Visual Studio

In addition to providing support for SQL Server 2008 database projects, this release incorporates many previously released Power Tools as well as several new features. The new features include distinct Build and Deploy phases, Static Code Analysis and improved integration with SQL CLR projects.

Database Edition no longer requires a Design Database. Therefore, it is no longer necessary to install an instance of SQL Express or SQL Server prior to using Database Edition.
You can download Microsoft Visual Studio Team System 2008 Database Edition GDR R2 here http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Once you have everything installed, start up Visual Studio and create a new database project. You should see something similar to the pic below

Create a new database connection, you can do that by going to View-->Server Explorer. Under Data Connections add a new connection to your database that you want to connect to, follow the wizard and create the connection. Now I decided to run static code analysis against the aspnetdb database that is used with ASP.NET. If you want to follow along and use this same database, take a look at this post Setting up SQL Server with ASP.NET MVC to see how to set it up

Once your database is setup go to the Solution explorer, right click on the project and select Import Database Objects and Settings.

The following dialog will be shown

Pick your connection and then click start

The output will be similar to this

1/30/2010 11:26:40 AM Import of database schema has started.
1/30/2010 11:26:45 AM Adding all files to the project...
1/30/2010 11:26:46 AM Finished adding all files to the project.
1/30/2010 11:26:46 AM Done
1/30/2010 11:26:46 AM Import of database schema is complete.
1/30/2010 11:26:46 AM A summary of the import operation has been saved to the log file C:\SVN\InterrogateASP\InterrogateASP\Import Schema Logs\InterrogateASP_20100130042639.log.
1/30/2010 11:26:46 AM Press Finish to continue...

Here is what the solution explorer looks like, as you can see the folder hierarchy is very similar to the one in SSMS

Okay now we are ready to run our static code analysis, click on Data-->Static Code Analysis--Run

Here is the output from that, this creates 216 warnings, I have not pasted the whole output here because it is longer than this whole blog post and most warnings are the same but just for different objects. Here is just a small part and we will look at two of those procedures mentioned in this

Running Code Analysis...
Verifying project state...
Finished verifying project state.
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
ASPNET_APPLICATIONS_CREATEAPPLICATION.PROC.SQL(7,79)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_APPLICATIONS_CREATEAPPLICATION.PROC.SQL(24,15)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_CHECKSCHEMAVERSION.PROC.SQL(9,35)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(11,38)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(11,58)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(12,31)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(14,13)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_GETUSERSINROLES.PROC.SQL(9,75)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_GETUSERSINROLES.PROC.SQL(17,14)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_GETUSERSINROLES.PROC.SQL(23,32)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_ISUSERINROLE.PROC.SQL(10,75)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_ISUSERINROLE.PROC.SQL(20,31)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_ISUSERINROLE.PROC.SQL(27,31)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(10,64)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(52,32)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(60,83)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(86,32)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(94,83)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(102,35)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(102,47)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(108,23)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(108,36)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(108,56)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(110,7)Warning : SR0004 : Microsoft.Performance : A column without an index that is used as an IN predicate test expression might degrade performance.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(111,7)Warning : SR0004 : Microsoft.Performance : A column without an index that is used as an IN predicate test expression might degrade performance.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(118,8)Warning : SR0004 : Microsoft.Performance : A column without an index that is used as an IN predicate test expression might degrade performance.
ASPNET_WEBEVENT_LOGEVENT.PROC.SQL(44,9)Warning : SR0014 : Microsoft.Design : Data loss might occur when casting from Decimal to Decimal.
ASPNET_WEBEVENT_LOGEVENT.PROC.SQL(45,9)Warning : SR0014 : Microsoft.Design : Data loss might occur when casting from Decimal to Decimal.
Code analysis complete -- 0 error(s), 216 warning(s)

Now I was intrigued so I picked the aspnet_Applications_CreateApplication stored procedure from that list.

Code: tsql

USE [aspnetdb]
GO
/****** Object: StoredProcedure [dbo].[aspnet_Applications_CreateApplication] Script Date: 01/17/2010 15:51:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[aspnet_Applications_CreateApplication]
@ApplicationName NVARCHAR(256),
@ApplicationId UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

IF(@ApplicationId IS NULL)
BEGIN
DECLARE @TranStarted BIT
SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0

SELECT @ApplicationId = ApplicationId
FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
WHERE LOWER(@ApplicationName) = LoweredApplicationName

IF(@ApplicationId IS NULL)
BEGIN
SELECT @ApplicationId = NEWID()
INSERT dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
VALUES (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
END

IF( @TranStarted = 1 )
BEGIN
IF(@@ERROR = 0)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
END
END
END

As you can see it uses the LOWER function in this line

WHERE LOWER(@ApplicationName) = LoweredApplicationName

It actually tells you on which line and position this is actually used (ASPNET_APPLICATIONS_CREATEAPPLICATION.PROC.SQL(7,79)Warning) so this means line 7, position 79

Here is another stored procedure, this one uses the LOWER function and old style joins

Code: tsql

USE [aspnetdb]
GO
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 01/17/2010 15:53:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
@ApplicationName NVARCHAR(256),
@UserNames NVARCHAR(4000),
@RoleNames NVARCHAR(4000)
AS
BEGIN
DECLARE @AppId UNIQUEIDENTIFIER
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)

DECLARE @TranStarted BIT
SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END

DECLARE @tbNames TABLE(Name NVARCHAR(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles TABLE(RoleId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
DECLARE @tbUsers TABLE(UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
DECLARE @Num INT
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @Name NVARCHAR(256)
DECLARE @CountAll INT
DECLARE @CountU INT
DECLARE @CountR INT

SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END

INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
SELECT @CountR = @@ROWCOUNT

IF (@CountR <> @Num)
BEGIN
SELECT TOP 1 N'', Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END

DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1

WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END

INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

SELECT @CountU = @@ROWCOUNT
IF (@CountU <> @Num)
BEGIN
SELECT TOP 1 Name, N''
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)

IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(1)
END

SELECT @CountAll = COUNT(*)
FROM dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId

IF (@CountAll <> @CountU * @CountR)
BEGIN
SELECT TOP 1 UserName, RoleName
FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END

DELETE FROM dbo.aspnet_UsersInRoles
WHERE UserId IN (SELECT UserId FROM @tbUsers)
AND RoleId IN (SELECT RoleId FROM @tbRoles)
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END

So, as you can see it is pretty simple to use Visual Studio Team System 2008 Database Edition to perform static code analysis, the question is of course if you dare to run this against your own database?

I will be back with another post to explore some other things that this tool can do

*** Remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Original post blogged on LessThanDot.

Backup and copy warm-standby (log shipped) databases in SQL Server

onpnt — Wed, 27 Jan 2010 15:13:19 +0000

by onpnt

Most DR solutions include log shipping strategies. Log shipping (LS) is an extremely inexpensive solution for DR and also one I recommend. There is little learning curve to individuals just coming into the administration career for setting LS up and maintaining the flow.

One thing that will undoubtedly be asked of you at some point in time will be to make a backup of the warm-standby database for other purposes. Reasoning for this is commonly due to the geographical location of the standby database verses the online production environment. Often in order to move a backup that is larger in size, there will be several hours involved in the actual copy of the phsyical files and sometimes even days depending on the variables in place on the WAN.

When you go to make a backup of a database in standby, you will be presented with the following

The database "bah" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.

In the state the database is in, you will not be able to use normal methods to get this task done. Depending on the restore rate you have for LS, you could also break the LS stream and in the worst case scenario, cause you to reinitialize the entire LS plan. I’d like to show you a quick and easy solution to that though.

Note: This method is the quickest way I have found. As always with my work, I am open to others knowing better and more efficient methods. There is a break point below in the copy. It is a good idea to disable you LS plan while you perform this to prevent failed restore job.

Let’s setup a test database to play with. We’ll create, backup and restore to standby…

Code: tsql

CREATE DATABASE bah
GO
BACKUP DATABASE bah TO DISK = 'C:\bah.bak'
GO
RESTORE DATABASE [bah] FROM
DISK = N'C:\bah.bak'
WITH FILE = 1, STANDBY = N'C:\ROLLBACK_UNDO_bah.BAK',
NOUNLOAD, REPLACE, STATS = 10
GO

We should now have, “bah (Standby /Read-Only) listed in our database tree.

The regular Copy Database with either detaching or SMO is not ideal for us at this point. We can however take this database offline, grab the mdf, ndf’s and ldf’s to bring to the other instance we want to restore them to.

To do this we perform the following steps

Code: tsql

USE MASTER
GO
ALTER DATABASE bah SET OFFLINE

Now go to the default folder where the mdf and other files are located and copy/paste them to a local drive. I mentioned local drive because we need to do this as quickly as possible. If there is enough free space locally, it will be the fastest copy transmission. Otherwise at this stage it is crucial to ensure you have disabled the LS plans so no unwanted jobs attempt to access the database while it is offline.

Once you’ve copied the files off, run the following to bring the database back up

Code: tsql

USE MASTER
GO
ALTER DATABASE bah SET ONLINE

We should see the database has come back up in standby mode and log shipping can proceed as normal.

Next, we only need to attach the files to a new instance given the following commands or using SSMS attach/detach wizard.

Note: You cannot bring these files up on the same instance. This is due to being required to leave the database "as is" and with the same file names. When the database was taken offline, it was in standby mode and will be the same when attached again during the initial loading. Once on the new instance, you can bring the database into any state you require and move it back to the other instance is required.

On the secondary instance do the following steps

Code: tsql

CREATE DATABASE bah
ON
( NAME = bah_data,FILENAME = 'c:\restore\bah.mdf')
LOG ON
( NAME = 'bah_log',
FILENAME = 'c:\restore\bah_1.ldf' )
GO
ALTER DATABASE bah SET OFFLINE
GO
--Copy the bah files from our original standby database into the location you just created the
--new database to. in my case C:\restore\
--make sure you replace the files and that the file names, both data and logs are identical to the original
ALTER DATABASE bah SET ONLINE
GO

Now we can see already in the tree of object explorer that the database online and read-write state. There is no need to issue a restore to recovery or anything.

To verify, we can check the read only state in sys.databases

Code: tsql

SELECT is_read_only FROM sys.databases WHERE [name] = 'bah'

This will result in 0 meaning the database is read-write

We now have a backup of a warm-standby database that is used in a log shipping plan.

Original post blogged on LessThanDot.

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

SQLDenis — Fri, 22 Jan 2010 15:23:49 +0000

by SQLDenis

In order to figure out if you need more memory for a SQL Server you can start by taking a look at Buffer cache hit ratio and Page life expectancy.

Buffer cache hit ratio

Here is what Books On Line has to say about Buffer cache hit ratio
Buffer cache hit ratio
Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.

Basically what this means is what is the percentage that SQL Server had the data in cache and did not have to read the data from disk. Ideally you want this number to be as close to 100 as possible.

In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one counter is Buffer cache hit ratio and the other counter is Buffer cache hit ratio base. We divide Buffer cache hit ratio base by Buffer cache hit ratio and it will give us the Buffer cache hit ratio.
Here is the query that will do that, this query will only work on SQL Server 2005 and up.

Code: tsql

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Page life expectancy

Now let's look at Page life expectancy.
Page life expectancy is the number of seconds a page will stay in the buffer pool, ideally it should be above 300 seconds. If it is less than 300 seconds this could indicate memory pressure, a cache flush or missing indexes.

Here is how to get the Page life expectancy

Code: tsql

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND OBJECT_NAME = 'SQLServer:Buffer Manager'

What I currently get for the queries is a Page life expectancy of 470333 and the Buffer cache hit ratio is 100. What I would like you to do is run these 2 queries on your systems and leave me the results in a comment so that we can compare

Also take a look at how to capture this info if you prefer to run perfmon (or if you are still running SQL Server 2000) by reading this excellent article by Brent Ozar here: SQL Server Perfmon (Performance Monitor) Best Practices

*** Remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Original post blogged on LessThanDot.

Use alter table alter column to change datatypes for a column in SQL Server

SQLDenis — Tue, 19 Jan 2010 18:10:48 +0000

by SQLDenis

This question popped in the microsoft.public.sqlserver.programming forum yesterday. A person wanted to change a column from varchar 500 to varchar 2000. This is actually pretty easy to do in SQL Server, you can use the alter table alter column command
Let's take a quick look at how this works
First create this table

Code: tsql

CREATE TABLE [TestTable] (
[id] [INT] IDENTITY (1, 1) NOT NULL ,
[itemdate] [DATETIME] NOT NULL ,
[title] [VARCHAR] (500) NOT NULL ,
) ON [PRIMARY]

Now insert one row of data

Code: tsql

INSERT [TestTable]
SELECT GETDATE(),'bla'

Now do a simple select and verify that you have one row of data

Code: tsql

SELECT * FROM [TestTable]

Results

id          itemdate                title
----------- ----------------------- ----------
1           2010-01-18 17:28:19.820 bla

We will change the column from varchar 500 to varchar 2000. All you have to do is alter table [table name] alter column [column name] [new data type and length]
So in this case the command would look like this

Code: tsql

ALTER TABLE [TestTable] ALTER COLUMN [title] [VARCHAR] (2000)

To verify that indeed the column is now varchar 2000 run the following query

Code: tsql

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TestTable'

Results

COLUMN_NAME    DATA_TYPE       CHARACTER_MAXIMUM_LENGTH
-------------- -----------------------------------------
id              int             NULL
itemdate        datetime        NULL
title           varchar         2000

Now let's change the datetime column to a varchar, execute the following query

Code: tsql

ALTER TABLE [TestTable] ALTER COLUMN [itemdate] [VARCHAR] (49)

Now verify that it was changed

Code: tsql

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TestTable'

Results

COLUMN_NAME    DATA_TYPE       CHARACTER_MAXIMUM_LENGTH
-------------- -----------------------------------------
id              int             NULL
itemdate        varchar         49
title           varchar         2000

Insert a new row

Code: tsql

INSERT [TestTable]
SELECT GETDATE(),'bla'

Now check what is in the table

Code: tsql

SELECT * FROM [TestTable]

Results

id          itemdate                                          title
----------- ------------------------------------------------- -----------
1           Jan 18 2010  5:28PM                               bla
2           Jan 18 2010  5:30PM                               bla

Now we will add a column. The command to add a column is very similar to the one where you alter a column, the difference is that you don't use the column keyword. Below is a query that will add an int column with a default of 0

Code: tsql

ALTER TABLE [TestTable] ADD [SomeInt] INT DEFAULT 0 not null

Run this query to see what is in the table now

Code: tsql

SELECT * FROM [TestTable]

Results

id          itemdate                    title     SomeInt
----------- ---------------------------- ---------- -----------
1           Jan 18 2010  5:28PM         bla       0
2           Jan 18 2010  5:30PM         bla       0

As you can see the column was added and the default of 0 was used.

We can interrogate the INFORMATION_SCHEMA.COLUMNS view again to verify that the column is there

Code: tsql

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TestTable'

Results

COLUMN_NAME    DATA_TYPE       CHARACTER_MAXIMUM_LENGTH
-------------- -----------------------------------------
id              int             NULL
itemdate        varchar         49
title           varchar         2000
SomeInt		int		NULL

Now let's insert a row, we will use a value of 2 for the newly added SomeInt column

Code: tsql

INSERT [TestTable]
SELECT GETDATE(),'bla',2

When we run this query again

Code: tsql

SELECT * FROM [TestTable]

We can see that the value 2 is there

Results

id          itemdate			title     SomeInt
----------- ------------------------------------------------- 
1           Jan 18 2010  5:28PM           bla       0
2           Jan 18 2010  5:30PM           bla       0
3           Jan 18 2010  5:33PM           bla       2

If you try to change a column to a datatype which is incompatible with the data that is stored you will get an error message

Code: tsql

ALTER TABLE [TestTable] ALTER COLUMN [itemdate] INT

Here is the error for the above command
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Jan 18 2010 5:12PM' to data type int.
The statement has been terminated.

That is it for this post, as you can see it is pretty easy to change the data type of a column by using the alter table alter column command

*** Remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Original post blogged on LessThanDot.

SQL Server 2008 R2 to be released in May

SQLDenis — Tue, 19 Jan 2010 17:10:42 +0000

by SQLDenis

A quick post. It has just been announced, SQL Server 2008 R2 will be released in May.

Customers with Software Assurance can upgrade to SQL Server 2008 R2 and take advantage of the new features without incurring additional licensing costs.

I am glad I have Software Assurance so hopefully I can roll this out sometimes this year

More about this announcement here: http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

Original post blogged on LessThanDot.

Activity Monitor to gather statistics and health of SQL Server 2008

onpnt — Mon, 18 Jan 2010 14:53:31 +0000

by onpnt

The enhancements to the Activity Monitor were an extremely useful addition to SQL Server 2008. We can gather a moving view of statistics and the health of the instances we connect to. In the real world however, the activity monitor loses value when you need to connect to dozens of SQL Servers in order to obtain that moving feel of each server you manage. There is also the nuisance of being required to open a separate Activity Monitor instance for each server we connect to from SSMS. There is a way we can eliminate those aspects though.

One method we can use is SQL Server Profiler to capture and reuse valuable information gathered from the new Activity Monitor. Profiler allows us to see exactly what is populating the tables behind the engine.

Let’s take a look

Open SSMS 2008 and connect to a development instance. Once open, right click the server name in object explorer and select Activity Monitor from the list.

Now open SQL Server Profiler from the performance tools section located in the all programs section of SQL Server 2008.

Create a new trace and either save to a table or a file for the output. Saving to a table for this type of task is useful so we can query the results later and will not be a heavy performance issue as the trace will not be running long. Next, click the Event Selection so we can filter out most of the batch statements that we don’t want to see. I put my filter to only show ApplicationName value of “Microsoft SQL Server Management Studio” and also LoginName of myself.

Once that is completely setup, hit Run to start the trace.

You should start to see all of the main statements that are driving the Activity Monitor start coming into profiler.

We can see that the queries are based on several DMV/DMF and are being inserted into temporary tables for the Activity Monitor to show. At this point the query isn’t helpful sense the data is being inserted into temporary tables we cannot control. The temporary tables are already created by the monitor so recreating them manually ourselves would also cause a problem. We can use these queries though to manage our own results by simple changes in them and executing them in tasks we create ourselves.

Let’s show this by grabbing the query that populates the file information in the “Data File I/O” section of the Activity Monitor. The query that does this should look like this in the beginning of the batch

To use this query for ourselves, all we need to do is comment out the INSERT INTO #am_dbfilestats and we can either convert the INSERT INTO to a SELECT INTO statement with our own temporary table or simply use the query as a direct SELECT

Code: tsql

SELECT
d.name AS [DATABASE],
f.physical_name AS [FILE],
(fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB READ],
(fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
(fs.num_of_reads + fs.num_of_writes) AS [Total I/O COUNT],
fs.io_stall AS [Total I/O Wait TIME (ms)],
fs.size_on_disk_bytes / 1024 / 1024 AS [SIZE (MB)]
FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.FILE_ID = f.FILE_ID
INNER JOIN sys.databases d ON d.database_id = fs.database_id;

We can take this even further by using the statements we captured in order to insert data from SQL Server over a time period.

We can already see the value in the information we’ve captured out of Profiler and the Activity Monitor for our own use in reporting and overtime analysis of SQL Server with a rapid development feel to the process.

Original post blogged on LessThanDot.

How to return all the skipped identity values from a table in SQL Server

SQLDenis — Mon, 18 Jan 2010 14:41:34 +0000

by SQLDenis

Every now and then someone will ask how to return a list of all the identity values in a table that have been skipped. You will probably have a table with an identity column, the 'problem' with identity columns is that if an insert is rolled back or fails in any way then the identity value is not reused...you end up with gaps. Identifying gaps is pretty easy to do if you have a table of numbers in your database.

If you don't have a table of numbers, here is some code that will create a table with numbers between 1` and 2048

Code: tsql

CREATE TABLE Numbers (number INT not null PRIMARY KEY )
go
INSERT Numbers
SELECT number + 1
FROM master..spt_values s
WHERE s.type='P'

Now that we have our numbers table we can proceed with creating another table which we will populate with some numbers

Code: tsql

CREATE TABLE #bla(id INT)

INSERT #bla VALUES(1)
INSERT #bla VALUES(2)
INSERT #bla VALUES(4)
INSERT #bla VALUES(5)
INSERT #bla VALUES(9)
INSERT #bla VALUES(12)

Here is the code that will return the gaps (the values 3,6,7,8,10,11) from the temp table

Code: tsql

SELECT number
FROM Numbers n
LEFT join #bla b ON n.number = b.id
WHERE n.number < (SELECT MAX(id) FROM #bla)
and b.id IS null

As you can see it is a simple left join, we also check for the max value otherwise you would get everything back that is greater than the max value in the #bla table.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Original post blogged on LessThanDot.