6/6/2011 9:57 AM | ||||
Posts: 291 Rating:
|
Hello Stab & Krish, You must take a look at this: /tf/WW/en/Posts/1340 Please do as Salma said~!
&
Thanks to Salma!!! Hoping Stab's database can be rescured soon! Hope all is well, Komi P.S.: Years ago, there was not such a sub-folder, "CommonArchiving\" in WinCC V5.x ~ V6.0 sp2. Thanks again to Salma!! |
|||
6/7/2011 7:39 AM | |
Joined: 10/3/2010 Last visit: 7/31/2024 Posts: 1560 Rating:
|
If you're transaction log file (TGCMRT.ldf) has that size, there ain't enough room to let SQL handle this cause it needs certainly the same size to fix this. Here's some more info on transaction log files but be aware that this is pure SQL-sided information: Whenever a data update is made entries are added to the transaction log. It is not possible to prevent this as it is part of the way sql server maintains integrity - particularly during recovery. The transaction log is a circular file i.e. when the end is reached any free entries at the start will be used. This means that all being well the file will stay at a constant size as the current entry cycles round. The system maintains the MinLSN which is a pointer to the first active log record. Any log records before this (in the circular file) are free. The MinLSN will be prevented from moving forward by any open transactions - i.e. the oldest open transaction entry will be >= the MinLSN. The MinLSN is updated at checkpoint so committing a transaction will not immediately free entries and anything that holds up the checkpoint can cause problems. If the database is in simple recovery mode all entries prior to the MinLSN will be freed at checkpoint. If the database is in full recovery mode (and a full backup has been taken) the entries prior to the MinLSN will only be freed by a transaction log backup (not full backup). Unfortunately the sql server default (except local editions) leaves the databases in full recovery mode. This menas that if no action is taken no tr log entries will be freed and the log file will eventally fill the disk and crash the system. The SQL Server installation process is very simple and commonly carried out by inexperienced personel. This will appear to work happily but cause problems later. I would recommend always setting the model database to simple recovery mode to set the default for new databases. If the log file has grown do to being in full recovery mode then set it to simple before going any further. This should immediately stop the log from growing. Enterprise manager Right click on the database, properties, Options, set model to simple, OK. t-sql sp_dboption [dbname], 'trunc. log on chkpt.', 'true' Shrinking the transaction log file (.ldf) Before this make sure there are free entries by setting the recovery model to simple or backing up the log. Enterprise manager Right click on the database, All tasks, Shrink database, Files, Select log file, OK. t-sql dbcc shrinkfile ([db_log_name]) Here [db_log_name] is the logical name of the log file as found from sp_helpdb or the table sysfiles
Safest way is surely trying to make a backup of both mdf & ldf-file first. You could try the shrink method first. After that, I would try to copy these 2 files to another PC with big enough harddisk (500GB) and the same version of SQL. |
Last edited by: SteBo at: 6/7/2011 7:45 AMB r g d s , |
|
6/7/2011 7:58 AM | |
Joined: 10/3/2010 Last visit: 7/31/2024 Posts: 1560 Rating:
|
Anyway, I would try shrinking the ldf first. It shouldn't become that big but it might fail cause the ldf-size is bigger than the free space on your disk: Once again, make sure to have a backup first (mdf & ldf). Open Sql Server Managment Studio, rightclick your database and go to Tasks -> Shrink -> Database According to some online sources, you can also detach the database, delete or rename the ldf-file, attach the database and a new empty ldf will be created but whatever you try, it will all be tricky and time consuming. http://www.codeproject.com/KB/database/truncate_log_SQL_server.aspx |
B r g d s , |
|
6/7/2011 8:25 AM | |
Posts: 291 Rating:
|
Hi SteBo, Sorry!! I need to tell Stab not to try "shrinking" this time"!!! We all agree keeping the files, mdf & ldf, well (backup) is important. Stab knows that, too. But Please understand that: 1. the segment is compressed by WinCC/WinCC OLEDB Provider. & 2. we all know that the Data file structure (segmentation) is hidden for accessng data by WinCC OLEDB Provider. Our problem is simply that we know the .ldf lost linkage to the old segmentation, but we just don't know how to make the linkage recovered correctly.
Personally ~ I will sugguest not to do anythink may harm the integrity of the archive segment any more. Please understand this~ SteBo Regards, Komi |
6/7/2011 8:37 AM | |
Posts: 291 Rating:
|
Hi SteBo, Please take a look at: http://support.microsoft.com/kb/307487/en-us#XSLTH3152121122120121120120 There will be another question when you want to do the shrinking. Further note that the SQL Server management studio in 2005 does not show the correct size of tempdb files after a shrink operation. The 'Currently allocated space' value is always pulled from sys.master_files DMV and this value is not updated after a shrink operation happens for tempdb database. Just for your information,
(The truth is/was I tried for WinCC V6.x & felt myself wasting time B.R., Komi |
Last edited by: Komi at: 6/7/2011 9:18 AMBut I agree that It makes no loss to trying it once if Stab has another computer & another copy of archive-segment & disk space & time :D |
|
6/7/2011 9:12 AM | |
Joined: 10/3/2010 Last visit: 7/31/2024 Posts: 1560 Rating:
|
I had some similar problems in the past with ldf-files (not related to WinCC!) but in the end, I lost too much time and chances for a good/decent result are pretty slim. That's why I said trying to play with these files on another machine first (or even in a virtual machine). |
B r g d s , |
|
2/15/2016 2:43 PM | |
Joined: 9/3/2014 Last visit: 3/13/2019 Posts: 4767 Rating:
|
New question published by bilalzaidi is split to a separate thread with the subject Two problems with PCS7 WinCC V6.0. Best regards |
Follow us on