|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
An Interrupted Restore | |
I was restoring a database to my development server from a backup on a network share. This was a large, 500GB database and the process was taking a long time. There was a network interruption to the process and I got a failure message in SSMS. However, the database was shown as "restoring" in SSMS. The network is back now. What should I do to most easily complete the restore? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
The PowerShell Copy I have this PowerShell code: $source="c:\fileloading" #location of starting directory $destination="c:\fileloaded"; #location where files will be copied to $files="*dys_ihhist*" #files matching this pattern $a = get-childitem $source -filter $files $a | foreach {copy-item $_.fullname $destination\$($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)} What happens when I run this? Answer: The files matching the filter are copied from c:\fileloading to c:\fileloaded with the date appended to the file name Explanation: This PowerShell script matches files in c:\fileloading that have a "dys_ihhist" in the filename only, not the extension. These files are then copied, not moved, to c:\fileloaded with the date appended to the filename. Ref:
|
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Select blocking a Truncate stamemet - Hi All, I am seeing a SELECT blocking a TRUNCATE statement. THE SELECT is running over 4 hours and it is waitype is AYSN_NETWORK_IO. Isolation level is default read committed isolation level. Why or when does a SELECT takes "Sch-S” lock? I see this lock being acquired using sp_whoisactive. Truncate is waiting for="Sch-M" lock. How […] |
What's the right way of capturing expensive queries? - Hi All, While capturing expensive queries by CPU or IO, why it is important to group queries by query_hash and then tune them instead of directly pulling top 10 queries from sys.dm_exec_query_stats DMV?. What am I missing ? is it wrong way to capture expensive queries> if so, I want to understand why? please share […] |
High CPU & blocking - Hello SQL Experts, We have SQL2017 enterprise edition with about 60 databases. Recently we are seeing very high cpu spikes. We have DB maintenance job running twice/wk (Hallenger Script). Manually updated the statistics but it didn't helpful. I don't have baseline about the no of transactions or queries that ran previously. Followed the guidance provided […] |
SQL Server 2017 - Development |
Join on Column A when Column B is Null - hi, I need to join on a table T1 AND Table T2. Something like this : Select * from Table T1 JOIN TableT2 on T1.A =T2.A but here , condition is if (T1.a or t2.a is null ) then join on (T1.b =t2.b ) where t1.b and t2.b is not null. How to achieve […] |
Administration - SQL Server 2014 |
SQL 2014 Standard - All SQL agent jobs disabled after patch - We have a SQL server 2014 (with SSRS) and over the weekend the list of patches below got installed around 1AM. KB5029312 KB5028970 KB5028957 KB5029368 The following morning it was noticed that no jobs ran and when we checked, EVERY single SQL agent job had a red x and were all disabled. I have never […] |
SQL 2012 - General |
The target princial name is incorrect. Cannot generate SSPI context. - I've just created new certs, new SPNS and applied via SSCM. Within AD I've ticked trust this user for delegation, use kerberos only to all of my new SPNS. SQL starts fine & I can logon locally but get the above error logging on from a different machine (therefore attempting kerberos). I'm sure I've done […] |
The target princial name is incorrect. Cannot generate SSPI context. - I've just created new certs, new SPNS and applied via SSCM. Within AD I've ticked trust this user for delegation, use kerberos only to all of my new SPNS. SQL starts fine & I can logon locally but get the above error logging on from a different machine (therefore attempting kerberos). I'm sure I've done […] |
SQL Server 2019 - Administration |
msdb job history permission error - SELECT * FROM msdb.dbo.sysjobhistory error: The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'. the user is a member of the following msdb role: SQLAgentReaderRole SQLAgentUserRole SQLAgentOperatorRole What other permissions do i need? |
How to Backup SQL Active/Passive cluster Database to File Share - Hi We have 2 node Active/Passive SQL serer cluster with 2 databases, due to the backup disk capacity limitations we are planned to backup databases to the File Share location (\\192.168.0.x\Backup). When we trying to connect File Share to the WSFC there is no option to add file share it is showing only the availble […] |
Query joins and SQL Server mirroring (2019) - Have a bit of a situation with database mirroring and table joins, hopefully someone can shed some light on possible solutions. Scenario: DB01 is on Server01 and is mirrored to Server02 DB02 is on Server01 and is mirrored to Server02 DB03 is on Server 03 and is mirrored to Server04 Linked servers are setup between […] |
Color Particular Column - Hi I want to color particular column . If value is -ve then color should be red Thanks |
SQL Server 2019 - Development |
Powershell script run through Task Scheduler - Cannot validate argument on param - I have a Powershell script which is being called through Windows Task Scheduler My script is set up as follows: param( $FileDirectory, $VerboseDirectory, $ServerInstance, $Database ) Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query "EXEC spImportReturn @FileDirectory = '$FileDirectory'" -verbose 4> $VerboseDirectory Now I then call this Powershell script from Windows Task Scheduler as follows: Executable: […] |
Powershell |
change delimeter - This script converts from xlsx to csv, but how can I change delimiter from comma to semicolon, and pickup the same file name from multiple folders to process? Thanks. # To execute the script without agreeing with the execution policy Set-ExecutionPolicy Bypass -Scope Process # Defines the directory where the file is located $dir = […] |
Integration Services |
Job Step Properties - Configuration - Parameter Issue - Hi, I am fairly certain that both myself and my colleagues could ALL change the configuration parameter on a Job Step, but I am now informed that only a Sys Admin or the job owner can change this value. ( currently on SSMS 18.12.1 ) This is a bit annoying as it makes being able […] |
SQL Server 2022 - Development |
Proc and Table permissions (Grantor column) - I have several tables and stored procedures that have an user ID of a person that is no longer with the company that is listed in the Grantor Column under schema properties permissions window. We ran into an issue with a stored procedure executing until we change the Grantor (the user Id) Column to a […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |