October 2, 2008 at 7:40 pm
I'll be honest, I'm not a DBA. I've identified my SQL skills are lower than I'd like and will be attending classes and working more with SQL now that it appears it's going to be affecting me and my applications a lot more.
I would truly appreciate any advice you would care to throw someone on the edge of a break down. Luckily I safely migrated this offending database over to a SAN which offers me the ability to extend the storage until I get this figured out.
Problem: I have a single SQL 2000 server on a Windows 2003 Enterprise OS. It houses a lot of databases. One database in particular is for our very large Microsoft IIS middleware. It weighs in at about 9GB worth of data. It used to be about 80GB until I used the shrink file function (Yay, me). But the transaction log file is a whopping 95GB and growing every day after the MIIS application kicks in.
I'm assuming this is due to replication setup and then deactivated/turned off/broken... because the shrink option shows that there is no free space in the transaction log file. Unfortunately the server is trying to replicate to was a virtual test machine that was blown away months ago before I got this.
Question: How can I shrink this log file and regain control over this (and the many other databases) on this server?
Additional information: The backup of these databases is very successful, BackupExec is running (surprisingly) well.
Thank you for any help you can provide and please talk to me like the SQL newbie I am. 🙂
P.S. I did read Otto's very detailed response to the topic a few down, but I'm not grasping what I would need to do... :unsure:
Binary
October 3, 2008 at 5:26 am
First, run DBCC OPENTRAN and see if there's any reference to replicated and non-replicated transactions. If not, skip to the 3rd paragraph.
Drop any publication that may exist, and make sure that you remove any trace of replication. Most of that should be doable through enterprise manager's replication configuration.
If you're not having joy there, you can run the following commands. They should clean everything up
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
Run sp_helppublication to get the names of any publications you have, then run
EXEC sp_droppublication @publication = < publication name >
EXEC master..sp_replicationdboption
@dbname = < Database name >,
@optname = N'publish',
@value = N'false';
Once you've done that, check the recovery model of the database. If it's full or bulk-logged, check that log backups are running (They'll be either a job or a maintenance plan). I don't know if backupexec does log backups or not. I'm not even sure how backupexec backs up databases.
If you're in simple recovery, run a checkpoint and then shrink the log to a reasonable size. If you're in full/bulk logged, wait for the next log backup to run and then shrink the log to a reasonable size
I don't have enterprise manager anywhere and I don't recall the exact steps.
An aside not completely related.
By shrinking the data file you have fragmented the indexes badly. Once you have the log situation sorted out, be sure to rebuild all of your indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2008 at 7:40 am
Thank you for the detailed explination, although I'll appologize up front for still being a bit confused on where to start. My MS SQL skills are limited to the Enterprise Manager and even then, I'm a novice.
Where would I run DBCC OPENTRAN?
How would I go about dropping publications. I do notice a lot of the databases are listed in the Sql server -> Replication -> Publications folder. Each of them have an icon that looks like a blue book with an aqua arrow over it.
Where would I run these commands that you provided?
BackupExec has a SQL plugin that integrates with MS SQL to gather data from the databases and truncate the log files. Each database is setup for FULL recovery, that's a requirement of BE.
Based on recommendations from other sites I created a maintenance job to reindex the databases on a weekly basis after shrinking the files. This has recovered a considerable amount of drive space too.
Thank you in advance!
October 3, 2008 at 10:44 am
binaryspiral (10/3/2008)
Thank you for the detailed explination, although I'll appologize up front for still being a bit confused on where to start. My MS SQL skills are limited to the Enterprise Manager and even then, I'm a novice.Where would I run DBCC OPENTRAN?
Query Analyser. Make sure that the query window is connected to the DB that has the problem. There should be a drop down somewhere on the tool bar with all the databases listed in it
How would I go about dropping publications. I do notice a lot of the databases are listed in the Sql server -> Replication -> Publications folder. Each of them have an icon that looks like a blue book with an aqua arrow over it.
I don't have enterprise manager, and I haven't used in a few years, so I can't give you exact instructions. If I recall correctly, there's a replication configuration entry on one of the menus. I don't recall which one.
Are you sure that none of them are in use? You said they were replicating to a server that doesn't exist any longer. Is that true for all of them?
Where would I run these commands that you provided?
Those are for if you can't get rid of the publications via Enterprise manager. Run them from Query Analyser
BackupExec has a SQL plugin that integrates with MS SQL to gather data from the databases and truncate the log files. Each database is setup for FULL recovery, that's a requirement of BE.
I hope it backups up the log before truncating it. It should. If those jobs are running, then your only problem is the replication.
Based on recommendations from other sites I created a maintenance job to reindex the databases on a weekly basis after shrinking the files. This has recovered a considerable amount of drive space too.
You should not be shrinking the files on a regular basis. As a once-off, it's OK, but regularly all you're going to do is force the DB to grow again next time someone inserts data. The growing of the file will have an impact on performance and the repeated shrink/grow can cause fragmentation at the file system level. Databases do grow and they do need some free space inside to work properly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2008 at 12:02 pm
Fantastic help. Thank you! I will work with your suggestions and get back to you with a result.
Quick Question: If you don't have Enterprise Manager, how do you work with your databases?
October 3, 2008 at 12:50 pm
binaryspiral (10/3/2008)
Quick Question: If you don't have Enterprise Manager, how do you work with your databases?
SQL Server Management Studio. I only have SQL 2005 and 2008 instances. Enterprise manager doesn't work to versions higher than 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply