I'm in the admin group of a server, but yet I don't have access to the sql server, is there anyway to add myself without the need to reinstall the sql server?

I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem: I have the following code to perform database backups. DECLARE @Filename VARCHAR(256) DECLARE @FileDate VARCHAR(15) DECLARE @Path VARCHAR(50) DECLARE @Name VARCHAR(50) -- specify database backup directory SET @Path = '\MyPath' -- specify filename date SELECT @FileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108),':','') DECLARE db_cursor CURSOR FOR SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb') AND [state_desc] = 'ONLINE' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @Name WHILE @@FETCH_STATUS = 0 BEGIN SET @Filename = @Path + @Name + '_Full_Backup_' + @FileDate + '.bak' BACKUP DATABASE @Name TO DISK = @Filename WITH CHECKSUM, COMPRESSION FETCH NEXT FROM db_cursor INTO @Name END CLOSE db_cursor DEALLOCATE db_cursor Sometimes, only some databases are backed up, suggesting that the cursor is not iterating over all of the rows returned or that the query itself is not returning the names of all the databases it should be. I am trying to understand *why* this happens. I know that the fix is to use a STATIC cursor, suggesting the issue is with the results in the underlying query SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb') AND [state_desc] = 'ONLINE' are changing, but I can't see what would be changing (no database names would change and the Error log doesn't suggest that the database state has changed)

I'm using Ola Hallengren's IndexOptimize script for index maintenance on my SQL Server databases. We have a Clustered and 3 non-clustered indexes on a big table having rows over 685 million. Reorganize was running more than 7 hours till morning on a non-clustered index with option LOB_COMPACTION = ON until it's killed explicitly. This index has only one key column on DATETIME type and no covering columns in there. There is one column with type geography on this table but this column is not part of that index. According to my understanding, LOB_COMPACTION should not be applicable to the index since DATETIME is not a LOB data type. I'm expecting when REORGANIZE run on this non-clurtered index, it should run without LOB_COMPACTION = ON. Here is the script I'm running in my SQL Agent job:

EXECUTE dbo.IndexOptimize @Databases = 'Database1,Database2',
                          @LogToTable = 'Y',
                          @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
                          @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
                          @FragmentationLevel1 = 15,
                          @FragmentationLevel2 = 50,
                          @FillFactor = 90,
                          @Indexes = 'ALL_INDEXES',
                          @SortInTempdb = 'Y',
                          @MaxNumberOfPages = 10000000,
                          @Execute = 'Y';

We did not specify the @LOBCompaction parameter, so it defaults to 'Y'. Has anyone else encountered this issue? Is there a known problem with the script, or am I missing something in my configuration? Any insights or suggestions would be greatly appreciated! Thanks!

I am creating SQL Server Unit Tests. We are testing various stored procedures. In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (truncate/delete) the database tables, and resetup for each test. This way every unit tests will have a clean environment to validate sprocs which insert, select, update, delete, etc, Does anyone where or how to reinitialize the tables values in Sql Unit Testing? Resources are pretty new for unit testing in SQL SSDT VS 2017, so I think lot of people are trying to figure out and understand. Feel free to show or add pictures below. http://www.sqlservercentral.com/articles/Unit+Testing/155651/ http://www.erikhudzik.com/2017/08/23/writing-sql-server-unit-tests-using-visual-studio-nunit-and-sqltest/ Pictures in Visual Studio SSDT: Also, trying to review this class in SQLDatabaseSetup.cs: [TestClass()] public class SqlDatabaseSetup { [AssemblyInitialize()] public static void InitializeAssembly(TestContext ctx) { // Setup the test database based on setting in the // configuration file SqlDatabaseTestClass.TestService.DeployDatabaseProject(); SqlDatabaseTestClass.TestService.GenerateData(); } } } using Microsoft.Data.Tools.Schema.Sql.UnitTesting;

I'm trying to view SQL Server logs on a specific server and is missing Windows NT section in same. Though other server are showing that section. In the screenshot shared below, I highlighted section that I'm not getting for one of the SQL Server instances. **Question** - Is there any configuration or any required steps, for enabling **Windows NT** section in SQL Server Logs? Server details with the issues: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) Server details having no issue or the screen server details Microsoft SQL Server 2016 (SP2-CU11-GDR) (KB4535706) - 13.0.5622.0 (X64) Dec 15 2019 08:03:11 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

When attempting to start the SQL Full-text Filter Daemon Launcher service through the SQL Server Configuration Manager, it attempts to launch, but then fails. In the event log we get the following error message: "The SQL Full-text Filter Daemon Launcher (instancename) service failed to start due to the following error: Access is denied." It is currently set in "Manual" start mode. And uses the default service account generated at installation, "NT Service\MSSQLFDLauncher$instancename". Named pipes is enabled. This service was not installed as part of the original SQL Server installation, but done after. This is a SQL Server 2016 Enterprise (13.0.5216.0) instance.

We're running SQL Server 2016 Standard Edition with database mirroring in synchronous mode and automatic failover enabled. Our .NET applications use connection strings with Failover Partner. Occasionally (1–4× per day), some applications try to connect to the mirror server, even though the principal is online and fully healthy. We see login failures on the mirror (Error 4060 – "Cannot open database ...") but no issues or alerts on the principal side (Redgate, Zabbix, SQL logs, etc.). This affects different databases (we have dozens mirrored) and different apps or users – it’s not tied to one specific service. Extended Events show only mirror-side errors, with various client hosts and application names. PerfMon once showed a spike in TCP hard connects around the same time, but nothing consistent. Based on online research such as [Database mirroring login failure attempts on mirror server](https://serverfault.com/questions/236163) , we suspect it may be caused by things like expired connections in the pool, network hiccups, SQL Browser issues, or connection resets (e.g. firewall/NAT dropping idle sockets). When this happens, the .NET SqlClient might believe the primary is unreachable and trigger an immediate failover attempt. So far this seems to be expected behavior, but it causes false-positive alerts in our monitoring tools. We're trying to confirm: 1) Is this behavior expected from ADO.NET when Failover Partner is used? 2) Is there any way to reduce/suppress the probing of the mirror? 3) Are there best practices for detecting/logging/tuning this (e.g., connection settings, retry logic)? 4) Should we just tune our monitoring and ignore these errors as “normal noise”?

Found an error log informing that data movement to a secondary availability replica was suspended. I'm not sure whether the cause of the suspension is relevant to answering my question, but the cause was "Error 3456 ... could not redo log record" etc. This is a known issue with a resolution involving patching SQL Server. However this condition wasn't addressed for a few days. The nightly full backup file sizes (taken against the primary replica) began increasing by about 10% per night. The database also has log backups made every 15 minutes for most hours in the day, but log backups are disabled during a window at night while indexes are rebuilt and full backups made. To get the secondary back online it was required to restore from backup. In this instance some issues were encountered - first an attempt was made to rejoin the AG but that hung for ages. Then a command was given to have that replica database removed from the AG but of course it hung too. Killed both those processes but could not kill the session that had faulted in redoing the log records (as it is not a user session - although its ID was above 50 by the way). I therefore restarted SQL Server on that replica before eventually restoring from backup and rejoining the AG. Having resolved the issue, the next nightly full backup file size (on the primary, again) had reduced back to normal size. Why would the *full* backup file increase in size until this issue was resolved? Is it because the log could not be cleared and this was being included in the full backup? If so, then what was getting backed up in the 15-minutely log backups? Imagine for a moment that the primary had suffered a failure during this period - could I not have restored from the available full and log backup files I had (ie. because obviously something remained in the log such that it wasn't clearing)? And if it was a case of the log not clearing, then why (because the secondary had been suspended, so surely the primary no longer waits for the secondary before committing its own log)? Afterthought: the system process that I could not kill without a server restart was the one that faulted while attempting to apply the logs to the secondary - leading the secondary to be suspended. I suppose this is a "long running transaction"? If this is the reason, can someone still please clarify for me the earlier questions - particularly about how "at risk" the primary was until this was resolved?

I have a stored procedure which takes about 4 min to complete. I added some logging and discovered that the reason it is slow is the Cross Apply queries, of which there are 5. Each one takes about 40 seconds to complete. I'm sure there are ways to improve the performance, which I'm hoping someone can help me with. Here is the query: update @Data1 set Open = b.Open_Sum from @Data1 a cross apply (Select Count(*) as Open_Sum from [Data] c where (c.Sunday > a.Week or c.Sunday is Null) and c.Project = a.Project and c.Item = a.Item and c.IPT = a.IPT and c.Product = a.Product and c.Center = a.Center and c.Name = a.Name and c.Project in (select * from SplitParmList(@Project )) and c.Product in (select * from SplitParmList(@Product )) and c.Item in (select * from SplitParmList(@Item )) and c.Area in (select * from SplitParmList(@Area )) and c.IPT in (select * from SplitParmList(@IPT )) and c.Name in (select * from SplitParmList(@Name )) and c.Center in (select * from SplitParmList(@Center )) and c.Effectivity in (select * from SplitParmList(@Effectivity)) and c.Planned in (select * from SplitParmList(@Planned)) and CURRENT = 'Y' ) as b Query Explanation: 1. Populates a table variable (@Data1) with the aggregate count, in this case records still open 2. The SplitParmList is a function that parses a parameter passed into the SP. ie. ('a,b,c,d,e') 3. The Data table is indexed I'm looking for suggestions that may help speed this query up. As requested, here is the SplitParmList function:
CREATE FUNCTION [dbo].[SplitParmList] (@StringList VARCHAR(MAX)) RETURNS @TableList TABLE( StringLiteral VARCHAR(128)) BEGIN DECLARE @StartPointer INT, @EndPointer INT SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList) WHILE (@StartPointer Here is the index:
CREATE CLUSTERED INDEX [ClusteredIndex-20210222-092308] ON [dbo].[Data] ( [Name] ASC, [Center] ASC, [Project] ASC, [Effectivity] ASC, [Product] ASC, [Drawing] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [dbo]

I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.

I have installed an instance of SQL Server 2016 SP2 called DNF containing 2 DBs and also the system DBs. After providing the instance to the dev team, they realised that they need to change the collation. They changed the collation for the 2 DBs by themselves and asked me to make the change for the system DB. I took a backup of all databases and rebuilt the system DB. It works fine and all the dbs changed to new collation. I made a mistake by restoring the msdb, model and finally I tried to restore the master. I succeeded to restore the model and msdb and faced a lot of issues when coming to themaster. I ran the query select name, collation_name from sys.databases and found that the model and msdb have the old collation, and this is logic and was a mistake from me. I tried to rebuild again, but collation refused to change on model and msdb and found the sql server and sql server for the instance stopped and won't start anymore. The situation now is : master and the 2 non system DBs have the new collation and model, msdb have the old one, and the services won't start. What is the way to solve this issue? Does an instance repair will solve this issue? Or there is something else that I can do? .

My backup jobs are failing with following error when check it's history. I have already checked this link regarding maximumerrorcount value: [SQL Server 2008 - Change the MaximumErrorCount or fix the errors](https://stackoverflow.com/questions/3250648/sql-server-2008-change-the-maximumerrorcount-or-fix-the-errors) . At this point I'm not sure what to do if maximumerrorcount is only masking the actual problem. Log job history (DB_TaxExemption_BKP.Subplan_1): > Step ID 1 Server INHOUSE-DB Job Name DB_TaxExemption_BKP.Subplan_1 > Step Name Subplan_1 Duration 00:00:00 Sql Severity 0 Sql Message ID 0 > Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 > Message Executed as user: NT AUTHORITY\LOCAL SERVICE. Microsoft (R) > SQL Server Execute Package Utility Version 14.0.1000.169 for 64-bit > Copyright (C) 2017 Microsoft. All rights reserved. Started: 10:12:00 > PM Error: 2022-03-30 22:12:00.47 Code: 0xC00291EC Source: > {4D6AAF94-D3FC-4873-9F66-E35E323A6BEE} Execute SQL Task Description: > Failed to acquire connection "Local server connection". Connection may > not be configured correctly or you may not have the right permissions > on this connection. End Error Warning: 2022-03-30 22:12:00.47 Code: > 0x80019002 Source: OnPreExecute Description: SSIS Warning Code > DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but > the number of errors raised (1) reached the maximum allowed (1); > resulting in failure. This occurs when the number of errors reaches > the number specified in MaximumErrorCount. Change the > MaximumErrorCount or fix the errors. End Warning Error: 2022-03-30 > 22:12:00.48 Code: 0xC0024104 Source: Back Up Database (Full) > Description: The Execute method on the task returned error code > 0x80131904 (Login failed for user 'backupuser'.). The Execute method > must succeed, and indicate the result using an "out" parameter. End > Error ## Heading ##Error: 2022-03-30 22:12:00.48 Code: 0xC0024104 > Source: {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX} Description: The > Execute method on the task returned error code 0x80131904 (Login > failed for user 'backupuser'.). The Execute method must succeed, and > indicate the result using an "out" parameter. End Error Warning: > 2022-03-30 22:12:00.48 Code: 0x80019002 Source: OnPostExecute > Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The > Execution method succeeded, but the number of errors raised (2) > reached the maximum allowed (1); resulting in failure. This occurs > when the number of errors reaches the number specified in > MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End > Warning DTExec: The package execution returned DTSER_FAILURE (1). > Started: 10:12:00 PM Finished: 10:12:00 PM Elapsed: 0.219 seconds. The > package execution failed. The step failed I know for sure that the user for this backup job is sysadmin and it is not locked out all rights are grated. And this is production so I cannot take many risks. What am I missing ?

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back? Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE? I know certain statements still execute though like PRINT "MESSAGE". I'm just trying to get an idea of what kinds of events still occur.

I have modified sp_send_dbmail procedure in sql server. Laster mails stopped working. Dbmail is shutting down automatically and getting this error while I restart process >Exception Information ===================\ Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: The Transaction not longer valid. \ Data: System.Collections.ListDictionaryInternal TargetSite: Void ValidateConnectionAndTransaction() HelpLink: NULL Source: DatabaseMailEngine HResult: -2146232832 StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction() at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction() at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout) Please do help me

Owing to a ransomware attack on our production server Reporting Services went down. This server was installed few years ago and its been in production for long. We have quite a few reports on this server. It was decided we build a new server and migrate the ReportServer Database. Unfortunately we don't have the Encryption Key. Because the service is down I can't even take the back up now, its too late. I tried backing up the ReportServer and ReportServerTempDB databases and then restoring those to the new servers. Restore went well, I even changed the database from the reporting services configuration manager. But, When I tried to go to the web portal URL, It shows the error , The report server isn't configured properly. Check the report server trace log for details. I found out that logs shows the scale out deployment errors. I believe since I don't have the encryption key file and password I am receiving this error. What can I do to get reporting services up and running...? What should be my approach..? Can I restore the encryption key some how or create a new one with rskeymgmt..? If not then can I delete it (if it is possible) and try to reconfigure all the reports again..? (There are about 350 different reports on this server). Please help.

I need to move two databases, WES and WILL from a Win2012/SQL2012 instance to a Win2016/SQL2016 instance for a software upgrade. I'm using the full backup files from the SQL2012 maintenance plans. I've verified I can restore them to the original SQL2012 instance with no errors. Restoring WES to SQL2016 reports no errors, but dbcc checkdb shows errors: >Msg 8939, Level 16, State 98, Line 13 Table error: Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data), page (1:453). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4. ...etc Restoring WILL to SQL2016 stops partway through the upgrade steps with >Database 'WILL' running the upgrade step from version 805 to version 806. Msg 601, Level 12, State 3, Line 2 Could not continue scan with NOLOCK due to data movement. I thought the upgrade between versions was automatic. Is there anything else I need to do?

