Archive for December, 2014

Writing About Check Database Recovery Status

December 22, 2014 Leave a comment
Categories: T-SQL-Scripts

Return Function value in variable with Open Query on Remote SQL Server Database

December 22, 2014 Leave a comment

Recently I received a request from Development Team on how we can get a function value from a remote server.

DECLARE @CTVAL int Select @CTVAL = VERSION_TEST from Openquery([Remote SQL Instance],'DECLARE @var VARCHAR(100) SET @var = ''USE DatabaseName; select CHANGE_TRACKING_CURRENT_VERSION() AS VERSION_TEST''; EXECUTE(@var) ') UPDATE [DatabaseName].[dbo].[TableName] SET ConfigValue= @CTVAL WHERE ConfigKey='Condition Value’

Categories: T-SQL-Scripts

Equally distribute data in Secondary File within a File Group

December 21, 2014 Leave a comment

This is a really a pain job.The Primary Data file has grown out of proportion and reached to the drive limit.Adding a new data file (secondary file.ndf) will only be used for new allocation of data or index pages. Then how to equally distribute data between the primary and secondary files , I have read lot of blog post but nothing actually helped me.  Eventually I thought to REBUIL all indexes with fill factor



Print 'Rebuilding indexes on every table in the database.' EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)" GO


Categories: Maintenance

Enabling FileTable Prerequisite

December 19, 2014 Leave a comment

The FileTable feature builds on top of SQL Server FILESTREAM technology

Enabling the Prerequisites for FileTable

To enable the prerequisites for creating and using FileTables, enable the following items:

  • At the instance level:

    • Enable FILESTREAM at the Instance Level

      • Enable using PowerShell

        # Enable FILESTREAM CLS $instance = "MSSQLSERVER" $wmi = Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement11"-Class FilestreamSettings | where {$_.InstanceName -eq $instance} $wmi.EnableFilestream(3, $instance)

        • Restart SQL Server Services
      • Enable using Configuration Manager

        FILESTREAM Enabled

      • Enable using Registry Key

        By default [EnableLevl] is set to 0, change it to 3 and restart the SQL Server Services.SQL Server Services will create the Share Name by itself.


        Connect to SQL Server instance from SQL Server Management Studio and Enable FILESTREAM for file I/O streaming access



        sp_configure ‘filestream access level’,2

        reconfigure with override

  • At the database level:

Administrative Considerations

About FILESTREAM and FileTables

  • You configure FileTables separately from FILESTREAM. Therefore you can continue to use the FILESTREAM feature without enabling non-transactional access or creating FileTables.

  • There is no non-transactional access to FILESTREAM data except through FileTables. Therefore, when you enable non-transactional access, the behavior of existing FILESTREAM columns and applications is not affected.

About FileTables and non-transactional access

  • You can enable or disable non-transactional access at the database level.

  • You can configure or fine-tune non-transactional access at the database level by turning it off, or by enabling read only or full read/write access.

Categories: File Table

FileTable with AlwaysOn AGs – Bug

December 4, 2014 Leave a comment

After a long while of testing and troubleshooting we identified two bugs in SQL Server that can hit you if you run FileTables in conjunction with AlwaysOn Availability Groups. One concerns the way Checkpoints are done in FileStream, which could lead to very long failover times, the other one concerns a handle issue that could lead to the system running out of sync without noticing.

Right now there is no feasible workaround to both problems, but Microsoft has already confirmed that both issues will be fixed in Service Pack 1.

If you should encounter this problem and need help working around it please ping me and I will share the necessary details.

Categories: File Table

Understanding Shared Folders and Windows Firewall

December 4, 2014 Leave a comment

Understanding Shared Folders and the Windows Firewall


Sharing a folder or file creates a Windows Firewall exception for File and Printer Sharing. The exception opens the ports listed in the following table.



139, 445


137, 138

The default scope is to allow access from any computer on the network, including computers on the Internet. Unless you block incoming connections on these ports using a hardware firewall, firewall server, or other Internet-sharing device, your computer will be vulnerable to attack from the Internet as long as your Internet connection is active.

Categories: SQL Server Security