I just got assigned to be our companies sql server dba, there are about 80 databases on various servers. I am suspecting many are not even used. Is there a way for me to tell the last time they were accessed and by whom? And, more importantly, the last time any changes have been made in the database? Thanks in advance.
View 7 RepliesCan i find out when a table was last accessed?
The purpose of this is to create a list of tables that could potentially be deleted. I would then prefix them with a d and the auto delete them after x number of days? or move them to a deletions database?
Just trying to automate a bit of house keeping in the active databases.
I want to know howmany tables were accessed in a database from time A to B. How do I get the list of tables accessed (excluding the 'SELECT's)?
View 3 Replies View RelatedWhat are my options to find heavily accessed DBs on a server? I know I can do this by profiler and some counters. Is there any tool which gives me this information easily?
------------------------
I think, therefore I am - Rene Descartes
Is there a simple way to tell when a database was last accessed or changed?
We have some databases on old servers which we would like to discontinue but do not know if they are being used.
We would rather not just take them offline.
Is there anyway I can find out if they are still being used?
Please help if you can.
Regards, Major (that is my Christian name ;-)
I have a bunch of datbases I have created during the development process. I want to delete those no longer being used. How do I find when the last time a database was accessed?
Thanks.
Hi,
I have trouble displaying images that are accessed through URLs in my report.
If I call the URL directly in the Browser the Image can be opened without problems.
The images can be accessed with an anonymous access.
When I deploy the report or in the Preview of Visual Studio I get the following message:
[rsWarningFetchingExternalImages] Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access.
In Layout View of Visual Studio the image is showen correctly.
The Images are stored on a Solaris maschine with WebSphere 6.0 as WebServer.
Any ideas?
Thank you.
Frank
Can any one recommend an approach for identifying the frequently accessed tables and files in a SQL/Server application? Particularly, if the database is stored on raw partitions instead of FAT or NTFS. Thanks.
View 3 Replies View RelatedI am trying to manage a sql server.
In this sql server, I have about 200 databases.
Is there any way/suggestions to check to see if some of the database has not been accessed/updated for the last month or longer?
I am trying to develop a system to remove the database that's no longer needed.
Thank you.
Is anyone aware of a method of determining when a table was last modified or accessed?
Some of our databases have tables that I am sure are not being used and I would like to generate a list of tables that have not been accessed or modified for some period of time.
I looked for a system procedure but didn't see anything that satisfied my need.
Currently I rename suspect tables and wait for someone or some process to gripe, but I don't care for that method for obvious reasons.
Thanks!
I was having issue with one of my databases in SQL EXPRESS. It is offline this morning …it said “Database 'MyDB' cannot be opened due to inaccessible files or insufficient memory or disk space. When I checked the error log ..it only said “FCB::Open failed: Could not open file D:DatabasesMyDB_Data.mdf for file number 1. OS error: 32(failed to retrieve text for this error. Reason: 15100).
I did alter database offline and and online ..it works for me( I can access the database again but I need to find the cause of this issue )I checked the max memory setting is still by default .. not limited for SQL ..it could be the cause ?
We have an application that accesses a particular table, but it isnever updated. Without stepping through the complex application code,is there a method of logging when a particular table is accessed?
View 3 Replies View RelatedI'd like to find out who is accessing various databases and log the time they did so.
Is there a way to do this? I just need the account name and the time logged and then to write to a table so I can query it.
how to check the last accessed date of the list of all databases in a particular server in MS SQL SERVER.
View 12 Replies View RelatedHi, I have a package that I am running concurrently with different values. and sometime I'm getting following message for one of the running instances and it fails while the other ones are running.
Execution failed with the following error: "ERROR : errorCode=0 description=Failed to save package file "C:Documents and SettingssmillerLocal SettingsApplication DataMicrosoftSQL ServerSmoInnerPackage.dtsx" with error 0x80070020 "The process cannot access the file because it is being used by another process.". helpFile=TableTransferGenerator Task helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Any ideas what is the reason I have search everywhere for keyword InnerPackage.dtsx as this is not my package name and it is getting created by SSIS enginee
Thanks
Hi,
I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.
Also, are there any things that I should take care with my database from outside users.
Thanks
-Sudhakar
Hi,
I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.
Also, are there any things that I should take care with my database from outside users.
Thanks
-Sudhakar
Hi,I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents. Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.Also, are there any things that I should take care with my database from outside users.Thanks-Sudhakar
View 1 Replies View Relatedcode that I can use to determine if a certain table has been accessed in the past?
View 6 Replies View RelatedI wonder if anyone has any hard fact based pro or contra, especially onperformance, about having views as opposed to tables when the object isbeing accessed for read only by multiple sessions/users/spids. I amparticularly concerned about the multiple instantiations of the view.Relevant thoughts on this are much appreciated.Thanks,Caveman
View 1 Replies View RelatedHi,
I have succesfully created a Stored Procedure which runs under 2 seconds locally.
However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.
My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.
Any Ideas why this would be happening?
While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?
Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.
Our application team make design changes of the database in their development server and asks me to restore the bak file from the development server into the production server with replace command. The database in the production server is connected to the application server and so when I try to restore the database error message comes that the database is in use.
So first I find out the sessions with the logins who is the user in the database with sys.dm_exec_sessions joined with sys.dm_exec_requests and then kill the sessions. And after that it is possible to restore. But many times when I kill the session and try to restore in between new sessions comes into effect which are generated from the web server and many times I have to resubmit the query in the dmv's and find the sessions and kill it again and again before restore can be done. Is there any correct method to restore an active database which is being accessed by the application from a web server with out stopping the web server?
Able to access SQL express remotely using SQL mgt Studio after certain configuration.
But notice that I must use IP address/SQLEXPRESS for server name in order for connection to work. What is this "/SQLEXPRESS" ? Is it a standard surfix for SQL Express ?
I have installed the Oracle Transparent Gateway on the machine with SQL Express and setup the tnsname for SQL Express. For the tnsname, have used IP address/SQLEXPRESS as the host name but it gave this error :
ORA-28545: NCRO Failed to make RSLV connection
Tried with just the IP address and it gives the same error.
Any idea ?? ?? Help !
Hi,
I have a clustered environment which has many instances in it. I have modified the xml file to allow several instances to use ssis. Everything works great when the instances are on the same node as the clustered ssis. However if they are on different nodes there seems to be an aunthentication problem and it loses my credentials. For some reason it tries to use Anonymous Logon instead. Here is the full error:
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (MsDtsSrvr)
I would appreciate anyones help on this problem.
Thanks,
Adam
Is there anyway I can find or make a report if a databases has been accessed/used since last month?
View 4 Replies View RelatedI have a plan of decommissioning of unused databases which are not been in use for quite some time. is there any simple way/t-sql to find out When was the database last accessed by each user/login in sqlserver.
View 2 Replies View RelatedHi
I am having a problem with a particular stored procedure in a database application and I have ran out of ideas as to what is the cause. When calling this stored procedure from a .Net application it typically returns results in about 0.2 seconds. 24 hours after it's creation, the procedure takes over 40 seconds to return the same results to the application. However if I call the procedure via Management Studio or Query Analyzer, the performance remains consistently fast.
It's a fairly complicated query making use of the following features:
FOR XML EXPLICIT
The ROW_NUMBER function
Input Parameters
The procedure is replicated, along with the tables that it references
The calling application is using ExecuteXMLReader to retrieve the results.
To fix the problem, I can simply run an ALTER PROCEDURE statement (without changing any of the contents of the stored procedure). However, by the next morning, the problem will have reoccurred.
Can anyone shed any light on why this is happening?
Phil
Hello,
We have allowed one of our clients to create views in our .net /sql application so they can build reports using MS Access. They have been doing so successfully for years. However lately they are experiencing Timeout Expired messages when in the actual web application. I have confirmed that several of the views when opened in Access do cause blocking, the locks are promptly realeased when the the access odbc of the sql view is closed.
When the views are opened in SQL query analyzer, there is no sql blocking.
Would this be related to how the tables are linked?
Any suggestions would be greatly appreciated.
Hi,
I want to manage memory relational data in the memory(Insert, delete, return) and manipulate the data via SQL 2005 and CLR database objects.
The gool is to manage a table that relatively has a lot of inserts and deletes which takes a lot of IO operations and i want to reduce it. the data itself is not permanant each row deleted from the table after 15-20 minutes.
I looked at the extended stored procedure and other CLR enable objects and it seams that they all return data from the database itself.
How can i return data that is reside in the memory?
Thanks.
I'm trying to find out what tables are being used in a Database.
I don't want the last User but the User and the Dates.
I have a script that return the last user but that is not going to work.
The following script returns the last user but not all users and the Login Name:
ITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
[Code] .....
i have a report that runs on a huge table rpt.AgentMeasures , it has 10 months worth of data (150 million records as of today and will keep increasing). i have pasted my proc below , the other tables that are joined to this huge table do not have more then 3k records.This report will be accessed by multiple users (expecting 20 ppl). as of now this reports runs for 5 mins if i pull for 1 month worth of data. if it is wise to use temp tables.
ALTER proc [rpt].[Get_Metrics]
@MinDate DATETIME,
@MaxDate DATETIME,
@Medium Varchar(max),
@footPrint varchar(max),
[code]...
The goal is to address visual source safe database on the network. We have the srcsafe.ini in the network as \ipaddrsrcsafe.ini. Now I create a new VSSDatabase object and call its OpenDb. Well for simple consle app or winform it is ok. But I was running it under Sql server Stored Procedure. It failed for I cannot access the source safe path throgh the COM object.
I know it is because of Windows identity. So I add the following code before I want to open the database, changing the to the WindowsIdentity:
WindowsIdentity impersonId = SqlContext.WindowsIdentity;
WindowsImpersonationContext orgCtx = null;
try
{
orgCtx = impersonId.Impersonate();
VSS_Database = new MVSI.VSSDatabase();
// VSS_Database.ImpersonateCaller = true;
VSS_Database.Open(Path, UserName, PassWord);
}
catch (Exception err)
{
orgCtx.Undo();
throw err;
}
finally
{
orgCtx.Undo();
}
Without the commented line "// VSS_Database.ImpersonateCaller = true", this does not work at all. It just behave like no changes to the windows identity.
However if I add this code, well, OpenDb will result in a No-response query. The Sql server is running the query with no responses.
Have you ever met that before? I am really frustrated. Thanks