SQL 2012 :: Backups And Integrity Tasks No Longer Working
May 29, 2014
Up until a couple of days ago all my maintenance plans were working.
Now they are all failing with the following errors -
Message
Executed as user: MHS2sql2008. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:07:02 Progress: 2014-05-29 10:07:03.20 Source: {CE851AA2-045F-429A-885E-E60E75A38639} Executing query "DECLARE @Guid UNIQUEIDENTIFIER
[Code] ....
The package execution failed. The step failed. Now I have changed the SQL Server Agent service account to use a network account called "sql2008".Stopped and started the Agent service.
The account has access to the folders it is trying to back up to. It's a full admin on the sql box and the box where I am trying to backup to?
View 4 Replies
ADVERTISEMENT
Jul 11, 2015
Getting "The specified network name is no longer available" during SQL Backups dumping to Network share.
- problem started occurring on July 3, 2015
- our Ola Hallengren backup solution deployed to over 150 SQL Servers.. was running fine for almost 2 years
- Occurring in multiple SQL Server environments: W/Server 2012 Ent, W/Server 2008 Ent, SQL 2012 Ent, SQL 2008 R2 Ent
- We're utilizing latest Ola Hallengren backup solution (Jan 2015 release)
- Dumping to network share (jobs running under SQL Agent account w/ local admin & sysadmin on server and full rights to Network Share
View 1 Replies
View Related
Feb 18, 2015
I am thinking about moving some backups from the local machine to a remote server. Right now, I am using Ola Hallengren script for backups. The script performs checksum on the backup while it's being written on the disk. I am going to move the files using Robocopy utility. How do I check the data integrity once the backups are transferred to the remote server?
View 5 Replies
View Related
Jun 15, 2015
We are running a SQL Server 2008R2 64-bit database system on a Windows 2012 R2 64-bit Standard system. I have noticed in recent weeks that our differential backups periodically are taking longer than expected to complete. The usual amount of time is about one hour, but on several occasions, it has taken upwards to five hours. The nights when the job takes longer to complete are on Friday.
I did some checking online, and one possible reason for this issue is my scheduling the reindexing of the database on the morning of the Differential backup. For example, this past Friday the reindexing occurred at 1:00 AM with the Differential running at 10:00 PM that night.The article that I read suggested the reindexing, which takes several minutes, if that, to complete, should be scheduled to run just before the Full backup job.
View 2 Replies
View Related
Feb 11, 2005
Hello, I had a notification set up using xp_sendmail working fine for a while. Recently I updated the SQL Server (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) to sp3a and we moved the mailbox (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) that I was using to a Exchange 2003 server. I can still send my notification if I use the domain ID that runs the SQL service and the sa ID, but not the NT ID's that were running it before. I have users use NT authentication (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) from a domain that's different than the one that the SQL server (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) resides. There is a trust and nothing has changed with that. Below are the results when I try to run the notification using an NT ID. This ID has full permissions over the SQL server. SQL Mail session started.ODBC error 8198 (42000) Could not obtain information about Windows NT (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) group/user 'INTERNALxxx.xxx'. Stopped SQL Mail session. As you can see SQL Mail starts and stops ok, but I get the error on the xp_sendmail itself. I can run xp_logininfo to return all of the ID's using this NT login. But if I run xp_logininfo just on the problem ID, I get the following results. EXEC master..xp_logininfo@acctname = 'INTERNALxxx.xxx' Server: Msg 8198, Level 16, State 24, Procedure xp_logininfo, Line 58Could not obtain information about Windows NT group/user 'INTERNALxxx.xxx'. Here's when it works. EXEC master..xp_logininfo BUILTINAdministrators group admin BUILTINAdministrators NULLINTERNALxxx.xxx user admin INTERNALxxx.xxx NULLINTERNALSxx Axx group admin INTERNALSxx Axx NULLSISDOMsxx.dxx.axx user admin SISDOMsxx.dxx.axx NULLSISDOMSxx.Dxx.Axx group admin SISDOMSxx.Dxx.Axx NULLINTERNALlxxx.rxxx user user INTERNALlxxx.rxxx NULL The ones in bold work for everything. Please advise? Julie
View 2 Replies
View Related
Apr 13, 2007
I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:
Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.
Thanks
E.G.
View 10 Replies
View Related
Jun 1, 2015
I have a database in the 3rd normalized form. There is a need to load rows into a child table. To avoid having to drop RI, an ALTER TABLE WITH NOCHECK is being used. I am looking for a way to verify the integrity after the load is complete. After the load an ALTER TABLE WITH CHECK will be executed. Can I used DBCC to verify, that no orphaned child rows were loaded? If so, which parms would I have to use with DBCC?
View 0 Replies
View Related
Mar 1, 2005
Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be great
View 4 Replies
View Related
Oct 14, 2015
I'm putting a process together to run a DBCC CHECKCONSTRAINTS process against copies of client databases.The author application doesn't set the constraints as trusted, and therefore we need to check the integrity of the data.
The problem is that some of the Check constraints have a definition that is longer than 4,000 characters.When this is the case, DBCC CHECK CONSTRAINTS fails.One option is that I write a cursor to select the constraints that have a definition less than 4,000 characters and then call the DBCC command for those particular constraints. However, I'd prefer a more elegant approach - ideally a way to run DBCC CHECKCONSTRAINTS against all constraints regardless of the length of the definition
View 0 Replies
View Related
Sep 27, 2015
I am using sql server 2012 with HADR (Always on with sql cluster).
We have database maintenance plans through wizard for full backup & DBCC CHECK DB. It was running successfully but it failed with the below error
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.
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
I can able to take the backup from query window. It is succesful. The Sql Agent has full permissions. I don't think there are any recent changes happen.
View 9 Replies
View Related
Aug 9, 2015
We are importing xer formats through the wizard to sqlserver database and It takes upto 35-45 mins for each import (single project), any option to reduce the time.Is they any other import options - which can give us faster results.
View 0 Replies
View Related
Sep 19, 2014
In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?
View 1 Replies
View Related
Feb 4, 2008
The following code is taking longer and longer to run. I am not talking about the gradualy increase in size. this job has been taking 30-40 mins normaly and in the last few days it has gone 1hr to 2 hr to 3 hr... ANy ideas why this is happening? I can not see and other jobs running at this time.
declare @filename varchar(255)
set @filename =
(select top 1 physical_device_name
from ****.msdb.dbo.backupset bs, ****.msdb.dbo.backupmediafamily bf
where bs.media_set_id=bf.media_set_id
and database_name = 'Live_PRD'
and backup_start_date>getdate()-1
and type = 'D'
order by backup_start_date desc)
restore database REPORTS_REP
from disk=@filename
with
move 'LIVE_PRD_Data' to 'T:SOUTHREPORTS_REP_Data.mdf',
move 'LIVE_PRD_Log' to 'U:SOUTHREPORTS_REP_Log.ldf',
move 'LIVE_PRD_Log2' to 'U:SOUTHREPORTS_REP_Log2.ldf',
replace, stats=2, recovery
View 5 Replies
View Related
Dec 2, 2014
I have this procedure to remove certain characters from file names.
The SQL Task has this: exec dbo.spCleanseFileName @strFileName = ?, @strFileNameCleansed = ?
The stored procedure:
CREATE PROCEDURE [dbo].[spCleanseFileName](@strFileName varchar(40),@strFileNameCleansed varchar(40) output)
I have it in an SSIS package and my problem is that, after that SQL Task completes, the value for the
),@strFileNameCleansed variable is blank. I HAVE confirmed that the procedure DOES set the correct value inside the SP.
View 2 Replies
View Related
Oct 29, 2015
I am setting up mirroring on one of our live databases tonight. The database is quite big, around 300GB.
At the moment we are doing log backups once a day and full backups every night.
My question is, when I set up the mirror, will the log backups on my Principle DB affect the mirror in anyway?
View 4 Replies
View Related
Dec 11, 2014
I have a scenario where i have to run update task on multiple servers in parallel and once all of them are completed (success or failure) another task is to be run on another server
1. in maintenance plan, if we add tasks which are not joined, will they run in paralled at the same time
2. if we link the last task to all the tasks with link type 'completed' will the last task complete after all tasks are completed or when any one of them is completed (i have big doubt here)
the business requirement behind this is to bring data from multiple servers into shadow copies locally and then process them together. its ok if some server data transfer fails, but its not ok to start processing centrally while data transfer is going on. further, we want to run data transfer from multiple servers in paralleled to save time.
View 0 Replies
View Related
Jun 2, 2014
I have inherited a new SQL Server 2008 database server and can not figure out how my user databases are being backed up. This database server is running under a VM.
All the user databases are being backed up nightly per the SQL server log. The backups are written to a virtual disk and is kicked off by the NT AUTHORITYSYSTEM user. I can not see the virtual disk. A restore task does not provide any information about the last backup. I have created a new database, and it is automatically included in the next set of backups.
I have looked at the windows event viewer with out any luck. There are no SQL Server Maintenance Plans or Agent jobs that call a backup. I have also checked the Windows Task Scheduler and can not find any task that does a backup.Could the backups be called from another server ?
View 6 Replies
View Related
Oct 15, 2014
I am using Ola Hallegren's scripts to do backups. He uses @Cleanup Time to delete backups older than a certain number of hours. My situation is I do a full backup of a database on Sunday and then I have a few Differentials and then log backups for the rest of the week. When Sunday rolls around again and my full backup is finished, I would like to delete all the differential backups and log backups. Any way that I could accomplish this using Ola's scripts?
View 1 Replies
View Related
Dec 8, 2014
I have tried doing sql server backups to a file share and that has been taking too long. So I've decided to backup locally and then taking those backups and getting them off the server. For those that are doing this what do you use to get your backups off the server?
View 9 Replies
View Related
Jan 23, 2015
I am sure I have seen in the past in a monitoring tool that PLE drops off to 0 whenever we do a backup. I was doing some reading around this however and found something that said backups use a different portion of memory external to the buffer pool (minmax settings).
Is this correct and how can I tell how much memory will be required for a backup?
View 2 Replies
View Related
Mar 23, 2015
Just hit a issue where we had a 40gb backup file for a database with an mdf of less than 2gb and tran log less than half a gb.
Noticed someone had set the backup for this database to append the backup file in stead of overwrite it.
Just wondering why would you want to append a backup instead of overwrite it?
View 1 Replies
View Related
Jun 11, 2015
I just completed a copy-Only compressed backup of a DB (with a FULL Recovery Model ) on SQL Server 2012 and the resulting backup (the bak file) is 1/100th the size of the data & log file. Is the compression in SQL Server 2012 just that good or did something else happen that I did not catch? Below is the T-SQL to re-create the backup. The size of the data file is 750MB and the log file is 75GB and is %95 used according to the SQLPERF command.
Does the compression in SQL Server 2012 simply that good
BACKUP DATABASE [MYBIGOLEDB] TO DISK = N'Z:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMYBIGOLEDB_20150611.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'MYBIGOLEDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
View 1 Replies
View Related
Aug 21, 2015
So I'm testing some things in our new servers and was trying to restore a database from some striped backup sets. We have 4 files for our backups and restoring the FULL backups with no recovery worked beautifully via SSMS. But when I tried to restore the differentials (also striped across 4 files), the GUI gave me this error:
Unable to create restore plan due to break in the LSN chain.
How to locate when the break happened and I came across this article about how this is an SSMS 2012 bug.
So I tried the advice in this article to attempt a restore via Files and Filegroups, and ended up with the below error:
EDIT: Picture is attached if it is not showing in post.
I was able to restore via T-SQL, but I want to also be able to restore through the GUI.
View 5 Replies
View Related
Aug 25, 2015
The import from Flat File Source fails: Error 0xc02020a1: Data Flow Task 1: Data conversion failed.
The data conversion for column "ArticleName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
I have changed the size of the column "ArticleName" (varchar) to max but the error comes up again.
The data i want to import came with multiple flat files. They all could import properly but this one is a problem.
View 1 Replies
View Related
May 8, 2014
We have a SQL 2012 server instance that has log shipping set up to another SQL 2012 server to provide a warm standby for a forward facing application. The databases on the primary server occasionally are required to be backed up and restored to a development environment, completely different server. Is there a way to schedule full backups with log shipping enabled?
View 2 Replies
View Related
Jan 12, 2015
Native backups to NAS do not complete.We have been experiencing an issue whereby our native backups are hanging with status': SUSPENDED/ RUNNABLE...I ran select * from sys.sysprocesses. All of the backup SPIDs processes show BACKUPTHREAD/PREEMPTIVE_OS_FILEOPS
This first occurred last Wednesday evening. When I discovered this on Thursday, I attempted to kill the backup jobs. This also hung with 0% completed/0% time remaining. Backups hung on more than one instance.That evening, I attempted to restart the instance which also failed with something along the lines of: could not start MASTER file in use.
I then restarted the server--which I really did not want to do--and this cleared it. I was also able to manually kick off maintenance plans (DBCC CHECKDB and full backup) without issue.I was off Friday and the weekend. I came in this morning and found the maintenance plans (diff/tlog backups) did not complete in some of the instances--in one case, the instance affected now was not affected before. They appeared to have hung on their next scheduled kickoff which was later that night after I went home
Remembering the "file in use" error, I have run process monitor to see if anything unusual had a lock on any files. I saw only SQL Server and Double-Take processes accessing log files.Being a relatively new DBA, I am user where to go next in trying to track down the cause of this issue. This is fairly urgent as one of the instances that has had this problem both times is our production SharePoint environment.
ENVIRONMENT:
SQL version:
Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)
May 22 2013 17:10:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
View 8 Replies
View Related
Aug 4, 2015
I am in plan to implement following for backup of one of our database Enable Full recovery mode
1- Create full backup nightly
2- Create transaction log backup after every 25 min
as I am taking full backup every night, I think I can remove transaction log file backups at the time of full backup, as we can apply transaction log backup over full backup.My question is regarding removal of transaction log backups.
-Should I remove all transaction log backups and then execute full backup?
-Should I execute full backup and remove all transaction log backup older than 24Hrs ?
-Do I have to consider SCN or related info before deleting any transaction log backup ?
View 9 Replies
View Related
Jul 11, 2014
We have just implemented a SQL 2012 always on environment. We have a primary and secondary server. I am confused about how to set up the backup plans. The application team was happy to tell me that in sql 2012 always on we can offload the backups to the secondary, thus reducing overhead on the primary server.
However, the secondary only supports copy only full backups. I am unsure how these would be useful in a disaster event? I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?
View 7 Replies
View Related
Oct 29, 2014
We had our backups backing up to the server where the databases reside. Now I modified the backups to backup to a file share. Now when we try to restore from the file share the restore fails, so we have to copy the backup to a drive on the server and recover for there. Should I be able to restore directly from the file share (using the gui)? Do I need to change something else to modify the default backup drive?
View 9 Replies
View Related
Feb 22, 2008
Why does it take longer and longer for the same code to run very simply I have 8,0000,000 records I want to delete from a table . I have tried a few options
Option 1 a while loop which deletes 10,000 rows per loop starting from the earliest until it hits the cut of number I have set. THIS TOOK 5 HOURS
Option 2 created an SP which found the oldest 100,000 records then deleted them. If I run this SP manually it takes 30 €“ 60 secs. Which I thought was much better than above. So I put this SP in a while loop to run 80 odd times thinking the time it would take would be 80 mins a huge improvement.
But every time this SP is called it takes longer and longer (36,30,32,39,37,37,123,163,155,182€¦and so on(In seconds)).
All the sp is doing is as follows(8860000 is just to insure I don€™t delete to much). this sp is then called from in a while loop.
set @recnumber = (select top 1 recnumber from
(select top 100000 recnumber from TabletodeleteFROM where recnumber < 8860000
order by recnumber asc ) TabletodeleteFROM
order recnumber desc)
delete TabletodeleteFROM where recnumber < @recnumber
please help why is it taking longer and longer
View 8 Replies
View Related
Apr 20, 2015
I am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?
Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?
Do you just check that the SQL Agent job that runs the backup was successful?
Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?
Or is there a better method?
View 1 Replies
View Related
May 5, 2015
Can we backup our cluster databases directly to tape using native backups (without using any third party tool) ? It's SQL Server 2012 two node Active/Passive cluster. One of the DB will be huge in size, hence checking if we can directly backup from the cluster instance to a tape.
View 7 Replies
View Related
Sep 24, 2014
I downloaded the SSRS Report RDL file from the report manager.That report is consolidated report. It has 4 dataset for four tablix one for each tablix. Actually in that report, I don't want to display one of the tablix data. So I deleted the tablix and related dataset.
Then I upload that rdl file in to the report manager.Here I haven't change any connection strings but when I click on the report from the report manger, it is not showing any thing
View 5 Replies
View Related
