Ms sql ошибка 1073548784

(9) Вот. Скопировал из нашей базы. Только MYDB поменяй на имя базы.

declare @minRows int

set @minRows = 10000

declare @reindexQuery nvarchar(max)

set @reindexQuery =

REPLACE(REPLACE(

cast(

(

select

‘BEGIN TRY ALTER INDEX ‘+idx.name+’ ON ‘+ sc.name+’.’+ t.name+

CASE

WHEN st.avg_fragmentation_in_percent > 30 THEN ‘ REBUILD WITH (ONLINE=OFF) END TRY BEGIN CATCH ALTER INDEX ‘+idx.name+’ ON ‘+ sc.name+’.’+ t.name+ ‘ REBUILD WITH (ONLINE=OFF) END CATCH;’

ELSE ‘ REORGANIZE END TRY BEGIN CATCH END CATCH;’

END as query

from sys.dm_db_index_physical_stats( DB_ID(),NULL,NULL,NULL,NULL) st

join sys.tables t on (st.object_id=t.object_id)

join sys.schemas sc on (sc.schema_id=t.schema_id)

join sys.indexes idx on (t.object_id=idx.object_id and st.index_id=idx.index_id)

join sys.partitions p on (p.index_id=idx.index_id and p.object_id=idx.object_id)

where p.rows > @minRows and st.avg_fragmentation_in_percent > 30

order by st.avg_fragmentation_in_percent desc

FOR XML PATH(»), TYPE

) as nvarchar(max))

,'</query>’,’;

‘),'<query>’,»)

print @reindexQuery

exec (@reindexQuery)

null

ms sql error 1073548784 access to path ... denied 

Hi,

I am having a Microsoft SQL Server 2012 Standard version 11.0.6020.0 installation where all our SharePoint databases are placed.

select @@version returns:

Microsoft SQL Server 2012 (SP3) (KB3072779) — 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

We are having a rather large SharePoint environment (6 SharePoint servers) and we have over 70 SharePoint related databases (more than 6 TB data).

During the last couple of months, we have upgraded our SharePoint environment to SharePoint SP1 and SharePoint CU september 2015 edition and upgraded the SQL server to MSSQL SP3.

After all these upgrades, we now experience that our nightly «Rebuild index tasks» jobs fails every night.

Alle the databases are placed in 4 different Rebuild index tasks, and each job is configurated like this:

«Object:» Tables and Views

«Free space options»:

x Default free space per page

«Advanced options»

x Sort results in tempdb

x Keep index online while reindexing

x Rebuild indexes offline

But now they fail with the following error:

Failed:(-1073548784) Executing the query «ALTER INDEX [UserInfo_Mobile] ON [dbo].[UserInfo] …» Failed with the following error: «Transaction (Process ID 1468) was deadlocked on lock resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.». Possible failure reasons: Problems with the qurey, «Resultset» property not set correctly, parameters not set correctly, or connection not established correctly.

Each job used to run between 2 or 3 hours before completing succesfuld, and now they run for around 2 hours before failing with the deadlock error above.

I have tried to change the running schedule to different times during the night where the user workload on the SharePoint environment not should be so large, and no other SQL jobs are running (diffential backup or update statistics).

The only SQL job that are running, is the transaction log backup every 3 hours 24/7.

If I just create a job with just one of the databases and run it, it also fails after running for 13 minuts (the database is one of the smaller (~20 GB —  but it is active in the SharePoint environment).

My weekly «Update statistics job» and «Integrity check job» runs succesfull without errors (The integrity check is performed on all online databases and include indexes).

Low diskspace is not an issue, there is plenty availiable on each disk and the transaction log drive also have plenty.

There is not errors or warnings in SharePoint about this issue.

Do you have any suggestions to what I can try to get the rebuild index tasks to run succesfully again?

Kind regards,

Carl-Marius

Hi,

I am having a Microsoft SQL Server 2012 Standard version 11.0.6020.0 installation where all our SharePoint databases are placed.

select @@version returns:

Microsoft SQL Server 2012 (SP3) (KB3072779) — 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

We are having a rather large SharePoint environment (6 SharePoint servers) and we have over 70 SharePoint related databases (more than 6 TB data).

During the last couple of months, we have upgraded our SharePoint environment to SharePoint SP1 and SharePoint CU september 2015 edition and upgraded the SQL server to MSSQL SP3.

After all these upgrades, we now experience that our nightly «Rebuild index tasks» jobs fails every night.

Alle the databases are placed in 4 different Rebuild index tasks, and each job is configurated like this:

«Object:» Tables and Views

«Free space options»:

x Default free space per page

«Advanced options»

x Sort results in tempdb

x Keep index online while reindexing

x Rebuild indexes offline

But now they fail with the following error:

Failed:(-1073548784) Executing the query «ALTER INDEX [UserInfo_Mobile] ON [dbo].[UserInfo] …» Failed with the following error: «Transaction (Process ID 1468) was deadlocked on lock resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.». Possible failure reasons: Problems with the qurey, «Resultset» property not set correctly, parameters not set correctly, or connection not established correctly.

Each job used to run between 2 or 3 hours before completing succesfuld, and now they run for around 2 hours before failing with the deadlock error above.

I have tried to change the running schedule to different times during the night where the user workload on the SharePoint environment not should be so large, and no other SQL jobs are running (diffential backup or update statistics).

The only SQL job that are running, is the transaction log backup every 3 hours 24/7.

If I just create a job with just one of the databases and run it, it also fails after running for 13 minuts (the database is one of the smaller (~20 GB —  but it is active in the SharePoint environment).

My weekly «Update statistics job» and «Integrity check job» runs succesfull without errors (The integrity check is performed on all online databases and include indexes).

Low diskspace is not an issue, there is plenty availiable on each disk and the transaction log drive also have plenty.

There is not errors or warnings in SharePoint about this issue.

Do you have any suggestions to what I can try to get the rebuild index tasks to run succesfully again?

Kind regards,

Carl-Marius