[THIN] Re: IT Could happen to you...SQL Database Corruption 1 01
- From: Henry Sieff <hsieff@xxxxxxxxxxxx>
- To: "'thin@xxxxxxxxxxxxx'" <thin@xxxxxxxxxxxxx>
- Date: Wed, 1 Sep 2004 09:39:07 -0500
In a bind, if your tlog gets full,
(From Query Analyzer)
BACKUP LOG $DBNAME WITH TRUNCATE_ONLY
(Where $DBNAME is the name of the DB. This flushes all commited transactions
from the log)
USE $DBNAME
GO
DBCC_SHRINKFILE ($FILENAME, $SIZE)
GO
(This shrinks the file $FILENAME, which should be the filename of your log
file, to $SIZE is MB's. This can be set as low as the low end of the tlog
file of the db).
This is not as good as David's, but it was my quick and dirty way to get out
of a bind.
The best idea is regular backups of the tlog. Also, you should look into
setting alerts. You can set an alert in SQL to tell you when your tlog is X%
full. Check out Ent. Man.->Server->Management->Alerts and Ent.
Man.->Server->Management->Operators
Define your operators, define your alerts.
Henry
> -----Original Message-----
> From: thin-bounce@xxxxxxxxxxxxx [mailto:thin-bounce@xxxxxxxxxxxxx]On
> Behalf Of David Teague
> Sent: Wednesday, September 01, 2004 8:24 AM
> To: thin@xxxxxxxxxxxxx
> Subject: [THIN] Re: IT Could happen to you...SQL Database
> Corruption 101
>
>
> Jim,
> I know a lot about administrating sql servers, all this is great
> advice, remember the transaction logs are the important part! When I
> setup a database for a customer depending on their needs I
> will back up
> the transaction logs at least every 4 hours, and the database once a
> day, sometimes less. I do this for two reason, 1st, the backup of the
> transaction log, also cleans out the log so you do not run
> out of space,
> also if you have to do a restore from a previous nights
> backup you will
> at least a have some recent restores of your transaction logs
> to get you
> close to when the database failed, four our customer's the database's
> change by the minutes so this is important. Depending on your webpage
> database I would at least back up the transaction log daily. I have
> attached a sql script that can get you out of a bind if your
> transaction
> log gets full, so you do not have to restore from backup to
> resolve that
> issue. This works for both sql 7 and 2000.
> David Teague
> Support Anaylst
> TMTsoftware
> 919-493-4700
>
> -----Original Message-----
> From: Jim Kenzig http://thin.net [mailto:jimkenz@xxxxxxxxxxxxxx]
> Sent: Wednesday, September 01, 2004 8:53 AM
> To: thin@xxxxxxxxxxxxx; windows2000@xxxxxxxxxxxxx
> Subject: [THIN] IT Could happen to you...SQL Database Corruption 101
> Hi All,
> As some of you may or may not of noticed my web site the Thin.net had
> some
> "issues" yesterday. Issues which led me down an all night path of why
> you
> need to regularly backup the transaction logs of your SQL
> 2000 database.
> First off all disclaimers on the Thin.net apply. I am a NOVICE SQL
> admin, I
> have had to learn SQL because it is showing up everyplace.
> (Now even in
> Citrix) You need to know some basic backup procedures to save
> your soul.
> Perform these functions at your own risk. I take no liability
> about what
> you
> do to your system by using this information. OK Here goes:
> Apparently there are two files critical for you database. One
> has a .mdf
> extension and the other has a .ldf extension. The transaction
> logs as I
> understand it reside in the .ldf portion of your Database
> My web site is completely backended with SQL via the use of
> Cold Fusion.
> There are about 4 different databases that control different things
> there. I
> regularly back up the databases to a file on my drive and then back up
> the
> back up file onto my DLT Tape drive. I have always been able to
> restore my
> databases from backup by performing the following procedure:
> Expand the server that contains the "Database" directory that has your
> databases that need restored.
> 1. Right click on "Databases" < Select "New" < Restore Database
> 2. Under the "General" Tab < Select the radio button for "From Device"
> a) Add device
> b) browse to directory - only one database should be added since the
> application will create a .mdf and .ldf file (logs) for each database.
> Adding in the names of ALL databases will result in ONE .mdf and .ldf
> file
> shared for all of them.
> c) after database is added in, click OK
> 3. Back on "General" tab, make sure "Database Complete" is selected.
> 4. Also check that the database name you are restoring is accurate in
> text
> field (located at top of menu).
> 5. Go to "Option" menu. At botton of menu, select "Leave Database
> Operational. Do not restore Transition log." if you aren't restoring
> logs
> and only database.
> 6. Click OK A progress bar should appear on the screen indicating the
> restore is in progress. Repeat these steps for each database
> that needs
> to
> be restored.
> Now if you had the smarts to take your database offline and backup its
> .ldf
> and .mdf files you could of simply taken the database offline and
> swapped
> out these files into your database directory with the
> corrupted ones and put the database back online. Or you could right
> click on
> the server and attach the database by selecting these files.
> But what happens when your backup .bak file is too old or is corrupted
> and
> either the .mdf file or the .ldf file is corrupted. You pretty much
> end up
> SOL if you haven't been backing up something
> Anyways here is what happened to me and it could very easily happen to
> you.
> The .ldf file ran out of space on my Ads database for some
> reason. This
> database also controls some other stuff on my site and
> it virtually broke my site by being down. I knew it was out of space
> because that is what the error message said. It even told me
> how I could
> try
> and fix it. The error read: Error: 9002, Severity: 17, State: 6
> The log file for database 'aspads' is full. Back up the
> transaction log
> for
> the database to free up some log space. Ok so I learned that
> there is a
> tick box when you back up the database to clear out free space in the
> transaction log. So I tried to just back up the transaction
> log to clear
> it
> and that did not work.
> We'll I thought I had a good .bak file yesterday but it
> turns out that
> my
> backup job wasn't set up exactly right. My fault. After a bunch of
> different restore attempts, I ended up going back a year where I did
> have a
> good backup to restore what I could and then enter the rest (til 3 am
> this
> morning) to get the database up to date.
> Lesson learned:
> Make sure that: BACKUP FREQUENTLY!
> 1. Your database .mdf and .ldf files are set up with enough space to
> grow on
> your drive. You can take your database offline, right click on
> properties
> for it and make changes to these in the appropriate fields and put the
> database back online.
> 2. Backup the .bak files from your backup often.
> 3. Take the database offline and make a backup copy of the
> .ldf and .mdf
> files so that you can return to a point in time if all else fails.
> 4. Look carefully at your maintenance plan. SET ONE UP IF YOU
> DON'T HAVE
> ONE! SQL Server has a great wizard that sets it up for you. Be certain
> that
> the Transaction log is getting backed up and cleared of extra space by
> right
> clicking on your maintenance plan in the MMC and choosing
> properties and
> then clicking on the Transaction Log Backup tab and checking
> the box to
> BACK
> UP THE TRANSACTION LOG AS PART OF THE MAINTENCE PLAN. I don't
> think this
> is
> chosen by default so you need to do this after you set up
> your maintence
> plan. There is a similar choice if you do a manual backup.
> By backing up the transaction log it will free up some space in it so
> that
> it doesn't run out of space and crash your database. IT CAN and DID
> happen
> to me.
> Do this and you will save yourself a lot of headaches in the future.
> Trust
> me.
> My only savior was that I was able to restore my database
> from an older
> .bak
> file using the above procedure.
> Hope this helps someone out there. If you have anything to add let me
> know.
> I'll post this as an article on Ondemandaccess.com once I
> have all ideas
> and
> feedback.
> Regards,
> Jim Kenzig
> http://thin.net
> ********************************************************
> This Weeks Sponsor triCerat:
> Have you had your fill of printing support calls, unauthorized apps
> running on unsecured Terminal Servers, profile headaches, and
> application performance problems? Join us and learn how you can have a
> less demanding on-demand enterprise!
> http://www.tricerat.com/?page=events#register
> **********************************************************
> Useful Thin Client Computing Links are available at:
> http://thin.net/links.cfm
> ***********************************************************
> For Archives, to Unsubscribe, Subscribe or
> set Digest or Vacation mode use the below link:
> http://thin.net/citrixlist.cfm
>
********************************************************
This Weeks Sponsor triCerat:
Have you had your fill of printing support calls, unauthorized apps running on
unsecured Terminal Servers, profile headaches, and application performance
problems? Join us and learn how you can have a less demanding on-demand
enterprise!
http://www.tricerat.com/?page=events#register
**********************************************************
Useful Thin Client Computing Links are available at:
http://thin.net/links.cfm
***********************************************************
For Archives, to Unsubscribe, Subscribe or
set Digest or Vacation mode use the below link:
http://thin.net/citrixlist.cfm
Other related posts:
- » [THIN] Re: IT Could happen to you...SQL Database Corruption 1 01
