14

On the face of it, this looks like a classic lookup deadlock. The essential ingredients for this deadlock pattern are: a SELECT query that uses a non-covering nonclustered index with a Key Lookup an INSERT query that modifies the clustered index and then the nonclustered index The SELECT accesses the nonclustered index first, then the clustered index. The ...


8

As this is SharePoint your hands are tied. You can't create indexes or statistics without loosing support from Microsoft. CXPACKET just means that one thread of a parallized query is waiting for another thread to finish waiting for something else. SOS_SCHEDULER_YIELD can mean a few things, typically it means that you need to fix your indexes are you have ...


6

If this was a classic lookup deadlock, the resource list will include both the Clustered Index and Non-Clustered Index. Typically the SELECT will hold a SHARED lock on the NC index and wait for a SHARED lock on the CI, meanwhile the INSERT will acquire an EXCLUSIVE lock on the CI and wait for an EXCLUSIVE lock on the NC. The resource list in the deadlock xml ...


5

This is SQL Server updating the statistics on a table. It can be triggered automatically when about 20% of the data in your table changes, or on demand if you run jobs that update statistics. You have a few ways to lessen the pain. You can enable asynchronous statistics updates, which lets SQL Server update stats behind the scenes without blocking other ...


5

Increasing MWT can lead to increased SOS_SCHEDULER_YIELD waits. Not the end of the world, but think of it like adding a buncha kids to a teacher's class. All of a sudden, it's gonna be harder for each kid to get attention. When a process exhausts its 4ms quantum, there will potentially be more threads ahead of it waiting to get on the CPU. It's really ...


4

Would it be possible to migrate my database to SQL Server Std? Yes, a backup and restore approach would work for this situation. For a detailed migration steps (ignoring the ones that you dont need - as it is for migrating from lower to higher version), refer to this answer. Can I install my SQL Server 2012 Std on the same server I have of my SQL Server ...


4

The AG usually fails over when the secondary and witness can't talk to the primary for a length of time. (I'm simplifying that a little, but for more technical details, check out MS's documentation on cluster quorum.) To fix this, I'm going to suggest a few different things. You can do one, two, or all of 'em - just start with the things that are easiest ...


3

Even if you configured RBS to use FILESTREAM, TDE does not apply to FILESTREAM: Transparent Data Encryption and FILESTREAM DATA FILESTREAM data is not encrypted even when TDE is enabled.


3

The files are stored in a blob in the sharepoint database and as TDE encrypts all the pages in the database all the files will be encrypted there within. It's important to notice that while the database is mounted on the server the database server will serve the files unencrypted to the Sharepoint application server and it's clients. The Sharepoint ...


3

I write Minion Backup and this kind of thing would be very easy to do. We allow you to run Pre and Post code before each DB, so for your SharePoint DB, you can just put it into single-user mode and change it back when it's done. However, we can't guarantee that you'll be the one to get that connection. But you can download Minion Backup here and give it a ...


3

So after messing with this off and on for a while I figured it out. It is possible. All this was done on one server with SharePoint 2013 enterprise and SQL Server 2012 Enterprise with SP2. I have a default SQL server instance with all features selected, specifically the shared features "Reporting Services - Sharepoint" and "Reporting Services Add-in for ...


3

It is not possible, because you cannot create a backup of an offline database. From MSDN: Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed ...


3

Restore from your backup if possible. "Allow data loss" is really a terrible option. This excellent article will help you: http://www.sql-server-pro.com/dbcc-checkdb.html


3

I would suggest double checking all the requirements for enabling FILESTREAM as per: How to: Enable FILESTREAM


3

When you create a database snapshot, it logs a message in the Error Log that says 'starting up database...' and the database name. You could filter the error log on those messages. CREATE DATABASE test_snap ON ( NAME = StackOverflow, FILENAME ='D:\Data\stack_snap.mdf' ) AS SNAPSHOT OF StackOverflow; When you run checkdb, the filename looks like this, and ...


3

I'd go with just updating stats. I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats". Also, running UPDATE STATISTICS gets you both index and columns stats updates. Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" ...


3

Msg 227, Level 15, State 1, Line 20 "Modify" is not a valid function, property, or field. XML is case sensitive and in SQL Server so are the functions used on the XML datatype. Change Modify(... to modify(.... If you have untyped XML you will still get an error. Msg 2356, Level 16, State 1, Line 21 XQuery [AllUserData.tp_ColumnSet.modify()]: The ...


2

Unfortunately no, not in SQL Server 2008. According to FILESTREAM Overview - multiple containers can be added to a single FILESTREAM Filegroup but only as of SQL 2012.


2

Two things. First if you are going to do that then make your script this: ALTER DATABASE [TEST_1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [TEST_1] GO BACKUP DATABASE [TEST_1] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test\test_1.bak' WITH NOINIT, STATS = 10 GO ALTER DATABASE [TEST_1] SET MULTI_USER WITH ROLLBACK ...


2

SQL Server gets its time Zone information from the operating system on which it is installed. So if SELECT GETDATE() reveals the right information, then SQL Server is most likely not the problem. You can query the registry on your SQL Server, if you have the appropriate permissions, and see what the server's time zone is set to: DECLARE @TimeZone ...


2

I have never used SharePoint or worked with its databases specifically, but both links in your question indicate that you should be able to see, and work with, SharePoint databases in SQL Server Management Studio. The fact that you cannot see them in your particular case most likely means that they are hosted by a different server instance – not the ...SBS\...


2

SQL does not allow to backup a database when in offline mode. You can also create maintenance plans in order to automate the job. So the first step of you job can be setting the offline databases to online mode and then the maintenance plan will do is work and then the third step will be to set your databases back to online.


1

This is an operating system error, reported to the SQL Server service. The SQL Server service account, which creates the files, needs to have at least change permissions on the d:\data folder and it needs to exist.


1

DPM is checking the consistency of the SharePoint meta-base, which is not consistent anymore due to you deleting the database in SQL Server. That is, you have removed the logical/physical database in SQL Server but you have not removed references to it from the SharePoint metadata. My advice to you is to restore the database that you deleted and then remove ...


1

Having a different default collation for the instance than for the SharePoint DB is not necessarily a problem. The main thing to ensure is that the SharePoint DB is set to the correct collation for SharePoint, which is Latin1_General_CI_AS_KS_WS, and which you have done. You quoted a section from Supportability regarding SQL collation for SharePoint ...


1

A busy SharePoint site is expected to have a lot of users which generate a huge volume of simple database requests. If you had 8 cores and 200 users, you'd fill the queues parallelising just a few user's worth of workloads. By forcing each workload onto one core, you are ensuring a better "minimum standard" experience for everyone while reducing the time ...


1

Use stored procedures, which can validate input before issuing the SQL commands. The user can be given rights to the stored procedure without any other access.


1

One option is create login with default database option and add role member to login user in database USE [master] GO CREATE LOGIN [CUREMD\NewLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[AdventureWorks] GO USE [AdventureWorks]--- Change DB name to Sharepoint DB Name GO CREATE USER [CUREMD\NewLogin] FOR LOGIN [CUREMD\NewLogin] GO USE [AdventureWorks] --- ...


1

View the default database in user's properties, may be it is master, if so, then change it


1

Verify if your user has permision to connect to database Engine. Right click on Logins -> Properties -> Status. Or grant CONNECT for user. CREATE LOGIN [mydomain\mygroup] FROM WINDOWS; GRANT CONNECT SQL TO [mydomain\mygroup]


Only top voted, non community-wiki answers of a minimum length are eligible