# Friday, 08 July 2011

Shrink SQL Server 2008 R2 log files

I just discovered that if you run SQL Server on your developer box and never bother to cleanup your log files, then perhaps they get to be a little big Smile.
Here is a script to quickly shrink a log file:
 
 
Use [YourDatabaseName]
Go
 
select name,recovery_model_desc from sys.databases
GO 
Alter database [YourDatabaseName] SET RECOVERY SIMPLE
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName
 
DBCC Shrinkfile(@LogFileLogicalName,100) 
#    Comments [1] |
# Monday, 20 June 2011

SQL Server: Getting table size for all tables

Here is a script for getting size information on every table in your SQL Server database. Based on a script by Mitchell Sellers, this script also works if you have multiple schemas in your database:

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
 
 --Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
 FOR 
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
 AS SchemaTable
 FROM sys.tables where type_desc = 'USER_TABLE'
FOR READ ONLY
 
--A procedure level temp table to store the results
CREATE TABLE #TempTable
 (
     id int identity,
     tableName varchar(100),
     numberofRows varchar(100),
     reservedSize varchar(50),
     dataSize varchar(50),
     indexSize varchar(50),
     unusedSize varchar(50)
 )
 
--Open the cursor
OPEN tableCursor
 
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
 
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
     --Dump the results of the sp_spaceused query to the temp table
     INSERT  #TempTable
         EXEC sp_spaceused @TableName
     UPDATE #TempTable set tableName = @TableName where id = @@identity 
     --Get the next table name
     FETCH NEXT FROM tableCursor INTO @TableName
END
 
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
 
--Select all records so we can use the results
SELECT * 
FROM #TempTable
ORDER BY tableName
 
--Final cleanup!
DROP TABLE #TempTable
 
GO
#    Comments [0] |
# Monday, 10 May 2010

Error installing SQL Server 2008 R2

I just tried installing SQL Server 2008 R2 in a VirtualPC environment. This caused an error, redownloading the .iso image from MSDN did not help. I Binged it and found Bob Beauchmin had the same problem. Apparently the .iso reader in VirtualPC causes the problem.

More here: http://www.sqlskills.com/BLOGS/BOBB/post/Getting-error-2337-installing-SQL-Server-on-a-VPC.aspx

#    Comments [0] |
# Monday, 20 July 2009

ReportViewer in VS2010

I’ve been using the ReportViewer control in Visual Studio quite a bit to create RDLC (offline) reports that are based on the result of LINQ queries against object trees. It’s been a while since there has been a new release of this control and Visual Studio 2010 did not include anything major with regards to the ReportViewer control. I asked around and got an email from Robert Bruckner answering my two main questions:

1. Yes, the ReportViewer control in VS2010 will run on both .NET 3.5 as well as .NET 4.0.
2. Yes, the ReportViewer control in VS2010 will support Export to Word for RDLC (offline) scenarios.

Thanks Robert (and the rest of the people working on this technology), great news!

#    Comments [8] |
# Thursday, 18 December 2008

SQL Server file sizes

Today I needed a query to see what the size and growth settings were for our databases. The query below will output name, size and growth information.

SELECT  SUBSTRING([name], 0, 40) as [Name],
                (([size]*8)/1024) as SizeMB,
                CASE [growth]
                        WHEN 0 THEN 'Fixed Size'
                        ELSE
                                CASE [is_percent_growth]
                                        WHEN 0 THEN 'Absolute growth: ' + CAST([growth] as varchar)
                                        WHEN 1 THEN 'Percentage growth: ' + CAST([growth] as varchar)
                                END
                END as [GrowthInfo]
FROM    sys.database_files

Output looks like this:

Name                                     SizeMB      GrowthInfo
---------------------------------------- ----------- -------------------------------------------------
AdventureWorks                           10          Fixed Size
AdventureWorks_log                       5           Fixed Size

#    Comments [0] |

SQL Server 2005 SP3

Microsoft has release SQL Server 2005 Service Pack 3, download the service pack here.

Release notes can be found here and there is also a list of the bugs that are fixed in SQL Server 2005 Service Pack 3. Looks like around 45 bugs fixes!

#    Comments [0] |
# Monday, 28 January 2008

SQL Server 2008, Visual Studio 2008 and BI Designers

In this blog the release schedule for SQL Server is explained and it becomes clear that SQL Server 2008 won't become available until 2008-Q3. Now aside from SQL Server 2008 not becoming available, this also means that the business intelligence suite which comes with SQL Server, the one you need to create SSIS packages and SQL Server reports, will not RTM until Q3. This in turn means that you won't be able to use these designers in Visual Studio 2008.

The Business Intelligence Suite uses the Visual Studio Shell and integrates in such an excellent manner that it is quite thightly bound to a specific version of Visual Studio. The SQL Server 2005 Suite is bound to VS2005 and the SQL Server 2008 Suite is bound to VS2008. So if you're planning on moving to VS2008 you'll have to run VS2005 as well, at least until 2008-Q3.

