Root cause..

Question

Hi Team,

In out database user tables are not appearing,

I have checked errorlog,event log, windows event logs but i did not find the root cause why tables are missing how can i find out the reason for the above

How can i find out root casue for this. please suggest me.

Tx

---

subu

Answer 1

Hello subu,

They are eighter not created or in an other DB created or you don't have any permissions on it, and so you can't see them.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hi

Yesterday also it is appear we are working with that tables but to day tables are not appearing.

Can you please suggest me what are reasons behind this ?

How can i find out what the casues i have checked every thing but no luck using MASTER database if any thing is there please suggest me.

Tx

---

subu

Answer 3

Then maybe someone have deleted the tables or someone did a restore with an older backup, where the tables are not included.

In SSMS make a right mouse click on the database => Reports => "Standard Reports"

=> Schema Changing History, to see if someone has deleted the tables
=> Backup and Restore Events, to see if someone has restore the database.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Hi Olaf,

I have created the same report as per your suggestion but no luck those operations are happend bit long back

Could you please guide me how can i find out the causes on this issue..please its urgent

Tx

---

subu

Answer 5

Just to be clear, Are you saying you are not able to see the objects on object explorer or the objects are not in the sys.tables?

Can you just try below query...Let us know

Select * From sys.tables Where name='<table_Name>'

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 6

check my blog to know who have deleted the table

http://sqlservr.blog.com/2012/01/20/how-it-happen-so/

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 7

Hi Team

Thanks for your reply...Not only one table all tables are not appearing.

Tx

---

subu

Answer 8

Hello subu,

What about you permission, do you have SysAdmin rights on the SQL Server?

If not, you can only see those objects (table, view, etc) where you have permissions for.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 9

yes olaf i m sa here but still we are not able to see any tables, i checked Reports backup & Restore,schema changes Reports  as your suggestion, but no luck, is there any way to check the Root Cause of this situvastion how the tables are deleted.

Tx

---

subu

Answer 10

What version of SQL server are you using ?

did you upgrade recently ?

What is the compatibly level of the database ?

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
My Blog
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 11

Can you please execute the below statement and see for any clue. As you said this has happened quite long back, I doubt, still you can try it out.

use msdb;
select 
      DBRestored  = destination_database_name,
      RestoreDate = restore_date,
      SourceDB    = b.database_name,
      SourceFile  = physical_name,
      BackupDate  = backup_start_date
from RestoreHistory h
inner join BackupSet b
      on h.backup_set_id = b.backup_set_id
inner join BackupFile f
      on f.backup_set_id = b.backup_set_id
order by RestoreDate desc

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 12

yes olaf i m sa here but still we are not able to see any tables

Hello subu,

I guess you mean "can see in SSMS" the tables? Have you refereshed the "Tables" node in SSMS? SSMS don't automatically refereshes the view, if you e.g. create tables with T-SQL statements.

You can also query all existing tables with:

SELECT *
FROM sys.tables

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 13

Hi Team,

Some one was deleted all the tables in the database, & we dont know exactly what happend, but tables are not appearning in SSMS 

My boss was asking the root casue, any help on this really appriciate those guys

Thanks

---

subu

Answer 14

Hello Subu,

If the default trace is enabled (see script below) then you can get the information from the default trace; that's what the "Schema Changing History" report also do. With this script you can test it: It creates a table, drop it again and queries this events from the default trace:

USE [AdventureWorks];
GO

-- Is the default trace enabled?
SELECT CASE WHEN value_in_use = 1 THEN 'Yes' ELSE 'NO' END AS DefaultTrace
FROM sys.configurations where name = 'default trace enabled';  

-- Test: Create and Drop the table
CREATE TABLE myTestTable(myColumn int);
GO
DROP TABLE myTestTable;
GO

-- Read the trace file
declare @curr_tracefilename varchar(500);  
declare @base_tracefilename varchar(500);  
declare @indx int ; 
declare @temp_trace table (
        obj_name nvarchar(256),
        obj_id int,
        database_name nvarchar(256),
        start_time datetime,
        event_class int,
        event_subclass int,
        object_type int,
        server_name nvarchar(256),
        login_name nvarchar(256),
        user_name nvarchar(256),
        application_name nvarchar(256),
        ddl_operation nvarchar(40));

select @curr_tracefilename = path from sys.traces where is_default = 1 ;  
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx  = PATINDEX('%\%', @curr_tracefilename);
set @curr_tracefilename = reverse(@curr_tracefilename);
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; 

