March 8, 2018 at 7:37 am
I am hoping you can help me out here.
Specifications
SQL Server 2014 SP2 CU8
Windows Server 2012
3 instances on one server (but hardly used)
Scenario
We have a daily restore that happens - taking .bak files from our production server and restoring them to another Non Production server. We use PowerShell to copy the bak files over then we have an SQL Agent job that drops all the current databases and restores the bak files one at atime. The process has been working for overone year now.
Problem:
Suddenly the restore is failing with the following error message
RESTORE FILELIST is terminatingabnormally. [SQLSTATE 42000] (Error 3013) The instance of the SQL Server Database Engine cannot obtain a LOCKresource at this time. Rerun yourstatement when there are fewer active users. Ask the database administrator tocheck the lock and memory configuration for this instance, or to check forlong-running transactions. [SQLSTATE HY000] (Error 1204)
I am also getting the following error in the SQL Server Error Log
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7216 seconds. Working set (KB): 542564, committed (KB): 2194080, memory utilization: 24%.
Actions:
We checked the memory configuration on the instance and it is set to the default.
Today we rebooted the server – it’sa VM
Thoughts
The restore happens at 2AM, so there are litteraly are no users connecting to the machine,
We are constantly dealing with SAN latency issues and I wonder if this could be the cause.
Please any help isappreciated.
Jeff
March 8, 2018 at 8:45 am
Maybe a silly suggestion but can you set the DB offline and then run the restore or maybe change your script to kill active sessions?
ALTER DB [DBNAME] set offline with rollback immediate
GO
Restore DB..................
March 8, 2018 at 11:23 am
Thanks for the reply
we are actually dropping the databases and then restoring.
The memory settings are both default Min = 0 and max = 2147483647
We have been using these setting for over one year now with no errors
I am asking the system administrators to run a full diag on the host to see if there are any hardware or driver errors
Jeff
March 8, 2018 at 11:41 am
This morning I rebooted the server and re ran the job and It completed fine
I am hoping the reboot takes care of the problem.
Jeff
March 9, 2018 at 1:31 pm
you can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
go
March 9, 2018 at 2:13 pm
goher2000 - Friday, March 9, 2018 1:31 PMyou can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
go
GOD NO!!!! NEVER EVER PUT A PASSWORD IN CLEAR TEXT!!!!
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2018 at 2:15 pm
jayoub - Thursday, March 8, 2018 11:41 AMThis morning I rebooted the server and re ran the job and It completed fine
I am hoping the reboot takes care of the problem.
Unless your scripts are putting the databases that you're trying to drop into the "SINGLE USER" mode, you will likely run into this problem again. The reboot you did probably cleared a connection somewhere.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2018 at 2:20 pm
Jeff Moden - Friday, March 9, 2018 2:13 PMgoher2000 - Friday, March 9, 2018 1:31 PMyou can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
goGOD NO!!!! NEVER EVER PUT A PASSWORD IN CLEAR TEXT!!!!
well you dont have to if you give permission to service account of your destination server on backup folder..
March 9, 2018 at 2:24 pm
goher2000 - Friday, March 9, 2018 2:20 PMJeff Moden - Friday, March 9, 2018 2:13 PMgoher2000 - Friday, March 9, 2018 1:31 PMyou can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
goGOD NO!!!! NEVER EVER PUT A PASSWORD IN CLEAR TEXT!!!!
well you dont have to if you give permission to service account of your destination server on backup folder..
another way, you can create credentials and use them
March 9, 2018 at 2:27 pm
goher2000 - Friday, March 9, 2018 2:24 PMgoher2000 - Friday, March 9, 2018 2:20 PMJeff Moden - Friday, March 9, 2018 2:13 PMgoher2000 - Friday, March 9, 2018 1:31 PMyou can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
goGOD NO!!!! NEVER EVER PUT A PASSWORD IN CLEAR TEXT!!!!
well you dont have to if you give permission to service account of your destination server on backup folder..
another way, you can create credentials and use them
I never tried but you might be able to run it using
March 9, 2018 at 2:41 pm
goher2000 - Friday, March 9, 2018 2:20 PMJeff Moden - Friday, March 9, 2018 2:13 PMgoher2000 - Friday, March 9, 2018 1:31 PMyou can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
goGOD NO!!!! NEVER EVER PUT A PASSWORD IN CLEAR TEXT!!!!
well you dont have to if you give permission to service account of your destination server on backup folder..
Doesn't matter. You NEVER put a password in clear text, period!
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2018 at 10:02 am
Jeff Moden - Friday, March 9, 2018 2:13 PMgoher2000 - Friday, March 9, 2018 1:31 PMyou can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
goGOD NO!!!! NEVER EVER PUT A PASSWORD IN CLEAR TEXT!!!!
Not only should you never put a password in clear text - but you don't even need to map a drive. You can perform the restore directly over the UNC path:
RESTORE DATABASE demodb FROM disk='\\serverserver\backupdir\backupfile.bak';
You do have to setup permissions for the SQL Server service account - or utilize a proxy account to get access to the file share but that is trivial and much safer than mapping a drive and trying to use a mapped drive.
Note: unless you have a really fast and dedicated backup network - expect your restore process to take much longer than expected. It is often much faster to copy the file to a local drive and restore than actually restoring across the network.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 11, 2018 at 11:03 am
Jeffrey Williams 3188 - Saturday, March 10, 2018 10:02 AMJeff Moden - Friday, March 9, 2018 2:13 PMgoher2000 - Friday, March 9, 2018 1:31 PMyou can probably avoid additional resources used by power-shell by simply mapping the drive on the destination server through tsql and restore the backup, you do not even need to copy file
exec xp_cmdshell "net use x: \\sourceserver\backupdir /user:domain\user password"
go
restore database demodb from disk='x:\demodb.bak'
go
exec xp_cmdshell "net use x: /delete"
goGOD NO!!!! NEVER EVER PUT A PASSWORD IN CLEAR TEXT!!!!
Not only should you never put a password in clear text - but you don't even need to map a drive. You can perform the restore directly over the UNC path:
RESTORE DATABASE demodb FROM disk='\\serverserver\backupdir\backupfile.bak';
You do have to setup permissions for the SQL Server service account - or utilize a proxy account to get access to the file share but that is trivial and much safer than mapping a drive and trying to use a mapped drive.
Note: unless you have a really fast and dedicated backup network - expect your restore process to take much longer than expected. It is often much faster to copy the file to a local drive and restore than actually restoring across the network.
Precisely. And, if you use backup compression combined with a couple of tweaks to the backup command and the other things are in order as you say, then you end up doing what I'm doing and backing up 2+TB of data in about 90 minutes. Restores take a similar amount of time provided that your log files and related VLFs are in good shape. If you can handle it security-wise, "Instant File Initialization" helps a lot with the restore of the typically much larger MDF file.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2018 at 2:30 pm
Thank you very much for the posts
We actually do not restore through the network we take full backup in PRD, copy the files to the NON-PRD via Powershell within an SQL Agent Job and then do the restore locally. I dont think any passwords are need but I will double check this since you brought it up. So far after the reboot, the job has run successfully two times and I will see on Monday if it still works.
I will keep you posted
Again thank you very much for the help.
Jeff
March 27, 2018 at 12:40 pm
I am also getting the following error in the SQL Server Error Log
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7216 seconds. Working set (KB): 542564, committed (KB): 2194080, memory utilization: 24%.
How much memory is in your server? Are their any other applications on this server? The reason I ask is you are not putting a cap on the amount of memory that SQL Server can use. So, SQL Server will put as much into memory as possible. Meaning that it can consume ALL of the memory on the server and bringing the server to a crawl starving the OS. It is my practice to put a max on how much memory SQL Server can consume. How much, it depends. if this is ONLY a database server you should leave about 4 gig of memory free for the OS and anything else that needs to run. I believe the above message is attempting to tell you that SQL Server took all of the memory in the server and the OS is pushing some of what SQL Server has put into memory out.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply