SQL Server 2008 :: How To Find If A Database Has Been Accessed / Used Since Last Month

Jul 6, 2015

Is there anyway I can find or make a report if a databases has been accessed/used since last month?

View 4 Replies


ADVERTISEMENT

SQL Server 2008 :: Database Can't Be Accessed

Apr 9, 2015

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 ?

View 9 Replies View Related

How Do I Find When My Database Was Last Accessed

Dec 11, 2007



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.

View 3 Replies View Related

SQL Server 2008 :: How To Find Who Is Connected To A Database From DMV

Sep 16, 2015

I have a query that finds all SPID's connected to a particular database:

select d.name, p.*
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'my_db'

But now we have a new rule that we should not use outdated compatibility views, and one of them is sys.sysprocesses. I checked sys.dm_exec_connections/session/requests but failed to replace my existing code. The first two don't have dbid, the last one, requests, has it, but it selects only currently executing statements.

View 0 Replies View Related

SQL Server 2008 :: How To Find Who Is Indirectly Connected To A Database

Feb 25, 2015

Recently I needed to find all processes connected to a particular database, let's call it Test_db. I have a simple query to find all connections to my database:

select *
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'test_db'

But there was a process that was connected to another database like USE another_db_name; but was actually selecting from tables in test_db. Is it possible to catch such connections?

View 7 Replies View Related

SQL Server 2008 :: Use Cursor To Run Dynamic To Find Special Characters Used In Database

Apr 16, 2015

I need to find all uses of special characters in a database. I used the following code to do this:

USE dbName
GO
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
GO

[code]...

This will check all tables in the database, but if you want to check specific tables you can uncomment the line in the where clause and specify tables to be checked. The query will return any text fields that have any characters other than letters, numbers or spaces.

This code works fine for me because all the tables in my database have single column primary keys. However I know how much Jeff Moden hates cursors or RBAR queries, so my question is could this have been done by any method other than using a cursor?

View 9 Replies View Related

SQL Server 2008 :: Find All Transaction (insert / Delete / Update) On A Database For A Day?

May 8, 2015

i would like to know it's possible to find all transaction(insert, delete,update) on a database for a day. if yes what can i do.

View 2 Replies View Related

SQL Tools :: When Was Database Last Accessed By Each User / Login In Server

Jul 6, 2015

I 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 Related

SQL Server 2014 :: How To Find 5th Working Day In A Month

Oct 29, 2013

I would like to know how to find fifth workingday* of a given month and year.

workingday - working day is just any day apart from saturday and sunday. no need to consider any other holidays.

ex: I would just give month number and year as input and i would like to know date and day of the fifth working day.

Input : 10/2013
Output : 7th October 2013, Monday.

View 4 Replies View Related

SQL Server 2008 :: Query Grouping By Month

Mar 5, 2015

I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these

MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010

SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],

[Code] ....

View 2 Replies View Related

Find Difference In Month And Year In Sql Server 2005

Jun 17, 2008

--find day,month,year
--for day select datediff(d,'01 may 2008',getdate())
 -- --for month select datediff(m,'01 jun 2006',getdate())
-- --for year select datediff(year,'01 jun 2006',getdate())
above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively.
but I want completely so I can use this in case of expired user in my project.
User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize.
 thanks

View 3 Replies View Related

SQL Server 2008 :: How To Retrieve Only Monday Dates From Each Month

Jan 23, 2015

How to retrieve only Monday dates from each month in sql server.

If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.

I want to calculate bi weekly range data in the sql.

View 9 Replies View Related

SQL Server 2008 :: How To Sum Values Based On Quarter And Month

Mar 20, 2015

We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....

We will be having date column values as 201401,201402,.....

How can we implement in sql sever performance should be good.

View 1 Replies View Related

SQL Server 2008 :: Month Name And Year (2 Digits) Format

May 12, 2015

How to convert a date to the format as Month name and year(2 digits).

For e.g.- Jan 14, Feb 14......Mar 15

View 7 Replies View Related

SQL Server 2008 :: Show Different Last 6 Month In Format DD-MM-YYYY?

May 15, 2015

How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?

01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014

I have this Select statement

select replace(convert(char(11),getdate(),113),' ','-')

But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement

View 4 Replies View Related

SQL Server 2008 :: Selecting Days In Month Between Two Dates

May 19, 2015

I need some with selecting the number of days, in a month, between a date range. For example, my data looks like:

FileNumb | startdate | enddate
1 04/25/2015 05/02/2015
2 05/01/2015 05/10/2015

The output I am looking for would be:

FileNumb | Year | Month | Days
1 2015 4 6
1 2015 5 2
2 2015 5 10

View 9 Replies View Related

SQL Server 2012 :: Query To Find The Difference In Values From Previous Month?

Dec 12, 2013

I have my sql tables and query as shown below :

CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);

[code]....

I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'

SELECT T.[Year],
T.[Month]

-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)

[code]....

What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.

Something like this :

+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----

View 3 Replies View Related

SQL Server 2012 :: Find Trend How Employee Makes Sales Every Month?

Jun 7, 2015

How do I find sales trend of an employee via comparing current month and previous month sales?

I got so far query upto following,

;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,

[Code]....

I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

View 3 Replies View Related

SQL Server 2008 :: How To Default Start / End Date Parameters To First / Last Day Of Current Month

Feb 12, 2015

I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month.In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

And this code to get the last day of current month:

SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),
DATEADD(MONTH, 1, GETDATE()))

But I don't know how to do this in SSRS 2008. How can I make my start / end parms to get these values.

View 5 Replies View Related

SQL Server 2008 :: Find Out Top Expensive Queries

Apr 22, 2015

How do I find out top expensive queries from SQL Server 2008 – Standard edition ?

View 9 Replies View Related

SQL Server 2008 :: Find Table Dependency

Jun 5, 2015

I want find all dependent objects related to a table. I am using -

SELECT DISTINCT so.name
INTO #tmp
FROM dbo.sysobjects so
JOIN dbo.SysComments sc
ON sc.id = so.id
WHERE sc.text LIKE '%tablename%'But, I want all those SP/functions/views that use the output of this query and so on...

Eg.
Table -> used in usp1 -> usp1 used in usp2 ...and so on

View 3 Replies View Related

SQL Server 2008 :: Script To Find A Value In All Databases?

Aug 4, 2015

Script to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists.

My script is only for finding a value in one database.

View 8 Replies View Related

SQL Server 2008 :: Find Characters Before And After Underscore

Sep 22, 2015

I have data like ABC_Tablename_12345, i just want everything before and after _ also if there is no underscore in the name just return the name.

View 2 Replies View Related

SQL Server 2008 :: Find Unused Databases Or When Last Used In A Instance?

Mar 30, 2011

Find unused databases in a instance or when last used or accessed?

I'm on SQL SERVER 2008 R2 64bit -enterprize

I need to find when the databse is last accessed.

View 3 Replies View Related

SQL Server 2008 :: How To Find Order Of Lock Taken And Released

Feb 16, 2015

I have express edition [advance] of sqlserver 2008 r2 , is it possible to trace every event with out using profiler as u know it does not ship with it.

Basically i want to see how locks are taken and released in each isolation level when query is executed. I am using

SELECT resource_database_id, t.resource_type , t.resource_associated_entity_id,partitions.index_id,blocking_session_id,
indexes.name as index_name,o.name,
t.request_status, t.request_mode,t.request_session_id,
t.resource_description

[code]....

But i am not sure of the order becuase total_elapsed_time is same for many locks

like

resource_typeresource_associated_entity_idrequest_session_idrequest_modeindex_namenameresource_description
DATABASE051SNULLNULL
OBJECT209427886651Sch-SNULLwt
OBJECT209427886652IXNULLwt
PAGE7205759715088793652IXPK_wtNULL1:204815
KEY7205759715088793652XPK_wtNULL(4fb98fd9bfad)
DATABASE052SNULLNULL

View 5 Replies View Related

SQL Server 2008 :: Find Patients With Age 6 Months To 5 Years

Mar 3, 2015

formula to return patients ages 6 months to 5 years?

create table dbo.TEST
(
MRN varchar(10),

[Code]....

View 6 Replies View Related

SQL Server 2008 :: How To Find Which Backup File Belongs To Which LSN

Mar 18, 2015

I am getting following error:

"The log in this backup set terminates at LSN 9566000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 986000002731000001 can be restored."

If I am missing to restore previous log backup file, how to find which LSN mapped to which log backup file (.trn file name)? Another word looking at LSN can we tell which log backup file is missing?

View 2 Replies View Related

SQL Server 2008 :: Find Out Duplicate Order Sequence

Jun 30, 2015

In my asp.net project there are about 100 drop down list.I created a table to store data for drop down list in which including [DropdownID],[Order Sequence] and [Description] three columns. The sample like below. Data was input manually by a user. How to code to find out duplicate [OrderSequence]?

DropdownID--OrderSequence--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequence
2-------------2--------------WWW 'DUPLICATE OrderSequence
2-------------3--------------RRR

View 2 Replies View Related

SQL Server 2008 :: Find Records Comparing Two Lists

Jul 31, 2015

Below is the code for two data sets and I can't seem to get my head around the issue. I need to find the number of 'ER' visits and 'IN' visits, separately, in dbo.VisitData for the 'Active' patients in dbo.PatientStatus. So, consider patient 69. He is Active on 5/5/2014 but becomes Inactive on 9/15/2014. I only want to count the number of visits ER or IN that are between those dates. In addition if patient 69 becomes active again after 9/15/2014, I need to capture that data as well. Patients can change there status multiple times.

create table dbo.PatientStatus
as
(
patient_id varchar(10),
status_type varchar(10),
status_date datetime

[Code] ....

View 2 Replies View Related

SQL Server 2008 :: Query To Group And Find Latest

Aug 25, 2015

I have a scenario as below for one ID -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+

I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+

But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.

CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)

INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')

View 8 Replies View Related

SQL Server 2008 :: Using Cursors To Find First / Highest And Last Values

Sep 20, 2015

I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.

@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal

-- First Cursor

DECLARE TESTCURSOR CURSOR
DYNAMIC

[Code] ....

The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.

SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id

View 9 Replies View Related

SQL Server 2008 :: How To Find Duplicates In A Table As Per Particular Column

Nov 2, 2015

I am having a main table and temptable.

Every sunday, new data will be loaded from temptable to main table. I have to make sure that, duplicates does not get loaded from temptable to maintable.

For example, if last sunday a record gets loaded from temp to main. If this sunday also the same record is present then it means that is a duplicate.

The duplicate is decided on below scenario

select 'CodeChanges: ', count(*) from CodeChanges a, CodeChanges_Temp b
where a.AccountNumber = b.AccountNumber and
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and
a.HexaId = b.HexaId and

[Code] ...

Yesterday (Sunday) , data from temp got loaded onto maintable but with duplicates.

There is a log which just displays number of duplicates.

Yesterday the log displayed 8 duplicates found. I need to find out the 8 duplicates which got loaded yesterday and delete it off from main table.

There is a column in both tables which is 'creation date and time'. Every Sunday when the load happens this column will have that day's date .

Now i need to find out what are all the duplicates which got loaded on this sunday.

The total rows in temp table is : 363
No of duplicates present is : 8

I used below query to find out the duplicates but it is returning all the 363 rows from the maintable instead of the 8 duplicates.

Select 'CodeChanges: ', * from CodeChanges a where
exists
( Select 1 from CodeChanges_Temp b where
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and

[Code] ...

Need finding the duplicate records which has creation date time as '2015-11-01 00:00:00.000' and all the above columns mentioned in the query matches.

Example

Few colums only metioned below

creationdateandtime HexaNumber HexaCode
1. 1987-10-01 00:00:00.000 5 3
2. 2015-11-01 00:00:00.000 5 3

So here the second record is duplicate. This is what I am trying to find.

View 4 Replies View Related

Is There Anyway To Tell When A Database Was Last Accessed Or Changed?

May 29, 2007

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 ;-)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved