August 29, 2005 at 10:16 am
I'm new to SQL so pardon my ignorance.......I am trying to dump my transaction log as it has grown excessively but I am not all that familiar with the syntax. I know that database_name = the name of my db but what does @database_name_var = in the following syntax:
BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
any help is appreciated.
August 29, 2005 at 1:03 pm
That would contain the database name if the database name was being passed to this command through a variable. Is the only reason you wish to dump the log is to free up space? Or do you wish to be able to restore to a point in time?
August 29, 2005 at 1:26 pm
To free up space, I run an hourly backup regardless.
August 30, 2005 at 12:21 am
If you only need to free up space and are happy to go back to the last FULL backup, then set the database to "Simple" recovery model rather than scheduling a log truncate.
Simply, this will stop the log file growing and filling up because nothing is kept in it for any length of time.
It makes the system to issue "BACKUP LOG database_name WITH NO_LOG" automatically when a "checkpoint" occurs (every minute or 5 - it depends...)
I think that's what dbamark is asking...
September 1, 2005 at 3:31 pm
To free up space you run DBCC Shrinkfile with Trunacte option, SQL Server will return space less space it marks as active log. Run a search for DBCC Shrinkfile in SQL Server BOL (books online). Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
September 2, 2005 at 1:10 am
hi Ruberli,
Their is no need to run the backup every now and then, you set the database option such that you dont have to do this again and again
do the following and your problem will be solved
sp_dboption 'Database_Name','Autoshrink','on'
go
sp_dboption 'Database_Name','trunc. log on chkpt.','on'
go
dbcc shrinkdatabase('Database_Name',10)
go
the sp_dboption will set the database defaults to autoshrink and truncate on check point on. this will your problem will solved forever
Run the above statements in Sql Query Analyzer
cheers
LEOAUGUST
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply