Uncategorized

New Year’s resolutions kill Log Shipping!

ok…funniest issue I’ve run across in a while 🙂

We have some custom log shipping script that was working just fine until the end of 2006. Starting around January 2, the restore process couldn’t restore fast enough to keep the standby server current.

I looked at the T-Log files for the last 2 weeks and saw that the T-log backups between 3 and 5 am were 2 to 3 times the size they used to be.

Why? Because the customer here is a national Fitness center business that had a huge influx of memberships and activities 🙂

People trying to lose weight broke log shipping.

The fix? Run the restore process more often (it was only running off hours).

You get fries AND a low-fat shake with this one…

Kevin3NF

Old stuff can still work…

Got called to a “server down” the other day. SQL 7.0, running on Windows NT 4.0, on a dual PII 333 server maxed out at 1GB RAM.

Seems that the SQL Server for some reason was extremely slow. Customer thought disk space was an issue, but he had over a gig free and wan’t autogrowing.

Ran sp_updatestats, which took over an hour on 12gb of data.

Looked into task manager….found SQL Server only using 50MB of RAM.

As it turns out, Microsoft Message Queue was taking over 800MB of the 1GB of memory. Whoops.

Solution (3 hours later): Purge the MSMQ journal.

SQL Server is now flying.

You want fries with that?

Kevin3NF

Replication troubleshooting

If you are getting errors in any of the replication agents (SQL 2000), and the GUI isn’t giving you the details you need, turn on logging.

This is not an intuitive process, and when you are done you will want to make sure you turn it off or the log file can eventually fill up your drive given enough time.

To log an agent’s activities, right-click on the Agent in Replication Monitor in Enterprise Manager. Select Agent Properties. You should get the Job info for the agent. Click the steps tab and Edit the “Run Agent” step.

You should see a command line with a bunch of parameters. Add these at the end:
-output c:\Agent_log.txt -outputverboselevel 3

Change the path and file name of the first one to an appropriate drive on the Distribution server.

The outputverboselevel parameter is documented as taking 0, 1 and 2 in Books Online. 3 is also an option and records everything.

If you wind up calling Microsoft SQL Server support, they will likely ask you for this info.

Good luck, and happy replicating!

Kevin3NF