| Author |
Topic |
|
unnamed
Starting Member
5 Posts |
 Posted - 2006-06-13 : 11:05:19
|
| Ok, I'm somewhat familiar with Exchange, but I'm totally new to SQL. Here's my problem: Each night I have a batch file shut down the SQL processes. I then copy the relevant files to another hard drive for back up purposes. I have 2 databases. The first database file and transaction log goes just fine and both files copy. The second database file goes just fine as well, but the transaction log returns "incorrect Parameter". It does this even if I simply try to right click & copy the file in windows with the SQL processes down so I've ruled out it being the syntax of my batch file. The .LDF for this database is huge (to me at least), it weighs in at +50GB's and is growing 2-4 GB's a week. Is it safe to simply delete this transaction log and start a new one since I have a good backup of it's associated database? I'm not exactly sure what caused the incorrect parameter errors either. I inherited this problem so my background info is limited. Any help is greatly appreciated! Thanks in advance! |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 11:16:11
|
| You're starting from the wrong place!You can backup the database without having to shut down the server. Just copying the database files may NOT get you back upright after a system failure.The backup files will smaller (quite possible MUCH smaller) than the actual SQL files.Sounds like your database is set to a Recovery Model of FULL. This will log every change to the database (so that you can recover up to a specific point in time), however for that to work you must take Transaction Log backups - i.e. you backup the Transaction log and then SQL Server clears it down (it doesn't truncate the file, it just makes the space available for reuse).As yours have clearly grown huge you are going to need a one-time SHRINK as well.Typically companies make Transaction log backups every 10-15 minutes, or maybe once an hour. That gives reasonable scope for recovery if the machine / database / etc. gets hosed.Alternatively you can set your Recovery Model to SIMPLE which will not keep the transaction log - then you can jsut recover to your most recent FULL backup.More info here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,BackupKristen |
 |
|
|
unnamed
Starting Member
5 Posts |
Posted - 2006-06-13 : 11:59:30
|
Thanks for the quick reply!Ok, so...1) set the Recovery Mode to simple2) Make a FULL Backup of the databases (via enterprise manager)This should then clear the TL's? I don't mind running without the full TL's since I can loose a day or even a few days of data without much of a problem. If it doesn't clear the TL's, i should then run shrink, possibly truncate too? Sorry, but I'm new to both SQL and the job so I don't wanna screw it up!  Thanks again! |
|
|
|
unnamed
Starting Member
5 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 13:41:13
|
| Changing to SIMPLE will stop the logging, but it won't shrink the files. You need to use the SHRINK DATABASE (and the "Move pages to start of file") option. Note that you should not use this option other than in exceptional circumstances because its a lot of "effort" for SQL to re-acquire space - but you clearly have unnecessarily huge files at present, so it needs doing as a one-off.You then need to add a step (3) to copy the Backup files to your other hard drive - as you were doing originally with the database files themselves. (You could build a scheduled task that made the backup and then immediately copied the files, if that would be appropriate?)Kristen |
|
|
|
unnamed
Starting Member
5 Posts |
Posted - 2006-06-13 : 14:47:41
|
| That's basically what I was thinking too about the copying files, etc. Thanks again for the help, it's much appreciated! Do you have any idea what the incorrect parameter message is all about? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 16:38:48
|
| "Do you have any idea what the incorrect parameter message is all about?"Nope, unless the path/filename you are copying has a space in it and you haven't enclosed it in double-quotes.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-13 : 16:50:28
|
| It doesn't sound like he is running BACKUP LOG when he gets the incorrect parameter message. unnamed, please walk us through exactly what you are doing or running what you get this message.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 17:32:27
|
| I was guessing that its during a DO/CMD prompt COPY operation.Kristen |
|
|
|
unnamed
Starting Member
5 Posts |
Posted - 2006-06-15 : 09:11:23
|
| Kristen is correct that it is in the CMD copy operation, however it also occurs if I were to right click and say copy on the file or simply drag it to a folder. Here are the steps I take to get the message, note that this is not through the SQL back up procedure, it was how I was incorrectly backing up the file previously.1) Stop MSSQLSERVER process (which also stops the SA)2) Locate and 'copy' the database file and associated transaction log. (two ways that I Was accomplishing this, by copying in windows explorer and by copying via my batch file, neither were successful) It is here in step 2 where it will copy only the database and NOT the bloated transaction log file. The TL returns the incorrect parameter error. However, I have since started using the backup procedure through Enterprise Manager and changed the recovery mode to 'simple'. I also did a shrink on the DB and the TL is now very manageable (THANKS!!). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-15 : 13:14:44
|
| The way that you were previously backing up the files is known as a cold backup. The new process that you are using is known as a hot backup. Most people perform hot backups only. Your error probably isn't related to SQL Server at all. It's probably a DOS error.Tara Kizeraka tduggan |
|
|
|
|
SQL 2000 transaction log "incorrect parameter"