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

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 > 

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



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

         sys.dm_db_index_usage_stats i JOIN
         sys.tables t ON (t.object_id = i.object_id)
         database_id = db_id()

Which will return following output


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

we can see fields will track last update time stamp


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

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:

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: