Skip to main content

Database last used

For a DBA its a routine issue to keep watch on the tables and database usage. Hence, it should be possible for any DBA to track the changes made to either a database or a table and be able to take necessary action according to the situation. Consider, if there is a database which is never used in a server but still there in the server. The metadata of the idle database will be stored in the system databases as this is connected to the server and if the database is very big with many objects, its obvious that a huge data should be stored in the system databases related to that database. Hence to counter the problem we can check the server databases which are critical in respect to its usage and if we found some databases never being used, we can take them offline. We can check the database, when it was last used or updated by running a query as shown below.

select distinct DB_NAME(database_id), object_name(object_id),  last_user_seek,last_user_scan, last_user_lookup, last_user_update
from
sys.dm_db_index_usage_stats

Note: This is valid only till the SQL Server session is open. If the server is once restarted, the table sys.dm_db_index_usage_stats looks empty.

To counter the above drawback, I am still working and update the same in the near future. I also wanted to know the number of times a database of a server is being used for a specified period, say a month. If someone have any suggestion please be kind enough to put on a comment.
Labels:

Comments

Post a Comment

Popular posts from this blog

Digging into a long running job

Revisiting the blogger to finally start again blogging about the SQL Server after a long long time.  This post is on analyzing and digging into what is causing a job significant time to complete. This will be a basic level of approach and I wish, I could have done better. But, its was enough in my case to get to a conclusion, with the knowledge I equip.   Issue : A job that's scheduled to run every three hours, was running for more than 48hours. Average job run time, 1.5hrs.  Analysis : First and foremost thing to do, is to find out on what session id is the job running on the instance. You may use the below query to fetch the session id of the job from the job name. declare @Var1 varchar(36) select @Var1=substring(REPLACE(CAST(job_id AS VARCHAR(36)),'-',''),17,16) from sysjobs where name =' JOB_NAME ' select * from sys.sysprocesses where program_name like '%'+@Var1+'%' You may get a single row or multiple rows with ...
Post a Comment
Read more

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Key not valid for use in specified state - SSIS

You might see the following error while a package was newly imported and is set to be called from an SQL Agent through a job. The job would fail reporting the below error. Source:        Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error This is mainly because, the package protection level, which is by default set to  "EncryptWithSensitiveKey" Resolution: Set the package protection level to "DontSaveSensitive". But, if in case you know the password for the key, you could use it. How to change the SSIS Package Protection: In Business Intelligence Development Studio, open the Integration Services project that contains the package. Open the package in the SSIS designer. If the Properties window d...
Post a Comment
Read more