Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to View Table Logs?

Author  Topic 

raysefo
Constraint Violating Yak Guru

260 Posts
Posted - 2011-12-23 : 02:55:23
Hi,

I wonder if there is a way to see the logs especially update and delete operations on a table. By the way I am using MS SQL Server 2008 R2.Lets say, user XXX deleted a row on YYY date etc.

Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-23 : 03:16:49
whats the recovery model used for database

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts
Posted - 2011-12-23 : 03:19:22
Hi,

Thanks for your quick reply. But I am not DBA so I can not say anything about recovery model. How can I get the recovery model?

Best Regards.
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts
Posted - 2011-12-23 : 03:27:32
I tried to see the properties of the related database but I am getting this error TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

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

Property Owner is not available for Database '[TableName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-23 : 03:30:11
in Sql Management studio connect to your server, right click on desired database and select properties. go to options tab and you can see the recovery model set

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts
Posted - 2011-12-23 : 03:39:35
But when I try to select properties I am getting the error on my prior post.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-23 : 04:02:27
are you logged in using a restricted account?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts
Posted - 2011-12-23 : 04:11:09
Anyway does it matter if the recovery model is simple or full?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-23 : 04:23:05
full means it will log each and every transactions, simple will not log each transactions and in case of an intermediate failure you wont be able to recover all the intermediate data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts
Posted - 2011-12-23 : 04:25:12
Its simple I guess.
Thanks so much
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-12-23 : 04:26:30
SQL doesn't keep audit logs (which is what you're talking about there), if you want something like that, you need to develop it yourself with triggers, CDC, Change tracking, SQL Audit or some other similar feature.

What SQL does keep, and what visakh is alluding to is a transaction log, but that's not a human-readable log like you want (and only sysadmins can read it).
The transaction log rows would look something like this:

Current LSN	Operation	Context	Transaction ID	LogBlockGeneration	Tag Bits	Log Record Fixed Length	Log Record Length	Previous LSN	Flag Bits	Log Reserve	AllocUnitId	AllocUnitName
00001d19:000004ce:0011	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	0000:00456146	0	0x0000	62	612	00001d19:000004ce:000c	0x0002	722	72057594111066112	dbo.TestingRangeQueries.idx_RangeQueries_Cluster
00001d19:000004ce:0012	LOP_SET_BITS	LCX_PFS	0000:00000000	0	0x0000	54	56	00000000:00000000:0000	0x0000	0	72057594111066112	dbo.TestingRangeQueries.idx_RangeQueries_Cluster
00001d19:000004ce:0013	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	0000:00456146	0	0x0000	62	104	00001d19:000004ce:0011	0x0002	214	72057594106085376	dbo.TestingRangeQueries.idx_RangeQueries_NC1
00001d19:000004ce:0014	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	0000:00456146	0	0x0000	62	112	00001d19:000004ce:0013	0x0002	222	72057594106150912	dbo.TestingRangeQueries.idx_RangeQueries_NC2
00001d19:000004ce:0015	LOP_SET_BITS	LCX_DIFF_MAP	0000:00000000	0	0x0000	54	56	00000000:00000000:0000	0x0000	0	6488064	Unknown Alloc Unit
00001d19:000004ce:0016	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	0000:00456146	0	0x0000	62	112	00001d19:000004ce:0014	0x0002	222	72057594107789312	dbo.TestingRangeQueries.idx_TestingStats
00001d19:000004ce:0017	LOP_SET_BITS	LCX_PFS	0000:00000000	0	0x0000	54	56	00000000:00000000:0000	0x0000	0	72057594107789312	dbo.TestingRangeQueries.idx_TestingStats
00001d19:000004ce:0018	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	0000:00456146	0	0x0000	62	612	00001d19:000004ce:0016	0x0002	722	72057594111066112	dbo.TestingRangeQueries.idx_RangeQueries_Cluster
00001d19:000004ce:0019	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	0000:00456146	0	0x0000	62	104	00001d19:000004ce:0018	0x0002	214	72057594106085376	dbo.TestingRangeQueries.idx_RangeQueries_NC1
00001d19:000004ce:001a	LOP_DELETE_ROWS	LCX_MARK_AS_GHOST	0000:00456146	0	0x0000	62	112	00001d19:000004ce:0019	0x0002	222	72057594106150912	dbo.TestingRangeQueries.idx_RangeQueries_NC2
00001d19:000004ce:001b	LOP_SET_BITS	LCX_DIFF_MAP	0000:00000000	0	0x0000	54	56	00000000:00000000:0000	0x0000	0	6488064	Unknown Alloc Unit


That's a portion of the log for the deletion of 10 rows, in total there are 62 rows logged for that operation.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-12-23 : 04:30:04
quote:
Originally posted by visakh16

full means it will log each and every transactions, simple will not log each transactions


All operations are logged in all recovery models. Simple recovery does log each and every transaction.

The difference from full is that bulk operations get minimally logged (page allocations logged, not actual rows) and the log entries are marked inactive on checkpoint.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts
Posted - 2011-12-23 : 04:30:47
thanks so much
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-23 : 04:46:47
quote:
Originally posted by GilaMonster

quote:
Originally posted by visakh16

full means it will log each and every transactions, simple will not log each transactions


All operations are logged in all recovery models. Simple recovery does log each and every transaction.

The difference from full is that bulk operations get minimally logged (page allocations logged, not actual rows) and the log entries are marked inactive on checkpoint.

--
Gail Shaw
SQL Server MVP

Ok..thanks for that info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-12-23 : 05:46:28
If anyone's interested in more than a 1 liner on the recovery model differences - http://www.sqlservercentral.com/articles/Administration/75461/

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site