showing posts tagged with 'mssql'
by lunarg on August 9th 2017, at 12:20

You can easily run Transact-SQL (T-SQL) script files (with extension .sql) via the commandline utility sqlcmd:

sqlcmd -S Server\Instance -i C:\path\to\SomeScript.sql

By default, the output is written to the command prompt. You can also save the output to a file:

sqlcmd -S Server\Instance -i C:\path\to\SomeScript.sql -o C:\path\to\TheOutput.txt

Note that when using this, no output is written in the command prompt window.

by lunarg on July 26th 2017, at 12:30
When attempting to connect to a SQL Server 2014 or SQL Server 2016 using SQL Server Management Studio 2012, you may encounter the following error:

Microsoft SQL Server Management Studio
An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Additional information: VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

Apparently, this is a known bug when using Management Studio 2012 to connect to a newer version of SQL Server:

The solution is to updat  ...
by lunarg on May 9th 2017, at 15:54
This article contains the version matrix for Microsoft SQL Server. It outlines the product and its internal version numbers, along with download links for the service packs (if any).

 RTM (no SP)SP1SP2SP3SP4SQL Server 2017     SQL Server 201613.0.1601.513.0.4001.0   SQL Server 201412.0.2000.812.0.4100.1
* 12.1.4100.1   SQL Server 201211.0.2100.6011.0.3000.0
* 11.1.3000.011.0.5058.0
* 11.2.5058.0  SQL Server 2008 R210.50.1600.110.50.2500.0
* 10.51.2500.010.50.4000.0
* 10.52.4000.010.50.6000.34
* 10.53.6000.34 SQL Server 200810.0.1600.2210.0.2531.0
* 10.1.2531.010.0.4000.0
* 10.2.4000.010.0.5500.0
* 10.3.5500.010.0.600  ...
by lunarg on June 8th 2016, at 14:32

Download links for SQL Server 2012 Service Pack 3, including Express edition:

by lunarg on April 14th 2016, at 16:41

A bit hard to find through the search on Microsoft website, so here's the direct link:

by lunarg on November 5th 2015, at 13:10
When attempting to create a maintenance plan in SQL Server Studio, or you are attempting to view the SQL Agent settings, you may get an error about Agent XPs not being enabled. Agent XPs (Agent eXtended Procedures) is a requirement for SQL Agent to be configured through SQL Server Studio, regardless of whether the SQL Agent is running or not. This also includes the creation and modification of maintenance plans.

To enable Agent XPs, run this query on the instance (either through SQL Server Studio or osql):

sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Agent XPs', 1;GORECONFIGUREGO

The change is effective immediately (does not require a server stop/start). Note that  ...
by lunarg on October 28th 2015, at 09:58
Client tools (such as SQL Server Management Studio) are shared features that are installed on every node of a failover cluster. When a node is added, they get installed on the new node automatically. In case the tools haven't been installed before, you can install them without downtime. By default, the regular installation procedure installs the management tools on the entire cluster (i.e. all the nodes).

On older versions of SQL Server (2008, 2008 R2), installation of the client tools on a node may fail during the pre-installation checks, right after the feature selection. The check states that an instance is already present on the node, of course, stating the obvious.

Apparently, this is  ...
by lunarg on October 8th 2015, at 12:25
When attempting to edit files from a Visual Studio or SQL Studio project, you get the following error:TF204017 The operation cannot be completed because the user does not have one or more required permissions (Use) for workspace ...

This is because you are attempting to edit files inside folders that have been added to someone else's workspace. By default, that user's workspace is set to private, resulting in all files being locked for anyone except that user.

To circumvent the issue, you can either copy them, properly configure TFS, or, as a workaround, ask the user to set his/her workspace to "public".

Start Visual Studio (2012).

Connect to the Team Foundation Server

In th  ...
by lunarg on September 30th 2015, at 09:31

You can easily move the table to another filegroup by recreate the clustered index on the table:

ON dbo.YourTable(YourClusteringKeyFields)
ON [filegroup_name]

If the clustered index is unique:

ON dbo.YourTable(YourClusteringKeyFields)
ON [filegroup_name]

This creates a new clustered index and drops the old one. Because the new index is created in the other filegroup, the table will have been moved to that filegroup.

by lunarg on September 4th 2015, at 09:25
Often overlooked during the installation of a Microsoft SQL Server is the creating of the required Service Principal Name(s) (SPN) to enable authentication through Kerberos. SCOM highlights this oversight nicely with this warning:

Alert Description
SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.
Service Account: CONTOSO\s_clsql_sqlsrv_de
Missing SPNs: MSSQLSvc/, MSSQLSvc/
Misplaced SPNs:
Duplicate SPNs:

The solution is simple: create the SPNs. All the info you need is actually in the alert's description. Use the information to create the SPNs.

Log on to a domain   ...
by lunarg on September 3rd 2015, at 11:05
Run the following T-SQL statements.

Show a list of longest running SPIDs on a SQL Server:

select P.spid, right(convert(varchar, dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration', P.program_name, P.hostname, P.loginamefrom master.dbo.sysprocesses Pwhere P.spid > 50and P.status not in ('background', 'sleeping')and P.cmd not in ('AWAITING COMMAND' ,'MIRROR HANDLER' ,'LAZY WRITER' ,'CHECKPOINT SLEEP' ,'RA MANAGER')order by batch_duration desc

To retrieve the SQL for a specific SPID:

declare @spid int, @stmt_start int, @s  ...
by lunarg on August 11th 2015, at 10:48
I noticed there's many conflicting information on the internet about the need for page locking and its required privileges (Lock page in memory) for the instance's service account for 64-bit SQL Server. As part of a SQL Server health check, I decided to look more closely into the matter and came up with the conclusion that setting the page locking privilege for the SQL Server instance's service account is indeed still important, despite many other sources claiming otherwise. And here's why...

In the past, during the 32-bit era, a single process was limited to a maximum of 4GB of memory. This would become a problem with databases which would often require more than that in order to maintain   ...
by lunarg on July 17th 2015, at 09:15

When restoring a database, you usually start with restoring a full backup, then any differentials, and finally the transaction logs. You set the database restore mode to WITH NORECOVERY, which allows you to perform these additional restores.

But what if you find out that there's nothing more to restore, leaving you with a database stuck in Restoring mode? Then you only need to run a single T-SQL statement:


Replace db_name with the name of your database.

by lunarg on July 13th 2015, at 16:16

Follow the chart below to successfully perform a rolling upgrade of your HA SQL Server cluster.

by lunarg on June 8th 2015, at 09:47
Maintaining indexes on your table is an important part of keeping your database healthy and its performance adequate. There are two maintenance operations for any index: rebuilding and reorganizing. Both operations were designed to get rid of index fragmentation, but differ in how this is done.

 RebuildReorganizeWhat it doesDrops the existing index and recreates it from scratch.Physically reorganizes the leaf nodes of the index.When to useIndex fragmentation >= 40%Index fragmentation >= 10% and < 40%Impact on systemHigh.

Database will be offline during process, unless you have Enterprise Edition and have enabled the ONLINE option. Online rebuild requires more resources than o  ...
by lunarg on May 19th 2015, at 16:10

Probably MSSQL 101, but this is how to quickly retrieve the structure of a table:

EXEC sp_help tbl_name

tbl_name is the name of the table.

showing posts tagged with 'mssql'
« October 2017»
« Have you tried restarting your computer, Sir? »
first answer of any tech support