showing posts tagged with 'sqlserver'
by lunarg on August 4th 2016, at 17:25
Backup of a SQL Server may fail with a VSS error.

From the log, the following message can be found:

One or more errors occurred. ---> Replay.Core.Contracts.Transfer.VssGeneralErrorException: There was a problem with the VSS subsystem on '(agent-machine)l'. ---> Replay.Agent.Contracts.ShadowCopy.ShadowCopyWriterFailedException: The VSS writer 'SqlServerWriter' failed during the 'DoSnapshotSet' phase with error WriterErrorNonRetryable. The VSS writer state is FailedAtPrepareSnapshot ---> Replay.Common.Contracts.ReplayException

In the Application event log on the machine, event 8229 is logged:

Event 8229
A VSS writer has rejected an event with error 0x800423f4, The writer experienc  ...
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 25th 2015, at 10:15
SQL Server 2016CTP 2.3    
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.0
* 11.2.5058.0
SQL Server 2008 R210.50.1600.110.50.2500.0
* 10.51.2500.0
* 10.52.4000.0
* 10.53.6000.34
SQL Server 200810.0.1600.2210.0.2531.0
* 10.1.2531.0
* 10.2.4000.0
* 10.3.5500.0
* 10.4.6000.29
SQL Server 20059.0.1399.069.0.20479.0.30429.0.40359.0.5000
SQL Server 20008.0.1948.0.3848.0.5328.0.7608.0.2039
SQL Server

* Alternate version numbers.

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 June 2nd 2015, at 12:12
I got these messages in the error log of a SQL Server instance:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

I also saw messages about starting a certain database, even though the instance itself remained running. T  ...
by lunarg on May 26th 2015, at 16:17
Two methods of truncating the transaction logs for a database for SQL Server 2008 or newer:

Perform a backup to the nul device, essentially a "black hole":

BACKUP LOG [databaseName] TO DISK = 'nul:'

Temporarily set the recovery model to SIMPLE, then shrink the transaction log:


WARNING: truncating the transaction log without a backup may result in data loss in case of a database failure!

EDIT (26/05/2015):

You could attempt the backup/shrink routine without changing the recovery mode, but this will most likely onl  ...
by lunarg on May 26th 2015, at 14:17
You can move the tempdb of a SQL Server instance to another location using T-SQL, but this requires a little bit of downtime: setting a new location will only take effect after a restart of the SQL Server instance.

First, retrieve the tempdb's current location and logical name:

Use tempdbGOSELECT name,filename FROM sys.sysfilesGO

This should give you two entries: one for the actual database, and one for the transaction log of tempdb. Now that we have the location and logical name, we can change it:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'T:\newdir\tempdb.mdf');GOALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'T:\newdir\templog.ldf');GO

Replace the p  ...
by lunarg on May 26th 2015, at 14:05

If, when attempting to start SQL Server instance, you get an error 1814, this means there's a problem with the tempdb database. Either it can't be created because the disk or volume is not accessible for writing (i.e. a security permission problem), or the volume on which the tempdb resides does not have enough space available. If the latter is the problem, you'll need at least 2 MB of free space for tempdb to be created.

by lunarg on May 22nd 2015, at 11:26
You can perform resource configuration for SQL Server Analysis Services (SSAS) through SQL Server Management Studio (SSMS). Simply connect to the SSAS (select Microsoft Analysis Server as server type and connect to the instance name). Using SSMS allows to change only a limited number of common and advanced parameters. Many of the very advanced parameters (usually those that require a restart of SSAS) can only be configured through a file called msmdsvr.ini.

The INI-file msmdsvr.ini, by default located in %PROGRAMFILES%\Microsoft SQL Server\<ssas-instance-name>\Config and in fact is an XML-file, allows you to configure both common and advanced parameters of SSAS. By default, the file i  ...
by lunarg on May 21st 2015, at 16:30
After running complex queries that use the tempdb of a SQL Server instance, it may become necessary to shrink the database again. However, when running the shrink operation on the tempdb, it can result in the database not shrinking at all, even when the used space is minimal.

The reason for this is most likely that the clearance of the tempdb is still in cache and not flushed to disk. You need to flush the changes to disk first, after which you will be able to shrink the tempdb. Do note that flushing will most likely impact database performance, so use with caution. Also, the tempdb will not shrink beyond the initial size, configured in the file group.

showing posts tagged with 'sqlserver'