I am running this openrowset query about jobs on my current server and I see that the result set is not correct. I was expecting a bunch of jobs but I only get one. when I run the following piece of code I can see why:

select [my current server] = @@servername

select *
from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',
'select [my current server] = @@servername') AS a

that gives me the following result: 'MATHURA` is my server name (machine name) 'MATHURA\SQL2016` is the server name\instance name where I am executing this query obviously when I pass Server=(local); to the openrowset it takes it to be my server name, but I am running on the mathura\sql2016 instance. the question is: how can I pass the currently named instance to the openrowset ?

(New to SSIS. None of the similar questions mention this particular impersonation error.) I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. The credential object is for an AD account. I'm able to log in with the password to other services, but attempting to run the Agent job errors out in the Execute job step. Running the job as SQL Agent service account worked, but it doesn't have access to the filesystem that the SSIS job is supposed to pull data from. I checked the agent log file and it's this: SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000] I checked the Windows security log in Event Viewer: Failure Information: Failure Reason: Unknown user name or bad password. Status: 0xC000006D Sub Status: 0xC000006A I tried it with my (sysadmin) user ID and password in a different credential / proxy and got the same error. I know that account works. We granted both SQL Agent and this account user rights to logon as batch job, and the agent account permission to impersonate. We also tried making the various accounts Windows admins and sysadmins, but nothing has helped. This worked right out of the box in our QA domain. Any suggestions how to get this to work? What domain/local/SQL Server security settings would enable or prevent the agent impersonating a user? Windows 2016 + SQL Server 2016

Query store has been running for years on the server without any noticeable disk IO spikes on log backups. We run on SQL Server 2016 EE version 13.0.5622.0 Recently we experienced a sudden surge in disk I/O every twenty minutes or so. We backup logs every five minutes, and every third or fourth backup would increase dramatically (from ~100 MB to >3 GB in size). It happened at 2 AM without any changes being done to the server. I dumped the contents of some of the log files and noticed a huge surge in swappages on the Query store system data tables when these log backup IO spikes occured. Disabling query store entirely caused the disk IO to resume at normal levels when backing up the log. I presume the reason for the large transaction log backups would be online index rebuilds on the query store tables. We had configured query store to capture all transactions, auto cleanup, retain data for 180 days and max the data cache to 15 GB. So far so good. I re-enabled query store after changing the configuration to auto, auto, 120 days and 8 GB, but now we are starting to see the same type of spikes in disk IO every 20 minutes or so when the transaction log is being backed up. Have anyone had an experience like this and what did they do to fix the issue?

I was running SQL Server 2016 SP3 with 2022/06/13 GDR. The new GDR on 2023/02/14 came out - I applied it on May 4th. My integrity scan SQL Agent Job went from 5 mins to 20+ overnight. Nothing else changed. Anyone else seen these issue? DBs on this server here are low usage.... SQL Sentry repository and some once a year used HANA transaction simulators. So its not like some DB doubled in size.