Friday, August 16, 2013

[SQL Server 2008 issues] DBCC FREEPROCCACHE to shink TEMPDB

[SQL Server 2008 issues] DBCC FREEPROCCACHE to shink TEMPDB

DBCC FREEPROCCACHE to shink TEMPDB

Posted: 15 Aug 2013 02:09 AM PDT

Good Day AllAs you know sometimes when a query is run that fills up or grows tempdb to a large size, sometimes "something" prevents the files from shrinking.In my case I believe that something to be a query plan that is associated with the query that grew the database that needs to be flushed before the shrink will succeed.But I have been trying to find out If it's possible to find the individual plan and remove that rather than doing a full freeproccache or using flushprocindb.There is currently no guarantee that removing the plan would allow the files to shrink but I would hate to miss the opportunity to test this by removing the plan individually and having it shrink successfully ;-)I have a rough idea what the query was that caused the tempdb to grow but I would like to know if this would of happened on random server if you would be able to find the query plan to be removed.Any links, thoughts, and input to how to avoid this scenario would be appreciatedRegards

Database Properties Data and Log initial and Auto growth help!!!!

Posted: 15 Aug 2013 07:45 AM PDT

F: drive is total 100GB and now 75GB space left (only drive besides C: and it's on VM Ware don't know if that makes any different)Initial size :Datafile = 2885MBLogfile 3399MB 1. Question is What is the best number for Data and Log initial size and ALSO what is best practice on Data 'File Growth' in Megabytes and Maximum File Size should I choose Restricted file growth or Unrestricted file growth??2. Same on Log file FILE GROWTH what's good no. in Megabytes and on Maximum File Size restricted or unrestricted if restricted what's the good number?

select only some rows and then the rest

Posted: 11 Aug 2013 03:51 PM PDT

Hi Professionals.I am stuck on a way around a query and wonder if it is possible.I want to always select the first 3 rows in the exact order from my table and then the rest of the rows in now particular order. The table is created dynmaically so it will not always be the same columns in the first 3.I have tried[code]select softwaremanufacturer,productname,productversion, * from newtable[/code]but it shows those columns listed and then those columns listed again plus the rest like so[code]Microsoft Corporation Office Access 2003 11.x Microsoft Corporation Office Access 2003 11.0.SP3 (jp) 22/04/2005 30/04/2008 11.xMicrosoft Corporation Office Access 2003 11.x Microsoft Corporation Office Access 2003 NULL 22/04/2005 30/04/2008 11.x[/code]is there a way around this so it selects just the softwaremanufacturer,productname,productversion oncehope this makes sensethanks in advance

PowerShell script to monitor SQL Server error log

Posted: 15 Aug 2013 10:18 AM PDT