#    Comments [1] |
# Monday, 03 December 2007

Materialized Views

During our usergroup meeting last week the topic of 'materialized views' in Oracle came up. I wasn't familiar with the term, but John looked it up for me (thanks John!).
Materialized Views in Oracle seem to differ in implementation, but accomplish much the same as Index Views in SQL Server.

More on Materialized Views:
http://en.wikipedia.org/wiki/Materialized_view

More on Indexed Views:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

 

#    Comments [0] |
# Sunday, 11 November 2007

LINQ to SQL query inside SQL Server 2008

Niels confirmed it for me. Yes, it is possible to run a LINQ to SQL query in SQL Server 2008 using a .NET managed stored procedure.

As he puts it: "yes you can - but as soon there are any transactions invloved things will go pear-shaped".

Note that there will also be an optimized LINQ to SQL provider as part of SQL Server 2008:

"Language Integrated Query (LINQ) enables developers to issue queries against data by using a managed programming language such as C# or Visual Basic.NET, instead of SQL statements. LINQ enables seamless, strongly typed, set-oriented queries written in .NET Framework languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping Provider. SQL Server 2008 features a new LINQ to SQL Provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns."

#    Comments [0] |
# Tuesday, 11 September 2007

SQL Server 2005 - Export Role

I needed to export all rights granted to a specific role from a SQL Server 2005 database in order to check these roles into our TFS server.

/*
	Script	:	ExportRole.sql
	Version	:	1.0
	Date	:	11-09-2007
	Author	:	Mark Blomsma, Develop-One (www.develop-one.com)
	Desc.	:	Export all rights for a role from a SQL Server 2005 database.
			Including Service Broker messages, contracts and services.
*/

-- Role is the parameter for this procedure
DECLARE @Role VARCHAR(40)
SET @Role = 'Rolename goes here'

-- Retrieve the roleid for the role which we need to export
DECLARE @RoleId int
SELECT 
	@RoleId = dp.principal_id
FROM
	 sys.database_principals dp
WHERE
	dp.name = @Role

-- Declare a temp table for collecting results.
-- Each grant statement will be a line in the results table.
DECLARE @Result 
TABLE
	(
		Line VARCHAR(256)
	)

-- Use the current database
INSERT INTO
	@Result (Line)
VALUES
	('USE [' + DB_NAME() + ']')

-- Declare variable for retrieving data from cursor
DECLARE 
	@permission_type	char(4),
	@permission_name	sysname,
	@object_name		sysname,
	@object_type		char(2),
	@type_desc			nvarchar(60)

-- Declare object cursor for retrieving rights on database objects.
DECLARE objectCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT     
	p.type AS permission_type, 
	p.permission_name, 
	o.name, 
	o.type AS object_type, 
	o.type_desc
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.objects AS o ON p.major_id = o.object_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	o.type, o.name

OPEN 
	objectCursor;

-- Fetch first row with object permissions
FETCH NEXT FROM 
	objectCursor
INTO
	@permission_type,
	@permission_name,
	@object_name,
	@object_type,
	@type_desc	

-- Loop through object permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		objectCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name,
		@object_type,
		@type_desc	
END

-- Cleanup cursor
CLOSE 
	objectCursor
DEALLOCATE
	objectCursor

-- Declare object cursor for retrieving rights on message types.
DECLARE messagetypeCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	mt.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_message_types AS mt ON p.major_id = mt.message_type_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	mt.name

OPEN 
	messagetypeCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	messagetypeCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON MESSAGE TYPE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		messagetypeCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	messagetypeCursor
DEALLOCATE
	messagetypeCursor


-- Declare object cursor for retrieving rights on contracts.
DECLARE contractCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	c.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_contracts AS c ON p.major_id = c.service_contract_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	c.name

OPEN 
	contractCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	contractCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON CONTRACT :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		contractCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	contractCursor
DEALLOCATE
	contractCursor



-- Declare object cursor for retrieving rights on services.
DECLARE serviceCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	s.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.services AS s ON p.major_id = s.service_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	s.name

OPEN 
	serviceCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	serviceCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON SERVICE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		serviceCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	serviceCursor
DEALLOCATE
	serviceCursor



SELECT
	*
FROM
	@Result

#    Comments [0] |

SQL Server 2005 - TRUNCATE TABLE

Useful change to know about.

In SQL Server 2000 you needed quite some permissions (SS2000: TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.)

In SQL Server 2005 you're allowed to perform a truncate table as soon as you have 'ALTER' permissions on that table.

#    Comments [0] |
# Wednesday, 15 August 2007

Microsoft® Visual Studio® 2005 Team Edition for Database Professionals Power Tools

