April 21, 2008 at 11:34 am
I am looking at the event viewer under application on the server log. Msg. 18278 :
Database log truncated: Database: ILLData.
here is what is says in the error log of sql
2008-04-11 07:00:04.36 backup Database backed up: Database: ILLData, creation date(time): 2007/03/12(16:30:24), pages dumped: 31998, first LSN: 16781:269:1, last LSN: 16781:272:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\illiad\backup\ILLDataBackup'}).
2008-04-11 07:00:04.47 backup Database log truncated: Database: ILLData.
Below you will find the script that runs the backup:
backup log ILLData with no_log
dbcc shrinkdatabase (ILLData, 10)
>>>Do I need the "with" option to do the backup? <<<<<<???????
Can this error cause any problems if I have to restore this dbase?
Thanks for any responses.
April 21, 2008 at 12:35 pm
tseymour (4/21/2008)
Ibackup log ILLData with no_log
dbcc shrinkdatabase (ILLData, 10)
Can this error cause any problems if I have to restore this dbase?
the with no_log option means you are merely emptying out the log without creating an actual backup of the log. This option is the same as truncate_only but is usually only used when the log is so full there is not even room to record in the log the fact that you are taking a log backup.
It means you will not be able to do a point in time restore using the transaction log because you are destroying your log chain. You will not have another recovery point until you do a full backup.
If you want to be able to restore to any point in time remove the no_log option, ensure database in full recovery mode and remove that dbcc shrinkfile, it is inefficient to keep shrinking a database.
---------------------------------------------------------------------
April 28, 2008 at 1:35 pm
Can you please tell me where in SQL would I verify if the dbase is setup for recovery mode?
April 28, 2008 at 1:48 pm
In enterprise manager, right click on database , select properties, select options tab, it lists the recovery mode in there.
or TSQL command sp_helpdb dbname gives details on database including recovery mode.
---------------------------------------------------------------------
April 28, 2008 at 1:55 pm
Thank you very much for your expertise. I have one dbase that is set to simple instead of full. I would like it to be full. How do I correctly change the model to full without screwing things up? Is it just as simple as changing the option on the pull down menu? Can recommend sources to study to learn about SQL?
April 28, 2008 at 2:33 pm
don't worry you can't screw anything up. It is as simple as changing option in drop down menu and selecting ok.
TSQL to do the same is 'alter database dbanme set recovery full'
Take a full backup once done and then set tran log backups as your log file will now start to grow.
Keep an eye on your log file to determine how often to do the backups and in case of large growth.
Check the 'grow log file by' settings on the tran log tab under properties. if log file is tiny and growth is 10% you are going to get a log file thats fragmented on disk, so set log file size to a sensible value (say 20% of data file) and growth to a sensible value in MB, rather than %.
Learning? this site and others like it, Books on line, download SQL 2000 evaluation edition and SQL 2005 express and just try things out, or use a test server if you have one. If your firm will pay for it the admin courses and even better the design and programming a server courses. There are books out there but I am not sure which are best as I go for e-resources.
---------------------------------------------------------------------
April 29, 2008 at 1:36 pm
I just noticed after I removed the with no_log option on my backup it has not worked. What should I put in its place after removing the no_log option?;)
April 30, 2008 at 3:30 am
I would remove that backup log statement and the shrink database. Set up a maintenance plan to backup your transaction logs. Frequency of log backups will depend on how much data you feel you can afford to lose and the growth ratew of the log.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply