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...
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 want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
---- Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
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
Suppose you have two (or more) tables with foreign key constraints. Myquestion is thus:Is it better to check if the fk exists before you try to perform theinsert or let SQL do it for you?On one hand, if you check yourself and the key does not exist you cangracefully handle it (maybe exit out of method with error). If you letSQL do it, the server will throw an error which cannot be suppressed.On the performance side, you doing the check will incur a slight (VERYslight) hit since SQL will ALSO check anyways.
I'm starting to collect and develop some scripts that will tell me the health and welfare of my MSSQL 2k server. I have a few for blocks, db size, who is on and what they are currently running.
I was wondering if you guys could share some of the scripts you guys use to watch the health of your servers.
On weekends I have Integrity Checks scheduled to run. Many of these fail for individual databases because users do not log off and the databases cannot be switched to single user mode.
I have checked Books-on-line and have not yet stumbled onto a TSQL command that breaks the connections.
Is there a TSQL command to do this? If not, how can these connections be broken?
I'm starting to collect and develop some scripts that will tell me the health and welfare of my MSSQL 2k server. I have a few for blocks, db size, who is on and what they are currently running. I was wondering if you guys could share some of the scripts you guys use to watch the health of your servers.
I have a few databases on this Windows 2000 Server running SQL 2000 which were detached from SQL 7.0 and attached to SQL 2000. The problem is the Maintenance Plans (Integrity Checks keep failing on SQL 2000. I 'DTS'ed a SQL 7.0 database to this SQL 2000 server and ran the Maintenance Plans on that database. Works fine only for the DTS'ed database. What am I missing ??? :confused:
Hello, I had a DB Maintenance plan, the schedule is every day, but today I found teh 'Integrity checks job is failed". What is that mean? How to check this. Thanks.
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
I have Daily User DB Integrity Checks job running daily From past 2 days i am getting below error.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515). [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'. [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast). [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have two connections in a package pointing to two different databases on the same server. I have to insert records from 'DB1' table 'Gender1' to 'DB2' table 'Gender2'. Before I do that though, I have to make sure the minimum value (of all the Gender Keys that are going to be inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the maximum value of DB2-GenderKey (which is a primary key but not an identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.
I'm trying to use a custom check constraint to insert into a table. I have created a function that checks an item against a authorization date as well and I've coupled this into the constraint. So for example, my function starts:
Code Snippet ALTER FUNCTION {function1} (@ItemID INT, @AuthDate DATETIME) RETURNS INT AS BEGIN
DECLARE @Ret INT SET @Ret = 0 IF NOT EXISTS(SELECT top 1 ItemID FROM {table1} Where b1.ItemID = @ItemID AND @AuthDate <= ISNULL(b1.expirydate, @AuthDate)) BEGIN SET @Ret = 1 END ELSE BEGIN SET @Ret = 0 END
RETURN(@Ret) END GO
Now i couple that into a check constraint for the same table:
Code Snippet ALTER TABLE {table1} ADD CONSTRAINT {contraint_name} CHECK (([function1]([ItemID], [AuthorizedDate])=(1))) GO
Now, when i insert a record into this table with an Authorized Date greater than the ItemDate it should set the @ret value to 1. This is being passed back to the constraint should set 1=1 which is true and therefore should allow an insert, however, i cannot do this. I think this is because it inserts the record and THEN does the check which makes it fail. Is there anyway to do this check before the INSERT without having to use a trigger? I dont want to use a trigger because when we're doing an insert for bulk rows, performance decreases correct?
I'm trying to simplify a SQL Stored Procedure.The query accepts an int, @ItemTypeID intI have the Query:SELECT ... FROM ItemList WHERE ItemTypeID = @ItemTypeIDor, if @ItemTypeID is 0,SELECT ... FROM ItemList Is there a way to do this query without doing:IF @ItemTypeID = 0BEGIN ...SELECT QUERY...ENDELSEBEGIN ...SELECT QUERY...END?
My SQLMaint integrity checks consistently fail when the "Repair any minor problems" option is checked. The reported reason is that the database must be in single user mode. This doesn't seem practical. Am I missing an option somewhere?
I have got a script which checks for the databases that are offline. and mails the dba when any of the database is offline.
When I schedule the job , for every hour , it gives me an blank mail only with the subject ' status of database on testsql' even though no databases are offline.
So how can I change the script , so that it mails the dba only when a database is offline even though its scheduled every hour or half an hour.
The script is:
Set NoCount on DECLARE @dbname VARCHAR(100) deCLARE @Status varchar(100) Declare @Message VARCHAR(8000) DECLARE @date varchar(100) set @date = convert(varchar(100), getdate(),109) set @Message = ''DECLARE dbname_cursor CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE' order by name OPEN dbname_cursor FETCH NEXT FROM dbname_cursor INTO @dbname, @Status WHILE @@FETCH_STATUS = 0 BEGIN select @message = @message + @@Servername + '-' + @dbname + ' - ' + @Status + Char(13)+ ‘- ‘ + @date FETCH NEXT FROM dbname_cursor INTO @dbname, @Status END CLOSE dbname_cursor DEALLOCATE dbname_cursor
print @message EXEC master.dbo.xp_smtp_sendmail @FROM = N'testsql2000@is.depaul.edu', @TO = N'dvaddi@depaul.edu', @server = N'smtp.depaul.edu', @subject = N'Status of the Database on Testsqlserver!', @type = N'text/html', @message = @message
I want to check to see if a database exists in SQL Server 2005 Express... using VB.NET (or C#)... Can not use the SQLConnection Object... because I get a Failed Login Attempt... regardless if the DB does not exist or if it is because the User Login is incorrect
Is there some way to check to see if the Database exists (is attached) to the SQL Server 2005 Express Engine?
The integrity checks job on the user databases failed over the weekend and here is the error I got from the report: Database DB_Stores: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. The following errors were found: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. ** Execution Time: 0 hrs, 0 mins, 1 secs ** [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server] Database state cannot be changed while other users are using the database 'DB1' [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed. [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed. Googled the issue and found some articles on this issue, but wanted to also run it by you. I looked at the maint plan properties and under the integrity tab of the maint plan attempt to repair any problems is checked, this is what I think: Since attempt to repair is checked, the db was trying to repair the issue and since users were logged into the system it could'nt repair the issue as the system needs to be in single user mode. I think if we uncheck the attempt to repair option, then the job would run fine. But is this the best way to do? how about the errors it was trying to repair? Do we have to fix the error's it was trying to fix later by changing the db to single user mode. PLease let me know your ideas, thanks!!
I have a VM set up for offloading DBCC checks. Specs are below. I've read through this, but I'm not seeing the performance gains by enabling the trace flags and using the physical only switch.
Is the whole drawback that I'm on SATA storage? Is there a VM configuration with the CPU I can/should change? I've been playing with MAXDOP trying to see if I can get any benefits but I'm not seeing a much.
I am using sql server 2012 with HADR (Always on with sql cluster).
We have database maintenance plans through wizard for full backup & DBCC CHECK DB. It was running successfully but it failed with the below error
Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
I can able to take the backup from query window. It is succesful. The Sql Agent has full permissions. I don't think there are any recent changes happen.
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?
-----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!
