SQL2012 – T-SQL.dk

SQL Server Quick Tip 26 – 30

Posted by Jens Vestergaard on 11/07/2013 in Programming 0 Comment

[Blog]: So you think you need Enterprise Edition, huh? http://t.co/zRWoumqRQf #DBA #SQLServer #SQLTip #in

— Jens Vestergaard (@vestergaardj) July 4, 2013

A ratio between the size of max_server_memory and the size of the buffer pool extension of 1:16 or less is recommended #sqltip #sql14 — Jens Vestergaard (@vestergaardj) July 2, 2013

New user databases will inherit the compatibility level of the model database. #sqlserver #sqltip — Jens Vestergaard (@vestergaardj) June 28, 2013

For information about credentials, see the sys.credentials catalog view. http://t.co/365220QLE5 #SQLServer #SQLTip #SQL2012 — Jens Vestergaard (@vestergaardj) May 24, 2013

Truncation does not reduce the size of the log file. To reduce the phys. size of a phys. log file, you need to shrink the log file. #SQLTip

— Jens Vestergaard (@vestergaardj) May 21, 2013

Microsoft Connect Bug Reports

Posted by Jens Vestergaard on 17/05/2013 in Programming 0 Comment

Just posted two bug reports to Microsoft Connect

We don’t take kindly to sensitivity ’round here…
First, I perceive it as a bug, when a foreach loop fails to list variables when a project parameter is set as sensitive. When a project parameter is set as sensitive, I get this error message when setting up the foreach loop:

Following is the failure to populate the list of variables:

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/786696/foreach-from-variable-enumerator-fails-when-one-or-more-parameters-are-marked-as-sensitive

Flexibility? MEH!
The other bug is about the file connection manager.
When adding a file connection manager as a project connection manager, I want to be able to se the connection string as an expression in a given package. But none of the project parameters are present when I open the expression builder dialog box. Huh?

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/787145/project-parameters-not-present-in-file-connection-manager-expression

SQL Server Quick Tip 16-20

Posted by Jens Vestergaard on 31/03/2013 in Programming 0 Comment

#SQLTip #SQLServer #TSQL amzn.com/k/P3Livr0TRReO… TRUNCATE TABLE resets the IDENTITY property to its original seed, while DELETE doesn’t.

— Jens Vestergaard (@vestergaardj) March 1, 2013

When dealing with parent-child pcks in #SSIS, be sure to verify the ProtectionLevel prop is set properly on all pcks #SQLServer #SQLTip

— Jens Vestergaard (@vestergaardj) February 12, 2013

Move your tempdb to a separate storage, example here:andynifong.com/blog/2008/1/31… #SQLTip #SQLServer

— Jens Vestergaard (@vestergaardj) February 10, 2013

#SQL2012 features supported by Windows Server Core #SQLTip #SQLServer amzn.com/k/AD4Y6C_YS7aD… Windows Server 2008 R2 Server Core with …

— Jens Vestergaard (@vestergaardj) December 12, 2012

The BIDS tools will _NOT_ install of the web download of SQL Server Data Tools #SSDT : msdn.microsoft.com/en-us/data/hh3… #SQLTip #SQLServer

— Jens Vestergaard (@vestergaardj) December 10, 2012

Run out of disk you say, eh?

Posted by Jens Vestergaard on 29/03/2013 in Programming 0 Comment

In the rare occasion, that when you are executing a project from the Information Services Catalogs, and the SysAdmin didn’t foresee some, unforeseeable event causing the disk to run out of space. And the execution doesn’t fail, just waits for more disk to become available, you will maybe be tempted to stop the operation through the ‘Active Operations’ pane in SSISDB.

This cannot be done, simply because, catalog.stop_operation which is the stored procedure called, also needs to write to disk. As the following screen shots will verify, you need to restart the instance.

In SQL Server Management Studio (SSMS), select the Integration Services Catalogs, and right click the SSISDB. Click Active Operations.

When you select the operation, the option to Stop the operation becomes available.

You will the be prompted, if you really want to Stop the operation.

And if you select Yes, you will be met by the following error.

So, in order to get the operation, which is running, to stop, you need to restart the whole instance.

SQL Server Quick Tip 11 – 15

Posted by Jens Vestergaard on 04/12/2012 in Programming 0 Comment

You can validate a #SQLServer setup by running ‘Setup.exe /Action=RunDiscovery /q’, more info at #MSDN bit.ly/TGw9Yl #SQLTip #SQL2012

— Jens Vestergaard (@vestergaardj) December 4, 2012

The only way to access SSAS is by using XML for Analysis Services Listener : See #MSDN bit.ly/UD8Kdb #SQLTip #SQLServer #SQL2012

— Jens Vestergaard (@vestergaardj) December 4, 2012

ALTER DATABASE <add_or_modify_files> options are not available in a Contained Database. #SQLTip #SQL2012 #SQLServer — Jens Vestergaard (@vestergaardj) November 26, 2012

Data that is moved to shrink a file can be scattered to any location in the file. This causes index fragmentation ##SQLTip #SQLServer

— Jens Vestergaard (@vestergaardj) November 25, 2012

Rebuilding the master database rebuilds all of the system databases. #SQLTip #SQLServer #SQL2012

— Jens Vestergaard (@vestergaardj) November 18, 2012