insert into @temp_trace 
select ObjectName,       ObjectID,
       DatabaseName,       StartTime
       EventClass,       EventSubClass
       ObjectType,       ServerName
       LoginName,       NTUserName
       ApplicationName,       'temp'
from ::fn_trace_gettable(@base_tracefilename, default)  
where EventClass in (46,47,164) 
      and EventSubclass = 0  
      and DatabaseID = db_id() ; 

update @temp_trace set ddl_operation = 'CREATE' where event_class = 46;
update @temp_trace set ddl_operation = 'DROP' where event_class = 47;
update @temp_trace set ddl_operation = 'ALTER' where event_class = 164; 

SELECT *
FROM @temp_trace

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 15

Hi Subbu,

"Some one was deleted all the tables in the database, & we dont know exactly what happend"

Are you sure that Someone has deleted the tables? How do you know that?

"Tables are not apprearing in SSMS" - you mean in object explorer or by querying the sys.tables?

When did you first observed this?

Please answer the above questions clearly. We would try our level best to get info, atleast, we would tell you is it possible or not.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 16

Hi Subu,

As Olaf suggested in his earlier post did you try to execute SELECT * FROM sys.tables in your database? Did you see any user tables created in the database so far when you execute the query?

Just create a dummy or test table in the database (if this is allowed by your manager) and refresh the SSMS -> Databases -> yourd -> Tables and check wether you can see the newly created table in SSMS. If you can see the table you have created there, then as you mentioned earlier, someone having adequete permission dropped the tables.

And also do you see other database objects like stored procedures, functions etc in the database through SSMS?

If you are connected to several SQL Server instances having several databases in SSMS then also it will not display the objects. It is a limitation in SSMS (I am not remembering it exactly though it will show an informational message in this situation instead of listing the objects).

---

Krishnakumar S

Answer 17

Hi Olaf,

i have executed the above script but i got the output yes.

Tx

---

subu

Answer 18

i have executed the above script but i got the output yes.

Which script, what for an output and: Yes = it works and you can see now what happend/who dropped the tables?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 19

Here i have tested in QA Env but in prod there is no any tables right now.

once i execute the script myTest Table is created, i m thinking its production Env... can i go head or not ?

---

subu

Answer 20

Sorry, but I don't understand what you mean or what you want to know?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 21

Hi Team,

Some data deletion was happend in our Environment.

Now i m planning to implementing AUDIT of the database & Server level any suggestions

How can i start to do the AUDIT can you please help me.

Thanks

---

subu

Answer 22

Hello subu,

There is an "old" DBA trick to avoid the deletion/modification of tables: Create for every table a view with SCHEMABINDING; as long as this view(s) exists, no one can modify it (even not a DBA/DBO); see CREATE VIEW (Transact-SQL)

And for auditing data changes you can use AFTER TRIGGER to log all changes to a history table; see CREATE TRIGGER (Transact-SQL)

BTW, you should check and modify your security setting: Give the users only that less permission they need to do there work; not more. If everyone can delete/modify object then you will have all the time trouble in this current way.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 23

HI Olaf,

Thanks for your reply...Really appriciate to your help...every time im going right way with your suggetions only.. Right now i m using SQL SERVER 2008 R2.

i m planning to implementing auditing all level can you please suggest me what are the steps we have to start auditing its really helpful to me. Any scirpts also plase suggest me.

Tx

---

subu

Answer 24

Are you using an Enterprise Edition of SQL Server? Then you could use the build-in function CDC = Change Data Capture.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 25

I think the CDC is basically not for auditing purposes like who made what changes to the tables at what period etc. The main data consumer that is targetted by CDC is ETL tools for incremental data loads. CDC is based on transaction logs and creates many tables, and other objects like SPs, UDFs etc and agent jobs for each table that have CDC enabled. And also it utilizes a retention based cleanup policy, that means it automatically cleanup the DML operations and changes it captured after the sepcified time, the default retention period is three days.

I think a better approach, if OP is using Enterprise edition of SQL Server 2008 is SQL Auditing, which is more granular and manageble:

Auditing in SQL Server 2008

SQL Server Audit (Database Engine)

An Introduction to SQL Server 2008 Audit

Basics of Change Data Capture

---

Krishnakumar S

Answer 26

some more suggestions in audting can you please help me, i m planning to implement asp. please help me.

---

subu

Answer 27

some more suggestions in audting can you please help me, i m planning to implement asp. please help me.

---

subu