Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Carl Forgey
Starting Member
6 Posts |
 Posted - 2008-07-03 : 00:56:48
|
| Hello all,I'm a former technical support rep that got backed into administering our ticketing system, and I'm running into a bit of an... Issue.We have two databases - one main one created by the ticketing software, and a second database that is transactionaly (???) replicated from it filtering out certain client information for their perusal.Somehow the subscription database's ldf file has grown to 58 gigs, and I can't get it to trim. I tried the steps in the link provided in the FAQ, but have gotten no where.Management Studio has given me a hint that there is a possible issue with a transaction that has not completed:---------Transaction information for database 'BridgeTrak7'.Replicated Transaction Information: Oldest distributed LSN : (162486:5435:6) Oldest non-distributed LSN : (162486:5574:1)DBCC execution completed. If DBCC printed error messages, contact your system administrator.---------The transaction seems to be on the publishing database. I ran sp_who to see if I could identify the offender, but cannot suss out which process might be hung and what to do about it should I find out anyway...Any hints that you guys could provide as to what to do, or even where best to look for a solution would be greatly appreciated!Cheer,Carl |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-03 : 02:31:21
|
| Did you run opentran on the publisher or on the subscriber?If on the publisher, it's just showing you that replication is active and that there are a few unreplicated transactions. The LSNs look close enough that it shouldn't be a problem. Are you running SQL 2000 or 2005?If 2005, go to the subscriber database and runSELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databasesWhat does it say for the offending database?Is the subscriber database in full recovery mode? do you have transaction log backups scheduled?--Gail ShawSQL Server MVP |
 |
|
|
Carl Forgey
Starting Member
6 Posts |
Posted - 2008-07-03 : 14:43:57
|
| >Did you run opentran on the publisher or on the subscriber?I just ran it on both. The publisher has open transactions (not surprising this time of day), the subscriber does not.>If on the publisher, it's just showing you that replication is active >and that there are a few unreplicated transactions. The LSNs look >close enough that it shouldn't be a problem.Oldest distributed LSN : (162486:5435:6)Oldest non-distributed LSN : (162486:5574:1)Is there a quick way to interpret the numbers that this gives? They do look close, but the numbers are meaningless to me, and I wasn't able to find a way to interpret them on the MSDN website.>Are you running SQL 2000 or 2005?2005.>If 2005, go to the subscriber database and run>SELECT name, recovery_model_desc, log_reuse_wait_desc from >sys.databases>What does it say for the offending database?Publisher:BridgeTrak7_Test, SIMPLE, REPLICATIONBridgeTrak7_RA, FULL, NOTHINGBridgeTrak7_Test is the Publisher, BridgeTrak7_RA is the Subscriber. BridgeTrak7_RA is the database with the HUGE log table.>Is the subscriber database in full recovery mode? do you have >transaction log backups scheduled?It appears that it is in full recovery mode. I'm not sure where to set that. In the properties, it shows initial size 182MB, autogrowth is by 10 percent, restricted growth to 2097152MB (that seems a little rediculous for log files).I honestly don't know where to set a transaction log backup schedule. I'll do some searching in that direction. Anyone have a quick answer? Are there SQL commands to set that, instead of relying on the Management Studio GUI? |
|
|
|
Carl Forgey
Starting Member
6 Posts |
Posted - 2008-07-03 : 14:56:45
|
| Not sure how to edit a post yet...I found a walk through of using the Management Studio to set up log back-ups. I'm going to try that after hours and see how that goes.So much to learn! |
|
|
|
contrari4n
Starting Member
27 Posts |
Posted - 2008-07-03 : 16:06:08
|
| BACKUP LOG BridgeTrak7_RA TO DISK = '<path>\BridgeTrak7_RA.bak'The resulting backup file will be big, so check disk space first.Once you have done this you can shrink the log file. Don't completely shrink it though as will probably have to grow again. Decide on a reasonable size, and schedule a regular log backup to prevent it growing again.To shrink the log use DBCC SHRINKFILE (<log_file_name>, <target_size>)Richard Fryarhttp://www.sql-server-pro.comSQL Server Articles and Tips |
|
|
|
Carl Forgey
Starting Member
6 Posts |
Posted - 2008-07-03 : 17:49:14
|
| Thanks for all the help. It seems like the more you learn, the more you realize you don't know. Not, I guess, that that isn't true for everything.From an administrative perspective, would it be a good thing to script those two commands>BACKUP LOG BridgeTrak7_RA TO DISK = '<path>\BridgeTrak7_RA.bak'>DBCC SHRINKFILE (<log_file_name>, <target_size>)to run on a schedule? I've seen discussions about intervals to script truncations varying from every 10 minutes to once a day. Would running them nightly at midnight be efficient?And on the subject of back-ups, if a database is set to FULL recovery model, how long should back-up files be kept? How long for simple? Is each back-up file a complete standalone back-up of the database as it was on that day, or do they need to be added together in some fashion for recovery, should the worst occur?(yeah, I know, alot of n00b questions, and most of them are answered in the BOL... Which I _am_ reading, just not fast enough!) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-04 : 02:41:48
|
| Don't regularly shrink your files. You should set log backups so that the log filees don't row, but shrinking them is a waste of time and often causes file-level fragmentationIn simple recovery mode, you can't backup the transaction logs.In full recovery mode, you can use the transaction log backups along with the full database backups to recover to the point of failure (if all the log backups are intact)Do you need to be able to restore the subscrber DB to the point of failure? Are you OK with only been able to restore the publisher to the last full backup?Generally, I would suggest that you keep 2 full backups and the transaction logs from the earlier one, That gives you a lot of options in a disaster to recover. Keep the backups somewhere other than the server that they are a backup of.Does that help at all?--Gail ShawSQL Server MVP |
|
|
|
Carl Forgey
Starting Member
6 Posts |
Posted - 2008-07-05 : 17:24:07
|
quote:
Originally posted by GilaMonster
Do you need to be able to restore the subscrber DB to the point of failure? Are you OK with only been able to restore the publisher to the last full backup?
The subscriber is set-up as read-only, so it can be a total loss, and I can always recreate it from a replication of the main database. No worries there. The publisher is our main ticketing system database, so I think that it would be best to be able to restore to any point in time. I think I've worked out a good back-up schedule for that.quote:
Generally, I would suggest that you keep 2 full backups and the transaction logs from the earlier one, That gives you a lot of options in a disaster to recover. Keep the backups somewhere other than the server that they are a backup of.Does that help at all?--Gail ShawSQL Server MVP
That helps a great deal! Thanks so much for the help. Between this forum and the BOL, I'm feeling alot more confident in at least maintaining the system.Carl |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-07 : 02:52:28
|
quote:
Originally posted by Carl Forgey Between this forum and the BOL, I'm feeling alot more confident in at least maintaining the system.Carl
Glad to hear it.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Bloated logs.. again.