We do a 4:00AM weekly reboot of every SQL 7.0 server on NT4 scheduled as a SQL Agent job using the os command "shutdown servername /l /c /y /r". The problem is some of the SQL servers recognize the NT shutdown command and gracefully exit out of SQL first with the SQL log entry "SQL Server terminating because of system shutdown", then closes all databases files. Most of the other SQL server do not list any SQL log messages and sometimes list startup error messages indicating a dirty shutdown.
Does anyone else reboot their SQL servers with a scheduled job? If so, how do you perform this task cleanly?
Currently, I have a report that takes two parameters: StartDate and EndDate.
I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well with StartDate and EndDate because the parameter is static. What is the most elegant way to implement this change?
I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate field that i use in the subtraction for calculating the number of months.
Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate
SET NOCOUNT ON GO declare @num_of_times int declare @count int declare @frequency varchar(10) declare @num_of_times1 int
Hi,I am facing one serious problem. As soon as i started the sql server firsttime after installtion, my windows 2000 machine reboots. Once the windowsmachine comes up, sql server does not get started event if i manually try tostart it.My Machine configuration is as follows:1. Windows 2000(5.00.2195 SP3) with intel 2.4 Ghz processor, having 512MBramI am using SQL server 2000 evaluation version.Event log is giving following error messages:1. The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannotbe found. The local computer may not have the necessary registry informationor message DLL files to display messages from a remote computer. Thefollowing information is part of the event: Error: 17822, Severity: 16,State: 1Could not load Net-Library 'SSNETLIB'..2. The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannotbe found. The local computer may not have the necessary registry informationor message DLL files to display messages from a remote computer. Thefollowing information is part of the event: Error: 17059, Severity: 18,State: 0Operating system error 0: The operation completed successfully...Do any one know how to fix this problem ?Thanks a lot,Dinesh
I am working with a client that after every reboot of there SQL 2000 DB server, they experience slow response time for a couple of hours. The server has 12 GB RAM and a Dual 3.8 processor. It is believed that the slow response is due to as queries run after the reboot, they are re-building information in memory and after the memory is built up, it goes back to the normal performance utilizing the memory for speed. Is this an accurate assumption or is there something else to be looking at after the server is rebooted?
Hi,I am facing one more problem. As soon as i started the sql server firsttime, my windows 2000 machine reboots and i was not able to start sql serveragain. I had to uninstall and install again and this process repeats. Doanyone has idea about what is happening ??Thanks a lot,Dinesh
Hi, I'm just looking into installing a second instance of SQL 2005 with SP2 on a W2K3 R2 machine that already contains a default instance, if i do so is the order of installation 1) Install named instance 2) Apply SP2 to the newly installed named instance
If i do this, will either of these require a system reboot ? The default instance should remain operational if no reboot required as the service pack is for a specific instance in this case ?
This is sort of a DR issue since it involves reboots and power failures.
We have MSSQL 2005 SP1 and unpatched systems, but in particular we have this problem still with the SP1 server (in addition to the unpatched systems). Running on Win 2k3.
It seems that upon reboot, our stored SSIS jobs lose their pemissions and/or ownership for execution. It doesn't happen at every reboot or power failure, but seemingly more than half the time.
Here is the error reported: The job failed. The owner () of job Job_1 does not have server access.
But checking the properties of the job and it shows an valid owner in the Owner field. I believe we are using Active Directory for authentication and the user listed is valid in that respect.
We have "fixed" this problem before by removing the job completely and re-installing it, with the same owner. No issues until the next system outage.
Has anyone else seen this before? Is there a fix or is this some kind of bug?
-----Original Message----- From: Driggers, John To: 'SQL Discussions' Sent: 10/27/99 9:10 AM Subject: FW: Weekly server hang
I also see the one below prior to another crash....going through tech net now...but not seeing anything that reflects the messages below. The results from searching on "Exception_Access_Violation" I'm not sure apply in my case...also looks like I have at least two causes of crashes (how can one interpret the below statements???)
Thanks, John --------------------
99/10/24 10:38:00.06 spid10 EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump 99/10/24 10:38:00.06 spid10 Initializing symptom dump and stack dump facilities 99/10/24 10:38:02.61 spid10 ***BEGIN STACK TRACE*** 99/10/24 10:38:02.61 spid10 0x00404CD9 in SQLSERVR.EXE, rm_ods_handler() + 0x0329 99/10/24 10:38:02.64 spid10 0x00405571 in SQLSERVR.EXE, st_do_enlist() + 0x00C1 99/10/24 10:38:02.64 spid10 0x004071CA in SQLSERVR.EXE, CDTCState::init() + 0x033A 99/10/24 10:38:02.65 spid10 0x005A70A3 in SQLSERVR.EXE, lddb_fixdbosuid() + 0x0423 99/10/24 10:38:02.68 spid10 0x005A6CC2 in SQLSERVR.EXE, lddb_fixdbosuid() + 0x0042 99/10/24 10:38:02.68 spid10 0x005963CB in SQLSERVR.EXE, textalloc() + 0x04CB 99/10/24 10:38:02.71 spid10 0x00463F4B in SQLSERVR.EXE, agghaving() + 0x004B 99/10/24 10:38:02.71 spid10 0x00409829 in SQLSERVR.EXE, opencheck() + 0x0089 99/10/24 10:38:02.71 spid10 0x00427B09 in SQLSERVR.EXE, tbswritecheck() + 0x0969 99/10/24 10:38:02.71 spid10 0x00250FED in opends60.dll 99/10/24 10:38:02.71 spid10 0x0025055B in opends60.dll 99/10/24 10:38:02.71 spid10 0x002414D1 in opends60.dll 99/10/24 10:38:02.71 spid10 0x00241384 in opends60.dll 99/10/24 10:38:02.71 spid10 0x10219D84 in MSVCRT40.dll 99/10/24 10:38:02.71 spid10 0x77F04F3E in KERNEL32.dll 99/10/24 10:38:02.71 spid10 ***END STACK TRACE***
Cindy, nothing in the NT logs but found this in the SQL logs:
99/10/25 09:25:15.45 spid71 EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump 99/10/25 09:25:15.45 spid71 Initializing symptom dump and stack dump facilities 99/10/25 09:25:20.45 spid71 ***BEGIN STACK TRACE*** 99/10/25 09:25:20.46 spid71 0x00404CD9 in SQLSERVR.EXE, rm_ods_handler() + 0x0329 99/10/25 09:25:20.52 spid71 0x005725C1 in SQLSERVR.EXE, stuff() + 0x0241 99/10/25 09:25:20.54 spid71 0x0056D35F in SQLSERVR.EXE, ncrid_update() + 0x057F 99/10/25 09:25:20.57 spid71 0x0051DD35 in SQLSERVR.EXE, prRESOURCE() + 0x0055 99/10/25 09:25:20.57 spid71 0x00464C65 in SQLSERVR.EXE, genbuiltin() + 0x0445 99/10/25 09:25:20.59 spid71 0x00427B09 in SQLSERVR.EXE, tbswritecheck() + 0x0969 99/10/25 09:25:20.62 spid71 0x00250FED in opends60.dll 99/10/25 09:25:20.62 spid71 0x0025055B in opends60.dll 99/10/25 09:25:20.62 spid71 0x002414D1 in opends60.dll 99/10/25 09:25:20.62 spid71 0x00241384 in opends60.dll 99/10/25 09:25:20.62 spid71 0x10219D84 in MSVCRT40.dll 99/10/25 09:25:20.62 spid71 0x77F04F3E in KERNEL32.dll 99/10/25 09:25:20.62 spid71 ***END STACK TRACE***
This proceeds my 'crashes', which it looks scary enough to do the trick!
Any idea what could be causing this exception?
Thanks, John
ps. someone else mentioned backup software - we use BackupExec and I have a sched. task that dumps one of the databases to a network drive 2x day. But these are running throughout the week...looking over the logs I really don't see a correlation...(ie. these same processes run on days that no crash occurs and successfully later in the day that the crashes do occur (some hours earlier).
-----Original Message----- From: Gross, Cindy [mailto:CindyGross@hmhs.com] Sent: Monday, October 25, 1999 2:14 PM To: SQL 6.5 Discussions Subject: RE: Weekly server hang
Did you check the SQL Server errorlog (sometimes things are written here that don't go to the event viewer) and the NT event viewer (application and system)?
You could try turning on SQL Trace to see if you can capture a "bad" query but depending on how SQL goes down it may not be captured.
If you are auditing successful logons you could take a look to see if there is any pattern in who logs in just before SQL restarts.
Any chance someone is actually stopping it on purpose? Or maybe a program that is stopping it (maybe a backup system trying to backup the device files instead of the dumps)?
Cindy Gross SQL Server MCP Texas Health Resources http://members.tripod.com/cindygross/sqlsrvr.htm
> -----Original Message----- > From:Driggers, John [SMTP:John_Driggers@spspay.com] > Sent:Monday, October 25, 1999 12:26 PM > To: SQL 6.5 Discussions > Subject: Weekly server hang > > List-Unsubscribe: <mailto:leave-mssql-13928C@ls.swynk.com> > List-Software: Lyris Server version 3.0 > List-Subscribe: <mailto:subscribe-mssql@ls.swynk.com> > List-Owner: <mailto:owner-mssql@ls.swynk.com> > X-URL: <http://www.swynk.com/sysapps/sql.asp> > X-List-Host: swynk.com discussion lists <http://www.swynk.com> > Reply-To: "SQL 6.5 Discussions" <mssql@ls.swynk.com> > X-Message-Id: <06F417B00B8CD1119BA400008322DF6D03515E78@spsgex01> > Sender: bounce-mssql-14964@ls.swynk.com > Precedence: bulk > > This one is bugging the tar out of me. Running SQL 6.5 sp5a, NT4 sp4 on > the > server. Either on the weekend or Mon. mornings (happened all 3 days this > past week) the SQL service stops on the server. This is during low usage > times. The box is a DELL 4300 dual 450 w 512 RAM (250 dedicated to SQL). > All > other services on the server are ok, except for SQL. > > I'm thinking maybe a bad query hitting the server (I've seen this happen > before) but the programmers claim there is nothing special about these > time > periods that something "unique" would be happening. After I restart the > server it may not happen until the next week (this past weekend being an > exception). I thought maybe I had a memory leak but running perf. monitor > before a crash once revealed 99+% data cache, available proced. crash, low > CPU usage, low swapping....anything else I could check? > > Maybe reinstalling the sp5a? Any suggestions on things to try would be > most > appreciated... > > Thanks, John > > > ------ > FAQ: http://www.swynk.com/faq/sql/sqlserverfaq.asp > Please post SQL Server 7.0 questions to the SQL 7 list > http://ls.swynk.com for list server signup/maint options > You are subscribed as cindygross@hmhs.com > Archives: http://www.swynk.com/sitesearch/search.asp > To unsubscribe send a blank email to leave-mssql-13928C@ls.swynk.com
------ FAQ: http://www.swynk.com/faq/sql/sqlserverfaq.asp Please post SQL Server 7.0 questions to the SQL 7 list http://ls.swynk.com for list server signup/maint options You are subscribed as John_Driggers@spspay.com Archives: http://www.swynk.com/sitesearch/search.asp To unsubscribe send a blank email to leave-mssql-13928C@ls.swynk.com
I need some help. Does anyone know how to create a select statetement that will generate a list of records that have been timestamped (datetime) on the week of the system date (getdate())? Assuming that Monday is the start of the week and Sunday is the lastday of the week.
Hi. I want to be able to calculate weekly averages of our report data. Here is a sample from our EmplyeeTrends table:
EmployeeID ReportDate TotalCases 2 1/1/2007 77 2 1/2/2007 63 3 1/1/2007 56All the report data is produced daily. However, my boss wants to be able to see weekly/monthly averages for each employee. Is this possible? Thanks so much for your help!
I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.
Hi All I am using following code for my application SELECT convert(varchar(10),acr._eventTime,120) [Date], sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning], isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK], FROM ActiveCheckIssueResult r GROUP BY convert(varchar(10),acr._eventTime,120)
it gives me Result as,
Date warning OK --------- --------- ----- 1/1/1008 1 1 2/1/2008 0 2
i want count on weekly basis Could any one help me?
What daily/weekly checks do you guys currently perform on your servers and databases?
I recently ran across with an article from SQLServerCentral that listed a couple of daily checks that I'm thinking about implementing on my environment, and some of them are: DB Missing Recent Backup - Report DB Missing Recent Log Backup - Report Drives Low on Disk Space - Report Error Log Messages Report - Report Instance Recently Restarted - Report Job Failures - Report Large Databases Log File - Report
I already have in place: Verify is SQL Agent Service is running Check Disk Space Available
Since I'm going to spend some time on this, I was wondering if there's anything else that you guys have in place or any other 'nice to have' that you guys also might have, so I don't leave anything behind...
Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday. Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
Hi: in sp_add_jobschedule @freq_type = 8 for weekly @freq_interval = 1 for Sunday.
I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak. and next Sunday's should be backup_DBTest_2003_07_27.bak. (if this way, the backup device will be created at the begining of the job running...)
this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?
Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....
I want to display the reports in weekly format suppose
today is sept 27 2006, so i know from datepart(weekday,..) its value is 4 and end of this week is sept 30 2006 and again next week will start like that....also search should be monthly...
my report looks like Weekly Report (09/27 - 10/12) Week====09/27-09/30======10/01-10/07======10/08-10/12 Sales======50===============100===============80
I am new to this forum (actually to forums in general). I apologize for the long post, but I feel that someone must have done all this before and perhaps there are better approaches, so I felt I had to explain my objectives.
I have written a Daily(date) report for all the pertinent data in our production database. We want this data to persist for two years and be easily accessible. We also want other reports to use this data as a datasource rather than the production data because the production database is periodically purged of old data that may be of interest to these reports. So I am using SSRS not only as a reporting tool but also a sort of historical database.
To persist the daily report I added a Yesterday report that includes the Daily(date) as a subreport. The Yesterday report uses yesterday's date and has no paramters so it can be set up to run as a snapshot and be stored in the history. Daily(date) is set to use the cache which expires after 7 reports. So far so good.
I have now created a Weekly(weeknumber) report that includes 7 Daily(date) subreports and a LastWeek report that is schedule to run weekly as a snapshot and is stored in the history. I am hoping that because the Daily(date) reports have already been run by the Yesterday report, their datasets will still be in the cache and this will not cause a refresh from the production database. Am I right?
Basically, I want to keep access to the production database to a minimum, not store (much) duplicate data in the Report Server database and yet still have quick and easy access to the data going back 2 years even though it has long since been purged from the production database.
I considered to use the 7 daily reports as a datasources for the weekly report so that I could aggregate the data each week and expire the daily reports (and again for months, quarters and years). However, I was unable to figure out how to use a report as a datasource.
My questions come down to these...
Will the approach I am pursuing work?
Is it the best approach?
If a report includes subreports that have already been run individually with the same parameters, will the cached subreport be used.
How do you use an existing reports as datacsources for an aggregated report? I look forward to hearing your suggestions.
I hope someone can help me with a big problem... I'm using Citrix Resource Management Services with a SQL 2000 database. Their are 15 citrix servers which are all reporting to the SQL database.
The database is expanding very quickly and is becoming slower and slower.
My question is: I want to schedule a purge of old records on a friday afternoon, like this:
I have a weekly Maintenance Plan Reindex job that has failed because of a deadlock. My question seems simple enough and I'm ashamed to say I ought to know this answer, but here goes: Does the rest of a given job continue after such failures (this one was maybe 3/4 through the log) occur?
Hi Guys, I am generating Transaction Activity report,which should get data by weekly.Report shold look like this.
W1 W2 W3 W4 W5 W6
OKC 79 38 50 76 35 47
NFL 0 0 45 43 33 28
LA
5
12
10
0
0
10
Total 79 38 95 119 68 75
Iam passing 3 parameters @startdate,@enddate,@Market. when i select one Market(OKC/NFL/LA), report generating properly, but when i passing 3 markets values(OKC,NFL,LA), iam getting wrong report,report format is not correct.I used Cross tab for generating this report.Result lam getting like this.
W37 W38 W39 W40 W41 W42 W43 W44 W45
OKC 80
OKC
38
OKC
95
OKC
119
OKC
68
OKC
75
OKC
74
OKC
70
OKC
59
OKC
OKC
LA
And i wrote query like this,
select m.Market_name as Market,'W'+datename(ww,ut.creation_date) as Week,count(ut.transaction_id) as Count from POS.DSC_TRANSACTION_STATUS_VL ts inner join POS.DSC_USER_TRANSACTION ut on ts.transaction_status=ut.transaction_status inner join POS.RETAIL_LOCATION rl on ut.rl_number=rl.rl_number inner join POS.BILLING_MARKETS bm on rl.bm_code=bm.bm_code inner join dbo.Market m on bm.market_id=m.market_id where (ut.creation_date between @startdate and @enddate) and m.market_name IN(@Market) group by m.Market_name,ut.creation_date order by m.Market_name desc
Could you please some one help me to get this correctly.
I have the following code which works and outputs the desired results, however I am attempting to figure out a way to get this output by week starting from January 5th to July 6th without having to manually change the date for each week and running again. Ideally I would like to be able to save the results to Excel separated by server by week.
Code: SELECT AVG(cast(StatValue as float))as 'CPU Utilization', StatName, ServerName as 'Server Name', from dbo.ServerStats s WHERE s.StatName IN ('MEMPTAGE-TOTAL', 'CPUPEAKP-TOTAL', 'DISKPERC-C', 'DISKPERC-F',
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....
DELETING 100 million from a table weekly SQl SERVER 2000Hi AllWe have a table in SQL SERVER 2000 which has about 250 million recordsand this will be growing by 100 million every week. At a time the tableshould contain just 13 weeks of data. when the 14th week data needs tobe loaded the first week's data has to be deleted.And this deletes 100 million every week, since the delete is taking lotof transaction log space the job is not successful.Can you please help with what are the approaches we can take to fixthis problem?Performance and transaction log are the issues we are facing. We trieddeletion in steps too but that also is taking time. What are thedifferent ways we can address this quickly.Please reply at the earliest.ThanksHarish
Requirements: We have Our Local DataBase(SQL Server) Guess eg. DBLocal We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data. This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.
Both Side we have only one table. Means Source is One Table and Destination is Table One.
Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).
can any one tell me what I need to do for achive this requirement?
please provide me link also; from there i can get enough information for my requirements.
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
