Home > Query, SQL > BISQL # 52 :How To Check Database log file Details and Who Access last

BISQL # 52 :How To Check Database log file Details and Who Access last

September 7, 2011 Leave a comment Go to comments

Hi friends ,

In this post we are going to covering two main points

  1. How To Check Database
  2. How To Check  Who Access last

Lets see one by one ..

1.How To Check Database

Its often Daily DBA work to check exactly how big my log file and its size and how advance do I have allocate new space for same database

So I want my result should return following things

  1. File Name
  2. Physical Name
  3. Total Size in MB
  4. Space Used

as I can directly use above result for report as well

Following is script for same

 

Use  AdventureWorksDW --<Your Data Base Name > 

SELECT
name              AS 'File Name' ,

physical_name     AS 'Physical Name',
 
size/128          AS 'Total Size in MB',
 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 
            AS'Available Space In MB'
 
FROM  sys.database_files

Output for Above script is simple as we want

image 

 

2.How To Check Who Access last and on which segment exactly

When we are running DB on real time is need to always track user based on the who have done last recent changes

I got following script and which is very useful to track who have don last changes in data base  

Following is script

select
         t.name,
         user_seeks,
         user_scans,
         user_lookups,
         user_updates,
         last_user_seek,
         last_user_scan,
         last_user_lookup,l
         ast_user_update
from
         sys.dm_db_index_usage_stats i JOIN
         sys.tables t ON (t.object_id = i.object_id)
where
         database_id = db_id()

Which will return following output

image

From above result we can determine various tracking for record such as

we can see fields will track last update time stamp

image

Hope this helps !!

Thanks for reading my blog !!

For more interesting links and daily update please subscribe our link resource website

Todays link are follows:

Link Resource # 23: Sept 04–Sept 07« Dactylonomy of Web Resource

Advertisement
Categories: Query, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: