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
ADVERTISEMENT
Apr 14, 2004
How to find out which NT group(s) an user belongs to? any ideas?
View 4 Replies
View Related
Aug 30, 2006
Where can I find info about how to backup Sql Server 2005 using a .bat file?
Is it possible doing it this way?
If so, can anyone direct me to some links how how to do this?
If not possible using a bat file... then how can I make schedueled backups? What tool to use and hopefully free.
View 2 Replies
View Related
Jul 15, 2015
easy way to compress backup file. I use SQL Server 2008 R2
View 4 Replies
View Related
Mar 8, 2015
My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.
What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.
Is there any way to find how much the backup file will be generate before we run the backup job?
i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db
currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled
View 3 Replies
View Related
Aug 11, 2009
It's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...
The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (ServerBSQL_backupsdvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date2009/08/08 06:43:30 AM
LogJob History (Backup_AssetData_2009-08-08)
Step ID3
ServerSQL01
Job NameBackup_AssetData_2009-08-08
Step NameBackup Database - Second Attempt
Duration00:05:33
Sql Severity16
Sql Message ID3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: COMPANYSERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData(
iscsrv-dcm04RISCSRV-SQL01_Backupsdvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The file is generated, the same size as a successful backup, but the job terminates unsuccessful.I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.
I have a few databases that backup to the UNC described above (different files) that don't fail..My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.
My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.
##20090812## UPDATE
I tried another way to do the backup; with the same results:
Backup database Infovest to disk = '
iscsrv-dcm04
iscsrv-sql01_backupsdvc_Infovest_Z.bak' with INIT,STATS=1
[code]....
View 9 Replies
View Related
Dec 17, 2007
I am getting a backup file in default directory by using this script
Code Block
BACKUP DATABASE CPHONE
TO DISK = 'CPHONE.Bak' -- send to default pathaway
WITH FORMAT,
NAME = 'Full Backup of CPHONE'
Is there any way to find where is the default path of the SQL server, in order to show to the user from a VB program in which pathway the database back up is stored
View 4 Replies
View Related
Feb 18, 2015
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
View 3 Replies
View Related
Mar 13, 2015
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
View 2 Replies
View Related
Apr 22, 2015
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
May 29, 2013
When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
View 4 Replies
View Related
Feb 13, 2015
I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.
CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250
I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.
CustLoc
A35
B15
If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.
View 2 Replies
View Related
Mar 4, 2015
I see some time email say blocking took place and but it get resolved itself. how to find the history of batch/query which blocked another SPID?
View 3 Replies
View Related
Apr 22, 2015
I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).
How to find particular queries/stored procedures that causing this?
View 5 Replies
View Related
Apr 28, 2015
SELECT
P.Publication
,P.Publication_type
,S.Subscriber_ID
,S.Update_Mode
FROM MSPublications P
INNER JOIN MSSubscriptions S
ON P.Publication_ID = S.Publication_ID
give me publication_type=0. So it is transactional replication but how do we know that is pull or push?
View 2 Replies
View Related
Jul 23, 2015
Without going to services.msc / configuration manager, is there anyway to know the service account through which SQL server is running?
View 6 Replies
View Related
Nov 2, 2015
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
View 9 Replies
View Related
Nov 6, 2015
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
View 6 Replies
View Related
Mar 3, 2015
Is there anyway,can we find the list of servers by querying at active directory?
View 3 Replies
View Related