I'm trying to use the below power shell script to monitor SQL Server error log:Write-host "entering Monitor errorlog". C:\DBAScripts\MonitorErrorLog\dbaLib.ps1Write-host "entered dbalib"$Servers = Import-CSV "C:\DBAScripts\MonitorErrorLog\Servers_Errorlog.txt" -Header Hostname,InstanceNamewrite-host $ServersForEach ($sqlhost In $Servers){ $sqlServiceName = $sqlhost.hostName $InstanceName=$sqlhost.instanceName} #gc Servers_Errorlog.txt | % {# $sqlServiceName,$InstanceName = $_ -split "," | convertfrom-stringdata | Select-Object -ExpandProperty Values#} #[String] $sqlServiceName=""#[String] $instanceName=""write-host "$sqlServiceName"write-host "$instanceName"# Get the Database Engine service name of the SQL Server instance $instanceNameif ($instanceName -eq 'MSSQLSERVER') { $sqlServiceName='MSSQLSERVER' }else { $sqlServiceName='MSSQL$' + $instanceName }# Only if the SQL Server instance is running, the monitoring starts.if ((Get-Service $sqlServiceName).Status -eq 'Running' ) { $query= "SELECT * FROM ERRORLOG WHERE Severity >= 1" $sqlNamespace= "root\Microsoft\SqlServer\ServerEvents\$instanceName" $selections= "ComputerName","SQLInstance", "Error","Severity","TextData" Notify-WMIEvent $query $sqlNamespace $selections }Write-host "leaving Monitor errorlog"[i]But getting the below error. Please advise[b][u][/u][/b][/i]PS C:\DBAScripts\MonitorErrorLog> .\Monitor-SQLServerErrorLog.ps1entering Monitor errorlogentered dbalib@{Hostname=hostName; InstanceName=instanceName} @{Hostname=sqldba1; InstanceName=ins1} @{Hostname=; InstanceNamGet-Service : Cannot find any service with service name 'MSSQL$'.At C:\DBAScripts\MonitorErrorLog\Monitor-SQLServerErrorLog.ps1:60 char:17+ if ((Get-Service <<<< $sqlServiceName).Status -eq 'Running' ) { + CategoryInfo : ObjectNotFound: (MSSQL$:String) [Get-Service], ServiceCommandException + FullyQualifiedErrorId : NoServiceFoundForGivenName,Microsoft.PowerShell.Commands.GetServiceCommandleaving Monitor errorlogPS C:\DBAScripts\MonitorErrorLog>

Need Urgent help to convert nVarchar to Numeric in SQL

Posted: 14 Aug 2013 09:37 PM PDT

Dear friends,I have field "Monthly_Cost" in DB that is nVRACHAR - with value '2094', how to convert it into a NUMERIC value please? Please advice with an example.thanksDJ

Design question

Posted: 15 Aug 2013 08:50 AM PDT

We get data from clients every quarter and load them into our database. We need to retain 10 quarters worth of data online and then it is archived off to flat files. The process mainly looks for the current quarter and two quarters prior. So my plan was to maintain three quarters worth of data in the main table and have seven quarters in a history table. So right now, I would have 2012 Q4, 2013 Q1 and 2013 Q2 in the main table and 2011 Q1 to 2012 Q3 in the history table. Create a view that joins both the tables for anyone who might be looking for older data. But the issue comes when our client resubmits the data for a quarter older than three quarters. Then I would not only need to replace the data for that quarter but also need two prior quarters to that quarter for processing. This means that I would have to move that data into main table. Each quarter there would around 16 million rows. So moving the data between tables on the fly would be a nightmare.Any ideas?Your time and suggestions are greatly appreciated.

Error moving from test to production server

Posted: 15 Aug 2013 01:34 AM PDT

Hello,I am receiving this error type always, when i move my package from test server to production server any idea abt it and wht to do..Error 1 Error loading package1.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.ThanksShaun

Cause of concern?

Posted: 15 Aug 2013 04:44 AM PDT

Is there a cause for concern if one of the tables in SQL2008 has a VARCHAR field. And in that field, the "data" stored is HTML tags?

DBA's facing customers...how do you handle?

Posted: 15 Aug 2013 12:50 AM PDT

I think this post is relevant here and I am curious to know DBAs thoughts on facing the toughest customers. How do you manage it?This is not something technical but would like to see everyone's experiences with their toughest customers:-)

Urgent Help needed - change multiple columns width at the same time in 2008 SSR Visual Studio for a report

Posted: 15 Aug 2013 02:43 AM PDT

Dear friends,I have created a report with 200 columns in SSRS visual studio tool, now the cleint wants me to change the COLUMN WIDTH for all 200 columns to '20' but in SSRS 2008 Visual Studio , the Properties does not have a property to do this for all Columns at once instead I see a manula effort to drag the column width for each individual column one at a time .Hence would be much obliged if any expert suggestion on how to do this please?thanksDhananjay

SSIS: XML Source Defining XSD File

Posted: 15 Aug 2013 02:53 AM PDT

I'm having some trouble with the XSD generator, the file it generates doesnt really make a whole lot of sense. The output for the data source doesnt contain all the data.Here is the XML I am importing:[code="xml"]<?xml version="1.0"?><Item> <itemID>3</itemID> <systemSku readonly="true">210000000003</systemSku> <defaultCost currency="USD">0</defaultCost> <avgCost currency="USD">0</avgCost> <tax>true</tax> <archived>false</archived> <itemType>default</itemType> <description>PBJ Shirt</description> <modelYear>0</modelYear> <upc>07777777777</upc> <ean></ean> <customSku>074604</customSku> <manufacturerSku></manufacturerSku> <timeStamp>2013-08-06T21:59:26+00:00</timeStamp> <categoryID>5</categoryID> <taxClassID>0</taxClassID> <departmentID>0</departmentID> <itemMatrixID>0</itemMatrixID> <manufacturerID>0</manufacturerID> <seasonID>0</seasonID> <defaultVendorID>0</defaultVendorID> <Prices> <ItemPrice> <amount currency="USD">9</amount> <useType readonly="true">Default</useType> </ItemPrice> <ItemPrice> <amount currency="USD">8</amount> <useType readonly="true">MSRP</useType> </ItemPrice> </Prices></Item>[/code]Here is the XSD that visual studio generated:[code="xml"]<?xml version="1.0"?><xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Item"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="itemID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="systemSku"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:unsignedLong"> <xs:attribute name="readonly" type="xs:boolean" use="optional" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="defaultCost"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:unsignedByte"> <xs:attribute name="currency" type="xs:string" use="optional" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="avgCost"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:unsignedByte"> <xs:attribute name="currency" type="xs:string" use="optional" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="tax" type="xs:boolean" /> <xs:element minOccurs="0" name="archived" type="xs:boolean" /> <xs:element minOccurs="0" name="itemType" type="xs:string" /> <xs:element minOccurs="0" name="description" type="xs:string" /> <xs:element minOccurs="0" name="modelYear" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="upc" type="xs:unsignedLong" /> <xs:element minOccurs="0" name="ean" /> <xs:element minOccurs="0" name="customSku" type="xs:unsignedInt" /> <xs:element minOccurs="0" name="manufacturerSku" /> <xs:element minOccurs="0" name="timeStamp" type="xs:dateTime" /> <xs:element minOccurs="0" name="categoryID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="taxClassID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="departmentID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="itemMatrixID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="manufacturerID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="seasonID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="defaultVendorID" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="Prices"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" maxOccurs="unbounded" name="ItemPrice"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="amount"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:unsignedByte"> <xs:attribute name="currency" type="xs:string" use="optional" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="useType"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:string"> <xs:attribute name="readonly" type="xs:boolean" use="optional" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element></xs:schema>[/code]

MCTS to MCSA - 2008 or 2012?

Posted: 08 Jan 2013 12:20 AM PST

Hi all, I'm after some advice if I may?After passing my SQL Server MCTS (imp & maint) late last year, Microsoft have decided to 'retire' the next two exams I need to do (SQL Server 2008 DB Dev and SQL Server 2008 BI, Dev & Maintenance) at the end of July this year due to the release of SQL Server 2012.Now, my way of thinking says that I should continue with my original development plan and do the two certifications for SQL 2008 (that are due for retirement in July 2013) and then do the SQL Server 2012 exams (#457 & #458) to achieve my transition to a MCSA.All of the SQL DB's I work with are 2008 and 2008 R2 and my primary goal is to integrate more development work into my role as sole DBA in my company (this is the way my company would prefer it as well luckily!).I'd be interested in peoples opinions (or to hear from people who have done the transition exams) as to whether sitting the transition exams for SQL Server 2012 would actually be worth it bearing in mind that alot of people shy away from upgrading DB versions until they've been in production for at least a year to a year and a half?Cheers, M

SSIS process log

Posted: 13 Aug 2013 05:25 AM PDT

I have the following SQL that I run for DTS packages to be able to determine how long individual steps in the package took.[code="sql"]USE msdb;declare @packagename varchar(255)set @packagename = 'Month_End_Step_2_Primary_Update'select stepname, starttime, endtime, convert(char(12),(endtime - starttime), 108) as elasped_time, elapsedtimefrom msdb..sysdtssteplogwhere lineagefull = (select lineagefull from msdb..sysdtspackagelog where [name] = @packagename and logdate = (select max(logdate) from msdb..sysdtspackagelog where [name] = @packagename --and starttime < '2012-08-01 00:00:00.000' ) ) and stepname not like '%e-mail%' and stepname not like 'Success email%' and stepname not like '%Parameter%'order by stepexecutionid[/code]Here is the results I get:stepname starttime endtime elasped_time elapsedtimeAgency Cross reference SQL 7/1/2013 8:51:49.000 7/1/2013 8:58:32.000 0:06:43 403.328DTSStep_DTSExecuteSQLTask_3 7/1/2013 8:58:32.000 7/1/2013 8:59:14.000 0:00:42 41.641DTSStep_DTSExecuteSQLTask_4 7/1/2013 8:59:14.000 7/1/2013 9:00:03.000 0:00:49 48.734DTSStep_DTSActiveScriptTask_5 7/1/2013 8:59:14.000 7/1/2013 8:59:14.000 0:00:00 0.031DTSStep_DTSActiveScriptTask_6 7/1/2013 9:00:03.000 7/1/2013 9:00:03.000 0:00:00 0.031UpdateRegionCodeRegionTable 7/1/2013 9:00:03.000 7/1/2013 9:12:35.000 0:12:32 752.093DTSStep_DTSActiveScriptTask_12 7/1/2013 9:12:35.000 7/1/2013 9:12:35.000 0:00:00 0.047Balancing_by_Region 7/1/2013 9:12:35.000 7/1/2013 9:12:50.000 0:00:15 15.641My question is does anyone have something similar I could run to get the same type of results for SSIS packages?I have SQL to look at the log file created from the SSIS package, it just doesn't put it in the nice format that this is with start and stop times on one row.

Incorrect paths used when moving datafiles

Posted: 14 Aug 2013 09:23 PM PDT

Hi all,I recently had to move some datafiles from one LUN to another in order to separate.mdf and .ldf datafile types. So I executed the following:[code="vb"]alter database test modify file (name = 'test1', filename = 'F:\Tlogs\log.ldf');[/code]The problem is that I mistyped the filename (more specifically the path) and didn't spot the error.The issue of course was when I attempted to start the instance after moving the datafiles, the start failed. Naturally, I couldn't reissue the alter database command with the correct path because the instance wasn't started!In end effect, I had to create a temporary pseudo-path on the filesystem and move the datafile affected to it before I could start the instance and reisue the command to place it in the correct location. Somewhat long-winded!Has anyone else had this problem and how did you resolve it? Surely there must be an easier way!Regards,Kev

Red gate backup issues

Posted: 13 Aug 2013 08:29 AM PDT

Hi I am using sql backup 7 from Redgate and i am having an issue whereby the sql backup process 'sqbcoreservice'is consuming way too much cpu. Way to much means, right now, when the server cpu should be near 1 - 10 % (as it now not being used) it is currently at 50% cpu. When business hours start cpu will go to around 90 - 100%.Ive just ran sp_whoisactive and i have this:dd:hh:ss.... waitinfo..........................................................CPU.......... STATUS00:11:56.....(43006055ms)ASYNC_NETWORK_IO...............32,953.........RunableI have omitted some of the other info for berevity !!When i examine the sql server processes that is running its always sqbcoreservice. We have transaction log backups throughout the day every 10 mins. There are currently no backups being performed by sql backup and there won't be for another 8 hours untill business starts again.Why am i seeing sqbcoreservice as the cpu bottleneck if no backups are currently happening?I have posted a question on the RedGate web site...i am still waiting for an answer on that one.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog