Friday, June 14, 2013

[SQL 2012] Database Tuning Advisor broken

[SQL 2012] Database Tuning Advisor broken

Database Tuning Advisor broken

Posted: 14 Jun 2013 01:59 AM PDT

Before I say anything else let me just acknowledge that DTA isn't the best or most liked tool out there, but I have been asked to use it so I did, well sort of......But it's broken - each time I use it, it churns away for anything up to an hour and then stops with an error message about it exiting unexpectedly. No other details than that! I've made sure it's not hitting a time limit in the options tab before starting it.I've run the SQL Profiler (which does work) and captured the trace results to a database table (I did also try log files but this didn't change anything) which I use as the load for the DTA to consume.The DTA starts and then some time later stops with the error either during Submitting Configuration Information (most often) or during Consuming Workload.I asked Google and found a Microsoft hotfix for 2012 SP1 ([url]http://support.microsoft.com/kb/2728419[/url]) which documents this error but even after applying this patch I still get the error.Does anyone know what's going on? Am I doing something fundementally wrong? This is a possibility as I have no previous experience of this tool, however, I have successfully used it on a 2008 R2 box so can't think that what I'm doing is flawed, but I'm happy to be corrected if that is the case.Thanks in advance

SQL Server TCP Port for a User

Posted: 14 Jun 2013 01:38 AM PDT

Is its possible to to configure a particular TCP port for an individual SQL Server Principal? I have a requirement where we require to access SQL Server via a firewall from a non trusted domain. I would like only 1 single user to be able to access SQL Server over this port and a separate TCP port which would allow other users. Thanks in advance.

Updating First Field Based on the First of Grouped Rows

Posted: 13 Jun 2013 05:47 AM PDT

I have the following table.Month EmpNum Prod intRate YTDRate1 1111 prd1 2 3.1232408142 1111 prd2 2 1.2053884623 1111 prd1 2 0.7891720694 1111 prd1 2 3.5661128845 1111 prd2 2 2.6691550991 2222 prd4 2 0.0543454622 2222 prd1 2 2.3697770673 2222 prd2 1.25 6.3458340074 2222 prd1 2 3.4960475615 2222 prd1 2 4.0542200566 2222 prd2 2 2.7600991612 3333 prd2 1.25 3.9345911853 3333 prd2 2 2.2208531364 3333 prd1 2 1.718195 3333 prd2 1.25 1.0837347476 3333 prd2 1.25 3.84453072I wrote the following query to find the first month in which YTDRate > intRate by Prod and by employee No.[code="sql"] SELECT MIN(t1.Trans_Month) as Trans_Month, t1.EmpNum, t1.Prod, t1.intRate, t1.YTDRate FROM Sales_Data t1 JOIN (SELECT Trans_Month, EmpNum, Prod, intRate, YTDRate FROM Sales_Data t2 where YTDRate > intRate GROUP BY Trans_Month, t2.EmpNum, Prod, intRate, YTDRate) t3 ON t3.EmpNum = t1.EmpNum AND t3.YTDRate = t1.YTDRate AND t3.intRate = t1.intRate AND t3.Prod = t1.Prod AND t3.Trans_Month = t1.Trans_MonthGROUP BY t1.Trans_Month, t1.EmpNum, t1.Prod, t1.YTDRate, t1.intRate[/code]Now I want to update a field Status with text "Exceeds intRate". How do I modify the queyr to do the update?Any help would be greatly appreciated.Thanks

The transaction log for database 'tempdb' is full due to 'NOTHING' (during cluster install)

Posted: 13 Jun 2013 06:10 AM PDT

I'm wondering if anyone has seen this rather peculiar error while installing SQL 2012 on a cluster before, specifically if putting tempdb on the local disks during setup.There's no serious urgency on my end, I'm literally messing around with the cluster installation on a test cluster exactly for this reason - to learn about any new good-to-know gotchas before I do the real thing. Already found a couple minor things (like the cluster machine account needs "Create Computer Object" on the pre-staged computer account's [u]OU[/u] ... not just full control on the account itself, which used to be good enough in 2008).This one has me a tad stumped though; during the "Advanced cluster completion" phase (both nodes were already prepared successfully, with no issues):[b]The transaction log for database 'tempdb' is full due to 'NOTHING'[/b]Never mind it being at least a candidate for a "best errors" list...Again this is during installation, there is no actively running instance involved. I'm installing a clustered SQL, default instance, with tempdb on the local disks. It's not the usual "log full" for a good reason; the instance isn't even fully installed yet. It's not disk space; both nodes have 20+ GB free on the path I'm putting it in. It's not a mistyped path or permissions; SQL setup clearly found it and was able to work with it, at least at some point, because the MSSQLSERVER service account (NT SERVICE\MSSQLSERVER) was added to the folder's ACL - with Full Control no less - and I didn't do it. There are no tempdb files (or anything else) in it though. There's nothing special about the folder at all; it's just an ordinary subfolder I created, C:\TEMPDB. It's not even a mounted volume (on the real cluster the intent is to put tempdb on lettered, local SSD drives).I'll keep poking at it, but meanwhile if anyone has already encountered this and knows what particular weirdness I've run into here... please let me know. I know in principle it works - I've already tested installing on a cluster a couple times before, including with tempdb on the C:\ drives of the nodes. But something strange happened here, and I'd really like to know what in case I run into it again.

Restore a database using a SAN snapshot

Posted: 13 Jun 2013 04:37 AM PDT

We are using SAN Snapshots as a quick way to recovery our SQL databases. We are taking nightly full backups using DPM, however on a 1TB database, a restore will take hours. We also take log backups every 30 minutes which are shipped off server. We are using SQL 2012 SP1.If we had to use a 2 hour old SAN snaphot to recover our data drive for something like complete logical disk corruption, how could we roll the database forward using the transaction log backups and the LDF file after the snapshot was presented to the server? Our SAN does not have the ability to mount the database in a NORECOVERY state, so the database comes online just fine, but the data is simply 2 hours old.

Upgrading MSSQL 2008 to MSSQL 2012

Posted: 13 Jun 2013 04:37 AM PDT

We are upgrading our MSSQL2008 DB server to MSSQL2012. Being new to MSSQL2012. My question is will the SSIS packages we have stored in the database be convert as well or do we have to reload them after they have been convert from VS2008 to VS2012? Thanks in advance.Kimball

Where is the Download for SQL Server Native Client 11.0 for Windows 7 (32 bit)

Posted: 30 May 2013 02:13 AM PDT

SQL Server Native Client 11.0 download for Windows 7 32 bit It is suppose to work. After searching an hour, MS claims it can be installed as part of the new SQL Server in one of the directories.Our development group doesn't have access to the servers.Where can this installation (or dll) be located? Microsoft has made this extremely difficult.So difficult that there is an exploit phishing site that pretends to download the driver, but actually installs maleware.We are also running a SQL Server 2008 on a server. Do we also need to upgrad it to this newer DLL? Would it help?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog