Im in the process of setting up logshipping on sqlserver 2005 enterprise edition.
My scenario is like this:
My Avg size of my tlog is 500MB and im planning to set the log shipping at 30mins interval(ie backup job schedule,Copy,restore job schedule).But at some part of the day the Tlog suddenly increases up to 1.5GB - 2 GB .So i wanted to know, wht if that 1.5GB-2GB tlog file is unable to get backed up,copy and restore at 30mins interval?.How to deal with this kind of issues where the size of tlogs are increased suddenly.I cannot do it at15mins interval due to some network restrictions at my office.
i have One more doubt about the setting on Logshipping screen:
Now let us suppose my settting on log shipping screen 'Alert if no restore occurs within' is set to '180mins', then does this setting mean that the restore job will keep on looking for the copied file in the folder on secondary for next 90mins and if its not able to find any, it will generate an alert after 90mins ??? or it will generate an error if its nt able to find any copied file after the first restore job execution.???
in the same way,
I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it? The features of this database are:
SQL Server 2000 Recovery Model = Full Auto Update Statistics = Yes Torn page detection = Yes Auto create statistics = Yes Full database backup taken once daily. No log backup is taken.
So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions??? My free disk space is very low.
We have 2 SQL Server 2k5 servers running the same build - 9.0.2047 . When I backup any database from one server and attempt to restore it to the other, the log file generally increases by 100 fold. It errors out after I try to restore a 100MB db and it tries to create a 9.8GB log file. This happens both when I use the GUI to restore and when I restore from a T-SQL script. What am I doing wrong?
We currently have a couple a large Databases running on SQL 2000 SP3 Clustered Windows 2000 SP3 environment.
Log Shipping is enabled for both databases shipping to a Standalone SQL 2000 SP3 Windows 2000 SP3 box.
Log Shipping occurs every 15 mins with the Transaction Files on average being no more than 500KB in size. However, every now and then a Transaction Log comes through and it can be as big as 3.52GB.
Not sure why this is happening. Anyone got any ideas?
I am going through a security audit on our servers. We use log shippingfor a standby database. One of the questions in the audit has melooking for answers."Are the transaction logs that are being shipped to the standbydatabase encrypted?"I am assuming no. However, I need to know definitively. I have not beenable to find an answer in BOL or in Google. If the logs are notencrypted, is there an option where I could send them encrypted, ifnecessary?Thanks,Jennie
I have a server in a datacenter (SQL 2005 ent) that collects large quantities of data from our visitors. I need to set up a secondary database in our office (different geographic location) that will server 2 purposes, 1, a backup of the database and 2, allow us to perform complex queries on the data.
There is no updating of the data on the secondary server so no changes need to go back to the primary server. A database in standby mode is fine and users on the secondary server can be disconnected when it's being updated.
I have transaction log shipping working well in a staging environment (LAN). My first question is is there any reason why transaction log shipping would not work over a WAN with a VPN connection?
And my second question is can I compress the trn files for transport over the WAN. If I manually compress the files with winzip they compress by 98%. That translates into a huge saving when I am leasing a line to transport these files.
Greetings: When I script out my log shipping configuration from the GUI and subsequently drop the log shipping and try to recreate it with the created script, the backup and restore functions do not seem to be working; please see script below. Is there an additional step (or steps) that the SSMS GUI does not output when it creates the script for log shipping? I noticed in the GUI after I run the script that the destination folder for copied files is blank as well.
Example error from backup/restore job - Error: The path is not of a legal form.(mscorlib)
-- Execute the following statements at the Primary to configure Log Shipping
-- for the database [rdevsql2].[SymbolLookUp],
-- The script needs to be run at the Primary in the context of the [msdb] database.
We have log-shipping set up between a source and 3 destination SS 2000 databases. Two of the destination servers actually perform their log restores across the network from the other secondary server. This allows us to only copy the files once from a remote location. All three servers stay caught up within 15 minutes of each other.
Recently, I added a fourth server to this that has SS 2005 SP2 (X64). I wrote a stored procedure that restores log backups from the same single location as the maintenance plan jobs. The problem that I'm experiencing is that this fourth server is not keeping up with the other three. It seems to take longer to restore the same log backups. The destination servers are all on the same domain. This fourth server was previously part of the same maintenance plan configuration as the others prior to rebuilding it for SS 2005 SP2 (X64). During that time, it stayed caught up with the other servers. There is another database on the new server that I am log-shipping to in the same manner and it stays caught up, though, for the most part, the log backups are smaller. There is a file on the fourth server with a ckp extension for the database in question that doesn't seem to exist for the other databases on this server and the other servers.
Any information on this behavior would be appreciated.
I am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
I'm experiencing a weird problem with log shipping in SQL 2005.
I've setup Log Shipping for a production database between two sites. The standby database is being updated correctly and everything seems to be working as expected but for one detail: the name of the transaction log backups are generated with an UTC timestamp instead of my local timezone.
The the data below extracted from the backup history:
I've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. Â I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. Â I could simply set up a jog to delete the older files, but that will only mask the issue.
Out of using stored procedure, reports and all this staff, I want to know the possible way to make sure that the data inside my Secondary Server Read only database are same as data in my primary server database.
I received an alert from one of my two secondary servers (all servers are running 2012 SP1):
File 'E:SQLMS SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.
The detail in the job step shows this additional information:
*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192. Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.
I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.
I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.
Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?
Hello! We have unusual situation. We increased the size of transaction log up to 100MB. After we run the transaction log backup the physical size of transaction log file getting smaller and smaller from 100 to 88 and then to 76 and so on. Do you now the reason? Thank you, Natalia
I have a very serious problem, if somebody can help me quickly. my transaction log file is getting bigger and bigger even after truncating. today morning when i checked, it was 1.5 GB, by evening it has gone upto 3GB, the total size of the database is 3.4 GB, out of which 3GB is Transaction log. why is it growing heavily!!!!!!!!. there is not much transaction happening. even after truncating the transaction log it is still showing 3gb.
I currently have an database that is 110 mb and grows on an average of 5mb a week. THe enviornment is IIS 5.0 /ASP /SQL 6.5. However I find myself continously increasing the log file since it frequently fills up. At this point it is 300mb and can be filled in less than an hour with an average of 35 users. I realize that this file has alot of overhead and is affected directly by the application making the updates. Last week on a busy day (time card app) I witnessed the log increase from below 75% to 100% in a minute, forcing me to truncate the log. This application is an administrative nightmare! Any ideas what could cause this type of activity in the trans log.
We have here a small database (around 400 MB) with simple recovery. Also autogrowth is active.
But why does the transactionlog also sized up, when it is not in use? Our TL is around 100 MB and filled with 5 MB. Everytime the DB gets bigger, the TL also....
This isin continuation with my previous query. If i cannot reduce th T Log size , how can i stop it from increasing further. Can i make secondary log files which can be deleted later on?
I need to create a script that would return the size of transaction logs for all databases. I ran a select statement against the sysfiles table but you can only run it against one db at a time. Any suggestions?
Hello dear friends, In my database i am not able to take the backup of transaction log. Even if i took the back up and then shrunk the file it doesnt make any change to my transaction log size. Still the size is same. So in every two weeks i am restarting my server. Once it is restarted then it is ok for another 2 weeks. After 2 weeks my transaction log size will be more than the size of my datafile. Can you suggest your openion. No replication or log shipping exist.
I need to create a script that would return the size of transaction logs for all databases. I ran a select statement against the sysfiles table but you can only run it against one db at a time. Any suggestions?
I have written an application which runs as a windows service. The application constantly requires to write data into the database.. pretty much every seond. Some of the tables have more than 800,000 rows.
the application works fine. Hoever I am noticing the transaction log grows very fast. All information added is handled through stored procedures. In one week the transaction log grew to 2GB. I assume a part of the reason was because I ran out of disk spae once. But generally this transaction log is growing fast. What could cause the transaction logs to grow fast, What could I do to keep the log file size down....I cant have the database down as the application needs to be running constantly.
I have a database whose recovery mode is FULL. I setup one maintenace plan do a backup for the database and another backup plan which backup the transaction log. The two maintenace plans runs daily.
However, the log file is still growing to a very large size. Should the log file be able to reuse after each backup of the transaction log ?
If I'd like to keep the database recovery mode as "FULL", what do I have to do to keep the transaction log within a reasonable size.
If I have a transaction log in a database of size 1GB ( space allocated is during creation of database) currently only 300 mb of its space is used i.e. nearly 700 mb is free. If I want to reduce physical file size of transaction log by 200 mb and release it for operating system then How can I do it???
I'm sure this question has been asked before but I need clarification on a couple of points.
I have a database (500 Mb) which is having a full backup every night at 2AM. I am doing a transaction log backup every 2 hours between 7AM and 7PM. I have noticed that the transaction log keeps growing bigger and bigger so I do a manual truncate.
- Will my transaction log keep growing bigger and bigger ? - How can I automate a task to reduce the log size ? - Does a full backup not truncate the log ?
I created a database and had its file size as automatic grow. Now the database file is of 17 MB and its transaction log file size is 230 MB. After checking transaction log file properties I came to that it is using 13 mb only and the rest of the 230 MB i.e 217 MB is free. I want that area in the transaction log to be freed and get the transaction file size to its actual size. Any help will be greatly appreciated.
My database's transaction log has become 1.7 GB. Can I reduce it's size? I have tried to shrink database and also set truncate on checkpoint option and also taken the backup after that. but nothing helps. Please advice.
