My Full backups are taking longer than the usual time on sundays.
I know this has nothing to do with the SQl Server storage engine or Database engine.
i hvae checked there are no jobs ruuning at this time..and this across all the servers sharing the SAN.
How can prove that some thing else is reponsible for this Behvaior and not SQL server.
are there any counters (perfmon) or tools or some sniffers which can tell me what is causing this.
The following code is taking longer and longer to run. I am not talking about the gradualy increase in size. this job has been taking 30-40 mins normaly and in the last few days it has gone 1hr to 2 hr to 3 hr... ANy ideas why this is happening? I can not see and other jobs running at this time.
declare @filename varchar(255)
set @filename = (select top 1 physical_device_name from ****.msdb.dbo.backupset bs, ****.msdb.dbo.backupmediafamily bf where bs.media_set_id=bf.media_set_id and database_name = 'Live_PRD' and backup_start_date>getdate()-1 and type = 'D' order by backup_start_date desc)
restore database REPORTS_REP from disk=@filename with move 'LIVE_PRD_Data' to 'T:SOUTHREPORTS_REP_Data.mdf', move 'LIVE_PRD_Log' to 'U:SOUTHREPORTS_REP_Log.ldf', move 'LIVE_PRD_Log2' to 'U:SOUTHREPORTS_REP_Log2.ldf', replace, stats=2, recovery
I have SQL Server 2005 installed on my machine and I am firing following query to insert 1500 records into a simple table having just on column.
Declare @i int Set @i=0 While (@i<1500) Begin Insert into test2 values (@i) Set @i=@i+1 End
Here goes the table definition,
CREATE TABLE [dbo].[test2]( [int] NULL ) ON [PRIMARY]
Now the problem with this is that on one of my server this query is taking just 500ms to run while on my production and other test server this query is taking more than 25 seconds. Same is the problem with updates. I have checked the configurations of both the servers and found them to be the same. Also there are no indexes defined on either of the tables. I was wondering what can be the possible reason for this to happen. If any of u people has any pointers regarding this, will be really useful
I see the following error on the sql server log of one of our sql servers running sql 2000 with sp4. SQL Server has encountered 1964 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [h: empdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000534. The offset of the latest long IO is: 0x0000002b09e000
Any idea as to what might be causing this error. Appreciate any comments.
We are running a SQL Server 2008R2 64-bit database system on a Windows 2012 R2 64-bit Standard system. I have noticed in recent weeks that our differential backups periodically are taking longer than expected to complete. The usual amount of time is about one hour, but on several occasions, it has taken upwards to five hours. The nights when the job takes longer to complete are on Friday.
I did some checking online, and one possible reason for this issue is my scheduling the reindexing of the database on the morning of the Differential backup. For example, this past Friday the reindexing occurred at 1:00 AM with the Differential running at 10:00 PM that night.The article that I read suggested the reindexing, which takes several minutes, if that, to complete, should be scheduled to run just before the Full backup job.
How is it possible that a 133MB SQL7 database, the backup of the database itself takes 2 seconds, the transaction log backup takes 25 minutes??? We are doing log backup every 10 minutes, and appending.
dbase in question is only about 5GB on a 450GB partition.
at the begining of the month I run:
BACKUP LOG [objectstore] TO DISK ='D:BackupsProdackup_objectstore.BAK' WITH NOFORMAT , INIT , NAME = N'objectstore backup'
and then every 10 minutes (within working hours) for the rest of the month I run:
BACKUP LOG [objectstore] TO DISK ='D:BackupsProdackup_objectstore.BAK' WITH NOFORMAT , NOINIT , NAME = N'objectstore backup'.
The amount of data that gets backed up is the same through out the month and the loading on the server as a whole also stays constant throughout the month - NOTHING increases throughout the month that would affect this server in any way, yet at the begining of the month the backup takes 10 seconds, and at the end, it gets up to 5-6 minutes.
why?
THanks
Alastair Jones.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
We have a database with 20 gig and with huge transactions. The transaction log backup is scheduled every one hour from 3.00 AM to 9.00 PM.
We take a full backup in the disk at 9.00 PM and again a full backup in the tape at 2.00 AM
It works fine in the day from 6.00 AM and complete within seconds and the size is approx. 50 to 200 MB.
But the very first transaction log backup at 3.00 AM is running like 3 hrs and the size is approx. 11 gig whick is almost equivalent to the Full backup size. There are some dts packages that are running in the night and as usual reindex, intergrity checks are running and there no large user traffic during night. But I have no idea which the very first transaction log backup in the morning takes longer time and has this bug size. Is there any work around to fix this proble.
I am having a serious problem which I need some help with regarding our SQL Server backup.
Basically it has started to take ages (as in 48hrs +), when it should only take about 4 hrs. The database is only 380GB and up until monday our backups have not been completing. When I check the activity monitor I have seen that the 'BACKUP DATABASE' process is set to suspended with a huge wait time and the wait type is ASYNC_IO_COMPLETION.
I am not sure how to solve this, but I am going to have to!
So if anyone has any ideas please help me! If you need any othe info please let me know.
Hi, I want to take a backup of the solution created on reporting server. Do i have to take backup of individual reports or can i take backup of the whole solution. Can you please tell me how to take backup?
In order to take automated backup of all user databases below is the query. This query will eliminate use of manual backups for user databases, in order to fully automate this just create a SQL Agent job and write this query in the job and forget about taking any manual DB backups.
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:DB_BKPUP'
i buyed hosting for my site , i am using sqlserver 2000 as backend. hosting compony allow to connect to my database through queryanalyzer not from enterprise manager.hosting compony charge me for taking database backup on there server. so i want to know how can i take databse backup from remote sql server 2000 to my local sql server 2000,any tool process by which it is possible to take databse backup at my own computers sql server 2000.
I keep getting below stack dump errors whenever I try to take Full/T-Log backup.
2015-11-26 05:18:03.44 spid79    *  Exception Code   = c0000005 EXCEPTION_ACCESS_VIOLATION 2015-11-26 05:18:03.44 spid79    *  Access Violation occurred reading address 00007FFFA6CF9C60
I used debugger and got below stack trace.
0:048> kC Â 1000 Call Site sqlmin!GetObjOffsets sqlmin!PerfmonManager::AddInstance sqlmin!BackupPerfmonCounter::AddInstance
I have SQL 2005 running on OS 2003. I am trying to take backup on a network drive (NAS box).
I have logged into the OS2003 machine as ADSadministrator and the same for SQL 2005. I have given full control to ADSadministrator on the network drive of the NAS box.
When I try to take a backup, I get the following error :
I'm trying to figure out why my transaction log backup is taking up to an hour to complete. I started off with a full recovery model with a Full database back up every Sunday, differential backups every Tuesday/Thursday and log backups every 5 minutes. I would have thought that the log file backups would execute much quicker because I'm backing them up more often.
Here is my backup statement, I'm hoping I've got a wrong option that you can point out to me:
BACKUP LOG [xxxx] TO [LogFilexxxxBackups] WITH NOINIT , NOUNLOAD , NAME = N'xxxx log backup', SKIP , STATS = 10, NOFORMAT
Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do? I don't know a lot about sql server so any help or suggestions would be great
hi,if we will two dates ie 2006/01/13 ,2006/01/30 and i want to count the no of sundays with in this date and i want to find in which dates suday is comming.please give me query for this please
i want to count how many sundays and mondays are coming with in a given date range.lets say for example i want to count how many sundays and mondays were coming from 05/10/2008 to 05/31/2008. thanks in advance
Why does it take longer and longer for the same code to run very simply I have 8,0000,000 records I want to delete from a table . I have tried a few options
Option 1 a while loop which deletes 10,000 rows per loop starting from the earliest until it hits the cut of number I have set. THIS TOOK 5 HOURS
Option 2 created an SP which found the oldest 100,000 records then deleted them. If I run this SP manually it takes 30 €“ 60 secs. Which I thought was much better than above. So I put this SP in a while loop to run 80 odd times thinking the time it would take would be 80 mins a huge improvement.
But every time this SP is called it takes longer and longer (36,30,32,39,37,37,123,163,155,182€¦and so on(In seconds)).
All the sp is doing is as follows(8860000 is just to insure I don€™t delete to much). this sp is then called from in a while loop.
set @recnumber = (select top 1 recnumber from (select top 100000 recnumber from TabletodeleteFROM where recnumber < 8860000 order by recnumber asc ) TabletodeleteFROM order recnumber desc)
delete TabletodeleteFROM where recnumber < @recnumber
Hell All,To reproduce one of our cusotmer's probem, I need to make the SQL torun for more than a minutes before it returns the result set. I do nothave large amount of data in the database to simulate the dealy.Is there a way in SQL to cause the delay while returning the resultsetThanks for the help.RegardsRaj
Hello, been having issue w/ symantec backup exec 12 for the past few months trying to backup the database files on a x64 windows 2003 sp2 server. After talking to about 6 techs from symantec, I finally come across this error and am told to contact microsoft.
I had created a .udl file to test the connection between the media server and the SQL server. It tested successfully from the media -> sql. Then I created a .udl on the SQL server to test it there, however when I tried to select the OLE DB provider for microsoft SQL server, I got the following error: "Microsoft Data Link Error: Provider is no longer available.Ensure that the provider is installed properly."
If anyone knows how to fix this issue easily that would be great. Thank you in advance.
Any body please give me the details about how to use 'ALLOW_DUP_ROW' in a CREATE CLUSTERED INDEX statement.
I tried executing the below statement but it throws an error "CREATE INDEX option 'ALLOW_DUP_ROW' is no longer supported."(both in SQL Server 2000 and SQL Server 2005)
CREATE CLUSTERED INDEX index121 ON raj(j) WITH ALLOW_DUP_ROW
Hi, Is there any way to audit or record in SQL Server 2000 what queries are the ones that consume more resources in the server so I can focus and improve them?
I am having a problem running a sql2k report service script on my new sql2005 server. It seems that the methods SessionHeader() and rs.render are no longer suport. can someone help me figure out what I need to do to continue automating my report running?
Dim skipreport as string = nothing dim skip as integer = 0
dim allbranchreport as string = nothing dim allbranch as integer = 0
dim report as CatalogItem dim repname as string = nothing dim specificreport as string = nothing dim filepath as string = nothing dim errorpath as string = nothing dim basefilepath as string = nothing dim baseerrorpath as string = nothing dim errorlog as integer = 0 Dim parameters() As ParameterValue = nothing Dim paramcount as integer = 0
' Render arguments Dim result As Byte() = Nothing Dim reportPath As String = nothing 'Dim format As String = "PDF" Dim format as string = "EXCEL" Dim historyID As String = Nothing Dim devInfo as string = Nothing Dim credentials As DataSourceCredentials() = Nothing Dim showHideToggle As String = Nothing Dim encoding As String Dim mimeType As String Dim warnings As Warning() = Nothing Dim reportHistoryParameters As ParameterValue() = Nothing Dim streamIDs As String() = Nothing Dim sh As New SessionHeader() rs.SessionHeaderValue = sh Dim omitdocmap as string = "True" '**************************************
'************************************** 'Repset run Dim repset as string = "" for each repset in repsetlist specificreports = specificreportsstart if specificreports = 0 then select case repset.tolower case "all" specificreports = 0 case "brkctr" specificreports = 1 reportcount = reportset_brkctr.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_brkctr(reportloop) next case "cashmgmt" specificreports = 1 reportcount = reportset_cashmgmt.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_cashmgmt(reportloop) next case "com" specificreports = 1 reportcount = reportset_com.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_com(reportloop) next case "com_vw" specificreports = 1 reportcount = reportset_com_vw.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_com_vw(reportloop) next repset = "com" case "comcons" specificreports = 1 reportcount = reportset_comcons.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_comcons(reportloop) next case "cons" specificreports = 1 reportcount = reportset_cons.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_cons(reportloop) next case "cons_vw" specificreports = 1 reportcount = reportset_cons_vw.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_cons_vw(reportloop) next repset = "cons" case "dep" specificreports = 1 reportcount = reportset_dep.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_dep(reportloop) next case "staff" specificreports = 1 reportcount = reportset_staff.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_staff(reportloop) next omitdocmapbranch = "False" case "staff2" specificreports = 1 reportcount = reportset_staff2.getupperbound(0) redim specificreportslist(reportcount) for reportloop = 0 to reportcount specificreportslist(reportloop) = reportset_staff2(reportloop) next omitdocmapbranch = "False" case else specificreports = 1 redim specificreportslist(0) specificreportslist(0) = "Undefined" end select
if repset.tolower = "staff2" then repset = "staff" End if
'Reports List Dim reports() as CatalogItem reports = rs.ListChildren(basereportpath, False)
'Execution if datasourceok = 1 then for each report in reports
skip = 0 allbranch = allbranchrun
for each skipreport in skipreports if report.name.tolower = skipreport.tolower then skip = 1 next
if skip = 0 then if specificreports = 1 then skip = 1 for each specificreport in specificreportslist if report.name.tolower = specificreport.tolower then skip = 0 next end if end if
if skip = 0 then for each allbranchreport in allbranchreports if report.name.tolower = allbranchreport.tolower then allbranch = 1 next
Redim parameters(1) parameters(0) = New ParameterValue() parameters(0).name = "branchall" parameters(1) = New ParameterValue() parameters(1).Name = "branch"
'5 parameters reports repname = report.name if (repname.substring(0,5).tolower = "loans" or repname.substring(0,4).tolower = "locs") then paramcount = 4 if (repname.length >= 13) then if repname.substring(0,13).tolower = "loans_beacons" then paramcount = 1 end if
if paramcount = 4 then paramcount = 0 redim preserve parameters(4)
parameters(2) = New ParameterValue() parameters(2).name = "comconsall" if repset.tolower = "cons" or repset.tolower = "com" then parameters(2).value = 0 else parameters(2).value = 1
parameters(3) = New ParameterValue() parameters(3).Name = "comcons" if repset.tolower = "com" then parameters(3).value = "commercial" else parameters(3).value = "consumer"
parameters(4) = New ParameterValue() parameters(4).Name = "brokered" parameters(4).value = 0 if repset.substring(0,3).tolower = "brk" then parameters(4).value = 1 end if
'All Branches Reports if allbranch = 1 then
parameters(0).value = "1" parameters(1).value = "admin" if client = "CSCU" then parameters(1).value = "newwst" if clienttype = "vw" then parameters(1).value = "1"
Try result = rs.Render(reportPath, format, historyID, devInfo, parameters, _ credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs) sh.SessionId = rs.SessionHeaderValue.SessionId Catch e As SoapException errorlog = 1 Console.WriteLine(e.Detail.OuterXml) Catch f as Exception errorlog = 1 Console.writeline(f.message) End Try
' Create an error log If errorlog <> 0 then Try Dim stream As FileStream = File.Create(errorpath, 1024) Console.WriteLine("Errorlog created: " & errorpath) stream.Close() Catch g As Exception Console.WriteLine(g.Message) End Try End if
' Write the contents of the report to a file. If errorlog <> 1 then Try Dim stream As FileStream = File.Create(filepath, result.Length) stream.Write(result, 0, result.Length) Console.WriteLine("Result written to file: " & filepath) stream.Close()
Catch e As Exception Console.WriteLine(e.Message) End Try end if
'Other Reports else parameters(0).value = "0"
for each branch in branches parameters(1).value = branch
errorlog = 0 if branch <> "h/o" then filepath = basefilepath & branch & "" & report.name & ".xls" else filepath = basefilepath & "ho" & report.name & ".xls" end if if branch <> "h/o" then errorpath = baseerrorpath & branch & "_" & report.name & ".txt" else errorpath = baseerrorpath & "ho_" & report.name & ".txt" end if reportpath = basereportpath & "/" & report.name omitdocmap = omitdocmapbranch devinfo = "<DeviceInfo><OmitDocumentMap>" & omitdocmap & "</OmitDocumentMap></DeviceInfo>"
Try result = rs.Render(reportPath, format, historyID, devInfo, parameters, _ credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs) sh.SessionId = rs.SessionHeaderValue.SessionId Catch e As SoapException errorlog = 1 Console.WriteLine(e.Detail.OuterXml) Catch f as Exception errorlog = 1 Console.writeline(f.message) End Try
' Create an error log If errorlog <> 0 then Try Dim stream As FileStream = File.Create(errorpath, 1024) Console.WriteLine("Errorlog created: " & errorpath) stream.Close() Catch g As Exception Console.WriteLine(g.Message) End Try End if
' Write the contents of the report to a file. If errorlog <> 1 then Try Dim stream As FileStream = File.Create(filepath, result.Length) stream.Write(result, 0, result.Length) Console.WriteLine("Result written to file: " & filepath) stream.Close()
Catch e As Exception Console.WriteLine(e.Message) End Try end if
The specified script failed to compile with the following errors: J:PRA Publisher> "C:WINDOWSMicrosoft.NETFrameworkv2.0.50727vbc.exe" /t:exe /main:MainModule /utf8output /R:"System.dll" /R:"System.Xml.dll" /R:"System.Web .Services.dll" /R:"C:Program FilesMicrosoft SQL Server90Toolsinn s.exe" / out:"C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.exe" /debug- "C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.0.vb" "C:Docu ments and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb"
Microsoft (R) Visual Basic Compiler version 8.0.50727.42 for Microsoft (R) .NET Framework version 2.0.50727.42 Copyright (c) Microsoft Corporation. All rights reserved.
C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(253) : error BC30002: Type 'SessionHeader' is not defined.
Dim sh As New SessionHeader() ~~~~~~~~~~~~~ C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(254) : error BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting Services2005.ReportingService2005'.
rs.SessionHeaderValue = sh ~~~~~~~~~~~~~~~~~~~~~ C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(434) : error BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005 .ReportingService2005'.
result = rs.Render(reportPath, format, historyID, de vInfo, parameters, _ ~~~~~~~~~
C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(436) : error BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting Services2005.ReportingService2005'.
sh.SessionId = rs.SessionHeaderValue.SessionId ~~~~~~~~~~~~~~~~~~~~~ C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(496) : error BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005 .ReportingService2005'.
result = rs.Render(reportPath, format, historyID , devInfo, parameters, _ ~~~~~~~~~
C:Documents and SettingsetopLocal SettingsTemp1fw7qn9k.1.vb(498) : error BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting Services2005.ReportingService2005'.
I could use a little help here. We have a stored procedure that runs on SQL2000 and for a large dataset only takes 1-2 minutes. On SQL2005 however, it takes around 25 minutes. Any advice or insight anyone could give would be great.
Here's the stored procedure:
CREATE PROCEDURE daa_upd_relationship_balance_hist AS begin tran insert fldarts..daa_relationship_bal_hist select <-- list snipped --> from daa_relationship_bal drb, daa_user_review dur where drb.acct_no = dur.acct_no and drb.control_2 = dur.control_2 and drb.nb_gl_cost_ctr = dur.nb_gl_cost_ctr and drb.nb_dda_sav_type = dur.nb_dda_sav_type and drb.acct_no+drb.control_2+drb.nb_gl_cost_ctr+drb.nb_dda_sav_type+convert(char(10),dur.activity_date, 101) not in (select acct_no+control_2+nb_gl_cost_ctr+nb_dda_sav_type+convert(char(10), activity_date, 101) from fldarts..daa_relationship_bal_hist) if @@error = 0 commit tran else begin rollback tran print '!!!Error (daa_relationship_bal_hist) : Relationship Balance History not updated' end return GO
So we have three tables. Here's a schema for each and the indexes on them. I've omitted columns from the tables that are not utilized in this query.
daa_relationship_bal:
CREATE TABLE [daa_relationship_bal] ( [control_2] [char] (3) NOT NULL , [nb_gl_cost_ctr] [char] (7) NOT NULL , [acct_no] [char] (14) NOT NULL , [nb_dda_sav_type] [char] (3) NOT NULL )
index:
idx_upd_balance_hist nonclustered located on PRIMARY acct_no, control_2, nb_gl_cost_ctr, nb_dda_sav_type
daa_user_review:
CREATE TABLE [daa_user_review] ( [control_2] [char] (3) NOT NULL , [nb_gl_cost_ctr] [char] (7) NOT NULL , [acct_no] [char] (14) NOT NULL , [nb_dda_sav_type] [char] (1) NOT NULL , [activity_date] [datetime] NULL )
index:
PK_daa_user_review_1__37 nonclustered, unique, primary key located on INDEXES control_2, nb_gl_cost_ctr, acct_no, nb_dda_sav_type
daa_relationship_bal_hist:
CREATE TABLE [daa_relationship_bal_hist] ( [control_2] [char] (3) NOT NULL , [nb_gl_cost_ctr] [char] (7) NOT NULL , [acct_no] [char] (14) NOT NULL , [nb_dda_sav_type] [char] (3) NOT NULL , [activity_date] [datetime] NOT NULL )
index:
PK_daa_rel_bal_hist_1__37 nonclustered, unique, primary key located on PRIMARY control_2, nb_gl_cost_ctr, acct_no, nb_dda_sav_type, activity_date
Any help on this would be great. If more information is needed, please let me know.
we are running a Windows Application(.Net 2.0) against a MS SQL Server 2000 on WinXP. The application is able to succesefully connect to Database and execute "smaller stored procedure". The application also must execute a stored proc that returns 2M rows for export into a flat file. When executing the stored proc from a remote box with 512M or less RAM(slow box), the application receives the error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
If a box has 1G or RAM or more, application seems running fine.
Now, I have the application running on a slow box, watching memory usage, and it seems fine - the memory acquired by the application never exceded 39M of RAM, so I'm still not clear why connection is being closed. Also, there seem to be no mesages from MS SQL server in Event Log.
The problem is that I have to make the app run on a "slow box".
Any ideas or comments are appreciated (somebody, please, help me ;-) )
