May 27, 2011 at 6:23 am
Hi All,
Someone in my team have run 'Backup database with truncate_only' command.
error log doesn't show who has run the command ?
its a SQL 2005 server
Thanks
May 27, 2011 at 6:39 am
Try opening the default trace, which you'll find in the same folder as your errorlog files. If it's not in there, and you don't have anything set up to monitor operations like this, you're not going to know.
John
May 27, 2011 at 6:44 am
there's also the msdb backup history tables that might have a little more info (not sure about user tho).
May 27, 2011 at 6:49 am
Ninja's_RGR'us (5/27/2011)
there's also the msdb backup history tables that might have a little more info (not sure about user tho).
There's a username column in the backupset table, but since the command in question doesn't create a backup set, I think we're out of luck.
John
May 27, 2011 at 6:51 am
Ya but it might pinpoint the exact datetime.
More to the point I had a similar issue that backupexec was doing the backups without anybody's knowledge. And the name of the app was saved in those tables. So maybe that could luck him out of this trouble.
May 27, 2011 at 6:55 am
I still don't think it'll help. BackupExec backups create backup files, so they'll appear in the backupset table. BACKUP LOG WITH TRUNCATE_ONLY doesn't create any files and so I don't think there'll be anything in backupset.
John
May 27, 2011 at 6:58 am
Lucking out is lucking out :w00t:.
I'll deffer to your expertise but to me it looks like B.E. can be configured to do pretty much anything a maintenance plan can do (it also did check db, check table here). So I'm not putting anything past them.!
May 27, 2011 at 7:05 am
Backup database or backup log?
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
May 27, 2011 at 7:14 am
BACKUP LOG WITH TRUNCATE_ONLY
May 29, 2011 at 12:18 am
Sqlsavy (5/27/2011)
Hi All,Someone in my team have run 'Backup database with truncate_only' command.
error log doesn't show who has run the command ?
its a SQL 2005 server
Thanks
I second that john.
I test the same the backupset table don't have any info.The default trace has the user name but its has only agent service account name.
SELECT * FROM sys.fn_trace_gettable('E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_114.trc',default)
where textdata like '%with truncate_only%'
You can use the ‘3031’ trace flag to stop the user to perform the deprecated option.
dbcc traceon (3031,1)
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 29, 2011 at 9:26 am
While you are researching this, I would recommend that you set the trace flags to make these type of operations NO-OPS so they don't cause problems with your log chain.
From the above article:
How to prevent it being used
If you're a sysadmin who wants to stop your database owners and backup operators from using the NO_LOG option, there is way to do it. Trace flag 3231 in SS2000 and SS2005 will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery mode, and will clear the log in SIMPLE recovery mode. Trace flag 3031 in SS2005 turns them in checkpoints in all recovery modes. These trace flags are undocumented but they're allowed to be publicized.
This way no-one can truncate the log unless you allow them to, and when they come to you to complain, you can educate them!
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply