showing posts tagged with 'mssql'
edited by on December 9th 2020, at 10:27
To backup Microsoft SQL Server, the account used for VM-side processing (application aware processing) requires certain permissions. Veeam recommends assigning the sysadmin role on the SQL Server but it is also possible to assign minimal permissions on the databases it needs to backup, which is the preferred method for security hardening.

The User Guide for VMware vSphere outlines the required permissions as well but for convenience, I've listed them here as well.

Instance-level roles:

Assign these roles:public


Database-level roles:

Assign these roles:

System databases master and model:db_backupoperator



System database msdb:db_backupoperator

db_dat  ...
edited by on May 13th 2019, at 13:48
It may happen (on badly configured SQL Servers) that the event log for maintenance plans fills up your storage and that it is no longer possible (because the volume is full) to use a task to clear the history. In that case, you can use the script attached to manually clear out the data.

The maintenance plan history is stored in the system database MSDB. If maintenance plans are defined and there's no task to occassionally clear the history, it will continue to fill up the database (and the volume it is on). If there's insufficient disk space, manually running the clean up task will fail because it will temporarily require additional space. The attached script drops some specific constraints  ...
edited by on July 12th 2018, at 09:47
When creating a SQL Server maintenance plan, the owner of the plan is set to the user logged in when the plan is created. If another user makes changes, the owner does not change. Sometimes it may become necessary to change the owner (e.g. if the owner's account is being disabled or removed). You can manually change the owner through the SQL Server Agent's Job Activity Monitor but changing it there is only temporary: changes to a maintenance plan will reset the owner back to the original one. Changing the owner from within the maintenance plan is not possible through the GUI, but there is a way to make the change through T-SQL statements.

The following queries need to be run against the msd  ...
edited by 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.

edited by 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  ...
edited by 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  ...
edited by on June 8th 2016, at 14:32

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

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

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

edited by 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  ...
edited by 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  ...
edited by 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  ...
edited by 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.

edited by 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   ...
edited by 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  ...
edited by 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   ...
edited by 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.

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

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

edited by 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  ...
edited by 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'
« July 2024»
« Debating Windows vs. Linux vs. Mac is pointless: they all have their merits and flaws, and it ultimately comes to down to personal preference. »