Friday, August 23, 2013

[SQL 2012] Need help with the SSDT package conversion regarding the build script button.

[SQL 2012] Need help with the SSDT package conversion regarding the build script button.

Need help with the SSDT package conversion regarding the build script button.

Posted: 23 Aug 2013 01:18 AM PDT

I'm sure I'm just overlooking something, but after I converted my SQL Sever 2008 package in SSIS to SQL Server 2012 SSDT and go into the Script Task Editor step, I hit 'Build Script' and nothing happens. Well, the VSTA opens, but my script is not there. All I get is a shell. I am not even sure how to recreate the step either. I'm still new at this and it may be a easy fix, but I need some help.Thanks!!

Error message "BACKUP failed to complete the command BACKUP LOG" in Event Viewer

Posted: 23 Aug 2013 12:18 AM PDT

Hello,On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following [u]information messages[/u] are shown for every database:"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.""I/O was resumed on database <database name>. No user action is required.""Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."After the 3 messages the following [u]error message[/u] is shown for every database:"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."I have added a Maintenance Plan but these jobs run after 02:00:00 at night.Can somebody give me a clue how I can get rid of the error messages?Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?Thanks in advance.

files ndf

Posted: 22 Aug 2013 07:04 PM PDT

Hello.I need several files ndf because the size DB is more or less 600 GB.For example, with the historic data, what is the best, several files with fewer GB or one or two big files ?500 users work with DB.Thanks.

Unexplained Performance Observation

Posted: 22 Aug 2013 07:16 AM PDT

This is occurring on SQL 2012 but I do not think it is specific to 2012. I have also observed this on our 2005 servers prior to our upgrade to 2012 upgrade in June.I have a highly active session database that manages application session. I know it is not a good practice to manage application sessions in SQL Server but for now lets ignore that.Basically there is one extremely active table, here is the definition...[code="sql"]CREATE TABLE [dbo].[Session]( [SessionID] [varchar](100) NOT NULL, [CreateDate] [datetime] NOT NULL, [ExpireDate] [datetime] NOT NULL, [LockDate] [datetime] NULL, [LockID] [int] NOT NULL, [Timeout] [int] NULL, [Locked] [tinyint] NULL, [SessionItems] [varchar](max) NULL, [Flags] [int] NULL, [MachineName] [varchar](300) NULL,PRIMARY KEY CLUSTERED ( [SessionID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO[/code]There is one non-clustered index on ExpireDateI have two procedures that perform massive amounts of updates to this table (each over 6 million times per day).One update simply locks a session rows before returning information back to the caller (UpdateA):[code="sql"]UPDATE Session SET Locked = 1, LockDate = @Now WHERE SessionID = @SessionID AND Locked = 0[/code]The other physically updates session info etc (UpdateB)...[code="sql"]UPDATE Session SET ExpireDate = DATEADD(mi, @TimeoutMinutes, @Now), SessionItems = @SessionItems, Locked = 0, MachineName = @MachineName WHERE SessionID = @SessionID AND LockID = @LockID[/code]Both updates are executed about the same number of times per day.Judging by the nature of the updates, I would predict with relative confidence, that UpdateB would be the more expensive update given that it is performing an EXPENSIVE update by updating a varchar MAX, a Varchar(300) and updating a non-clustered index column.UpdateA is only updating two fields which should result in an in-place update.We have Confio Ignite installed on our server and we have been observing the exact opposite.The execution plans do not look bad yet we are seeing a DRASTIC difference in WriteLog waits where updateA is much worse than updateB.Can anyone provide a logical explanation for this?

AlwaysOn for High Number of Databases?

Posted: 22 Aug 2013 06:20 AM PDT

Hi All,Is it feasible to setup Availability Group(s) for 8,000+ databases, assuming high-end HW and huge network bandwidth are in place? The number of transactions per day is high but not very high, and only ~%30 of the databases are being accessed daily base.Thanks,K.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog