1. Maintenance Cleanup. 2. 'View History' Anomaly

Mar 31, 2008

Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.

I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:

- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).

All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History

with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.

Two questions from this:

1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?

2.The data displayed when I right-click a maintenance plan and choose View History shows:

date and time run
plan name
task name
duration
log type
log source

Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.

However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).

Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?

Thanks,

Ed

View 4 Replies


ADVERTISEMENT

1. Maintenance Cleanup. 2. 'View History' Anomaly

Mar 31, 2008

Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.

I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:

- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).

All three maintenace plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History

with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup.

Two questions from this:

1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a Maintenance Cleanup Task should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?

2.The data displayed when I right-click a maintenance plan and choose View History shows:

date and time run
plan name
task name
duration
log type
log source

Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.

However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).

Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?

Thanks,

Ed

View 4 Replies View Related

History Cleanup Task

Feb 11, 2008

I have set up a history cleanup task to be performed once per week, however it doesn't seem to delete the old files, what am I doing wrong?

View 8 Replies View Related

SQL Server 2008 :: Cleanup History Has Extra Output Message

Apr 16, 2015

Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015

Running Cleanuphistory Procedure On Server:

NO of records are qualifying for deletion:0

Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1

*** END OF PROCEDURE. ***

[Code] .....

View 6 Replies View Related

Maintenance Cleanup Task……

Sep 14, 2007




SQL Server 2005 Standard Edition
Windows Server 2003.

I have created an €˜Backup Database Task€™ to create the database backups and checked the option €œCreate a subdirectory for each database€?.

When I add the €œMaintenance Cleanup Task€? I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the €œCreate a subdirectory for each database€? option to create the backup files in one location.

Thank you,
Smith

View 10 Replies View Related

I Hate The Maintenance CleanUp Task :=)

May 4, 2007

I have a backup database task that runs at 4 am.

I added a maintenance CleanUp task pointing to the right folder
I checked the include first-level subfolder box, and delete files based on the age of the file at task run time

I specified "delete files older than " 1 day


But the cleanup task is not working, I have to manually go a delete the files myself.

Am I missing something? should I run the maintenance task first?
and then the backup task?

View 10 Replies View Related

SQL 2012 :: Maintenance Cleanup Task Not Cleaning Up?

Jul 8, 2015

I am running a maintenance cleanup task as shown below but despite the log saying "success" it is not successful as nothing is cleaned up.

Maintenance Cleanup Task (VPS20604)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2015-07-08T16:14:24.
Task end: 2015-07-08T16:14:24.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''C:SQLBkp'',N'''',N''2015-06-10T16:14:24''
GO

View 6 Replies View Related

Missing Option In Maintenance Cleanup Task

Mar 25, 2008

I have a problem with deleting old bak files from a Cleanup Maintenance Task. I have backups in subdirectories and I read in SQL Server 2005 Books Online that:

In contrast to earlier versions of the Maintenance Cleanup task, the SQL Server 2005 version of the task does not automatically delete files in the subdirectories of the specified directory. This constraint reduces the surface area of any attack that could exploit the functionality of the Maintenance Cleanup task to delete files maliciously. To delete the first-level subfolders, you must explicitly elect to do this by checking the Include first-level subfolders option in the Maintenance Cleanup Task dialog box.

I cannot find such an option in my Maintenance Cleanup Task dialog box. I do not think I installed any service packs yet, could this be the problem?

View 20 Replies View Related

Problem With SQL Server Maintenance Cleanup Tasks

Nov 2, 2007

I've had problems trying to get a new maintenance plan on a new server to work.

I'm running a plan that does an integrity check, reindex, shrink, backup, the a maint cleanup to get rid of backups older than 2 weeks.

I'm getting failures that say:

Error Number
-1073548784

Error Message:
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'BAK',N'2007-10-18T18:43:41',1
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


Yes, this is a post SP2 server.
Current version, with the patches I've applied looking to fix this is: 9.0.3054

Additionally, if you go in to edit a maintenance cleanup task, you cannot click on OK, or the T-SQL. Changing values within it does not help.

Any clues?


charles@at@borner.us

View 1 Replies View Related

Remove Old Backup And Tran Logs Thru Maintenance Plan Cleanup Task

Mar 27, 2007

Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2.

this is transact sql running in the back ground.
EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'"bak" & "trn"',N'2007-03-26T22:21:14',1

This DOESNT WORK.

It works if I just try to delete only one of the things ie trn or bak files.

EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'bak',N'2007-03-26T22:21:14',1

But is there a way to delete both bak and trn files using one task ?

View 5 Replies View Related

SQL 2005 Maintenance Cleanup Task Is Not Deleting Files On Remote File Share

Jan 16, 2008

I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share.
When I try and remove the old files with a clean up task I get an error and the files don't get deleted.


The version is as follows
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)

The error result is as follows,

Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file
0,N'\\EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The
system cannot find the file specified.'". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.

The maintenance plan seems to be adding extra "" though when i enter the
code directly in a query i get same error.

Query:

EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1BACKUPSABCD_BACKUPABC_DAILYABCD',N'trn',N'2008-01-13T12:52:49'

Error:

xp_delete_file() returned error 2, 'The system cannot find the file specified.'

The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task.

Trying to figure out how to get the Cleanup task to delete old files. The same happens for all file extensions and I have tried other locations with simpler file paths same error.


Regards,
Scott


View 6 Replies View Related

View Job History

Aug 19, 2000

Hi
I have a full database backup scheduled at 2AM in the morning
everyday and If I see that job status in Sql Server logs
current activity , it shows me that database has been backed
up at 2AM....But the problem is when I go to jobs and try to
do a view job history it doesn't give me the details of the
job and I didn't clear the job history..Can anyone tell me the
reason why it doesn't show me the job history.

Thanks
RAGHU

View 2 Replies View Related

View Job History Permissions For Non SA&#39;s

Mar 27, 2002

How can I allow non SA accounts access to view completed Job History of certain jobs.

Development application owners may receive notification of a Production SQL job failing and have no way to see step "details" of the failed job.

View 3 Replies View Related

Error While Trying To View Job History

May 15, 2008

I received this error while trying to view a job's history while using SSMS.

syntax error (ll DB Maint'']) (Microsoft.SqlServer.SmoEnum)

anyone see anything like this? I dropped a database yesterday that was included in the maintenance plan, but forgot to take it out of the plan itself.

View 2 Replies View Related

View Transaction Log/history

Mar 14, 2008



Hello, I browsed the internet for an answer but all I can
find is the mention of a third party tool called log explorer
that cost a grand for a single license ... no thank you,
but basically what i want to do is be able to open the .ldf file
that is created to log transactions when you first create a database.
Or if there is another method to view transaction logs then please mention it.
I tried DBCC Log('database') which did not provide me with much information
which I can use.


Thanks in advance,
Sharp_At_C

View 3 Replies View Related

Duration Coloum In The View Job History

Sep 25, 2007

Hello,

I have a critical job.
When i take the view job history of that job here it has a colum named as 'duration'. It gives the exact duration of each step it has taken in its last run.
But i have the same job in test environment.
But when i take take the history here i dont see any duration column. how can i add that column.

Thank you
ichayan

View 2 Replies View Related

Nothing Written In View History Table

Mar 25, 2008



Hi guys,

acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...

Can any one tell's me about this...

View 3 Replies View Related

SQL 2012 :: View Server Agent Job History ONLY

Jul 6, 2015

I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.

how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?

View 1 Replies View Related

SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 Replies View Related

Database Not In View In New Maintenance Plan SQL 7 (options)

Nov 23, 2006

I recently migrated my database to SQL2005 from SQL7.  It was simply a restore from a backup and was cake.

But now when I go to run a new maintenance plan I do not see my database.

I found that the database options are set to SQL7.  If I change it to SQL 2005 I see the database.

What impact will this have on my data?

View 5 Replies View Related

Integration Services :: Unable To Remove View From Maintenance Plan

Sep 9, 2015

We have a maintenance plan in place for updating the statistics on daily basis. Now, i would like to remove the view from maintenance plan. How can i remove that from Update statistics task?

View 8 Replies View Related

DB_NAME Anomaly

Dec 7, 2004

On my SP3 db_name(0) and db_name(1) are returning the same thing. Can anybody else try this? Beta of Yukon is doing the same thing...But the following code returns 1: select db_id(db_name(0))

View 3 Replies View Related

Anomaly With NULL

Dec 26, 2014

why these two SQL codes return same result 0?

select CASE WHEN NULL IN ('123') THEN 1 ELSE 0 END AS Test
select CASE WHEN NOT NULL IN ('123') THEN 1 ELSE 0 END AS Test

View 2 Replies View Related

Interesting Peculiarity (another Anomaly?)

Dec 8, 2004

Run the following: declare @s1 varchar(10), @s2 varchar(10) declare @t table( recordid int identity(1,1)not null,field char(1)not null) insert @t (field) select 'a' union select 'b'select @s1 = '', @s2 = ''select @s1 = @s1 + field +',' from @t order by 1select @s2 = @s2 + field +','from @t order by recordidselect[Weird]=substring(@s1, 1,(datalength(@s1)-1)),[Not]=substring(@s2, 1,(datalength(@s2)-1))

View 2 Replies View Related

SQL 2000 Install Anomaly

Jul 20, 2005

Hi allIn a new installation, if the data directory is stipulated as d:data, itinstalls alldata in d:dataMSSQLdata.I need to place the data in d:mssql7data to incorporate backsup from olddata backuspAny ideas ?CheersRay

View 4 Replies View Related

Unallocated Space Anomaly

Jul 20, 2005

At my current workplace, whenever I check table sizes using the'reserved' column from sysindexes, or sp_spaceused, I get a total forall user tables which exceeds the physical size of the database.Running sp_spaceused with no parameteres, I get a NEGATIVE value forunallocated space in the database, but only see this if I return theresults in GRID format in QA (text format gives less output):DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB-4874.80 MBReserved Data index_size unused16602800 KB7013752 KB2381904 KB7207144 KBIf I re-run sp_spaceused with the @updateusage='TRUE' option, thisoutput gets corrected to:DatabaseName DatabaseSize Unallocated Spacexxxxx_xxxxx11502.38 MB2773.76 MBReserved Data index_size unused8770680 KB6928168 KB1808096 KB34416 KBwhich shows a substantial difference in the Reserved/Data/Index/Unusedsizes.This happens every day - any ideas about:1) Why this might be happening on such a large scale, and2) Is it conceivable that these discrepancies in space allocationinformation could be causing performance problems? I can imagine thatif the database is trying to locate free pages on extents yet itsinternal view of these doesn't match reality then this could impededata insertion.Offers anybody?

View 2 Replies View Related

SQL 2005 SSIS Anomaly

Jun 4, 2007

I've sure I have a problem that hasn't been addressed on the forum exactly. I have a SQL 2000 database that the SSIS designer sees, meaning it is a member of the 'Server Explorer' group. I am trying to update my SQL 2005 database based on values from a SQL 2000 database table using an OLE DB Command data flow transformation. I use a SELECT query in the OLE DB Source control to get the required columns. I use an UPDATE statement to identify where to place the columns retrieved from the source. I get an error that tells me that the update statement fails because it can't identify the SQL 2000 database I am trying to use. The highlighted part of the error is something I haven't seen in the similar posts I've read. And, it's ironic because the server is actually 'linked'.

Error 1
Validation error. Import Moving Averages and Open Interest Data: OLE DB Command [3411]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not find server 'SIR-Research' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.". Prices_MA_OI.dtsx 0 0

Here is the SELECT statement:
SELECT
Equity_Moving_Averages.m_d_y,
Equity_Moving_Averages.Ticker_symbol,
Equity_Moving_Averages.MA_10, Equity_Moving_Averages.MA_20, Equity_Moving_Averages.MA_30, Equity_Moving_Averages.MA_40,
Equity_Moving_Averages.MA_50, Equity_Moving_Averages.MA_80, Equity_Moving_Averages.MA_100, Equity_Moving_Averages.MA_160,
Equity_Moving_Averages.MA_200, OI_Data.OI_Call, OI_Data.OI_Call_Pricewgt, OI_Data.OI_Put, OI_Data.OI_Put_Pricewgt, OI_Data.PCRatio,
OI_Data.pcratio_pricewgt, OI_Data.PctRank365, OI_Data.PC_Diff
FROM Equity_Moving_Averages INNER JOIN
OI_Data ON Equity_Moving_Averages.m_d_y = OI_Data.m_d_y AND Equity_Moving_Averages.Ticker_symbol = OI_Data.Ticker_Symbol
--

Here is the UPDATE statement:
UPDATE [dbo].[Equity_Price_DIM]
SET [10day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_10
, [100day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_100
, [160day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_160
, [20day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_20
, [200day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_200
, [30day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_30
, [40day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_40
, [50day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_50
, [80day_moving_average] = [SIR-Research].internet.dbo.Equity_Moving_Averages.MA_80
, open_interest_call = [SIR-Research].internet.dbo.OI_Data.OI_Call
, oi_call_priceweight = [SIR-Research].internet.dbo.OI_Data.OI_Call_Pricewgt
, open_interest_put = [SIR-Research].internet.dbo.OI_Data.OI_Put
, oi_put_priceweight = [SIR-Research].internet.dbo.OI_Data.OI_Put_Pricewgt
, oi_put_call_difference = [SIR-Research].internet.dbo.OI_Data.PC_Diff
, oi_put_call_ratio = [SIR-Research].internet.dbo.OI_Data.PCRatio
, oi_pcratio_priceweight = [SIR-Research].internet.dbo.OI_Data.pcratio_pricewgt
, oi_percent_rank365 = [SIR-Research].internet.dbo.OI_Data.PctRank365
FROM [dbo].[Equity_Price_DIM] INNER JOIN [SIR-Research].internet.dbo.Equity_Moving_Averages
ON [dbo].[Equity_Price_DIM].prices_market_date = [SIR-Research].internet.dbo.Equity_Moving_Averages.m_d_y
AND [dbo].[Equity_Price_DIM].prices_ticker_symbol = [SIR-Research].internet.dbo.Equity_Moving_Averages.Ticker_symbol
LEFT OUTER JOIN [SIR-Research].internet.dbo.OI_Data
ON [dbo].[Equity_Price_DIM].prices_market_date = [SIR-Research].internet.dbo.OI_Data.m_d_y
AND [dbo].[Equity_Price_DIM].prices_ticker_symbol = [SIR-Research].internet.dbo.OI_Data.Ticker_Symbol
--

Any help will be greatly appreciated.

Dave W.

View 3 Replies View Related

Clustering For Anomaly Detection

Mar 14, 2008

Hi,

I have been trying to use the Clustering algorithm for Anomaly Detection, but it has been quite difficult to get correct results for PredictCaseLikelihood.
Using a very large number of clusters helped, but there are other problems.
The algorithm does not take into account all the columns in my nested table, but only uses Exists/Not Exists for the nested table key column. In my case the nested table contains also a column with a count of key attribute.

I am about to abandon the Sql Data Mining.

Does anyone have more insights into tuning the Clustering algorithm for anomaly detection?

Thanks

View 1 Replies View Related

Real Time Anomaly Detection

Jan 20, 2008



Hi,
I'm would like know if the analysis service data mining enables to detect anomalies from "normal" behavior/patterns of data (1), and alert about such anomalies when detected (2). both above sql server relational DB (3).

Thanks,
Eyal

View 5 Replies View Related

Dateadd Date Calculation Anomaly

Apr 3, 2008

I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month
I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).

--dFrom is Jan 01 2008 00:00:00
select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))


when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008.
Why is this?
Only starts to behave when I set the number of seconds I am adding from -1 to -31.

Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).


Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.

View 8 Replies View Related

Strange Date/time Anomaly, Or Am I Just Stoopid?

Aug 30, 2005

To set up the problem, paste this into QA:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[WorkOTRate]GOCREATE TABLE [dbo].[WorkOTRate] ([TimeFrom] [smalldatetime] NOT NULL ,[TimeTo] [smalldatetime] NOT NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOINSERT INTO dbo.WorkOTRate (TimeFrom, TimeTo, RateMultiplier)VALUES ('18:00:00', '23:59:59', 1.2)SELECT TimeFrom, TimeTo, RateMultiplier FROM dbo.WorkOTRateThis gives the following result:1900-01-01 18:00:001900-01-02 00:00:001.2So, it's storing the time 23:59:59 as midnight. That's odd.(NOTE: If you rescript the table using datetime instead ofsmalldatetime types, the data are stored correctly.)It gets worse (or better, if you like perversity).If I go to Enterprise Manager, right-click on WorkOTRate and select"Open Table" -> "Return All Rows" I get:01/01/1900 18:00:0002/01/19001.2So, I bite the bullet and change the two column types to datetime,clear out the old data and run the INSERT again. The data looks betternow.Go back to the view in EM.If I put the cursor in a new row, and type into the TimeFrom column18:30:00 and the TimeTo column 19:30:00 and the RateMultiplier column1.3, and refresh the data by pressing the red shriek !, I get this:01/01/1900 18:00:0001/01/1900 23:59:591.218:30:0019:30:001.3If I re-run the SELECT from the QA, I get this:1900-01-01 18:00:00.0001900-01-01 23:59:59.0001.21899-12-30 18:30:00.0001899-12-30 19:30:00.0001.3Is it just me, or does this seem to be remarkably inconsistent?Edward

View 2 Replies View Related

Execution Time Anomaly In Stored Procedures In SQL Server 2005

Dec 3, 2006

Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users.
The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem.
The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend)
To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms.
I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get.
What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?

View 4 Replies View Related

Maintenance Plan Wizard Vs Tsql Maintenance

Aug 17, 2007

Hello,

I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.

Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved