sp_SQLLitespeedmaint

<b> <font color="red">** Updated 28th May 2004 **</font id="red"> </b><br /><br />This procedure has moved because its much too hard to maintain as a forum post. The latest version can be found at <a href='http://www.sqldbatips.com/showcode.asp?ID=1' target='_blank' title='http://www.sqldbatips.com/showcode.asp?ID=1'<a target="_blank" href=http://www.sqldbatips.com/showcode.asp?ID=1>http://www.sqldbatips.com/showcode.asp?ID=1</a></a><br /><br />I'd highly recommend anyone using the code to update it to the latest version as it includes increased error checking when running in command line mode as a non zero return code is not always returned when an error occurs plus the ability to specify a number of backups to keep rather than the amount of time to keep them.<br /><br />Thanks to Brad for hosting this for so long and I hope it has been (and continues to be) useful [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Cheers,<br />Jasper Smith

Thanks for this script. Have you shared it with the people at SQLLitespeed yet?

-----------------------------
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Yeah plus the script we have to generate standard maintenance jobs for a database (or all databases) that I should be able to post soon (I didn't originally write that one I just updated it for SQL Litespeed). We have been testing it pretty heavily and it seems to hold up well and the maintenance reports are pretty close to what you get from xp_sqlmaint which was one of the goals. I hadn't realised how bloody long it was till I posted it [<img src='/community/emoticons/emotion-2.gif' alt='' />] Pop in and say hi in the MVP newsgroup when you get a chance (congratulations BTW !)<br /><br />Cheers,<br />Jasper Smith

Have you ever integrated the sp_SQLLitespeedmaint into a Log Shipping senario?

Thanks,
Patrick

I think that there is a script on the SQL Litespeed website that covers a form of log shipping. I have not tried it myself.

-----------------------------
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

We tend to not use log shipping so no, I've not done any specific work with it in that area. However it would not be very hard to adapt. As Brad says, they have their own script plus you can look at how MS do it in SQL2000 and put together a soloution without to much effort.


HTH

Jasper Smith

Jasper,

have you ever had this kind of error with your SP?:-

Msg 241, Sev 16: Syntax error converting datetime from character string. [SQLSTATE 22007]
Msg 0, Sev 16: DELETE #files WHERE DATEADD(dd,7,CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),7,2) +'/' + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),5,2) +'/' + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),1,4) +' ' + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),9,2) +':' + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),11,2)),103)) > 'Sep 3 2003 1:54PM' [SQLSTATE 01000]

I am running the SP with @debug=1. My trouble trying to track this thing down is that are many similar sections with this kind of delete from #files.

Any help would be appreciated.



Tom Pullen
DBA, Oxfam GB

What's the login language set to that this runs under ? Also what is the server default language and the OS locale ? I can repro the error on my home PC (all our servers are set up the same so we don't see this issue - well I've seen no faults for it anyway) so I'll post a fixed version that's not reliant on the language (and it's effect on string->datetime conversion).


HTH

Jasper Smith

If you substitiute the following section into the code (2 places) does it work for you? I don't want to edit the original post until I get a chance to more fully test the change which probably won't be until Monday. I have no idea why I chose the original way - very insular of me [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br /><pre><br />SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@dbretainval as nvarchar(10)) + N',' +<br /> 'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) <br /> + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) <br /> + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +'' ''<br /> + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''<br /> + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),112)) &gt; ''' <br /> + CAST(@jobdt as nvarchar(25)) + N''''<br /></pre><br /><br />HTH<br /><br />Jasper Smith

Jasper, thanks very much for looking at this - I really appreciate it. I put in the fix you posted but now I get

Msg 242, Sev 16: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

on the same statement.

The default language is English. OS Locale is English [United Kingdom]. Server is Windows 2000 SP3, SQL Server 2000 SP2.

Tom Pullen
DBA, Oxfam GB

I saw that error too in testing various conversions. I was trying with both uk and us english settings and the fixed code was working fine for me although I can't for the life of me figure out why it was failing for some conversions I tried in testing. Let me have a bit more of a look at it. That code above turns the filename into a yyyymmdd hh<img src='/community/emoticons/emotion-7.gif' alt=':s' />s format which does seem to work for me for both languages but I think I'll change it to one with a non numeric month as that should (hopefully !) get around this issue.<br /><br />HTH<br /><br />Jasper Smith

Tom,<br />I can't get the procedure to fail using the change above. Can you post the command you're running and the list of filenames (e.g. from running dir /b &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />ath&gt; ) so i can try and replicate. Just to confirm that this is running as a job, SQL Agent account is set to english language and so is server default.<br /><br />Cheers,<br />Jasper Smith

Jasper, here is what you asked for:-

The command I'm running is

DELETE #files WHERE DATEADD(dd,24,CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),1,4) + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),5,2) + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),7,2) +' ' + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),9,2) +':' + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX('_',REVERSE(filename)))+2),12),11,2)),112)) > 'Sep 5 2003 12:06PM'

The default Language is English, for server and SQL Server Agent.

Example list of filenames

pubs_FullDBBackupSLS_200309091130.BAK

Let me know if there's anything else you need, and thanks for your ongoing work on this.

Tom

Tom Pullen
DBA, Oxfam GB

Apologies, for the delay, been rather busy. I have discovered that a couple of our servers actually are set to english and have been running this with no issues at all for some time so I'm more confused than ever now ! When I get a spare 5 minutes I will rewrite the bit with the dates to try and remove this stange error. I take it you still get this error ? Have you tried it on any other servers ?


HTH

Jasper Smith

Jasper, don't apologise - any time you can get to look at this is a bonus for me.

Yes, this is reproduceable on all 4 servers on which I have installed SQL Lite Speed.

Currently I am running backups by overwriting a single file every night, so no need for xp_sqlmaint-style funcionality, but I would like to get your SP working. Maybe I'll have a go at it myself, if you don't mind.

Anyway thanks for your help - you take it easy.

Tom


Tom Pullen
DBA, Oxfam GB