Microsoft Visual Studio 2005 Team Edition for Database Professionals Power Tools has been released and is available at: http://www.microsoft.com/downloads/details.aspx?FamilyID=da3f11ad-bd54-4eda-b08c-4df84df0d641&displaylang=en

The Microsoft Visual Studio 2005 Team Edition for Database Professionals Power Tools is a set of enhancements and tools that compliment and improve the user experience of Team Edition for Database Professionals.

This release includes 5 new refactoring types, a new dependency viewer, additional data generators and editors, 2 new MSBuild tasks for Schema and Data Compare as well as the introduction of the TSQL Static Code Analysis feature

TSQL Static Code Analysis
• Static Code Analysis - A precursor to the functionality that will be in future versions of VSTS that will allow you to perform Static Code Analysis on T-SQL code.

Refactoring
• “Move Schema” Refactoring - Allows a user to right click on an object and move it to a different but existing schema
• SP Rename Generation - Generate a new script that will contain sp_renames for all rename refactored objects that the user can then execute.
• Wildcard Expansion - Automatically expand the wildcard in a select to the appropriate columns.
• Fully-Qualified Name Support - Automatically inject fully-qualified names when absent in a script
• Refactoring extended to Dataset - Refactor into strongly typed dataset definitions

MSBuild Tasks
• Data / Schema Compare Build Tasks - MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI

Schema View
• API Access to Schema View - Insert / Update / Delete to schema View and list schema objects and their associated files

Dependency Tool Window
• Dependency Tree - Show the dependencies ( incoming / outgoing ) for selected schema objects in a new tool window

Miscellaneous Tools
• Script Preprocessor - Expand SQLCMD variables and include files and command line version (sqlspp.exe) & an MSBuild version ( wraps the command line version )

#    Comments [0] |
# Wednesday, 08 August 2007

Soon...

Can't wait for the new DB Pro Power Tools to become available!

#    Comments [0] |
# Tuesday, 24 July 2007

Microsoft® Visual Studio® 2005 Team Edition for Database Professionals Service Release 1

Microsoft® Visual Studio® 2005 Team Edition for Database Professionals Service Release 1 has been released. This is must have tool for people that develop using Visual Studio Team System!

Go to: http://www.microsoft.com/downloads/details.aspx?FamilyID=9810808c-9248-41a5-bdc1-d8210a06ed87&displaylang=en

#    Comments [0] |
# Friday, 01 June 2007

Visual Studio Team Edition for Database Professionals - Power Tools

Recently I've started using VSTS for DB Pro. I've installed CTP 2 of Service Release 1, and it's been great.

The VSTS DB Pro team plans to release power tools for VSTS DB Pro as well. These are tools that will be released as soon as possible, without having to wait for a major release.

The Power Tools are listed on the Future Release page of VSTS: http://msdn2.microsoft.com/en-us/teamsystem/bb407307.aspx

Team Edition for Database Professionals Power Tools

Feature Description
“Move Schema” Refactoring Allows a user to right click on an object and move it to a different but existing schema
SP Rename Generation Generate a new script that will contain sp_renames for all rename refactored objects that the user can then execute.
Data / Schema Compare Build Tasks MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI
Script Preprocessor Expand SQLCMD variables and include files and command line version (sqlspp.exe) & an MSBuild version ( wraps the command line version )
API Access to Schema View Insert / Update / Delete to schema View and list schema objects and their associated files
Data / Schema Compare Build Tasks MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI
Dependency Tree Show the dependencies ( incoming / outgoing ) for selected objects in a new tool window
Static Code Analysis A precursor to the functionality that will be in future versions of VSTS that will allow you to perform Static Code Analysis on T-SQL code.
Wildcard Expansion Automatically expand the wildcard in a select to the appropriate columns.
Fully-Qualified Name Support Automatically inject fully-qualified names when absent in a script
Schema Reporting DBSpecGen functionality for the project system allows you to easily document your database schema.

 

 

#    Comments [1] |
# Tuesday, 29 May 2007

More on SQL Server Service Broker

In earlier posts I already blogged about creating some simple queuing solutions with SQL Server Service Broker. Last week I spend some time actually implementing my research in a customer project and of course ran into two 'interesting' issues.

  1. It is possible for the Service Broker to somehow die on you, whilst appearing to be up and running and with RETENTION=ON sent message appear to be in the queue.

    When using the SQL Profiler it becomes clear that messages don't get processed because the Service Broker is not enabled.

    You can use the following statement to enable Service Broker:

    alter database PFA_DATA set ENABLE_BROKER

    I had to do it on two machines and on of them gave me the following error:

    Msg 9772, Level 16, State 1, Line 2
    The Service Broker in database "PFA_DATA" cannot be enabled because there is already an enabled Service Broker with the same ID.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.

    So instead I used:

    alter database PFA_DATA set NEW_BROKER

     

    After this the Service Broker was up and running and message started getting delivered.

    If the query takes extremely long to complete restart SQL Server and try again.

    One potential cause for this is when you restore a database.

  2. In my earlier post I just blatantly used WITH CLEANUP to avoid getting EndDialog messages.
    The documentation however states:

    WITH CLEANUP
    Remove all messages and catalog view entries for this side of the conversation without notifying the other side of the conversation. Microsoft SQL Server drops the conversation endpoint, all messages for the conversation in the transmission queue, and all messages for the conversation in the service queue. Use this option to remove conversations which cannot complete normally. For example, if the remote service has been permanently removed, you use WITH CLEANUP to remove conversations to that service.


    So use this sparingly.

    I've discovered a much cleaner way to handle it.
    When sending a message you can use "END CONVERSATION @dialog" to specify that as far as the sender is concerned the dialog is over. In other words: a fire and forget message.
    When the receiving side of the conversation receives the messages and performs its "END CONVERSATION" statement, Service Broker will detect that no acknowledgement (EndDialog) needs to be send.

#    Comments [1] |
# Monday, 20 November 2006

SSBAdmin - View SQL Service Broker info

My friends, Klaas and Hans, pointed out that there is a neat tool for viewing queues, contracts, types etc. in SQL Server 2005. It's called SSBAdmin and is written by Niels from DevelopMentor. Go to http://sqljunkies.com/WebLog/nielsb/archive/2005/12/27/17701.aspx to download the tool.

#    Comments [0] |
# Wednesday, 15 November 2006

Simple XML Queue in SQL Server 2005 - Sample code

Here is the sample SQL script to create a simple queue in SQL Server 2005:


-- Needed to send messages!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';
GO

-- All messages need to be typed.
CREATE MESSAGE TYPE
[//DEVELOP-ONE.COM/schemas/Test/XmlMessage]
VALIDATION = VALID_XML;
GO

-- Create a contract
CREATE CONTRACT
[//DEVELOP-ONE.COM/schemas/Test/BasicXmlQueueContract]
( [//DEVELOP-ONE.COM/schemas/Test/XmlMessage]
SENT BY ANY
) ;
GO

-- Create a queue which is turned on and which retains
-- messages in the database.
CREATE QUEUE BasicXmlQueue
WITH STATUS=ON, RETENTION=ON;
GO

-- Create the service needed to send messages.
CREATE SERVICE [//DEVELOP-ONE.COM/Sql/Services/Test/BasicXmlService]
ON QUEUE [dbo].[BasicXmlQueue]
([//DEVELOP-ONE.COM/schemas/Test/BasicXmlQueueContract]) ;
GO

-- Create a stored procedure to send a message
CREATE PROCEDURE SendXMLMessage
@MessageContent XML
AS
BEGIN

DECLARE @dialog_handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [//DEVELOP-ONE.COM/Sql/Services/Test/BasicXmlService]
TO SERVICE '//DEVELOP-ONE.COM/Sql/Services/Test/BasicXmlService'
ON CONTRACT [//DEVELOP-ONE.COM/schemas/Test/BasicXmlQueueContract] ;


SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [//DEVELOP-ONE.COM/schemas/Test/XmlMessage]
(@MessageContent) ;

-- return dialog_handle
SELECT @dialog_handle ;

END ;
GO

CREATE PROCEDURE ReceiveXMLMessage
@Timeout int
AS
BEGIN
DECLARE @tempTable as TABLE(
conversation_handle UNIQUEIDENTIFIER,
message_body XML) ;

WAITFOR( RECEIVE TOP (1)
conversation_handle,
message_body
FROM BasicXmlQueue
INTO @tempTable ), TIMEOUT @Timeout;

DECLARE @conversation_handle as UNIQUEIDENTIFIER;

SELECT TOP (1) @conversation_handle = conversation_handle
FROM @tempTable ;

IF ( @conversation_handle IS NOT NULL )
BEGIN
-- remove message from queue without sending an End Of Conversation message.
END CONVERSATION @conversation_handle WITH CLEANUP ;
END

-- return the message body
select message_body from @tempTable;

END ;
GO

#    Comments [0] |

Creating a simple queue in SQL Server 2005

SQL Server 2005 introduces Service Broker, an extensive mechanism for supporting asynchronous messaging with SQL Server.

The very heart of Service Broker is the new SQL Server object: QUEUE. To utilize just the queue you still have to go through the service broker and do all the extensive stuff which you may or may not need.

The minimum steps you need to create a queue are:

  1. Create a message type
  2. Create a contract
  3. Create a queue
  4. Create a service

Make sure there is a master encryption key available in your database, otherwise the message won't get send.

I've created stored procedures for sending en receiving messages. Works quite well. I'll post some SQL code later.

 

#    Comments [0] |