Help Backups - RETAINDAYS / Expiry Period
Dec 20, 2005
RETAINDAYS option in BACKUP command.
SqlServer2000 (standard) on Win xp server.
Old backups are not being removed from backup sets.
I've created logical dumpdevices which maps to a physical file on disk - using sp_adddumpdevice. Regular, scheduled backups are successfully written to the backup sets (Complete, differential & Tlog) - This works fine and I can restore from them OK.
I've set the RETAINDAYS option of BACKUP command to keep backups for varying periods (eg full = 8 days, diff = 2 days, and Tlog = 1 day).
When the backups are viewed by 'drilling down' thru >Backup > open backupdevice > view contents.... all previous backups are still available even though their expiry date has passed.
The backup set files (dumpdevice files) are getting inexorably bigger and will eventually fill the available disks !
I don't want to delete or overwrite the entire contents of the backup set - just those backups more than 8 days old.
I've run sp_configure 'media_retention' 1 RECONFIGURE WITH OVERRIDE
but the old backups are still there.
dbs are getting ever bigger (high activity OLTP system) currently 2 production dbs totaling 50 GB.
Is this a bug? - ie. the option to set a retention period even though I'm writing to a logical 'dump device' rather than a normal *.BAK / *.TRN file?
or Am I doing something wrong?
HELP.
Cris Yarker
email to crispin@paymentsystemsltd.com
View 3 Replies
ADVERTISEMENT
Dec 7, 2001
I need to have RETAINDAYS=6hours on a backup command. How can I code that,seems like it should be in integer only,any work-around to have the retain days only few hours(less than a day)?
View 1 Replies
View Related
Aug 19, 2006
According to SQL Server 2005 Books Online, the RetainDays property specifies the number of days that must elapse before a backup set can be overwritten.
I do not quite understand the implication of this property. Say, if I set RetainDays = 1, is it mean the backup set from yesterday MAY BE overwritten? If it is 'may be', how does SQL Server determines whether to overwrite or not? I tried RetainDays = 1 to backup a dummy database, but my test.bak keeps growing, the old files do not seem to be overwritten. Besides, what is the implication of accepting the default RetainDays = 0?
View 4 Replies
View Related
Jan 3, 2007
Hi all,
I'm using an olap cube in a report (dash board).
The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that.
Once the report is opened the users want to select another period. Then I want to put period as a parameter.
But that is not possible since the parameter check box is no longer available when using a MDX filter expression.
Is there any solution to this problem?
Any help will be appreciated.
View 34 Replies
View Related
Jun 21, 2007
Hi All,I have a job coming up which involves me creating a vacancy system for our website.I (think) I'll only need one table:ID (PK)JobTitleJobLocationSalaryHoursJobDescriptionValidFrom (Date/Time)ValidTo (Date/Time)I'll display this data using a C#.Net repeater but I was wondering how I can automatically strip the page of the jobs that have gone past their expiry date? Is is straightforward to achieve?Thanks in advance,Brett
View 2 Replies
View Related
Nov 18, 2005
SQL Server 2005 allows to use the EncryptByCert function to encrypt a Column, for example in an Instead-of Insert Trigger, and to use a DecryptByCert function to decrypt a Column, for example in a View.
This is indeed good news, but I have a simple :confused: question. To create a Certificate using the CREATE CERTIFICATE you have to give an EXPIRY DATE, or it will default to an expriry date of 1 year. How can one read the encrypted Column after the certificate has expired ?
Is there a way to renew a certificate without having to first decrypt all the fields and export them ?
View 3 Replies
View Related
Sep 18, 2015
I have a Column CCExp Varchar(7) that stores data like below
NULL
'01/15'
'02/15'
'03/15'
'04/15'
'05/15'
[Code] ...
I am trying to get all expired cards which means, I am trying to get all cards which are behind sept 2015..like when I query I would like to get 8/15 and so on expired cards I am able to get the year , but with the below query I am getting all months less then 9 but i am missing 10,11,12 months cards values also I am having Conversion failed when converting the varchar value '#N' to data type int.
SELECT CustomerBillingInfoID ,
CCExp
FROM CustomerBillingInfo
WHERE LEFT( CCExp, 2) < MONTH(GETDATE())
AND RIGHT( CCExp, 2) <= RIGHT(YEAR(GETDATE()), 2)
and CCExp NOT LIKE '#%'
and CCExp not like 'NULL'
View 8 Replies
View Related
Feb 24, 2004
Hi all,
The company that I work for has an internet application that has been running in our client's environment for 2 years.
The SQL box is a quad Xeon 750 with 4 gig of ram and over 50gig of disk. This box has 4 databases that are used for the application. The primary database is about 1.5gig in size (.mdf file) the others are relatively small.
The box is queried from a separate windows 2000 server box with our COM based application running under IIS.
The boxes are located on the same switched LAN.
Up until recently the application has been running fine, but all of a sudden we have had masses of database time outs and the processor utilisation is always 80-100. In turn these time outs crash IIS eventually.
I Understand all of the ideas about increasing the timeout in a command object, etc.
I have monitored blocking on the SQL server and over a 30 minute period of live usage I saw 1 block and 0 deadlocks.
In addition to the above often our application gets an imediate time out error, hence after no time.
We have also monitored the network bandwidth between the two boxes and they only use about 30% on average of available bandwidth.
My 1st question is - Does SQL give a time out error (hence refuses connections) if it is running at 100% and cannot handle any more load??
My 2nd question is, In other peoples experience is the box we are using powerful enough.
My 3rd question is, Can the network have an effect on the time outs even though there is plenty of bandwidth.
Thank you in advance for your time in reading this and I appreciate any input that you may have.
Simon Gratton
Senior/Lead Developer
Fastrack Software Ltd.
View 2 Replies
View Related
Aug 15, 2006
Hi,
I created a master key and
CREATE CERTIFICATE mycer
WITH SUBJECT = 'data encrypt',START_DATE = '12/1/2005',EXPIRY_DATE='7/9/2006'
GO
CREATE SYMMETRIC KEY mySymetric WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE mycer,password='mypass';
GO
and i created a table with varbinary field and i added 2 encrypted records and i got it properly
i need to update the expiry data for my certificate , so i dropped the symetric key and their certificate and run the following
CREATE CERTIFICATE mycer
WITH SUBJECT = 'data encrypt',START_DATE = '12/1/2005',EXPIRY_DATE='12/12/2006'
GO
CREATE SYMMETRIC KEY mySymetric WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE mycer,password='mypass';
GO
i tried to get the added record , i got a null value somthing is happened while dropping the key for the encrypted data??
any help ?
Jim
View 5 Replies
View Related
Oct 16, 2004
Hi, i'm trying to calculate on the expiry date for product which include the start date and end date.
the product can't b displayed if the start date haven't reached the date, besides, it oso can't b displayed if it has expired.
the storedprocedure tat i've written is:
WHERE
DateDiff(day, StartDate, getDate())<=0
AND
DateDiff(day, EndDate, getDate())>=0
i wonder why it can's show the result!!! is the code correct?
View 3 Replies
View Related
Feb 2, 2008
Hi,
My table has more columns than this but I will just use the ones here tht are important.
I have two columns in a table these are "ExpiryDate" and "Archived".
When the row is created the "Archived" field will be null but the user will have entered a expiry date. When this date is reached I would like the "Archived" data to change to "1". Is this possible? Would I have to do it through some kind of script or can I set the database to do this automatically?
Thank you, Mark
View 3 Replies
View Related
Mar 26, 2007
Consider the following tableCustomercustId char(10)accountExpiryDate datetimeaccountStatus bitNow, I want to update the accountStatus to False as soon as thecurrent date becomes accountExpiryDate.I think it can be done using "SQL Agent" but my webhost doesnt provideme access to that. I have access only to the Query Analyzer.ThanksShane
View 12 Replies
View Related
Feb 13, 2006
Hi
We are using anonymous HTTPS merge replication.
The windows password used for the client subscriber expired on the publisher.
I reset it on the publisher - but SQL still seems to think that it is not expired - I get the following message in the event log of the publisher running SQL Server 2005
Login failed for user 'XYZ'. Reason: The password of the account has expired. [CLIENT: <local machine>]
Any ideas ?
thanks
Bruce
View 1 Replies
View Related
Nov 15, 2015
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies
View Related
Jun 4, 2014
Disable logins on access expiry date(Not windows password expiry). we grant access to users on databases only for 60 days. So access is only valid for 60 days. Then the user should again request access to the database going thru security clearance. Thn the DBA's enable the login. Maintaining all these logins of users manually is causing more confusion.
As we know, we dont have any inbuilt functionality to automatically disable logins in SQL Server.
I have a table where the logins and expirydate were recorded in a DB.
Using this table and SQL Server agent. Can i achieve this process automated ?
CREATE TABLE [dbo].[LoginsExpiry](
[LoginName] [varchar](50) NULL,
[ExpiryDate] [date] NULL,
[Roles] [varchar](500)
) ON [PRIMARY]
GO
View 3 Replies
View Related
Apr 18, 2006
Hi all;
I need to make daily backups to a computer in another domain, where I can't map a drive, so I use the TSQL to create a dump device, which works fine:
EXEC sp_addumpdevice 'disk', 'MyBackupDevice', [ipaddress & directory/file]
Then I schedule a server job which faithfully backs up my database, with one hitch - it never deletes days older than 5 as per the code - it just keeps growing. The command couldn't be much simpler:
BACKUP DATABASE ProductionETC to MyBackupDevice WITH RETAINDAYS = 5
For now, I just create a new backup device every now and then and go back and forth, deleting the oldest one when I create a new one. But it's annoying! Any ideas what I'm doing wrong?
Thanks,
L. Rap
View 4 Replies
View Related
Aug 29, 2005
Hi,Just wonder if i can get a period of dates to be inserted into a temp table (with a single field [Sales_Date]) base on a Start and End Date using a select query?For Eg,Start = '8/1/2005', End = '8/5/2005'In the temp table,8/1/20058/2/20058/3/20058/4/20058/5/2005 Your help is appreciated. Thks.RgdsRyan
View 3 Replies
View Related
Dec 29, 2003
I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)
I need my resultant field data to look like the following:
"Doe, John P."
I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".
I have tried the following CASE statement:
CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END
However, I get an error indicating that the CASE statement is not supported in the Query Designer.
How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?
View 4 Replies
View Related
Dec 15, 2004
Hi,
I want to remove old databases from server.
Any way not running a trace to find if db was not in use for 6 months ?
Thank you
Alex
View 1 Replies
View Related
Apr 25, 2006
I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
between 400 & 500 # 20
> 501 # 10
NOV between 300 & 400 #30
between 400 & 500 # 20
> 501 # 10
and so on for a 6 month period.
View 1 Replies
View Related
May 12, 2006
I'm using SQL Server 2000.
Example table:
PeopleID Date Status
1 2004-01-01 True
1 2005-01-01 True
1 2006-01-01 True
2 2004-01-01 True
2 2005-01-01 False
2 2006-01-01 True
I'm trying to find a way to query whether or not someone has had a specific status for 3 years in a row. As you can see from the table above, PeopleID 1 has had a "Status" of "True" for 3 years in a row, whereas PeopleID 2 hasn't--there was one year where they had "False".
I'm wondering I can query this, or if I'm going to have to scan the records manually. :(
I suppose I could write a stored procedure and do some looping too.
Appreciate any help, thanks!
View 2 Replies
View Related
Aug 7, 2013
I have a table as seen below and need to calculate the downtime in a given period.
dateandtime Equipment No. Status
1/2/2013 ! 4 ! 0
1/5/2013 ! 3 ! 1
1/8/2013 ! 8 ! 0
1/3/2013 ! 5 ! 1
1/2/2013 ! 1 ! 0
1/4/2013 ! 4 ! 1
The ! is my attempt to make a line between columns...
View 13 Replies
View Related
May 27, 2007
Guys,
I am running SQL Server 2005 Enterp Trial version, which is about to expire in 14 days..
Is this possible to Extend this?
Or
Un-installing and re-installing may give me back 180 days again? I haven't tried this, but need to know before I do an attempt?
Don't have other machine, don't want to re-built my system..
Any help?
Thanks in advance
SKR
View 6 Replies
View Related
Aug 29, 2007
Alright here's my situation.
I have two servers both running Eval copies of SQL Server 2005 Enterprise. This is only because I had the promise that I would be getting a shiney new server with a fully licensed copy of the server soon so I wasn't worried when I setup these two programs to use these. Now they are dragging their feet to approve the server and software so I'm scared.
First question - What will happen when the eval period runs out?
Second question - Can I tell what the date was when I installed the server?
These both will help me push management to approve atleast the purchase of an SQL license if I can't get the server.
Thanks.
Rob
View 2 Replies
View Related
Jan 27, 2008
How do you build a query to calculate the time of employement or any space of time for each person in a table? I'm thinking I would want to use a "Current Date" and "Select Datediff" calculations of some sort and reference the hiredate against the current date but I have been unable to find much on this type of query.. Please help..
Thanks..
View 12 Replies
View Related
Jul 20, 2005
HiI am hoping someone might be able to help me out with this.I am writing a helpdesk system which records agents logging in and outof the system.I need to write a stored procedure which will show the peak number ofagents logged in concurrently during a specified time period. Withinthe time period, the person viewing the report should be able tospecify intervals at which to see the data.Eg. There is already a table in the system which holds loggedin/logged out data like22/11/2004 14:02 - 22/11/2004 17:3022/11/2004 09:00 - 22/11/2004 17:1222/11/2004 10:25 - 22/11/2004 16:3022/11/2004 11:02 - 22/11/2004 12:3022/11/2004 16:00 - 22/11/2004 17:30The report user can then say for example they want to view databetween 10th November and 12th November broken down into 15 minutesintervals which would produce a table like this:10/11/2004 00:00 - 10/11/2004 00:1510/11/2004 00:15 - 10/11/2004 00:3010/11/2004 00:30 - 10/11/2004 00:4510/11/2004 00:45 - 10/11/2004 01:00 etc etcAgainst each of these time slots, I need to work out the peak numberof concurrent agents logged in using the first table.Can anyone make any suggestions? The time period the report user canchoose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.Thanks in advance
View 3 Replies
View Related
May 21, 2008
Hello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.
Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)
Any hint (or web like example) would be very appreciated.
Best regards
Alessandro
View 1 Replies
View Related
Nov 6, 2007
Hi,
I have a table of the following structure:
TaskID ResourceName StartDateTime FinishDateTime
12345 Joe 01 Jun 2007 12:30PM 01 Jun 2007 13:30PM
99999 Joe 01 Jun 2007 16:30PM 01 Jun 2007 19:30PM
If I have a new task id that lasts for 1 hour how can I look for the first available space that my "1 hour task " will fit into
i.e i want it to return 01 Jun 2007 13:31PM as my "new task" will fit nicely between TASKID 12345 and Task ID 99999
Hope that makes sense
Thanks
View 3 Replies
View Related
Aug 31, 2006
Hi, I am runnig a system with SQL Server 2000, where I get the message (17502): SQL Server evaluation period has expired.
I have valid key for this product, can anybody tell me how to updgrade the SQL Server lisence without a new installation of SQL Server?
Cheers
Kim
View 1 Replies
View Related
Nov 16, 2006
Using the TimeSeries algorithm, how do I forecast more than one time period ahead? I read in you book on page 182 that PredictTimeSeries function can take a parameter for the number of time periods you want to predict. Fore example, SELECT PredictTimeSeries(Bread,5) tells the algorithm to predict the next 5 time periods. Can you tell me how to change that parameter using the graphic interfaces?
Thanks!
View 1 Replies
View Related
Aug 5, 2013
I have three tables Accounts, History and Dates . What I need to do is display all the accounts from History (900) records and compare them to the accounts in Accounts table pull all the matching records based on a certain date range , but If there is no record in the History table for this period I still need to display the account from Accounts and some text saying that there is no record matching for this period.
Account History
11
22
33
4NO information for this month
55
SELECT C.ACCOUNT, CASE WHEN C.ACCOUNT = LEFT(H.NUMBER,8)
THEN LEFT(H.NUMBER,8) END FROM ACCTS C
LEFT OUTER JOIN HISTORY H ON C.ACCOUNT = LEFT(H.NUMBER,8)
INNER JOIN DATES D ON h.PERIOD = D.CUR_PERIOD
GROUP BY C.ACCOUNT, H.NUMBER
This will give me all the matching records for the period but I need somehow to show all the accounts even if they don't have records for this period.
View 5 Replies
View Related
Nov 28, 2013
Aim – Calculate the number of days between CreatedDate and [Date_Docs_In_Complete__c],count how many Ids, Fall within a specific month by year, and then work out the avg number of days it took for each month
My query so far
select
ID,
left(CreatedDate,10) as CreatedDate,
left([Date_Docs_In_Complete__c],10) as [Date_Docs_In_Complete__c],
DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days
from #build
Produces following results
IDCreatedDateDate_Docs_In_Complete__cDays
0063000000ausKGAAY2010-03-262013-07-161208
0063000000mC359AAC2011-06-302013-07-03734
0063000000oyaSPAAY2011-11-292013-07-18597
Desired outcome results would be
Year Month Date_Docs_In_Complete__c Total Days Avg_Days
2013 07 3 2539846.3
View 2 Replies
View Related
Aug 16, 2005
Hi- I let my sql server evaluation expire and I have no access to the database now. Is there anyway I can create a new server on my old sql server 7 software and import the data? I have stored procedures that I really need to access. I see where the data files are but is there a way to get them opened by SQL Server 7 when I created it all in the latest version?
Thanks
Nicki
View 9 Replies
View Related
