Tag Info

Hot answers tagged

7

You could be utilizing the transaction log backups that are taken by the log shipping process. In fact, you can't double-up on transaction log backups. So, as long as you have the actual transaction log backups and the ability to copy them to other storage (in order for your custom backup preservation and retention for Disaster Recovery), you should be ...


5

When you restored the primary the log shipping has broken. The only way to resume it is to re-initiate it, with a new full backup. End of discussion. We do schema and data changes for new Release deployment on to D2 database. And that is your real problem. Do all changes as code changes and deploy by running deployment scripts. When you want to migrate ...


5

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012) This is a ONE WAY situation. When you have to failover, it will be easy as the logs from sql server 2008 can be restored to sql server 2012. The stopping stone will be FAIL BACK - if sql server 2008 is Primary and SQL Server 2012 is secondary (standby) server, after ...


5

You could do log shipping with the standby option rather than norecovery. The database would be read-only, and the users would get kicked off during each restore, but it might suit your needs better.


5

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain? The key to that question is "breaking the restore chain." If you want to query and verify numbers, then standby mode in logshipping is your best option. If you have SQL Server 2012 Enterprise ...


4

You just need to run two stored procedures to remove the setup of log shipping from the primary database How to: Remove Log Shipping (Transact-SQL). As the secondary doesn't exist you shouldn't need to perform step 2 & 4.


4

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though... Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary ...


4

The log file on the replica follows exactly the file on the master. They can't be different. You can shrink the master and the replica will automatically change to match.


4

WITH STANDBY is only supported when both SQL Servers are the same version. You can only use WITH NORECOVERY An alternative if you need read access to the destination databases, is to use replication.


4

I would suggest configuring the log shipping to STANDBY mode instead of NORECOVERY. This will allow you to query the secondary db for any reporting, etc. When you use NORECOVERY mode, the secondary database will not allow any users to access it, so the database does not have to worry about uncommitted transactions. The log can just be restored as it ...


3

In a word, no. You have to do it live. What you'll want to do it add 3 new files. Then simply start doing index rebuild operations. As long as you are doing rebuilds not defrags SQL will start spreading the data across all the data files. I'm assuming that you are running on SQL Enterprise Edition and can do online index rebuilds.


3

Running DBCC SHRINKFILE without the TRUNCATEONLY option on the primary will propagate the change to the secondary once the log is applied, so it will shrink to the same size..


3

The 180 day evaluation version from Microsoft is Enterprise Edition. AFAIK there is no ability to "trial" Standard edition from Microsoft outside of a licensing agreement you might have. From the point of view of overhead between editions, there's negligible difference. If you want something longer than 180 days, get the developer edition as its feature ...


3

Transactional replication gets you close, but as you said, changing the subscriber schema is a recipe for disaster. Transactional replication uses stored procedures to apply the data changes. You could, with every table change, change those procedures to deal with the underlying changes. That is a lot of additional manual work, but I don't think you will ...


3

As always, it depends on a lot of factors, but I'll answer like this: If the SSIS package takes only the deltas from the last run and performs well, I'd keep using the SSIS, since its something that is already working and I assume you are familiar with. Log shipping and replication will work, but they have drawbacks. For instance, with log shipping you ...


3

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be: ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE([logfilename], 1) ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT Changing the database to simple recovery may cause ...


2

It depends on your workload. As a baseline, use Performance Monitor and log the network counters when not using Log Shipping or Transactional Replication and measure for 1 hour. Then setup Log Shipping and use Performance Monitor to log the network counters for 1 hour. Then setup Transactional Replication and use Peformance Monitor to log the network ...


2

No, log shipping is not supported on SQL Express 2008 R2, even as the monitor. The official MSDN page does not state this explicitly, but I found this case where someone tried to use Express and got an error. If you have either a Workgroup or Web edition licence lying around unused, those support what you need, as well as Standard (and higher).


2

By "log shipping" I'm guessing you're meaning log backup? Log shipping is a high availability strategy. If your full backups are taking the better part of a day, yes I recommend that you switch things up a bit. But that all depends on a lot of environment specific parameters. For instance, do you have a big window on the weekend where you will be able to ...


2

I would like to answer my own question - Jungle disk can successfully be used as transport for logs to be delivered to off-site location. I have it working with SQL Server 2008. Before following steps I outlined below I recommend familiarizing yourself with log shipping by first setting it up within the same LAN - I found that wizard works very well for ...


2

RESTORE DATABASE dbname FROM DISK = 'dbname .bak' WITH REPLACE, RECOVERY --force restore or just RESTORE DATABASE dbname WITH RECOVERY the REPLACE Overwrite the existing database, do it only if you are sure you want to override your existing database as you mentioned you dont care to delete it RESTORE WITH RECOVERY is the default behavior which ...


2

My first suggestion would be to use intelligent re-indexing rather than just blindly re-indexing everything. This will help to minimise the impact on the logs un-necessarily. Ola Hallengren's offers one such solution. See http://ola.hallengren.com/ External storage on a SAN that uses either cheap discs (SATA), or dedupe technology, makes it more affordable ...


2

I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon. Taking transaction log backups, as are required for log shipping, internally clears portions of the transaction log, allowing the physical space on disk to be ...


2

If you have SQL Server 2012 Enterprise Edition you could implement Always On Availability groups. This is an extension of mirroring (built on-top of windows clustering) which allows the mirrored database to be a readable secondary. The benefit of this is that the secondary is updated as the primary database is updated. However it is expensive as the ...


2

Two options: On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and ...


2

You could potentially run a script such as this to attempt the shrink operation once every minute until it completes, or runs 1,000 times. As always, use at your own risk - and test this first on a non-production environment. USE tempdb; /* CHANGE THIS TO DESIRED DATABASE */ GO DECLARE @DesiredSizeInMB INT; SET @DesiredSizeInMB = 1; DECLARE @T TABLE ( ...


1

If your server has only 4GB of RAM, AWE isn't going to help you much. AWE is designed for when you have greater than 4GB of RAM you need to use. Timeouts are harder to define. The timeout period itself is likely defined in your application, so it's not inherently SQL Server that's causing the timeouts. However, what you're running into are queries that ...


1

The best preventative measure to stop your server running out of disk space is to switch off 'AUTOGROW'. That will prevent your database from grabbing more space, but obviously errors will occur if there is no space left in the file. One thing to note is that even though your files are using 490MB, you may not actually be using all that space. Use ...


1

As Thomas said, transaction log backups cannot be used unless you have a full database backup. But having a long chain of transaction log backups and a full database backup at the beginning of it will cause the restore to a point-in-time process to be very slow. Imagine you have to roll back several hundred of transaction log backups. The best would be to ...


1

Restarting both the source and destination servers housing the sql engines seemed to fix the issue. its been a week and all backup, copy and restore has no issues except for just that one SharePoint reporting db WebAnalyticsServiceApplication_ReportingDB_77a60938-####-####-####-##########. Thanks to everyone for their input, as I at least came out of this ...



Only top voted, non community-wiki answers of a minimum length are eligible