| Author |
Topic |
|
bryan99y
Starting Member
42 Posts |
 Posted - 2003-06-09 : 12:39:52
|
| I have sql server 2000 snapshot replication setup. The publisher database has a log that is currently 30GB and growing. I will not shrink. Cannot BACKUP LOG xy WITH TRUNCATE_ONLY. These do not work.HOW CAN I SHRINK THIS?????? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-09 : 13:01:07
|
| How did it get so large in the first place, don't you have maint for the db?What is your db recovery model? Full, simple or Bulk Logged?In books online, look up: "truncated transaction logs"Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-09 : 13:28:25
|
| Do you get an error with BACKUP LOG? Try running this instead (although I think they are equivalent):BACKUP LOG xy WITH NO_LOGTara |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-09 : 13:36:47
|
| the backup maint. plan was failing and the log kept growing. I have it on simple mode now. |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-09 : 23:39:45
|
| should I just try disabling snapshot replication? If so, how do I do this? |
|
|
|
DavidD
Yak Posting Veteran
73 Posts |
Posted - 2003-06-09 : 23:51:01
|
| Go into the database in Query Analyzer and use dbcc shrinkfile (logfilename, 5). You have already truncated the logfile so it should shrink heaps. To remove your snapshot replication use the wizard in Enterprise Manager (disable publication and distribution wizard I think) |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-10 : 07:30:57
|
| I will not shrink. I do not want to remove replication. Just disable it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-10 : 12:57:44
|
| Why won't you shrink though? The BACKUP LOG WITH TRUNCATE_ONLY only truncates the transactions out of the log. This means that you have lots of free space in the log, but the size of the log remains the same until a shrink occurs. If you don't want to shrink it, then your only other option is to detach the log file and attach a brand new one, but this means that your database comes offline temporarily. With shrink, the database is online the entire time although it will be slowed down a bit.Tara |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-10 : 13:01:54
|
| "It" will not shrink. I tried the DBCC Shrinkfile but it does not work. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-10 : 13:16:28
|
| What is the command that you ran for the shrink file? Was the log file truncated before hand? In order for it to be shrunk down to a reasonable size, the file must have free space in it.Tara |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-10 : 14:33:36
|
| will not truncate because replication does not allow it. |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-10 : 14:36:57
|
| "The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-10 : 14:39:19
|
| Ah, haven't worked very much replication so I wasn't aware of that. You could also BACKUP LOG to disk to accomplish the truncate. Once the backup is complete, simply delete the file that the backup log command created.BACKUP LOG xy TO DISK = 'C:\temp\xy.TRN' Why don't you want to delete replication and rebuild it after the shrink? It is very easy to do this if you generate the scripts for replication. Then you just run the scripts and replication is back without having to fill out all of the replication screens.Tara |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-10 : 14:42:13
|
| how do you script out all current replication setup? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-10 : 14:45:48
|
quote:
how do you script out all current replication setup?
Go to Enterprise Manager, connect to your server, right click on Replication, click Generate SQL Script. Then select whatever options that you want in the script and save it to a file. You'll want to create a script for the creation of replication and one for the deletion of replication. You should select the option to script out the jobs too. This script can easily be modified so that you can deploy to other environments. That's how we do it here. We just setup replication one time, then script it out. If it needs to be deployed to say the QA environment, we modify the script then run it on the QA server.Tara |
|
|
|
bryan99y
Starting Member
42 Posts |
Posted - 2003-06-10 : 17:07:57
|
| problem solved. sp_repldone. |
|
|
|
|
HELP!!!! Log file growing out of control