Transaction logs

Question

Transaction logs

Krasavita

SSC-Insane

Points: 23138
More actions
January 14, 2009 at 7:21 am

#129462

I need to start keeping data every 4 hours for transaction logs. DB is saet to full, how can I do it?What is the code to run? I know how to set up a job but don't know what code to run Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

quickest and easiest way to do this is to create a maintenance plan via SSMS. Use transaction type from the backup task.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Look up BACKUP LOG in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

I sat up a maintance plan and when I ran I get an error:

This is my subplan:

BACKUP LOG [Prod_Efficiency] TO DISK = N''F:\\Prod_Efficiency_backup_200901140944.trn'' WITH NOFORMAT, NOINIT, NAME = N''Prod_Efficiency_backup_20090114094452'', SKIP, REWIND, NOUNLOAD, STATS = 10

Hereis an error:

Executing the query "BACKUP LOG [Prod_Efficiency] TO DISK = N'F:\\\\Prod_Efficiency_backup_200901140944.trn' WITH NOFORMAT, NOINIT, NAME = N'Prod_Efficiency_backup_20090114094452', SKIP, REWIND, NOUNLOAD, STATS = 10

" failed with the following error: "Cannot open backup device 'F:\\\\Prod_Efficiency_backup_200901140944.trn'. Operating system error 5(Access is denied.).

BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Error:-1073548784

What I am doing wrong.

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Why a double \\ in the file name?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Prince Ali SSC-Addicted Points: 479 More actions · Answer 5

Another way is to set up a single job, with a schedule to kick it off every four hours, and run the BACKUP LOG command from within the job. Maybe add an alert, or a step to email you, in case the job fails.

The code to create a backup is found in BOL (SQL Server's help file, aka Books On Line), under BACKUP.